Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    53

    Unanswered: reorg- tablespace size increased

    db2 9.5 .05
    aix-6.1

    After Performing RE-ORG on a table,
    the tablespace size increased? How to get back that space

    I performed re-org on a table (table size16gb ) in tablespace tbspid-8
    Table space is Automated type , four containers defined on it.

    --> db2 reorg table tab16

    After reorg performed 4gb space on each container used.

    befor reorg 20 gb of free space available on each container. after reorg 16gb free space isavailable on all containers.
    I checked tablespace 8 which conatain the table tah i reorged
    The High Water Mark pages is went high.
    NOw
    HOW CAN I RECLAIM THAT SPACE?

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by chaitanya_db2adm View Post
    db2 9.5 .05
    aix-6.1

    After Performing RE-ORG on a table,
    the tablespace size increased? How to get back that space

    I performed re-org on a table (table size16gb ) in tablespace tbspid-8
    Table space is Automated type , four containers defined on it.

    --> db2 reorg table tab16

    After reorg performed 4gb space on each container used.

    befor reorg 20 gb of free space available on each container. after reorg 16gb free space isavailable on all containers.
    I checked tablespace 8 which conatain the table tah i reorged
    The High Water Mark pages is went high.
    NOw
    HOW CAN I RECLAIM THAT SPACE?
    reorg basically does a copy of your table. If you don't specify another tablespace for reorg this will happen in the same tablespace where the table resides. Pre 9.7 this will cause a raise of highwatermark. You can get info on howto lower the HWM using db2dart. It's usually a whole lot easier to handle tables than indexes (which quickly can turn into a nightmare).
    --
    Lennart

  3. #3
    Join Date
    Dec 2012
    Posts
    53
    Thanks for your reply
    I am using db2dart
    -->db2dart prd /lhwm /tsi 8 /np 100
    and lookin in to output file for suggestions.
    The problem is on that table space we have 13000 tables.
    Every time db2dart suggesting to do reorg on one table only
    Is there any quick way to do that?

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by chaitanya_db2adm View Post
    Thanks for your reply
    I am using db2dart
    -->db2dart prd /lhwm /tsi 8 /np 100
    and lookin in to output file for suggestions.
    The problem is on that table space we have 13000 tables.
    Every time db2dart suggesting to do reorg on one table only
    Is there any quick way to do that?
    It's a long time I used it so I don't recall the flags used, but if I remember correctly you should get a report with a whole bunch of tables. This might have changed, but perhaps you can create a loop around db2dart, grep, reorg?
    --
    Lennart

  5. #5
    Join Date
    Dec 2012
    Posts
    53
    I dont have idea on creating loop around db2dart, grep, reorg
    Could you please give me breif idea or links where i will get information about it?

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by chaitanya_db2adm View Post
    I dont have idea on creating loop around db2dart, grep, reorg
    Could you please give me breif idea or links where i will get information about it?
    If you post the report from db2dart I'll give it a shot
    --
    Lennart

  7. #7
    Join Date
    Dec 2012
    Posts
    53
    -->db2dart prd /lhwm /tsi 8 /np 0



    ART (V9.5) Report:
    2013-02-09-16.07.05.906136

    Database Name: PRD
    Report name: PRD.RPT
    Old report back-up: PRD.BAK
    Database Subdirectory: /db2/PRD/db2prd/NODE0000/SQL00001
    Operational Mode: Database Inspection Only (INSPECT)

    __________________________________________________ ____________________________
    ------------------------------------------------------------------------------


    Action option: LHWM
    Tablespace-ID: 8; Desired Highwater Mark (Number-pages): 0

    Connecting to Buffer Pool Services...

    Highwater mark processing - phase start.


    NOTES: All highwater mark values and/or object sizes listed below are
    given in extents and not pages (unless explicitly stated).

    The object ID and object type are shown for each extent listed.

    Extents marked with an asterisk (*) hold the first page of an
    object and these extents can only be moved by dropping and
    recreating that object.

    Extents marked as belonging to objects with ID equal to 65535
    are object table extents and they are not movable.

    After following a step and before continuing on to the next one,
    disconnect and reconnect to the database.


    Highwater Mark: 9109462 pages, 4554731 extents (extents #0 - 4554730)


    Lower highwater mark processing - phase start.
    Current highwater mark: 4554730
    Desired highwater mark: 0
    Number of used extents in tablespace: 4051255
    Number of free extents below original HWM: 503476
    Number of free extents below desired HWM: 0
    Number of free extents below current HWM: 503476


    Step #1: Object ID = 2504

    => Offline REORG of this table (do not specify a temporary tablespace
    and do not use the LONGLOBDATA option).

    Table: SAPPRD.GEOLDELREQ

    DAT object size: 2
    INX object size: 0
    XDA object size: 0
    LF object size: 0
    LOB object size: 0
    LOBA object size: 0
    BMP object size: 0

    Total size of object parts: 2
    Minimum number of extents that will move by this operation: 2

    Current highwater mark: 4554728
    Desired highwater mark: 0
    Number of used extents in tablespace: 4051255
    Number of free extents below original HWM: 503476
    Number of free extents below desired HWM: 0
    Number of free extents below current HWM: 503474


    ** Run the suggested offline REORG for the table first, and then run LHWM
    for the suggestion on other objects.


    Lower highwater mark processing - phase end.

    Highwater mark processing - phase end.

    ______________________________________

    The requested DB2DART processing has completed successfully!
    All operation completed without error;
    no problems were detected in the database.
    ______________________________________

    Complete DB2DART report found in:
    /db2/PRD/db2dump/DART0000/PRD.RPT

    _______ D A R T P R O C E S S I N G C O M P L E T E _______

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by chaitanya_db2adm View Post
    -->db2dart prd /lhwm /tsi 8 /np 0



    ART (V9.5) Report:
    2013-02-09-16.07.05.906136

    Database Name: PRD
    Report name: PRD.RPT
    Old report back-up: PRD.BAK
    Database Subdirectory: /db2/PRD/db2prd/NODE0000/SQL00001
    Operational Mode: Database Inspection Only (INSPECT)

    __________________________________________________ ____________________________
    ------------------------------------------------------------------------------


    Action option: LHWM
    Tablespace-ID: 8; Desired Highwater Mark (Number-pages): 0

    Connecting to Buffer Pool Services...

    Highwater mark processing - phase start.


    NOTES: All highwater mark values and/or object sizes listed below are
    given in extents and not pages (unless explicitly stated).

    The object ID and object type are shown for each extent listed.

    Extents marked with an asterisk (*) hold the first page of an
    object and these extents can only be moved by dropping and
    recreating that object.

    Extents marked as belonging to objects with ID equal to 65535
    are object table extents and they are not movable.

    After following a step and before continuing on to the next one,
    disconnect and reconnect to the database.


    Highwater Mark: 9109462 pages, 4554731 extents (extents #0 - 4554730)


    Lower highwater mark processing - phase start.
    Current highwater mark: 4554730
    Desired highwater mark: 0
    Number of used extents in tablespace: 4051255
    Number of free extents below original HWM: 503476
    Number of free extents below desired HWM: 0
    Number of free extents below current HWM: 503476


    Step #1: Object ID = 2504

    => Offline REORG of this table (do not specify a temporary tablespace
    and do not use the LONGLOBDATA option).

    Table: SAPPRD.GEOLDELREQ

    DAT object size: 2
    INX object size: 0
    XDA object size: 0
    LF object size: 0
    LOB object size: 0
    LOBA object size: 0
    BMP object size: 0

    Total size of object parts: 2
    Minimum number of extents that will move by this operation: 2

    Current highwater mark: 4554728
    Desired highwater mark: 0
    Number of used extents in tablespace: 4051255
    Number of free extents below original HWM: 503476
    Number of free extents below desired HWM: 0
    Number of free extents below current HWM: 503474


    ** Run the suggested offline REORG for the table first, and then run LHWM
    for the suggestion on other objects.


    Lower highwater mark processing - phase end.

    Highwater mark processing - phase end.

    ______________________________________

    The requested DB2DART processing has completed successfully!
    All operation completed without error;
    no problems were detected in the database.
    ______________________________________

    Complete DB2DART report found in:
    /db2/PRD/db2dump/DART0000/PRD.RPT

    _______ D A R T P R O C E S S I N G C O M P L E T E _______

    Try something like (untested, but should give you an idea):

    Code:
    #!/bin/sh
    
    report=RPD.RPT
    while :
    do
            # remove old report
            rm -f ${report}
    
            # generate new one
            db2dart prd /lhwm /tsi 8 /np 0
    
            # grep first table from report
            t=`grep "Table:" PRD.RPT | cut -f2 -d: | head -1`
            if [ "x${t}" = "x" ]; then
                    # nothing found
                    break
            fi
    
            # do reorg
            db2 connect to RPD
            db2 reorg table $t
            db2 terminate
    done
    --
    Lennart

Posting Permissions

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