Forcing reorg of all tablespaces in SAP? DB2 V9.1

[Former Member]

Hello all,

I know I am very new here, and very new to SAP, so any help you can give would be great.

As info...the company I work for is implementing SAP on zLinux (SLES 10) and DB2 V9.1.

Is there any way to force SAP to reorg all of its tablespaces and indexes?

I would like to reorg all of the SAP tablespaces and indexes because our IBM reps say that the built in data compression will save about 50% disk space, and improve performance! However, they also tell us that it will not take effect until after the first reorg. Being that my systems are fresh installs, I dont have any tables or indexes that SAP wants to reorg yet. Does anyone have any ideas on how to "fake" SAP in to thinking tablespaces and indexes need to be reorg'd?

Thanks,

Andy Howard

[thomas_vogt · Accepted solution]

Andy,

You can use transaction DB13 to run reorg on TS/IS that are recommended to be reorganized. Please run report RSDB2_COLLECT_DAILY before to gather the recommendations, stored procedure DSNACCOR is used for this.

The Database Administration Guide ( http://service.sap.com/~sapidb/011000358700002040342006E ) contains detailed descriptions on all CCMS functions and their prerequisites.

HTH.

Regards,

Thomas

[Former Member · Accepted solution]

Thomas,

Thanks for the info. I am actually using SP12 on netweaver2004s, so I use the DBACOCKPIT transaction, which in effect runs db13 for me. Thanks for the info on the RSDB2_COLLECT_DAILY report. For some reason this report runs in 1 second? I have verified with our head DBA that this is installed, and we even went back thru the Admin guide you mentioned and followed the steps again. For some reason, in SM21, sap thinks that RTS is not installed? I get the error message "Database DSNRTSDB does not exist. RTS probably not installed". In the DBA guide is states that RTS is installed by default with DB2 V9.1. I dont understand why SAP thinks RTS is not installed, when DB2 V9.1 should have it installed by default?

I have opened up an OSS ticket for SAP to look at this also.

Back to the original question...once I get this working, do you think there are any items in the DB Alert settings that could be used to "trick" SAP in to reorging all the tables and indexes?

Thanks

Andy

Message was edited by:

Andy Howard

[thomas_vogt · Accepted solution]

Andy,

This is a coding error, the V9 changes regarding RTS tables have not been taken into account at this stage.

I will respond in the problem ticket as soon as a fix is available.

You can change the thresholds used for StoProc DSNACCOR (DBACockpit - Configuration - DB Alert Settings) to get more recommendations or set column DB2REOTS-NEEDREORG = Y (DB2REOIX-NEEDREORG for indexes) to 'convince' the DB13 process to generate the REORG statements for more TS/IS.

Regards,

Thomas

[thomas_vogt · Accepted solution]

Andy,

Note http://service.sap.com/sap/support/notes/1085750 fixes the error.

Regards,

Thomas

[Former Member · Accepted solution]

Thomas,

Thanks for the fix. I was able to sucessfully implement this note! However, when I run the RSDB2_COLLECT_DAILY report I get an error. I have opened a new OSS note on this. If you are curious, here is the error message.

Program RSDB2_COLLECT_DAILY

ABAP-ReturnCode: 1

Stored Procedure Returncode: 12

Error Message:

***SQL error when executing EXECUTE IMMEDIATE :stmt#DSNT408I SQLCODE = -512, ERROR: STATEMENT REFERENCE TO REMOTE OBJECT

IS INVALID #DSNT418I SQLSTATE = 56023 SQLSTATE RETURN CODE

#DSNT415I SQLERRP = DSNXOTDA SQL PROCEDURE DETECTING ERROR

#DSNT416I SQLERRD = -300 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

#DSNT416I SQLERRD = X'FFFFFED4' X'00000000' X'00000000' X'FFFFFFF

F' X'00000000' X'00000000' SQL DIAGNOSTIC # INFORMATION

#

#

#

#

#

IFC Return Code: 0

IFC Reason Code: 0

XS Bytes: 0

Last Statement:

***SQL error when executing EXECUTE IMMEDIATE :stmt