IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How count number of rows in all the tables in a Database


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Oct 15, 2008 3:47 pm
Reply with quote

Hi,

How to count number of rows for each table in a database. This database has nearly 300 tables. I need the row count for all the tables.

I know how to count for a particular table,
Code:
select count(*) from test1.employee;


For all the tables in the database, i thought of getting the data from DB2 Catalog tables. Till now this is what i have come up with,

Code:
select tab.name,tab.tsname
from sysibm.systables
where tab.type="T"
and tab.dbname="inedatat"


This query will list all the tables in the "INEDATAT" database.
I need the row count. So, i came up with this,

Code:
SELECT  COUNT ( * )                                   
  FROM  ( SELECT TAB.NAME FROM SYSIBM.SYSTABLES AS TAB
  WHERE TAB.TYPE = 'T'                                 
    AND TAB.DBNAME = 'INEDATAT' )                     
;                                                     


This returned error,
Code:
 DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<EMPTY>". SOME SYMBOLS
          THAT MIGHT BE LEGAL ARE: CORRELATION NAME                     
 DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                       
 DSNT415I SQLERRP    = DSNHSM10 SQL PROCEDURE DETECTING ERROR           
 DSNT416I SQLERRD    = 502  0  0  -1  122  0 SQL DIAGNOSTIC INFORMATION
 DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'           
          X'FFFFFFFF'  X'0000007A'  X'00000000' SQL DIAGNOSTIC         
          INFORMATION                                                   
 BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.           
RETCODE =     8                                                         


Iam using DB2 V8. It in New-Function Mode

Thank You In Advance,
Sushanth BObby
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Oct 15, 2008 4:03 pm
Reply with quote

what about?

Code:
SELECT NAME,CARD                               
FROM SYSIBM.SYSTABLES WHERE DBNAME  ='INEDATAT' ;
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Oct 15, 2008 5:04 pm
Reply with quote

Quote:

SELECT NAME,CARD
FROM SYSIBM.SYSTABLES WHERE DBNAME ='INEDATAT' ;


Will this query count number of rows for each table in a database. It will list only the tables details that exists in database INEDATAT

Quote:

Code:
SELECT COUNT ( * )
FROM ( SELECT TAB.NAME FROM SYSIBM.SYSTABLES AS TAB
WHERE TAB.TYPE = 'T'
AND TAB.DBNAME = 'INEDATAT' )
;





This also won't retrieve the count of rows for each table in INEDATAT

I guess stored procedure is a good thing for this requirement. Suggestions are welcome
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Oct 15, 2008 5:27 pm
Reply with quote

This is the definition of the CARD column in SYSIBM.SYSTABLES: CARD number of rows for a table.

so, if I'm not wrong

Code:
SELECT NAME,CARD                             
FROM SYSIBM.SYSTABLES WHERE DBNAME  ='yourdatabasehere'


should give the TableName and number of rows in the database specified.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Oct 15, 2008 5:44 pm
Reply with quote

Thank You acevedo,

For this query.

SELECT NAME,CARD
FROM SYSIBM.SYSTABLES WHERE DBNAME ='INEDATAT' ;

Quote:
Will this query count number of rows for each table in a database. It will list only the tables details that exists in database INEDATAT

I think, it only displays the count, which has been updated by the RUNSTATS utility on that tablespace.

What happens if the DB2 CATALOG TABLES are not UPdated.

Sushanth
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Oct 15, 2008 5:44 pm
Reply with quote

I mentioned like it won't count the no: of rows because on dev environment the statistics may not be gathered in that case the CARD value will be -1 instead of the row count
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Oct 15, 2008 6:38 pm
Reply with quote

OK.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Generate random number from range of ... COBOL Programming 3
Search our Forums:

Back to Top