IBM Support

IT03511: SQL20540N (-1224) WHEN RUNNING AUTONOMOUS TRANSACTION DUE TO PLAN LOCK WHEN INTERNAL CLI PACKAGE IS INVALID

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • An autonomous transaction can fail with the following error when
    being executed from the command line:
    
    SQL20540N  An autonomous transaction executing a procedure has
    been terminated
    abnormally. Routine name: "BEGIN...END". Specific name:
    "SYSIBMINTER". Reason
    code: "-1224".  SQLSTATE=560D2
    
    This can happen if an internal DB2 "CLI" package package (eg,
    NULLID.SYSSH200 or NULLID.SYSLH202) is invalid.   This creates
    an exclusive Internal Plan Lock which ultimately causes the CLP
    and autonomous transaction to deadlock.
    
    Example lock scenario:
    
     $ db2pd -wlocks -db origins
    
    AppHandl [nod-index] TranHdl    Lockname                   Type
    Mode Conv Sts CoorEDU    AppName  AuthID   AppID
    
    19336    [000-19336] 29         5359534C564C3031956EB7B441
    Internal P ..X       G   131168     db2bp    db2abc
    *LOCAL.db2abc.140716171832
    19358    [000-19358] 26         5359534C564C3031956EB7B441
    Internal P ..S       W   58608      prepAnon db2abc
    *LOCAL.DB2.140716172013
    

Local fix

  • Explicitly rebind the invalid package using the REBIND command
    or the db2rbind utility prior to executing the autonomous
    transaction.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all modes, all plaforms. Specific to Anonymous Blocks        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to latest fixpack. The fix is propagated to all      *
    * current fix packs                                            *
    ****************************************************************
    

Problem conclusion

  • db2 v97fp10 supplied the fix. (Auto merged to following
    releases). Changed behavior = No more unexpected self-lock for
    anon blocks.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT03511

  • 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

    2014-07-31

  • Closed date

    2014-11-10

  • Last modified date

    2014-11-10

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

    IT03464

  • 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

  • R970 PSN

       UP

  • R980 PSN

       UP

  • RA10 PSN

       UP

  • RA50 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT03511

Modified date: 10 November 2014