Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2011
    Posts
    27

    Lightbulb Unanswered: Reorg table architecture view....

    Say we have TBS - TBS01 which has 10 Table - T1 to T10

    Say the TBS01 is sized 80GB
    T5 is occupying 50 GB out of it.

    Say if I drop the dropped the table T5 and created in TBS02.

    Now if I run reorg on all T1 - T9 tables in TBS01. Will that 40 GB will come down ?

    I can't use below two commands since this is Pure Scale....
    Alter table lower high watermark and
    alter table reduce max

  2. #2
    Join Date
    Oct 2011
    Posts
    27
    I did the research and found something,

    The used pages would not come down. The best thing is drop n recreate the table again on the same tablespace to use the unused pages.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Have you tried to run the following command?
    Code:
    alter tablespace tbs01 reduce
    Regards,
    Mark.

  4. #4
    Join Date
    Oct 2011
    Posts
    27
    Have you ever used this command in Pure Scale? I'll surely try this command will get back to you

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by ilugopal View Post
    Have you ever used this command in Pure Scale? I'll surely try this command will get back to you
    Yes, I have.
    Regards,
    Mark.

  6. #6
    Join Date
    Oct 2011
    Posts
    27
    The tablespace had 4 tables and I dropped all 4 tables. The before and after tablespace details are the same and its given below, No change and I didn't reclaimed any space on it.

    Total pages = 54720
    Useable pages = 54688
    Used pages = 54688
    Free pages = 0
    High water mark (pages) = 54688
    Page size (bytes) = 32768
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    So we can't reclaim space at all in PureScale ?

  7. #7
    Join Date
    Oct 2011
    Posts
    27
    Logs
    db2 "connect to CHECK1"
    db2 "list tablespaces show detail" > Before_drop_tbs_details.txt
    db2 "drop table TABLE_RAW_OLD"
    db2 "drop table TABLE_ENG_RES_OLD"
    db2 "drop table TABLE_NAVIG_OLD"
    db2 "drop table TABLE_CARRIER_OLD"
    db2 "alter tablespace TS_LARGE_LOB_32K reduce"
    db2 "list tablespaces show detail" > After_drop_tbs_details.txt
    db2 "commit work"
    db2 "connect reset"
    db2 "terminate"
    Database Connection Information

    Database server = DB2/LINUXX8664 10.5.4
    SQL authorization ID = DBASTAG
    Local database alias = CHECK1

    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    SQL1348W The table space could not be reduced in size.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The TERMINATE command completed successfully

  8. #8
    Join Date
    Oct 2011
    Posts
    27
    Mark - My apologies. I ran the above command on newly created TBS. The did worked however it didn't bring down the H W M. I found some free pages have came down. So something worked...
    I'll keep it posted.

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try to run against the tablespace the following command:
    db2dart /LHWM
    Or dump the HWM info with:
    db2dart /DHWM
    to understand what holds HWM.
    Regards,
    Mark.

Posting Permissions

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