hi ALL

is it possible to get the list of tables that has altered using "ALTER TABLE" command.

You can select all tables from the DB2 catalog where the create-timestamp differs from the alter timestamp. Have a look at SYSCAT.TABLES and columns CREATE_TIME and ALTER_TIME. On DB2 z/OS, you would use SYSIBM.SYSTABLES and columns CREATEDTS and ALTEREDTS.

probably db2look -d db_name -e -o outfile
and then look for ALTER in the outfile.sql

Markham

Alter table statements are not produced with db2look. Only exception is for creation of a PK.

Once you alter table to add a col or change data type it becomes part of its ddl and future db2look attempts will show this change as part of a regular ddl.

Knut,

That is only for 9.5 and above. Correct?

i stand corrected.

though i see
ALTER TABLE "DBA "."tab_name" PCTFREE 10;
statements in db2look output.

right, but you wont see any structural changes.

you are absolutely right

thanks Stolze.

i got is from syscat.tables and column name ALTER_TIME.

No, the timestamps to track the last table alteration are available in DB2 for a long time already. Such information is needed, for example, to rebind packages in case a table has changed.

I am not seeing it in 9.1 nor in 9.1 manuals. Do I have a stolen version or it is in another place?

Looks like it alter_time was added in v9.5


From v9.1:

$ db2 "describe select * from sysibm.systables" | grep -i time
392 TIMESTAMP 26 CTIME 5 0 SYSIBM .TIMESTAMP 18
393 TIMESTAMP 26 STATS_TIME 10 0 SYSIBM .TIMESTAMP 18
393 TIMESTAMP 26 REFRESH_TIME 12 0 SYSIBM .TIMESTAMP 18
393 TIMESTAMP 26 LAST_REGEN_TIME 15 0 SYSIBM .TIMESTAMP 18
392 TIMESTAMP 26 INVALIDATE_TIME 15 0 SYSIBM .TIMESTAMP 18



From v9.5:

$ db2 "describe select * from sysibm.systables" | grep -i time
392 TIMESTAMP 26 CTIME 5 0 SYSIBM 6 TIMESTAMP 9
393 TIMESTAMP 26 STATS_TIME 10 0 SYSIBM 6 TIMESTAMP 9
393 TIMESTAMP 26 REFRESH_TIME 12 0 SYSIBM 6 TIMESTAMP 9
393 TIMESTAMP 26 LAST_REGEN_TIME 15 0 SYSIBM 6 TIMESTAMP 9
392 TIMESTAMP 26 INVALIDATE_TIME 15 0 SYSIBM 6 TIMESTAMP 9
392 TIMESTAMP 26 ALTER_TIME 10 0 SYSIBM 6 TIMESTAMP 9

I stand corrected. The ALTER_TIME attribute was indeed added in V9.5 (ftp://ftp.software.ibm.com/ps/produc.../db2mgg952.pdf). I would have thought that it came with the ALTER VIEW statement in V9.1 already.