Topic
  • 4 replies
  • Latest Post - ‏2013-02-27T01:36:40Z by stiruvee
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic Unable to query SYSCAT.TABLES

‏2013-02-26T18:02:42Z |
Hello,

I am new to the i5 world and am trying to learn how an existing database is structured for a systems integration project. I would like to know the "last used" date for each of our user data tables.

I found on-line that there is a "last used" field in SYSCAT.TABLES.

I have tried using the iSeries Navigator to run a SQL statement. Specifically:

SELECT * FROM SYSCAT.TABLES

However, I get the following error meassage:

Connected to relational database S10542ed on S10a9204 as Bmcdermott - 443634/Quser/Qzdasoinit

> select * from SYSCAT.TABLES

SQL State: 42704
Vendor Code: -204
Message: SQL0204 TABLES in SYSCAT type *FILE not found. Cause . . . . . : TABLES in SYSCAT type *FILE was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, TABLES is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

Processing ended because the highlighted statement did not complete successfully

I am however able to do other queries like "select * from sysibm.tables" and "select * from qsys2.tables" but these catalogs do not have the "last used" field I need.

Any advice? Is it a permissions issue?

Thank you!
Updated on 2013-02-27T01:36:40Z at 2013-02-27T01:36:40Z by stiruvee
  • PWConner
    PWConner
    42 Posts

    Re: Unable to query SYSCAT.TABLES

    ‏2013-02-26T18:46:55Z  
    Try

    SELECT * FROM QSYS2.TABLES
  • PWConner
    PWConner
    42 Posts

    Re: Unable to query SYSCAT.TABLES

    ‏2013-02-26T18:48:31Z  
    • PWConner
    • ‏2013-02-26T18:46:55Z
    Try

    SELECT * FROM QSYS2.TABLES
    My bad on that last comment. I see now that QSYS2.Tables does not have the field you are looking for.
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Unable to query SYSCAT.TABLES

    ‏2013-02-26T19:07:17Z  
    • PWConner
    • ‏2013-02-26T18:48:31Z
    My bad on that last comment. I see now that QSYS2.Tables does not have the field you are looking for.
    I thought I read something that said SYSCAT is not availabile on the iSeries but there is a lot of conflicting info and I'm not sure what is and isn't available.
  • stiruvee
    stiruvee
    22 Posts

    Re: Unable to query SYSCAT.TABLES

    ‏2013-02-27T01:36:40Z  
    I thought I read something that said SYSCAT is not availabile on the iSeries but there is a lot of conflicting info and I'm not sure what is and isn't available.
    Check LAST_USED_TIMESTAMP column in qsys2.SYSTABLESTAT. Link for info. on catalog tables in iseries http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalogtbls.htm
    Satya...