Results 1 to 3 of 3

Thread: db2 table size

  1. #1
    Join Date
    Sep 2011
    Location
    Delhi
    Posts
    24

    Unanswered: db2 table size

    Hi,

    I am using DB2 V9.7 fp 3a on linux box. I had a table in database whose size was about 365Gb and had about 80million records. Due to table big size we decided to delete old data. After deleting, the records in tables are 6 million but there is not much drop in the table size. Table size is about 325GB.
    After deleting the records, I ran reorg and runstats as well on the table.

    Command which I used to calculate table size was:
    SELECT SUBSTR(TABSCHEMA,1,20) AS TABSCHEMA, SUBSTR(TABNAME,1,20) AS TABNAME, (SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE)) AS TABLE_SIZE_IN_KBS FROM SYSIBMADM.ADMINTABINFO where tabschema='SCHEMA_NAME' and tabname='TABLE_NAME' group by tabschema,tabname"

    Can you please tell me why the size did not decreased after deleting almost 70million records or I need to do something else ? thanks in advance

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    On the SELECT above, put data, index and long/lob on separate columns and see what consumes space.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if dms tablespaces you have to resize the tablespace to reclaim free space if possible
    check alter tablespace command
    find detailed info for free pages with (deprecated command) list tablespaces show detail or the new view...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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