Skip to Content
avatar image
Former Member

db2 drop table takes too long - SAP SUM SP update

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 27, 2017 at 02:38 PM

    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

    Add comment
    10|10000 characters needed 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

  • Jun 22, 2017 at 03:30 PM

    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

    Add comment
    10|10000 characters needed characters exceeded