Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2009
    Posts
    8

    Unanswered: alter tables list

    hi ALL

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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by arun-dba View Post
    hi ALL

    is it possible to get the list of tables that has altered using "ALTER TABLE" command.
    probably db2look -d db_name -e -o outfile
    and then look for ALTER in the outfile.sql
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Cougar8000 View Post
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    right, but you wont see any structural changes.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Cougar8000 View Post
    right, but you wont see any structural changes.
    you are absolutely right
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Jan 2009
    Posts
    8

    Thumbs up

    thanks Stolze.

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

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Cougar8000 View Post
    That is only for 9.5 and above. Correct?
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •