Results 1 to 4 of 4

Thread: Table Count

  1. #1
    Join Date
    Sep 2003
    Posts
    19

    Unanswered: Table Count

    I am running DB2 UDB 7.2 trying to get count of rows in the entire schema! Is there any single command vs typing in 566 individual sql command to get the counts?

    Please advice!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Table Count

    I don't know of any SQL which can do this ..

    I can think of two options :

    1) Do RUNSTATS on the tables and then select tabname,card from syscat.tables

    2) Do a Unix Script

    db2 "select tabname from syscat.tables where tabschema='SCHEMA1'" | while read tname
    do
    db2 "select '$tabname',count(*) from $tabname"
    done

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Posts
    19
    Thx! That worked perfect!

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Some other possiblities:

    If you don't have up to date statistics from a runstats on all tables, the run the following command:

    db2 reorgchk update statistics on table all

    To double check that that stats are collected for all tables, run the following:

    select tabschema, tabname, type from syscat.tables
    where card = -1 and type = 'T'


    Then run this query (make sure tabschema value is all caps).

    select count(*), sum(card) from syscat.tables
    where tabschema = '?????????' and card <> -1 and type = 'T'

    The count(*) will verify the number of tables.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •