There are specific instances when the system generates a system table, view, index, or column name. These instances and the name generation rules are described in the following sections.
A system-column-name is generated if the system-column-name is not specified when a table or view is created and the column-name is not a valid system-column-name.
If the column-name does not contain special characters and is longer than 10 characters, a 10-character system-column-name will be generated as:
For example:
The system-column-name for LONGCOLUMNNAME would be LONGC00001
If the column name is delimited:
For example:
The system-column-name for "abc" would be ABC__00001 The system-column-name for "COL2.NAME" would be COL2_00001 The system-column-name for "C 3" would be C_3__00001 The system-column-name for "??" would be Q____00001 The system-column-name for "*column1" would be QCOLU00001
A system name will be generated if a table, view, alias, or index is created with either:
The SQL name or its corresponding system name may both be used in SQL statements to access the file once it is created. However, the SQL name is only recognized by DB2 UDB for iSeries and the system name must be used in other environments.
There are two separate methods for generating the system name:
The data area is subject to the following restrictions:
If any of the above conditions are not satisfied or any error occurs while accessing the starting value in the data area, the default name generation rules will be used as if the data area did not exist at all.
If the data area meets all of the restrictions above, the generated name will be the same as if the default name generation rules below except that after the first 5 (or 4) characters of the name, the unique number will initially contain the 5 digits specified in the data area (instead of '00001' or '0001').
For example, if the value of the data area was '?????00999':
The system name for "??" would be "__00999" The system name for "longtablename" would be "lon00999" The system name for "LONGTableName" would be LONG00999 The system name for "A b " would be "A_b00999"
If the name does not contain special characters and is longer than 10 characters, a 10-character system name will be generated as:
For example:
The system name for LONGTABLENAME would be LONGT00001
If the SQL name contains special characters, the system name is generated as:
In addition:
For example:
The system name for "??" would be "__0001" The system name for "longtablename" would be "long0001" The system name for "LONGTableName" would be LONG0001 The system name for "A b " would be "A_b0001"
SQL ensures the system name is unique by searching the cross reference file. If the name already exists in the cross reference file, the number is incremented until the name is no longer a duplicate.
If a unique name cannot be determined using the above rules, an additional character is added to the counter in the name, and the number is incremented until a unique name can be found or the range is exhausted. For example, if creating "longtablename" and names "long0001" through "long9999" already exist, the name would become "lon00001".
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.