JR43231: LOCK TIMEOUTS CAN OCCUR ON THE PERFORMANCE DATA WAREHOUSE DATABASE

Version 7.5.1 Fix Pack 1 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• Primary key generation with auto-tracking enabled can cause the
  Performance Data Warehouse server to stop processing during
  report generation.  Symptoms in the logs are repeated lock wait
  timeouts such as the following (example is for Oracle, where
  this has been observed):
  
  org.springframework.jdbc.BadSqlGrammarException:
  ConnectionCallback; bad SQL grammar []; nested exception is
  java.sql.SQLSyntaxErrorException: ORA-02049: timeout:
  distributed transaction waiting for lock
  ORA-06512: at "SYS.DBMS_LOCK", line 82
  ORA-06512: at "SYS.DBMS_LOCK", line 110
  ORA-06512: at line 1
  ,
     This shows the SQLException along with the BPM message that
  is logged
  [6/26/12 14:21:18:103 HKT] 0000002f wle_perf  E
  CWLLG1172E: Could not lock system table.
  com.lombardisoftware.core.TeamWorksException: Error locking
  system table: ConnectionCallback; bad SQL grammar []; nested
  exception is
  java.sql.SQLSyntaxErrorException: ORA-02049: timeout:
  distributed transaction waiting for lock
  ORA-06512: at "SYS.DBMS_LOCK", line 82
  ORA-06512: at "SYS.DBMS_LOCK", line 110
  ORA-06512: at line 1
   at
  com.lombardisoftware.server.tracking.loader.LockHolder.runLockQu
  ery(LockHolder.java:260)
   at
  com.lombardisoftware.server.tracking.loader.LockHolder.runLockQu
  ery(LockHolder.java:243)
   at
  com.lombardisoftware.server.tracking.loader.LockHolder.lockSyste
  mTable(LockHolder.java:149)
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:  Users of BPM Express, BPM Standard and BPM  *
  *                  Advanced.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: Primary key generation with             *
  *                      auto-tracking enabled can cause the     *
  *                      Performance Data Warehouse server to    *
  *                      stop processing during report           *
  *                      generation.  Symptoms in the logs are   *
  *                      repeated lock wait timeouts such as     *
  *                      the following (example is for Oracle,   *
  *                      where this has been observed):          *
  *                      org.springframework.jdbc.BadSqlGrammarE *
  *                      xception:                               *
  *                      ConnectionCallback; bad SQL grammar     *
  *                      []; nested exception is                 *
  *                      java.sql.SQLSyntaxErrorException:       *
  *                      ORA-02049: timeout:                     *
  *                      distributed transaction waiting for     *
  *                      lock                                    *
  *                      ORA-06512: at "SYS.DBMS_LOCK", line 82  *
  *                      ORA-06512: at "SYS.DBMS_LOCK", line     *
  *                      110                                     *
  *                      ORA-06512: at line 1                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The primary key block size for the Performance Data Warehouse
  database was hardcoded to a value of 50 bytes.
  

Problem conclusion

• The fix for this APAR makes the primary key block size value
  configurable.  The lower the primary key block size, the more
  often the server needs to request new blocks from the database;
  each time a block is acquired, the transaction acquires a
  table lock.  This increases the probability of a deadlock (or
  lock timeout), as one transaction must wait on another to
  complete and release the table lock.  Increasing the
  primary key block size reduces the number of requests for
  blocks and hence the probability of deadlocks.
  
  Interim fixes for Business Process Manager Version
  7.5.0.1 and Version 7.5.1.0 have been published to Fix Central.
  Below is prerequisite information and
  installation/uninstallation instructions for both interim
  fixes (also published to Fix Central in a separate file
  that is automatically downloaded with each interim fix):
  
  JR43231 has been deprecated.
  Please use JR45327 as its replacement ifix.
  

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

  BPM STANDARD

• Fixed component ID

  5725C9500

Applicable component levels

• R750 PSY

     UP