PI34200: SQLCODE171 ISSUED WHEN QUERY CONTAINS SUBSTR FUNCTION AND XML TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -171 may issue when query contains substr function and
  XML table.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of an SQL          *
  *                 statement that contains a basic predicate    *
  *                 with an expression SUBSTR on a column from   *
  *                 an XMLTABLE function.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected SQLCODE171 could happen for  *
  *                      an SQL statement that contains a basic  *
  *                      predicate with an expression SUBSTR on  *
  *                      a column from an XMLTABLE function, and *
  *                      the second parameter of the SUBSTR is   *
  *                      1.                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1 (
    C1 INT,
    C2 XML
  );
  
  SELECT X.*
  FROM T1,
  XMLTABLE (
    XMLNAMESPACES(DEFAULT 'http://posample.org'),
    '//customerinfo'
    PASSING T1.C2
    COLUMNS
      "CUSTNAME" VARCHAR(30) PATH 'name',
      "CUSTCITY" VARCHAR(30) PATH 'addr/city'
  ) as X
  WHERE SUBSTR(X.CUSTNAME, 1, 1) = 'D'
  ;
  
  DB2 did not correctly process the aforementioned SQL statements,
  which caused SQLCODE171.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement, so there will be no SQLCODE171.
  
  Additional keywords: SQLXML SQLXMLTABLE SQLFUNCTION SQLSUBSTR
  

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:

  UI26256

Modules/Macros

•    DSNXOSP1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI26256

     UP15/04/10 P F504

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.