Skip to Content
avatar image
Former Member

reorg table TST03

Hi,

In our ECC system, the table TST03 has grown to 700 GB due to the spool reorg job not running. We have deleted a lot of data from the table by running the program RSPO1041. After the deletion, we first tried online reorg command. It got completed within few seconds without reducing the tablespace. Even the offline reorg also completed in few seconds without deleting anything. We noticed that the most of the data are Long Objects. Is it the reason, the reorg is not reclaiming any space. In this case, how to reorg the table and reclaim the space.

Version - DB2 9.5 on AIX

Thanks & Regards,

Saravanan

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 17, 2012 at 06:58 PM

    Hi Saravanan,

    First thing to keep in mind is tables with Long objects can only be reorged offline. Now the point is for reorg are you using

    1.Correct command- Note clause longlobdata is used in command to reorg long objects aswell.

    reorg table sap<sid>.TST03 allow no access use <temptablspacename> longlobdata


    2.Enough space in original tablespace or temporary tablespace you are using.-- For big tables like TST03 there is generally not enough space available.For doing reorg of such big tables you can follow below steps to create temporary tablespace with enough space:

    a)Ask your AIX team to create a temporary filesystem with required space (minimum equal to 1.5 times of table size) eg:/sapreorgtemp

    b)Assign filesystem permission to db2<sid>

    c)Using db2<sid> Create directory in temporary filesystem eg: mkdir /sapreorgtemp/PSAPTEMPREORG

    d)Create temporary tablespace PSAPTEMPREORG using command

    db2 "create system temporary tablespace PSAPTEMPREORG in nodegroup IBMTEMPGROUP pagesize 16k managed by system using ('/sapreorgtemp/PSAPTEMPREORG') extentsize 2 prefetchsize automatic NO FILE SYSTEM CACHING dropped table recovery off"

    e)Using this temporary tablespace you can run reorg with command

    reorg table sap<sid>.TST03 allow no access use PSAPTEMPREORG longlobdata

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 17, 2012 at 06:05 PM

    Hi Saravanan,

    if reorg is not helping then please try to reduce the high water mark of the TS, you should be able

    to reclaim space with that.

    486559  DB2DART: Reducing high water mark

    SCN DB2 9.7 reducing the HWM

    http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20057ef0-7952-2c10-949f-decd4fcbb392?QuickLink=index&overridelayout=true&44478681557819

    regards,

    Javier

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Javier,

      Unfortunately there is no reliable way to reduce the HWM in DB2 9.5 other than running db2dart. Only in DB2 9.7, it would be easy. In my case, the size of the table itself is still showing 700 GB. Only if the size of the table is reduced, then i can try to reclaim the disk space.

      Thanks & Regards,

      Saravanan