Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Unanswered: Number of rows in all tables in a DB

    Hi ,

    I want to know the number of rows in each of the tables in a database given the name of the database and schema.

    Can anyone tell me the easiest way possible.

    Thanks in advance.

    Regards,
    Shanmugapriya

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    rows

    create a script like
    db2 -x "select 'select count(*) from '||rtrim(creator)||'.'||name|| ' ;'
    from sysibm.systables where creator=''XXXXX" > outfile
    db2 -tvf outfile
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Posts
    11
    Hi ,

    Thanks for the suggestion.

    The obtained output for the script was

    select count(*) from <schema>.FLEET

    1
    -----------
    100000

    1 record(s) selected.

    But i also would require tablename,column count and row count.

    as follows ,
    <tablename> <column count> <row count>
    FLEET 20 100000

    Any suggestions ...

    Thanks,
    Shanmugapriya

  4. #4
    Join Date
    Jun 2006
    Posts
    471
    this will already return tablename and count
    select 'select '''||rtrim(creator)||'.'||rtrim(name)|| ''' ,count(*) from '||rtrim(creator)||'.'||rtrim(name)
    from sysibm.systables where creator='DB2ADMIN'
    group by creator,name;
    let see for column count....
    see next update
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Jan 2007
    Posts
    11
    Say we have tables test1,test2 and test3 in schema db2admin.
    The output file created would be as follows,

    select db2admin.test1,count(*) from db2admin.test1;
    select db2admin.test2,count(*) from db2admin.test2;
    select db2admin.test3,count(*) from db2admin.test3;

    How will these queries execute....it would return saying coulmn
    db2admin.<tablename> not found.
    Kindly elaborate.

    Thanks

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by shanmugapriya
    The output file created would be as follows,
    select db2admin.test1,count(*) from db2admin.test1;
    select db2admin.test2,count(*) from db2admin.test2;
    select db2admin.test3,count(*) from db2admin.test3;
    No, it should actually be
    Code:
    select 'db2admin.test1',count(*) from db2admin.test1;
    select 'db2admin.test2',count(*) from db2admin.test2;
    select 'db2admin.test3',count(*) from db2admin.test3;
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by shanmugapriya
    I also would require tablename,column count and row count.
    The column count can be found in the catalog.
    If I'm not mistaken, it should be returned by
    Code:
    SELECT RTRIM(tabschema)||'.'||tabname, colcount
    FROM syscat.tables
    WHERE tabschema = 'XXXXX'
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jun 2006
    Posts
    471
    that is why you have the quotes in the query
    select '''|.................
    2 quotes will be translated to a real quote in the output
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  9. #9
    Join Date
    May 2006
    Posts
    82
    Try this, it worked for me in Z/OS.

    One important thing to be noted is, Catalog tables wont be updated untill a RUNSTATS utility is ran against the tablespace.

    You can use the below query, provided there is an up to date statistics collected on all of the tablespaces of a given database.

    SELECT OWNER,NAME,PARTITION,CARD FROM SYSIBM.SYSTABSTATS
    WHERE DBNAME = 'ddddd'
    AND OWNER = 'xxxxxx'
    ORDER BY NAME ,PARTITION

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Exactly. The same can be done on UDB with (after RUNSTATS was executed on all tables in question):
    Code:
    SELECT tabschema, tabname, colcount, card
    FROM syscat.systables
    WHERE ...
    p.s: The above queries don't account for mixed case schema/table names. If you want to use that in a production environment, you should think about that as well and use delimited schema/table names, i.e. put double quotes around the names.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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