PH07195: ERRONEOUS SQLCODE -136 CAN BE ISSUED FOR A QUERY WHEN THE SORT KEY LENGTH IS NOT ACTUALLY TOO LONG

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Erroneous SQLCODE -136 can be issued for a query when the sort
  key length is not actually too long. This problem can happen for
  queries containing table expressions.                  (d85127)
  SQLCODE136 SQLTABLEEXPR
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 11 and 12 z/OS users who use:                        *
  * 1. SUBSTR built-in function with a BINARY                    *
  *    or VARBINARY string                                       *
  * 2. View, table expression, or SQL table                      *
  *    user  defined function containing                         *
  *    multiple outer joins and LOB columns.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * APAR PH07195 fixes the following                             *
  * problems:                                                    *
  * 1. Incorrect output may occur for an                         *
  * SQL statement that references a                              *
  * SUBSTR built-in function.                                    *
  *  1.1. Incorrect type may be returned                         *
  *       for the following cases:                               *
  *    a. The first argument is a                                *
  *       VARBINARY string and the third                         *
  *       argument is a literal constant                         *
  *       and it is less than 255.                               *
  *    b. The first argument is a                                *
  *       BINARY string, the second                              *
  *       argument is an expression,                             *
  *       and the third argument is                              *
  *       not specified.                                         *
  *    c. The first argument is a                                *
  *       BINARY string and the third                            *
  *       argument is an expression.                             *
  *  1.2. Incorrect atual length may be                          *
  *       returned if the first argument                         *
  *       is a BINARY string and the third                       *
  *       argument is an expression.                             *
  * After this APAR fix, some possible                           *
  * changes include, but are not limited                         *
  * to, access path and index changes.                           *
  *                                                              *
  * 2. Incorrect SQLCODE -136 may be                             *
  * issued for an SQL statement that                             *
  * references a view, table expression,                         *
  * or SQL table user defined function                           *
  * containing multiple outer joins and                          *
  * LOB columns.                                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  For example,
  1. Incorrect actual length
   SELECT LENGTH(SUBSTR(BINARY(BX'12345678', 20),1,SMALLINT(3)))
       FROM SYSIBM.SYSDUMMY1;
   The actual length returned from the SUSBTR built-in function
   is 20. The correct actual length should be 3.
  2. Incorrect type
    a. First argument is a binary string, second argument is
       an expression, and the third argument is not specified.
       SELECT SUBSTR(BINARY(BX'12345678', 20),1+1)
            FROM SYSIBM.SYSDUMMY1;
        The type returns from the SUBSTR built-in function
        is BINARY. The correct type should be VARBINARY.
  
    b. The first argument is VARBINARY string, and the third
       argument is a constant and it is less than 255.
  
       SELECT SUBSTR(VARBINARY(BX'12345678', 20),1,3)
             FROM SYSIBM.SYSDUMMY1;
       The type returns from the SUBSTR built-in
       function is VARBINARY. The correct type should be
       BINARY.
    c. The first argument is a BINARY string and the third
       argument is an expression.
     SELECT SUBSTR(BINARY(BX'12345678', 20),1,SMALLINT(3))
           FROM SYSIBM.SYSDUMMY1;
  
      The type returns from the SUBSTR built-in
      function is BINARY. The correct type should be
      VARBINARY.
  3. SQLCODE -136 may be issued incorrectly for an SQL statement
  that references a view, table expression, or SQL table user
  defined funtion containing multiple outer joins and LOB column
  because Db2 did not handle the LOB column correctly when sort
  merge join access path is chosen.
     SELECT TB_WAREHOUSE_BTT4.WAREHOUSE_ID,
             TB_WAREHOUSE_BTT4.WAREHOUSE_NAME,
             VW_SELVW05_V01.WAREHOUSE_ID,
             VW_SELVW05_V01.*,
             TB_WAREHOUSE_BTT4.*
       FROM SC001031.TB_WAREHOUSE_BTT4
        FOR BUSINESS_TIME
         AS OF   '2011-04-05-05.05.05.555555'
        FOR SYSTEM_TIME
         AS OF   '2011-04-05-05.05.05.555555555' BTT4
       FULL OUTER JOIN SC001031.VW_SELVW05_V01
        FOR SYSTEM_TIME
         FROM    '2011-04-05-04.04.04.444444444'
         TO      '2011-04-05-06.06.06.666666666' V01
         ON    BTT4.WAREHOUSE_ID = V01.WAREHOUSE_ID;
  SQLCODE -136 is issued incorrectly for the SELECT statement
  because Db2 did not obtain the LOB length correctly for a LOB
  column which is defined in table TB_WAREHOUSE_BTT4.
  

Problem conclusion

• Db2 has been modified to correctly process the
  aforementioned problems.
  Additional Keywords:  SQLSUBSTR SQLBINARY
                        SQLINCORROUT INCORROUT SQLINCORR
                        DB2INCORR/K
                        SQLLEFTJOIN SQLLEFT
                        SQLRIGHTJOIN SQLRIGHT
                        SQLOUTERJOIN SQLFULLJOIN
                        SQLOUTER  SQLFULL SQLJOIN
                        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:

  UI68740 UI68741

Modules/Macros

• DSNXOBFF DSNXOCT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI68741

     UP20/04/09 P F004 ¢

• RB10 PSY UI68740

     UP20/04/09 P F004 ¢

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.