IBM Support

LI74755: "COPY NO" LOADS IN RECOVERABLE DATABASES RESULTS IN DEADLOCK WITH DROP TABLE COMMAND.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • "COPY NO" Loads in recoverable databases can conflict with DROP
    TABLE command in very rare timing scenarios, resulting in
    Deadlock. The table being LOADed and the table being dropped
    would need to be in the same tablespace for this problem to
    occur.  Also, the table names may be different.
    
    Deadlock event monitor explains following behavior:
    
    LOAD with "COPY NO":
    - Holds lock on SYSIBM.SYSDATAPARTITIONS.
    - Waits for SYSIBM.SYSTABLES.
    
    DROP:
    - Holds lock on SYSIBM.SYSTABLES.
    - Waits for SYSIBM.SYSDATAPARTITIONS.
    
    Deadlock can be seen in db2diag.log as follows :
    
    2009-06-10-04.03.55.995750+120 I8813013E598        LEVEL: Error
    PID     : 24366                TID  : 183228164448 PROC :
    db2sysc 0
    INSTANCE: XXXXXXXX             NODE : 000          DB   :
    XXXXXXX
    APPHDL  : 0-2132               APPID:
    *LOCAL.XXXXXXXXX.090610022252
    AUTHID  : XXXXXXXX
    EDUID   : 40                   EDUNAME: db2agent (DBFORCEP) 0
    FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::fetch,
    probe:40
    RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
    detected"
              DIA8002C A deadlock has occurred, rolling back
    transaction.
    DATA #1 : Hex integer, 4 bytes
    0x0000000B
    
    2009-06-10-04.03.55.996244+120 I8813612E574        LEVEL: Error
    PID     : 24366                TID  : 183228164448 PROC :
    db2sysc 0
    INSTANCE: XXXXXXXX             NODE : 000          DB   : XXXXXX
    APPHDL  : 0-2132               APPID:
    *LOCAL.XXXXXXXXX.090610022252
    AUTHID  : XXXXXXXX
    EDUID   : 40                   EDUNAME: db2agent (DBFORCEP) 0
    FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
    DATA #1 : String, 139 bytes
    LOADID: 40.2009-06-10-04.03.51.114120.0 (2;93)
    Failed to get TS Contents , -2146435070, 0x2, Detected in
    file:sqluCCatalogInfo.C, Line:3164
    
    This deadlock problem can occur even if the registry variable
    DB2_LOAD_COPY_NO_OVERRIDE is used.
    

Local fix

  • (i)  We should not run "COPY NO" Loads in recoverable databases
         and DROP TABLE in parallel.
    
    (ii) Also, this problem does not occur if 'COPY YES' or
         'NONRECOVERABLE' is specified explicitly in the LOAD
         command.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users prior to DB2 V95 FP6                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * "COPY NO" LOADS IN RECOVERABLE DATABASES RESULTS IN          *
    * DEADLOCKWITH DROP TABLE COMMAND.                             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V95 FP6.                                      *
    ****************************************************************
    

Problem conclusion

  • Problem is first fixed in DB2 V95 FP6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI74755

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-07-27

  • Closed date

    2010-06-22

  • Last modified date

    2010-06-22

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC68452 IC68453

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
22 June 2010