Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Arrow Unanswered: list tables created on a specific date

    How can I list the tables created on a specific date? When I use...the options of 'list tables'...

    Code:
    list tables for user
    ... it shows the tables of the user only but to query by date?

    TIA

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can write an SQL query against the catalog table using the view called SYSCAT.TABLES and query by CREATE_TIME. The catalog tables are described in the SQL Reference Manual Volume 1, Appendix D.

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Originally posted by Marcus_A
    You can write an SQL query against the catalog table using the view called SYSCAT.TABLES and query by CREATE_TIME. The catalog tables are described in the SQL Reference Manual Volume 1, Appendix D.
    But, how can users who have no access to syscat.tables access this info?

  4. #4
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9

    Re: list tables created on a specific date

    you can write a stored procedure and grant non-privileged users the
    execute right on this stored procedure
    only the creator of the stored procedure need select access to the
    syscat tables

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: list tables created on a specific date

    If it is OK for the user(s) to see all SYSCAT.TABLES information, then grant SELECT Access on the view to the user(s) ...

    If the user(s) has to see only the TABNAME and CREATE_TIME, then create a view with those two columns and grant access on the view


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Re: list tables created on a specific date

    Originally posted by sathyaram_s
    If it is OK for the user(s) to see all SYSCAT.TABLES information, then grant SELECT Access on the view to the user(s) ...

    If the user(s) has to see only the TABNAME and CREATE_TIME, then create a view with those two columns and grant access on the view


    HTH

    Sathyaram
    Thanks for the input... however...

    I get the following error...

    db2 => select tabname, create_time from syscat.tables where create_time = DATE('
    2003-03-24')
    SQL0401N The data types of the operands for the operation "=" are not
    compatible. SQLSTATE=42818
    db2 =>
    ...any ideas... I'm searching for the date operators...

  7. #7
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9

    Re: list tables created on a specific date

    you can use:

    select tabname,create_time from syscat.tables where
    DATE(create_time) = DATE('2003-03-24')

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: list tables created on a specific date

    In case this matters : DATE('2003-03-24') can be just '2003-03-24'

    Cheers
    Sathyaram

    Originally posted by sandner
    you can use:

    select tabname,create_time from syscat.tables where
    DATE(create_time) = DATE('2003-03-24')
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Thank you all for the input! Solved!!

    Cheers.

  10. #10
    Join Date
    Sep 2002
    Posts
    456
    If you are on UNIX you can also use:

    db2 list tables | grep 2003-11-11

    something like that and find command if on windows to do the same.

    dollar

    Originally posted by ggnanaraj
    Thank you all for the input! Solved!!

    Cheers.

Posting Permissions

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