PH61314: ABEND04E RC00C90101 IN DSNOSELP ERQUAL5004

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Abend 04E 00C90101 in DSNOSELP ERQUAL 5004 may occur when a LOB
  is under a substring, with the substring size longer than the
  actual lob size + the substring result is used in a LIKE
  predicate
  The below dump was seen -
  
  D2I1 ABND 04E 00C90101 U=ACZ7 M=N C=131 RDS -SQL
  M DSNTFRCV LOC DSNIDM DSNOSELP 5004
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 12 for z/OS users and Db2 13 for     *
  *                 z/OS users with query with LIKE predicate    *
  *                 on LOB column with substring.                *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABND 04E 00C90101 at DSNXOSELP:5004     *
  *                      may occur for LIKE predicate on LOB     *
  *                      column with substring, and substring    *
  *                      parameter is longer than the actual     *
  *                      lob size.                               *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  ABND 04E 00C90101 at DSNXOSELP:5004 may occur for LIKE
  predicate on LOB column with substring, and substring
  parameter is longer than the actual lob size.
  
  The following is an example of error case:
  
  WITH CTEA AS
  (
  SELECT  C1, C3,
  SMALLINT(ROWNUMBER()
  OVER(PARTITION BY C5,C1,C3
  ORDER BY C4  DESC)) AS LAST,
  INTEGER(LENGTH(LOBCOL))  AS STMT#LEN ,
  SUBSTR(LOBCOL , 1, 16000) AS STMT_TEXT#16000
  FROM   T1
  WHERE 1 = 1
  AND LOBCOL LIKE '%3333__33%')
  
  SELECT LAST , STMT#LEN,
  LOCATE('3333', STMT_TEXT#16000) AS LOC#333,
  SUBSTR(STMT_TEXT#16000 CONCAT SPACE(80) ,
  LOCATE('3343', STMT_TEXT#16000) + 10   ,
  80 )  AS TEXT#3343  ,
  CASE WHEN LOCATE('3353', STMT_TEXT#16000) > 0   THEN
  SUBSTR(STMT_TEXT#16000 CONCAT SPACE(80)  ,
  LOCATE('3333', STMT_TEXT#16000)  , 80   )
  ELSE SPACE(80) END         AS TEXT#3353
  FROM CTEA
  ORDER BY C1,C3,LAST;
  

Problem conclusion

• Db2 Lob manager code is modified to reference the correct
  return code when sbustring parameter is longer than the LOB
  data length.
  
  Additional Keywords:
  ABEND04E SQLLOB SQLSUBSTRING SQLLOCATE SQLLIKE
  

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:

  UI97163 UI97164

Modules/Macros

• DSNOSELP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI97163

     UP24/06/12 P F406

• RD10 PSY UI97164

     UP24/06/12 P F406

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.