Skip to Content
0

db2 drop table takes too long - SAP SUM SP update

Jun 02, 2017 at 02:53 PM

129

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Frank-Martin Haas
Jun 27, 2017 at 02:38 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0
Frank-Herwig Walter
Jun 22, 2017 at 03:30 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded