db2 select statement from a table name where the table name is a variable [closed]

Question

I need to select all the table row count from a db2 database.

I have a query to select all the schema and table name:

select rtrim(tabschema)||'.'||rtrim(tabname) as tableName from syscat.tables where tabschema = 'COM' order by tabname;

This query give me list of the table name of the database.

I would like to do something like:

select count(*) from tableFromTheFirstQuery

Where the tableFromThePreviousQuery is subsitute by the tableName of my first query.

I can not do like

select count(*) from (select rtrim(tabschema)||'.'||rtrim(tabname) as tableName from syscat.tables where tabschema = 'COM' order by tabname);

I will only get result of count of my first query.

I am not sure the way is exist or not. Basically I need to save all the table row counts in a text file before doing DR activity.

Kindly advise

Answer 1

You need to do a loop over the tables. From a sh in Linux you can do something like:

db2 connect to db
for t in $(db2 -x "select rtrim(tabschema)||'.'||rtrim(tabname) from syscat,tables where tabschema = 'COM' order by tabname"); do
    db2 "select '$t', count(1) from $t"
done

-x removes headers and other info.

Answer 2

You can use a compound statement like this

CREATE TABLE COUNT_ROWS (
    TABSCHEMA  VARCHAR(128) NOT NULL
,   TABNAME    VARCHAR(128) NOT NULL
,   ROW_COUNT   BIGINT
)
@
BEGIN
    FOR C AS cur CURSOR WITH HOLD FOR
        SELECT 'INSERT INTO COUNT_ROWS SELECT ''' || TABSCHEMA || ''',''' || TABNAME || ''', COUNT(*) FROM '
            || '"' ||  TABSCHEMA || '"."' || TABNAME || '"' AS S
        FROM SYSCAT.TABLES
        WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%'
        WITH UR
    DO
          EXECUTE IMMEDIATE C.S;
          COMMIT;
    END FOR;
END
@
SELECT * FROM COUNT_ROWS
@

Note that you will need to use @ as your statement terminator to get the above to run