reorg table TST03

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Mohammed,

I tried the below command at the database level

db2 reorg table schema.tst03 allow no access longlobdata

After running this command, i didn't see any reorg process running when i check using the commands db2 list applications or db2top. I also checked using the command db2 get snapshot for tables on <dbid>, but the table status was not showing reorg. So i cancelled the reorg.

Will any process run in the background?

Eventhough the size of the table is 700 GB, but the actual contents of the table would be less than 5 GB only now as we deleted large amount of data. In this case, how much space would be required?

Thanks & Regards,

Saravanan

[Former Member · Accepted solution]

Hi Saravanan,

For checking progress of reorg you can use command:

db2pd -db <SID> -reorgs

This will give an idea as to Reorg is in which phase and what is current and what should be maxcount to finish reorg

Other command is :

db2 list history reorg all for <SID>

I will suggest you better create an sql script of command using vi editor and then run this script using nohup so that it runs in backround and nohup.out file can give you output of progress.Steps to follow are:

a) Cretae an sql script at home directory of db2<sid> using vi editor
   eg:-> reorg_tst03.sql and when you do more it should show command for reorg inside like below

  sapserverxxx:db2<sid> 2> more reorg_tst03.sql
  reorg table sap<sid>.TST03 allow no access use PSAPTEMPREORG longlobdata;

b)Start reorg using command from home directory of db2<sid>
nohup db2 -tvf reorg_tst03.sql &

c)Do monitoring using command told in beginning

Also more important take statistics of table from dbacockpit before doing reorg and after reorg compare the values.

For space required as a thumb rule it should be twice the actual size of table. If you are sure table size is 5 GB then space required would be 10 to 15 GB max

[Frank-Martin · Accepted solution]

Hi Saravanan,

please check the column type in table TST03.

db2 " describe table sap<sid>.TST03 "

if your are using DB2 V9.7 or higher this should show a BLOB data type for column DCONTENT. If it still shows a LONG VARCHAR data type , this can add to your trapped space problem. In DB2 V9.7 and higher most data in DCONTENT can be inlined using a BLOB data type.

To convert LONG VARCHAR to BLOB in table TST03 you can use report DB6CONV ( see SAP notes how to download ). As a side effect you can also solve your REORG problem using DB6CONV. DB6CONV even allows to "REORG" this table online.

Regards

                  Frank

[Former Member · Accepted solution]

Hi Mohammed,

Last time when i did the offline reorg, i checked the below command -

db2 list history reorg all for <SID>

But it was not showing any details about the reorg. So i was not aware if any reorg is running at all

Do i need to use a index scan so that the offline reorg will be faster?

Thanks & Regards,

Saravanan

[Former Member · Accepted solution]

Hi Frank,

DCONTENT column shows a LONG VARCHAR data type, but our database version 9.5. Is the report available only from 9.7

Thanks & Regards,

Saravanan

[Frank-Martin · Accepted solution]

Hi  Saravanan,

yes, DB6CONV is available independent of the DB2 version. You can use DB6CONV to reorg the table ( optionally online ) as an alternative to the offline DB2 REORG with LONGLOBDATA option.

If you use DB6CONV on V9.5, column DCONTENT will stay LONG VARCHAR. LOB inlinig has been introduced with DB2 V9.7 and SAP changed the DDIC -> DB2 type mapping with this version.

Regards

                  Frank

[Former Member · Accepted solution]

Hi Sravanan,

db2 list history reorg all for <SID> command gives detail about reorgs which are completed. For current running reorg you have to use command

db2pd -db <SID> -reorgs

I don't think index scan is required. Could you please share your table statistics from dbacokpit like values of F1/F2/F3 ,table size and index size

[Former Member · Accepted solution]

Hi Mohammed,

Please find the details below

Table size - 784.348.960

Index size - 35.840

F1 - 0%

F2 - 99%

F3 - 99%

Cardinality - 796.816

Overflow records - 0

Total number of pages with data - 2.015

Total number of pages - 2.018

Thanks & Regards,

Saravanan

[Former Member · Accepted solution]

Please verify when was last reorg check done for this table. Values of F2/F3 suggest doing reorg for this table is not fruitful.

[Former Member · Accepted solution]

REORG Pending - NO

Last REORG of Table - 17.10.2012

Runtime of Last REORG - 00:00:16

Last RUNSTATS - 17.10.2012 17:51:28

[Former Member · Accepted solution]

Try running RUNSTATS for this table and see if table values are changing

[Former Member]

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-decd4fcbb...

regards,

Javier

[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