Number of rows in all tables in a DB

shanmugapriya · #1 (**permalink**) 03-02-07, 01:56

Hi ,

I want to know the number of rows in each of the tables in a database given the name of the database and schema.

Can anyone tell me the easiest way possible.

Thanks in advance.

Regards,
Shanmugapriya

guyprzytula · #2 (**permalink**) 03-02-07, 02:34

create a script like
db2 -x "select 'select count(*) from '||rtrim(creator)||'.'||name|| ' ;'
from sysibm.systables where creator=''XXXXX" > outfile
db2 -tvf outfile

shanmugapriya · #3 (**permalink**) 03-03-07, 11:58

Hi ,

Thanks for the suggestion.

The obtained output for the script was

select count(*) from <schema>.FLEET

1
-----------
100000

1 record(s) selected.

But i also would require tablename,column count and row count.

as follows ,
<tablename> <column count> <row count>
FLEET 20 100000

Any suggestions ...

Thanks,
Shanmugapriya

guyprzytula · #4 (**permalink**) 03-04-07, 02:53

this will already return tablename and count
select 'select '''||rtrim(creator)||'.'||rtrim(name)|| ''' ,count(*) from '||rtrim(creator)||'.'||rtrim(name)
from sysibm.systables where creator='DB2ADMIN'
group by creator,name;
let see for column count....
see next update

shanmugapriya · #5 (**permalink**) 03-04-07, 09:07

Say we have tables test1,test2 and test3 in schema db2admin.
The output file created would be as follows,

select db2admin.test1,count(*) from db2admin.test1;
select db2admin.test2,count(*) from db2admin.test2;
select db2admin.test3,count(*) from db2admin.test3;

How will these queries execute....it would return saying coulmn
db2admin.<tablename> not found.
Kindly elaborate.

Thanks

Peter.Vanroose · #6 (**permalink**) 03-04-07, 17:26

Quote:

Originally Posted by shanmugapriya

The output file created would be as follows,
select db2admin.test1,count(*) from db2admin.test1;
select db2admin.test2,count(*) from db2admin.test2;
select db2admin.test3,count(*) from db2admin.test3;

No, it should actually be

Code:

select 'db2admin.test1',count(*) from db2admin.test1;
select 'db2admin.test2',count(*) from db2admin.test2;
select 'db2admin.test3',count(*) from db2admin.test3;

Peter.Vanroose · #7 (**permalink**) 03-04-07, 17:34

Quote:

Originally Posted by shanmugapriya

I also would require tablename,column count and row count.

The column count can be found in the catalog.
If I'm not mistaken, it should be returned by

Code:

SELECT RTRIM(tabschema)||'.'||tabname, colcount
FROM syscat.tables
WHERE tabschema = 'XXXXX'

guyprzytula · #8 (**permalink**) 03-05-07, 02:36

that is why you have the quotes in the query
select '''|.................
2 quotes will be translated to a real quote in the output

vini_srcna · #9 (**permalink**) 03-05-07, 06:44

Try this, it worked for me in Z/OS.

One important thing to be noted is, Catalog tables wont be updated untill a RUNSTATS utility is ran against the tablespace.

You can use the below query, provided there is an up to date statistics collected on all of the tablespaces of a given database.

SELECT OWNER,NAME,PARTITION,CARD FROM SYSIBM.SYSTABSTATS
WHERE DBNAME = 'ddddd'
AND OWNER = 'xxxxxx'
ORDER BY NAME ,PARTITION

stolze · #10 (**permalink**) 03-08-07, 08:35

Exactly. The same can be done on UDB with (after RUNSTATS was executed on all tables in question):

Code:

SELECT tabschema, tabname, colcount, card
FROM syscat.systables
WHERE ...

p.s: The above queries don't account for mixed case schema/table names. If you want to use that in a production environment, you should think about that as well and use delimited schema/table names, i.e. put double quotes around the names.

Internet Services

Web Development

Digital Marketing

Consumer Tech