Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How count number of rows in all the tables in a Database

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Oct 15, 2008 3:47 pm    Post subject: How count number of rows in all the tables in a Database
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    Post subject:
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    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Wed Oct 15, 2008 5:44 pm    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Wed Oct 15, 2008 6:38 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Moving UDB database to Db2 on z/OS Keith Hooley DB2 4 Wed Sep 27, 2017 12:38 am
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us