SQL using native AS/400 timestamp fields and SQL, qsys2/systables qsys2/sysindexes

I was struggling to find out how many tables were being generated as part of my upgrade audit scripts.  I like to report on the number of tables and indexes that are created during various stages of the JDE upgrade process, so that it’s uber repeatable…  The environment workbench creates and bunch, and the initial tasks workbench, etc etc.

The SQL below allows you to show the date / time as a long string – ‘YYYYMMSSHHMMSS’, so you can then see how many tables / indexes were created bwtween certain hours, before some hours and after others!

Indexes:

select * from qsys2.sysindexes where created_timestamp > timestamp('20110621114100' ) and table_schema = 'CRPDTA';

Tables:

select * from qsys2.systables where last_altered_timestamp > timestamp('20110614114100' ) and table_schema = 'CRPDTA';

I commonly do lots of SQL over these catalogs, as you would for oracle all_tables and all_indexes.