PM76088: ABEND04E 00C90101 DSNIDM DSNOGETD ERQUAL5001

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 Abend with 00C90101 at DSNIDM DSNOGETD 5001 while execute
  complex query involved with SUBSTR of LOB string.
  This can be recreate under lab environment.
  SQL sample:
  SQL STATEMENT:
  WITH MAXPKG (LOCATION,COLLID,NAME,CONTOKEN) AS
  (SELECT
  P.LOCATION,P.COLLID,P.NAME,SUBSTR(MAX(CHAR(P.PCTIMESTAMP)
  CONCAT
  CHAR(P.BINDTIME) CONCAT P.CONTOKEN), 53) AS CONTOKEN
  FROM SYSIBM.SYSPACKAGE P
  --WHERE P.LOCATION = ' ' AND P.COLLID = 'CB'
  --AND P.NAME LIKE 'R%'
  GROUP BY P.LOCATION,P.COLLID,P.NAME),BIF
  (LOCATION,COLLID,NAME,CONTOKEN,SEQNO,ZAEHLER,CHARAUFRUF,STMTREST
  )
  AS (SELECT M.LOCATION,M.COLLID,M.NAME,M.CONTOKEN,S.SEQNO, 1 AS
  ZAEHLER,
  SUBSTR(SUBSTR(STATEMENT,LOCATE(' CHAR ( ',S.STATEMENT),
  LOCATE(' ) ',
  SUBSTR(STATEMENT,LOCATE(' CHAR ( ',S.STATEMENT))) + 1), 1, 50)
  AS CHARAUFRUF,SUBSTR(S.STATEMENT,LOCATE(' CHAR ( ',S.STATEMENT)
  + 7)
  AS STMTREST FROM MAXPKG M,
  SYSIBM.SYSPACKSTMT S WHERE S.LOCATION = M.LOCATION
  AND S.COLLID = M.COLLID
  AND S.NAME= M.NAME AND S.CONTOKEN = M.CONTOKEN
  AND LOCATE(' CHAR ( ',S.STATEMENT) > 0 AND
  LOCATE('SELECT ', S.STATEMENT) > 0
  UNION ALL
  SELECT LOCATION,COLLID,NAME,CONTOKEN,SEQNO,ZAEHLER + 1 AS
  ZAEHLER,
  SUBSTR(SUBSTR(STMTREST,LOCATE(' CHAR ( ',STMTREST), LOCATE(' )
  ',
  SUBSTR(STMTREST,LOCATE(' CHAR ( ', STMTREST))) + 1) , 1, 50)
  AS CHARAUFRUF,SUBSTR(STMTREST, LOCATE(' CHAR ( ', STMTREST) + 7)
  AS STMTREST FROM BIF
  WHERE ZAEHLER < 100 AND LOCATE(' CHAR ( ', STMTREST) > 0)
  SELECT SUBSTR(NAME, 1, 8) AS NAME,SEQNO,ZAEHLER,CHARAUFRUF
  FROM BIF WHERE 1 = 1 AND
  LOCATE('(', SUBSTR(CHARAUFRUF, 8)) = 0
  --######################################################
  -- The line below is responsible for the dump.
  -- After having commented out this line the query works.
  AND CHARAUFRUF NOT LIKE '%, ISO )'
  --######################################################
  FETCH FIRST 2000 ROWS ONLY
  WITH UR
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of LOBs  *
  *                 used in LIKE predicates.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E RC00C90101 can occur at     *
  *                      location DSNOGETD 5001 when LOB data is *
  *                      used in a LIKE predicate.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E RC00C90101 can occur unexpectedly at location
  DSNOGETD 5001 (ERQUAL5001) when LOB data is used in a LIKE
  predicate.
  
  
  The following example helps to illustrate the problem.
  
  1. Create Table T1 with a LOB column C2 and populate it.
  
     CREATE TABLE T1 (C1 INT, C2 CLOB(100));
  
     INSERT INTO T1
     VALUES(1,
            CLOB('DECLARE VMPLR TABLE (EMPLOYEENUMBER CHAR( 6 )'
                  CONCAT ' , PHONENUMBER CHAR ( 4 ) )'));
  
  
  2. Execute a query which uses C2 in a LIKE predicate.
  
     SELECT * FROM T1
     WHERE SUBSTR(C2, LOCATE(' CHAR ( ',C2), 50)
           NOT LIKE '%, ISO )';
  
  
  The ABEND04E occurs when the above query is executed.  The DB2
  LOB component does not handle the LOB value properly when it is
  used in a LIKE predicate.
  

Problem conclusion

• The code in DB2 has been modified to process a LOB column
  properly when it is used in a LIKE predicate.
  
  Additional Keywords: SQLLIKE SQLLOB
  

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:

  UK90964 UK90965

Modules/Macros

•    DSNOGETD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90964

     UP13/01/31 P F301

• R910 PSY UK90965

     UP13/01/31 P F301

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.