PH62564: SQLCODE=-911 OR PERFORMANCE DEGRADATION USING LOOKUP FUNCTIONS FROM CM PROCEDURES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer is performing LOOKUP Column Map (CM) processing using
  a CM Procedure (CM PROC).
  The LOOKUP request is being created based on data in the row
  being processed and may vary from row to row. This is causing
  the LOOKUP environment to be initialized large number of times.
  The initialization overhead affects the process performance
  and may eventually lead to DB2 terminating with the following
  error:
  IBM InfoSphere Optim System Error Structure - ErrCB
  ErrCB.ID      : ZZZERRCBZZ
  SysRel        : 11.7.0
  DB2Rel        : 1215(NFM)
  ErrCB.Type    : DB2 SQL ERROR
  ErrCB.AuxInfo : Attempted fetch of csr
  ErrCB.Code    : -1
   DSNT408I SQLCODE = -911, ERROR:  THE CURRENT UNIT OF WORK HAS
            BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT.
            REASON 00C9008E, TYPE OF RESOURCE 00000302, AND
            RESOURCE NAME PENINF00.DEIFULNL.X'0-0014B11'
   DSNT418I SQLSTATE   = 40001 SQLSTATE RETURN CODE
   DSNT415I SQLERRP    = DSNXRRC SQL PROCEDURE DETECTING ERROR
   DSNT416I SQLERRD    = -190  13172746  13172878  13230790
            -499118076 536870912 SQL DIAGNOSTIC INFORMATION
  Number of Function Entries=  12
    ErrCB.N(0).Src=     FOPZCALC 20240103 PH58125
    ErrCB.N(0).Func=    (005441) LKUP_Compile
    ErrCB.N(1).Src=     FOPZXFM2 20240308 PH51232
    ErrCB.N(1).Func=    (009824) XFMValidateTable
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Optim users performing CM processing     *
  *                 with large number of tables and using the    *
  *                 LOOKUP CM function.                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: See Error Description.                  *
  ****************************************************************
  Prior APAR PH43664 PTF UI92465 introduced additional diagnostics
  for detection of empty LOOKUP tables. However, in environments
  where the LOOKUP tables do not have valid RUNSTATS, or REAL TIME
  STATISTICS, the SQL query used to determine the number of rows
  in the LOOKUP table may cause repeated table scans and possibly
  result in a deadlock.
  

Problem conclusion

• Optim design will be adjusted to introduce additional COMMIT
  requests to avoid deadlocks.
  

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:

Modules/Macros

• FOP1ODRV FOP1OSFN FOP4PEMN FOP4PMMN FOP9APIM FOPCFPMN FOPCXLIB
  FOPDYER3 FOPDYER5 FOPHSTM  FOPMAIN  FOPMCENU FOPMCMAI FOPMCOLA
  FOPMCOLP FOPMDMAI FOPMDPRC FOPMDXTR FOPMECMN FOPMEMAI FOPMMCMR
  FOPMMJBS FOPMMUNU FOPMMUTL FOPMUXIM FOPODPP  FOPOMN   FOPRS9
  FOPT0P10 FOPT110J FOPT29J  FOPT29K  FOPTC710 FOPTC718 FOPTUO50
  FOPTUO51 FOPUMCBT FOPZFMMN FOPZMAIN
  

Fix information

• Fixed component name

  OPTIM MOVE FOR

• Fixed component ID

  5655V0700

Applicable component levels

• RB70 PSY UI98046

     UP24/08/17 P F408

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.