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
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ...
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ...
When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to ...
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.