Page 1 of 1

Query to get table record count inDB2 schema

PostPosted: Thu Jul 14, 2011 8:06 pm
by ritikasingh
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,

Re: Query to get table record count inDB2 schema

PostPosted: Thu Jul 14, 2011 11:09 pm
by NicC
In that case you need to post in the DB2 section and also specify what you have tried already.

Re: Query to get table record count inDB2 schema

PostPosted: Fri Jul 15, 2011 12:01 am
by dick scherrer
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.

Re: Query to get table record count inDB2 schema

PostPosted: Fri Jul 15, 2011 5:06 pm
by GuyC
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 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