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

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 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