DB2 Naming Options

Access vs. DB2 Naming Conventions

Access table, field, and other object names may be up to 64 characters in length, include any combination of letters, numbers, spaces and special characters except a period (.), an exclamation point (!), an accent grave (‘), and brackets ([ ]). They may NOT begin with leading spaces or include control characters (ASCII values 0 through 31). Access names are not case-sensitive, that is the names ‘ABC’ and ‘aBc’ are identical when referenced. Attempting to name two tables or fields with like spellings is invalid in Access.

 

DB2 object names can be defined in two ways. When enclosed in double quotation marks, DB2 names may contain any ASCII characters and are case sensitive. There are no restrictions except the maximum object length that varies depending on object type and DB2 version. Version 6.x supports table names up to a length of 128 characters and columns can be up to 30 characters long. DB2 object names that are not enclosed in quotation marks may include any combination of letters, numbers and the (@), (#), ($), (_) characters.  They must not begin with a number and all letters are case insensitive. (When commands are sent to DB2, all letters in names not enclosed in quotation marks are interpreted as upper case.

When importing DB2 tables, any names that violate Access naming rules are renamed by Scriptoria. Leading and trailing spaces are removed; invalid special characters are replaced by underscores (_) and names that cause duplicates (due to the case-sensitive issue) are renamed with a unique numeric suffix.

 

After importing a DB2 database containing column names that violate Access naming rule, you can choose to have Scriptoria convert table and or column names into valid un-quoted DB2 names. If you wish to rename existing DB2 columns, use Prototyping Mode and specify the renamed columns when prompted. Be sure the Retain original table names option is selected or Scriptoria will not be able to find the associated DB2 tables.

DB2 Naming Rules:

Use Quoted Identifiers

It is advisable to avoid using DB2 quoted names that violate Access naming rules since they may not be accessible to ODBC and other applications. For this reason, the Use Quoted Identifiers option defaults to No.

Access names are enclosed with double quotation marks when they do not conform to DB2 un-quoted naming conventions. Access fields exceeding the maximum DB2 column length are truncated.

For DB2 for OS/390, this option must be set to NO when one or more tables in the database contain LOBS. This is a bug that has been reported to and acknowledged by IBM in November of 2000. You may wish to try compiling a table containing a LOB using the “Retain original table/field names” option. (Set “CURRENT RULES=’STD’ to automatically generate auxiliary tables and indexes required by LOBS. Do not use Scriptoria for this operation.) If no errors are reported, this bug has been repaired.