IBM Support

IC90809: MULTIPLE DETACH PARTITION FROM THE SAME UNIT OF WORK WILL DEADLOCK WITH EACH OTHER

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When performing a multiple detach partition on a partitioned
    table, in the same unit of work, the Asynchronous Partition
    Detach (APD) processes with each other. The APD function locks
    the row in SYSIBM.SYSTABLES for the partitioned table with an NS
    lock. Later, the process will need to lock this row in X-clusive
    mode to update the packed descriptor. This is will cause a
    deadlock between the two APD tasks.
    
    Here is the stack for when DB2 acquires the X-lock on the row in
    SYSIBM.SYSTABLES:
    
    sqlrlLockSystablesRow
    sqlrlSysPartitionsAdjustSeqno
    sqlrlSysPartitionsDelete
    sqlrlAlterDropCatalogChange
    sqlrlAlterDetachCatalogChange
    sqlrlAlterPartCatalog
    sqlrlPhysicalDetach
    apdTaskProcessor
    

Local fix

  • If you hit this problem and are concerned that you cannot
    reclaim the space by dropping or archiving the detached
    partition there are two workarounds available each with their
    own pros and cons.
    
    Workaround 1:
    Pros:  No need to recycle the instance if LockTimeout is already
    set to -1.
    Cons:  Need to export the data from the table and recreate it.
    Workaround:
    - Set LockTimeout to -1 using the UPDATE DATABASE CONFIGURATION
    command.
    - Recycle the instance using db2stop and db2start.
    - Drop the partitioned table.
    
    Workaround 2:
    Pros:  No need to recreate the table.
    Cons:
    - Need to recycle the instance
    - Time required for the system to resolve itself using this
    method may vary based on timing and the number system jobs to
    complete.
    Workaround:
    - Set LockTimeout to -1 using the UPDATE DATABASE CONFIGURATION
    command.
    - Set DLChkTime to 1000 (i.e. 1000 ms or 1 s) using UPDATE
    DATABASE CONFIGURATION and set DB2_ABP_SUSPEND_DELAY to 3 (i.e.
    3 seconds) using db2set.
    - Recycle the instance using db2stop and db2start.
    - Connect to the database and wait for the system to resolve
    itself.  Monitor SysCat.DataPartitions until there are no
    partitions left with STATUS='L'.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version V9.7 Fix Pack 9                       *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version v9.7 Fix Pack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC90809

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-03-12

  • Closed date

    2014-06-19

  • Last modified date

    2014-06-19

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

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

    IC92146 IC95308

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC90809

Modified date: 19 June 2014