How to perform single table compression in db2.

[Former Member]

Hi,

We are facing problem in CRM production.SMW3_BDOC3 table size is 215GB and it is growing very fast..

We have scheduled reorg report to delete the entries in SMW3_BDOC3 table. Now entries got deleted but size of the table remains same 215GB.

Please suggest how to compress single table in DB2.

we are using windows2008 OS. and database:DB6.,release:09.07.0003

Regards,

Harish.

[Former Member · Accepted solution]

Please try to below after re-org to reduce the HWM.

ALTER TABLESPACE <tablespace_name> LOWER HIGH WATER MARK

ALTER TABLESPACE <tablespace_name> REDUCE MAX

[Former Member]

Hi Harish,

You can first try to REORG table the using DB13 --> REORG and RUNSTATS for Set of Tables, in the window provide the table and choose ONLINE, check the table size after compleltion of the program.

You can also try to REORG the table from OS level as below..

Reorg Table:

$ DB2 ALTER TABLE <schema_name>.<table_name> ACTIVATE NOT LOGGED INITIALLY ---> to avoid logging
$ DB2 REORG TABLE <schema_name>.<table_name>
$ DB2 COMMIT

Reorg index:
$ DB2 REORG INDEXES ALL FOR TABLE <schema_name>.<table_name>

After reorganization, it is required to re-generate statistics for both the table and the indexes

$ DB2 RUNSTATS ON TABLE <schema_name>.<table_name> FOR INDEXES ALL

Can go through the below link...

Regards,

Prithviraj

[Former Member]

Hi Prithviraj,

Thanks for your suggestion.

In QAS system SMW3_BDOC3 table size is 98GB... at os level disk free space 30GB.

In PRD system  SMW3_BDOC3 table size is 215GB ..  at os level disk free space 85GB.

We have reduced SMW3_BDOC3 Table entries in both the systems by using reports SMO6_REORG2  and SMO8_REMOVE_BDOC_INCONSISTENCY as suggested by SAP.

But no change in table space level.

In QAS system i tried  using db13-->compress on --->run reorg to build dictionary---- But at os level we have only 30GB space.with in 2 hours the space get filled-up, reorg job has taken 30GB and system got hang. when i restarted the server  it has released 30GB. ... but Reorg table job got cancelled and table size had not compressed.

Regards,

Harish.

[Former Member]

Hi,

Why don't you try to reorg OFFLINE for the given table in QAS first. For this you can create a TEMP tablespace in some other file sytem and once REORG is completed you can delete the tablespace.

You can follow the below mentioned steps for this...

1. Create a temporary filesystem having permissions to db2<sid> with required space usually equal to 1.5 times of table size. (Create reorgtempsap directory under /db2/SID/sapdata1)
2. db2 connect to SID
3. db2 update db cfg for SID using auto_tbl_maint off
4. db2 "CREATE SYSTEM TEMPORARY TABLESPACE REORGTEMP IN NODEGROUP IBMTEMPGROUP PAGESIZE 16K MANAGED BY SYSTEM USING ('/db2/SID/sapdata1/reorgtempsap') DROPPED TABLE RECOVERY OFF"
5. db2 “REORG TABLE <schema_name>.<table_name> allow no access use R60REORGTEMP LONGBLOBDATA”
6. $ db2 "drop tablespace REORGTEMP"
7. $ db2 update db cfg for SID using auto_tbl_maint on

Ensure you have DB backup taken prior to the activity.

Once you complete the activity you should have table size decreased.

Regards,

Prithviraj.

[Former Member]

Hi Prithviraj,

I have done RE ORG for SMW3_BDOC* table by useing DB13 reorg ONLINE option. Table size has been decreased for 40GB.

Before Reorg  TB size   |||| After Regor TB size  

SMW_BDOC   40GB          10GB

SMW_BDOCQ 20 GB          10 GB

But in OS level Disk size remains same as 85 GB.. please guide me how to release 40 GB at OS level.

Thanks&Regards,

Harish

[Former Member]

Hi,

You need to perform OFFLINE reorg as informed in my earlier reply.

Only OFFLINE reorg can give the space at disk level.

Regards,

Prithviraj.

[Former Member]

Hi,

You need to perform OFFLINE reorg to give space at OS/disk level. ONLINE reorg will free the space at tablespace level.

Please perfrom the offline reorg on the table as informed in my last reply.

Regards,

Prithviraj.

[hugo_amo]

Hi Harish,

You may need to reduce the highwater mark. See Note:

486559 - DB6: Reducing the high-water mark (HWM)

https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F6465...

Regards,

Hugo

[Former Member]

Hello Harish,

please check the DBA guide for table compression

https://websmp205.sap-ag.de/~sapidb/011000358700001449002009E/DB2AdminGuide10_5_GMTfinal.pdf

regards,
Javier

[Former Member]

Hi Javier,

Thanks for your response.

Regards,

Harish.