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

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
    ACCEPTED ANSWER

    Re: Unable to query SYSCAT.TABLES

    ‏2013-02-26T18:46:55Z  in response to SystemAdmin
    Try

    SELECT * FROM QSYS2.TABLES
    • PWConner
      PWConner
      42 Posts
      ACCEPTED ANSWER

      Re: Unable to query SYSCAT.TABLES

      ‏2013-02-26T18:48:31Z  in response to PWConner
      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
        ACCEPTED ANSWER

        Re: Unable to query SYSCAT.TABLES

        ‏2013-02-26T19:07:17Z  in response to PWConner
        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
          12 Posts
          ACCEPTED ANSWER

          Re: Unable to query SYSCAT.TABLES

          ‏2013-02-27T01:36:40Z  in response to SystemAdmin
          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...