Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: Unique index creation failure is not clearing disk space

    Hi,

    While trying to create unique index on a table with 80 Million and 60 Million records in it. The job failed twice with error message of disk full. after the first failure I tried truncating both the tables and checked the disk size it had the same size of 100% full. Then tried locating files greater than 1GB which yielded /opt/datafiles/db2inst1/NODE0000/DBNAME/T0000003/C0000000.LRG occupying the 90% of the disk space. Then I truncated all other tables still the size was being shown as same.

    what am I missing here?

    Thanks for your help!!

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    DB2 doesn't free a space in the DMS tablespaces automatically. You have do it explicitly.
    Try to run the command which is returning as a result of the following select statement and check the container size afterwards.
    Code:
    select 
    '-- it''s expected that the tablespace size will be reduced by '||int((t.tbsp_usable_pages-t.tbsp_used_pages)*t.tbsp_page_size/power(2, 20))||' mb afterwards'
    ||chr(10)||'alter tablespace '||t.tbsp_name||' reduce max'
    from 
      table(mon_get_tablespace(null, -1)) t
    , table(mon_get_container(null, -1)) c
    where c.container_name='/opt/datafiles/db2inst1/NODE0000/DBNAME/T0000003/C0000000.LRG'
    and c.tbsp_id=t.tbsp_id
    Last edited by mark.b; 11-08-14 at 07:58.
    Regards,
    Mark.

  3. #3
    Join Date
    Jan 2005
    Posts
    80
    Quote Originally Posted by mark.b View Post
    Hi,

    DB2 doesn't free a space in the DMS tablespaces automatically. You have do it explicitly.
    Try to run the command which is returning as a result of the following select statement and check the container size afterwards.
    Code:
    select 
    '-- it''s expected that the tablespace size will be reduced by '||int((t.tbsp_usable_pages-t.tbsp_used_pages)*t.tbsp_page_size/power(2, 20))||' mb afterwards'
    ||chr(10)||'alter tablespace '||t.tbsp_name||' reduce max'
    from 
      table(mon_get_tablespace(null, -1)) t
    , table(mon_get_container(null, -1)) c
    where c.container_name='/opt/datafiles/db2inst1/NODE0000/DBNAME/T0000003/C0000000.LRG'
    and c.tbsp_id=t.tbsp_id
    Hi Mark - Thanks for your help with this.

    I did run the query and I got the below result. I am not sure how to proceed from this point? Can you please point how to proceed from here? I am not a DBA and there is no DBA available to help me here.

    it's expected that the tablespace size will be reduced by 11685 mb afterwards
    alter tablespace TBS4K reduce max

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    As I suggested before, try to run the following command and check the container size afterwards.
    Code:
    alter tablespace TBS4K reduce max
    Regards,
    Mark.

  5. #5
    Join Date
    Jan 2005
    Posts
    80
    Quote Originally Posted by mark.b View Post
    Hi,

    As I suggested before, try to run the following command and check the container size afterwards.
    Code:
    alter tablespace TBS4K reduce max
    Thanks, Yes, I figured out but only after posting the comment(a bit slow to understand things )

    Anyway, Thank you very much for your help. it is clearing the space

Posting Permissions

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