IBM Support

IT28601: LOAD REPLACE CAN DEADLOCK WITH APPLICATIONS ON SYSIBM.SYSTABLES

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • LOAD REPLACE can encounter DEADLOCKS since the upgrade from 10.5
    to 11.1. This is induced on column organized tables (BLU)
    processing and does not impact row organized tables.
    
    Event monitoring would show the LOAD job having "Lock Type:
    CATALOG"  while attempting to acquire a ROW lock usually held by
    applications on SYSIBM.SYSTABLES during prepare phase of the
    statement. This can be confirmed by setting up lock event
    monitor and using db2evmonfmt to format the deadlock events
    capture.
    
    Per example:
    
    -------------------------------------------------------
    Event ID               : 73
    Event Type             : DEADLOCK
    Event Timestamp        : 2018-12-17-12.47.01.228055
    Partition of detection : 0
    -------------------------------------------------------
    
    Deadlock Graph
    --------------
    Total number of deadlock participants : 2
    Participant that was rolled back      : 1
    Type of deadlock                      : local
    
    Participant     Participant     Deadlock Member Application
    Handle
    Requesting Lock Holding Lock
    --------------- --------------- ---------------
    ------------------
    1               2               0               028475
    2               1               0               028312
    
    
    Participant No 2 requesting lock
    ----------------------------------
    Lock Name            : 0x000100000A0003129248D380C3
    Lock wait start time : 2018-12-17-12.46.39.976047
    Lock wait end time   : 2018-12-17-12.47.01.228055
    Lock Type            : CATALOG
    Lock Specifics       : loadingID:00000000,loadingBytes:0A000312
    9248D380
    Lock Attributes      : 00000000
    Lock mode requested  : Exclusive
    Lock mode held       : Exclusive
    Lock Count           : 0
    Lock Hold Count      : 0
    Lock rrIID           : 0
    Lock Status          : Waiting
    Lock release flags   : 00000000
    Tablespace TID       :
    Tablespace Name      :
    Table FID            :
    Table Schema         :
    Table Name           :
    
    Participant No 1 requesting lock
    ----------------------------------
    Lock Name            : 0x00000098000000000045000552
    Lock wait start time : 2018-12-17-12.46.34.623845
    Lock wait end time   : 2018-12-17-12.47.01.228055
    Lock Type            : ROW
    Lock Specifics       : ROWID=5,DATA_PARTITION_ID=0,PAGEID=69
    Lock Attributes      : 00000000
    Lock mode requested  : Share (CS/RS)
    Lock mode held       : Exclusive
    Lock Count           : 0
    Lock Hold Count      : 0
    Lock rrIID           : 0
    Lock Status          : Waiting
    Lock release flags   : 00000000
    Tablespace TID       : 0
    Tablespace Name      : SYSCATSPACE
    Table FID            : 152
    Table Schema         : SYSIBM
    Table Name           : SYSTABLES
    
    
    Attributes            Requester                        Requester
    
    --------------------- ------------------------------
    ------------------------------
    Participant No        2                                1
    
    Application Handle    028312                           028475
    
    Application ID        *LOCAL.db2inst1.181218023046
    xxx.xx.x.xx.xxxxx.181217174634
    Application Name      db2bp                            db2bp
    
    Authentication ID     USER1                          USER2
    
    Requesting AgentID    72042                            186381
    
    Coordinating AgentID  72042                            186381
    
    Agent Status          Data Fast Load                   Compiling
    
    Application Action    Performing Load                  No action
    
    Lock timeout value    7200                             7200
    
    Lock wait value       0                                0
    
    Workload ID           1                                1
    
    Workload Name         SYSDEFAULTUSERWORKLOAD
    SYSDEFAULTUSERWORKLOAD
    Service subclass ID   13                               13
    
    Service superclass    SYSDEFAULTUSERCLASS
    SYSDEFAULTUSERCLASS
    Service subclass      SYSDEFAULTSUBCLASS
    SYSDEFAULTSUBCLASS
    Current Request       Fetch                            Prepare
    
    TEntry state          2                                1
    
    TEntry flags1         00020020                         00000000
    
    TEntry flags2         00000208                         00000200
    
    Lock escalation       no                               no
    
    Client userid                                          xxxxx,
    xxxx
    Client wrkstnname
    xxx.xx.x.xxx
    Client applname       CLP
    /dbawork/xxxxx/xxxx/TEMP//content/package[@name= xx - Log
    Client acctng                                          xxxxxxxx
    Utility ID
    

Local fix

  • To circumvent the issue, increase CATALOGCACHE_SZ
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All using LOAD                                               *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 version 11.1 m4 fp6                           *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 version 11.1 m4 fp6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT28601

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-03-27

  • Closed date

    2021-03-15

  • Last modified date

    2021-03-15

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
16 March 2021