Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Question Unanswered: Shwing table info with row info

    We currently have DB2 v8 and are running it with SAP. Our data archiving department is requesting DB info from our database.


    ALL TABLE NAMES:
    Allocated Space of each table
    Usuable Space of each table
    Number rows

    Using the db2 command prompt we determined we have 13,284 tables and 22,200 rows but when we are using control center to just view the tables under the database and it just hangs our pc and never returns anything. Is there a report or command line process that we can run to just view all the tables with the information needed above to place in a speadsheet for review?

    Any help in regards would be greatly appreciated,

    Jason

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    assuming your statistics are uptodate:

    select rtrim(tabschema) ||'.'||rtrim(tabname) as table, card as cardinality
    from syscat.tables

    will return you tablenames and number of rows.

    actual physical tablesize can be calculated additionally:
    select rtrim(a.tabschema) ||'.'||rtrim(a.tabname) as table, a.card as cardinality, a.npages * b.pagesize
    from syscat.tables a, syscat.tablespaces b
    where a.tbspaceid = b.tbspaceid

    this result is perfect, if you have one table per tablespace or your data is really good organized.
    npages is the number of physical pages where data from a table exist, but on one page can be data from all tables in one tablespace. So a few might be counted double.
    you can also calculate the theoretical used space by using something like the total number of rows/ number of rows per page * pagesize.

    Usually databases and tablespaces ar backuped, not tables.
    Juliane

  3. #3
    Join Date
    Nov 2004
    Posts
    374

    cli

    you can define your db from a windows db2 client as odbc source
    open excel or any windows appl and select : get external data with sql
    you can query the odbc resources with regular sql and the output will be present in your exc. sheet
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  4. #4
    Join Date
    Oct 2005
    Posts
    2

    Another question

    Thanks for the replies I do have another question, I am really struggling with this data collection. I was able to configure the command center and I was able to open up the database to see the tables. It is reading 20,000+ is there a global SQL query I can run on that database if I am not using statistics atm.

    I need total pages, used pages and # of rows on each table.


    I appreciate any help that can give me assistants.


    Sincerely,

    Jason

Posting Permissions

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