IBM Support

IT28743: ADMIN_MOVE_TABLE: MISLEADING ERROR MESSAGE REFERRING TO SEMAPHORES. SQL2102N REASON CODE 33

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

  • The error message SQL2102N reason code 33 with text "The maximum
    number of retries was exceeded when trying to acquire a
    semaphore." might be reported in ADMIN_MOVE_TABLE (AMT) phases
    INIT, SWAP and CANCEL. These phases execute many catalog
    operations such as RENAME INDEX/TABLE, ALTER INDEX/TABLE,
    TRANSFER OWNERSHIP etc. In order to avoid locking conflicts on
    the system catalog with other applications a semaphore mechanism
    is implemented within admin_move_table.
    
    Most users first think of OS semaphore resource bottleneck, when
    they get to read the message text of SQL2102N reason code 33.
    However this message doesn't refer to OS semaphores at all.
    Rather it refers to the serialization and retry mechanism inside
    AMT before issuing a lot of catalog operations consecutively.
    
    With this fix admin_move_table will report the usual SQL0911N
    reason code 68 (lock timeout) errors instead of SQL2102N reason
    code 33.
    The semaphore is realized as an exclusive lock on the table
    SYSTOOLS.OTM_SEMAPHORE_TABLE. This should reflect the root cause
    more clearly of not being able to acquire a semaphore within the
    locktimeout period while performing catalog operations.
    

Local fix

  • Apply fixpack
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to the latest fix pack.                              *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT28743

  • 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-04-10

  • Closed date

    2020-01-18

  • Last modified date

    2020-01-18

  • 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

  • RB10 PSN

       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":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
18 January 2020