IBM Mainframe Forum

by **ritikasingh** » Thu Jul 14, 2011 8:06 pm

Hi Friends,
I need query such that irrespective of running select count(*) on individual table,
I need auqery such that i get table count for all the tables in a schema..it will be great if i get a report format.

Thanks,

by **NicC** » Thu Jul 14, 2011 11:09 pm

In that case you need to post in the DB2 section and also specify what you have tried already.

by **dick scherrer** » Fri Jul 15, 2011 12:01 am

Hello,

Suggest you provide more info (i.e. show some sample "input" and what you want as the output from the query) so that someone can maybe offer a suggestion.

by **GuyC** » Fri Jul 15, 2011 5:06 pm

there is no 1 query that will do that.
You can select from sysibm.systablespacestats wich should have a fairly accurate (30 mins delay) of the row count in each tablespace.
You can select from sysibm.systables which has a rowcount at last runstats.
otherwise you will need to generate a lot of seperate selects which you can then execute and union
Following query will have as output a very long select statement that when run will give you your results (replace ? with the schema you want

Select all

select substr(replace(replace(cast(xml2clob(xmlagg(xmlelement(NAME Y , col)))
as varchar(30000))
,'<Y>',' UNION ALL ')
,'</Y>','')
,12)
from (select 'select '''
|| creator
|| ''' , '''
|| name
|| ''' , count(*) from '
|| strip(creator)
|| '.'
|| strip(name) as col
from sysibm.systables where type not in ('X','P','V') AND creator = ?
) X

IBM Mainframe Forum

Query to get table record count inDB2 schema

Query to get table record count inDB2 schema

Re: Query to get table record count inDB2 schema

Re: Query to get table record count inDB2 schema

Re: Query to get table record count inDB2 schema