Extended file names in IBM DB2 for i database

If I told you that I could use an exclamation point (!) in a database file name in an IBM DB2 for i database, would you call me crazy? But I just did it on an IBM i server running V4R5.

First, let's review the standard rules for file names:

• They are from one to 10 characters long.
• They must start with a letter or one of these special characters: $, @ or #. ($, @ and # have been considered letters by IBM at least as far back as the announcement of the IBM System/360 in 1964.)
• The remaining characters after the first may also be numbers, underscore (_) and period.
• Spaces (blanks) are not allowed as part of the name.
• Names are not case sensitive and lower case letters will be converted to upper case letters.

So far, no exclamation point. What's the trick? If you include a double quote ("; one character, not two single quotes in a row) as the beginning and ending characters of your file name, you can put any characters in between, except a double quote, and still have a valid file name. You must have at least one character between the double quotes and the total number of characters, including the double quotes, is limited to 10 characters. I like to think of the whole string, including the double quotes, as the file name.

Here are the rules for extended file names:

• They are from three to 10 characters long.
• They start and end with double quotes (").
• They must include one to eight characters using any characters except for a double quote.
• Spaces (blanks) are not allowed as part of the name.
• They are case sensitive. Lower case letters will remain in lower case.

I just created two database files with names of "a!+&=^]z" and "A!+&=^]Z" (including the double quotes). Despite the letters being the same except for case, these are two separate files.

I tested this on V4R5, but it should be available on any later version.

Will you ever need to use this trick? Who knows! But it's available in case you do.

Updated Nov. 12: Corrected the extended file name minimum length from one character to three characters.

(IBM i is also known as iSeries, System i and AS/400. The database is also known as DB2/400.)