db2 drop table takes too long - SAP SUM SP update

[JakubB]

Hi all,

I'm facing long run for db2 drop table during SUM update.

The SUM phase - MAIN_SHDIMP/SUBMOD_SHDDEL2/PSCRGEN_DROP_SHD_TABLE_EXE

Env:
AIX 7.1.TL4
DB2 9.7 FP9

Part of log file:

ETQ399 Executing SQL script '../var/TSHDDR25.XQL'.
2 ETQ399 Connecting to database 'DB6'.
4 ETQ010 Date & Time: 20170601204444
3 ETQ398 SQL: DROP TABLE "/OSP/T_MDR_SCNT~"
4 ETQ010 Date & Time: 20170601204904
3 ETQ398 SQL: DROP TABLE "/OSP/T_REP_CXTP~"
4 ETQ010 Date & Time: 20170601205922
3 ETQ398 SQL: DROP TABLE "/SAPDMC/LSRVLI~"
4 ETQ010 Date & Time: 20170601210146
3 ETQ398 SQL: DROP TABLE "ADACACHE~"
4 ETQ010 Date & Time: 20170601210526
3 ETQ398 SQL: DROP TABLE "ADR2S2~"

You can see that start time between two "db2 drop" commands are different - 4minutes, 10 minutes, 5 minutes.
It's running in parallel with using 24 DB2 agents.

Any clue what can cause it ? Can there be any problem during updating syscat.tables ?

We updated previous SAP systems in the same ladnscape and there wasn't any issue like this.

Thank you
Br, Jakub

[Frank-Martin · Accepted solution]

Hi,

maybe the old note

791325 - DB6: Performance with DROP TABLE statements

describes the problem. Is DROP table recovery active for some of your tablespaces.

This can have a huge impact when your history file is large.

Regards

Frank

[JakubB · Accepted solution]

Hi,

yes, this SAP note helped to solve this issue.

I had to hange settings for Tablespace and disabled DROP_RECOVERY feature.

By command:

db2 "ALTER TABLESPACE <name> DROPPED TABLE RECOVERY OFF"

and then tables were dropped fast as usually.

Thank you

Jakub

[former_member301417]

Hi, I just saw your 20 day old question. Is it still relevant? Has the problem been solved (and if yes, how)? If not, please sum up the current status.

Best regards, Frank