Get started Bring yourself up to speed with our introductory content.

Using SQL syntax to override DB2 automatic column name generator on iSeries

If you don't want to drop and recreate a table to change the internal names in your AS/400 DB2, a special SQL syntax can solve your problem.

In production, we never drop and recreate tables. In test we do. Recently, we used the ALTER statement to remove a column in the table. The column removed seemed to have the internal name of MAX_P00001. In the production table there is still a MAX_P00002 and a MAX_P00003. In test tables, because we recreate the tables, there is a MAX_P00001 and a MAX_P00002. Since we have a common I/O program, if we restore the production version of the table into a test library we get a -901 error on the select. If we recompile the I/O program into the test library, containing the restored table, it works. Can we do something other than drop and recreate the production table to force the internal names to MAX_P00001 and 2?
The best solution in this case would be to use special SQL syntax to explicitly control the short, internal name instead of relying on DB2's column name generator. Here's an example of using the 'FOR COLUMN' clause to explicitly specify a short, internal name instead of having DB2 generate that identifier.

CREATE TABLE test1 (longlongname FOR COLUMN shortname CHAR(4))

Dig Deeper on iSeries SQL commands and statements