Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Unanswered: System table containg number rows in DB2/AS400

    Hi -

    Basically I want to create a query that returns the
    tablename,
    number of columns in the table
    number of rows in the table

    Tablename and number of columns in the table I can find in this query -

    select char(TABLE_name, 25) TABLE_NAME,
    count(*) AS NUM_FIELDS
    from qsys2.SYScolumns
    where table_schema = 'STAGING_P1'
    group by char(TABLE_name, 25)
    order by char(TABLE_name, 25)

    however I can't seem to find any table where the number of rows for the different tables are stored.

    (have seen references to
    SELECT tabname, card FROM syscat.tables
    but there is no syscat library in our DB2/AS400 database)

    Thanks

    Regards
    Peter

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Look in the SQL Reference guide in the appendix for a description of the catalog tables. I am not familiar with DB2/400, but it is probably something like:

    SYSIBM.SYSTABLES

    Be advised the statistical information stored in the catalog for an object is only updated after you perform a runstats on that object. Not sure what the command is called in DB2/400, so check out the Command Reference manual for more information.

Posting Permissions

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