Db2 – How to identify row change timestamp columns in Db2 Table

db2db2-luwdb2-zosload

I need to copy some Db2 tables programmatically (java 11 using the standard Db2 jdbc driver). The tables in question lie on a Db2 for z/OS database (v12) and on a Db2 LUW database (v11.1). The target tables exist and have all the required columns.

Some tables contain rows defined like this:

<SOME_COL_NAME> TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

In order to do the copy, I wish to use the Db2 load utility and row change timestamps need to be preserved. The syntax for this is identical for both Db2 types. When the table contains a row change timestamp, then the LOAD statement requires a ROWCHANGETIMESTAMPOVERRIDE modifier. All this has been gleaned from the IBM documentation.

The difficulty is that I'm unsure how to tell whether the table in question has a row change timestamp. For Db2 LUW it is possible to query SYSCAT.COLUMNS, since this has a a field called ROWCHANGETIMESTAMP which would be either 'Y' or 'N'. That would mean checking each column in each table, but it's doable. However, it would be nice if there was an easier way to determine whether the table has a row change timestamp.

For z/OS it isn't clear what should be checked. Is there a catalog query that would help?

I can provide more details, if necessary.

Best Answer

In Db2 for z/OS this information is in the DEFAULT column of the SYSIBM.SYSCOLUMNS catalog table. In your case the value you'll be looking for is 'E'.