Add a column to a DB2/400 table with a specific ordinal position

Question

Is there an SQL command on the AS400/iSeries/System-i/whatever to add a column to a table in a specific ordinal position, or moving an existing column to a different position?

Heber

Answer 1

IBM i 7.1 now allows you to add a column in front of another.

ALTER TABLE table ADD COLUMN colname ... BEFORE othercolumn

Winthorpe

Answer 2

No. The ALTER TABLE statement will allow you add a column to a table, but, according to the documentation:

The new column is the last column of the table; that is, if initially there are n columns, the added column is column n+1.

If you'd like to change the order of columns in your table, your best bet is to:

1. Use the RENAME statement to rename the table.
2. Recreate the table, with its original name, with the columns in the order that you want.
3. Use an INSERT SELECT to populate the new table with the data from the renamed table.
4. When you're sure the data is intact, you can drop the renamed version of the table.

Gers

Agreed. If you had the DDS for the file, you could insert the field in the DDS and then use a CHGPF command. Or you could create the new table in SQL and copy the data using CPYF with FMTOPT(*MAP *DROP). – Beriberi

I have a feeling you are right but I am just hoping someone knows a super secret easy and safe way to do this... – Heber