View previous topic :: View next topic
|
Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
what about?
Code: |
SELECT NAME,CARD
FROM SYSIBM.SYSTABLES WHERE DBNAME ='INEDATAT' ; |
|
|
Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
OK. |
|
Back to top |
|
|
|