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

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.