Query to get table record count inDB2 schema

IBM's flagship relational database management system

Query to get table record count inDB2 schema

Postby 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.

Posts: 39
Joined: Wed Sep 01, 2010 9:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Query to get table record count inDB2 schema

Postby 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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Global moderator
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Query to get table record count inDB2 schema

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


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.
Hope this helps,
User avatar
dick scherrer
Global moderator
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Query to get table record count inDB2 schema

Postby 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 substr(replace(replace(cast(xml2clob(xmlagg(xmlelement(NAME Y , col)))
                                   as varchar(30000))
                             ,'<Y>',' UNION ALL ')
 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
I can explain it to you, but i can not understand it for you.
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Return to DB2


  • Related topics
    Last post