PI27928: SQLINCORROUT OF NATIVE STORED PROCEDURE WHICH CONTAINS LOB VARIABLES AND SUBSTR FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Sqlincorrout can happen when using native stored procedure which
  contains lob variables and substr function.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of      *
  *                 native SQL PL procedures with LOBs (Large    *
  *                 Objects).                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result can be returned     *
  *                      from a procedure that uses the SUBSTR   *
  *                      function with LOB objects.              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result can be returned from a procedure that uses
  the SUBSTR function with LOB objects.
  
  The incorrect result can occur because DB2 fails to propagate
  the resultant LOB length correctly when creating a LOB locator
  from another LOB locator.
  
  The following example helps to illustrate the problem.
  
  Step 1. Create a Table with a LOB column.
  
    CREATE TABLE TCLOB ( C1 CHAR(12), C2 CLOB(1K), C3 SMALLINT);
  
  
  Step 2. Create a procedure with LOB parameters.
  
    CREATE PROCEDURE CLOBSP1 (IN  PARM1 CLOB(1K),
                              OUT PARM2 CLOB(1K),
                              OUT PARM3 CLOB(1K))
    VERSION V1
    LANGUAGE SQL
    DISALLOW DEBUG MODE
    MODIFIES SQL DATA
    WLM ENVIRONMENT FOR DEBUG MODE DSNXWLM
    SPBODY:BEGIN
  
    SET PARM2 =SUBSTR(PARM1,1,9)|| '679'||SUBSTR(PARM1,13,3);
    SET PARM3 =SUBSTR(PARM2,1,3)||'1234'||SUBSTR(PARM2,7,10);
  
    INSERT INTO TCLOB VALUES ('CLOBSP1ROW1', PARM2,LENGTH(PARM2));
    INSERT INTO TCLOB VALUES ('CLOBSP1ROW2', PARM3,LENGTH(PARM3));
  
    END SPBODY!
  
  
  Step 3. When executing this procedure, an incorrect result can
  be returned.
  

Problem conclusion

• DB2 code has been modified to correct the problem which can
  result in an incorrect result being returned when using a LOB
  with the SUBSTR function for a native SQL procedure.
  
  Additional Keywords: DB2INCORR/K INCORROUT SQLLOB
                       SQLINCORROUT SQLSUBSTR SQLUDF
                       SQLSP SQLNATIVESQLPL SQLSTOREDPROCEDURE
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI23959 UI23960

Modules/Macros

•    DSNOTCSO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI23959

     UP15/01/03 P F501 Ž

• RB10 PSY UI23960

     UP15/01/03 P F501 Ž

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.