PI19804: SQLCODE401 FROM DSNXOBFC FOR A SQL PROCEDURE WHEN USING INLIST IN IF STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Sqlcode401 from dsnxobfc for a sql procedure when using inlist
  in if statement
  error will be received when coding something like this
  p1: begin
      decalre ws_types     char(2) default 'SN';
      declare ws_typew     char(2) default 'WS';
  
      if invar in (ws_types, ws_typew) then
         set outvar = 0;
      end if;
  
  Additional symptoms and search keywords
  SQLIN, SQLCODE401, SQLPROCEDURE, SQLSP, SQLSTOREDPROC
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of a compiled SQL  *
  *                 scalar function or native SQL procedure      *
  *                 containing control statement with IN-LIST    *
  *                 predicate which references SQL variables.    *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -401 or unexpected ABEND0C4     *
  *                      or ABEND04E may occur for a CREATE      *
  *                      PROCEDURE/FUNCTION statement that       *
  *                      contains control statement with an      *
  *                      IN-LIST predicate which references SQL  *
  *                      variables.                              *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following examples help to illustrate the problem.
  
  Example#1. statement which issued SQLCODE -401 incorrectly.
  CREATE PROCEDURE SYSADM.CTEST(
    IN PCTYPE CHAR(4),
    OUT PRC INTEGER)
    LANGUAGE SQL
    DETERMINISTIC
    MODIFIES SQL DATA
    DYNAMIC RESULT SETS 1
    DISABLE DEBUG MODE
    PTEST: BEGIN
      DECLARE WC_CTYPE_PGMS CHAR(4) DEFAULT 'PGMS';
      DECLARE WC_CTYPE_PGMD CHAR(4) DEFAULT 'PGMD';
      IF PCTYPE IN (WC_CTYPE_PGMS, WC_CTYPE_PGMD) THEN
        SET PRC = 0;
      END IF;
      RETURN;
    END PTEST?
  
  Example#2. statement which issued ABEND0C4.
  CREATE PROCEDURE SYSADM.CTEST(
    IN PCTYPE CHAR(4),
    OUT PRC INTEGER)
    LANGUAGE SQL
    DETERMINISTIC
    MODIFIES SQL DATA
    DYNAMIC RESULT SETS 1
    DISABLE DEBUG MODE
    PTEST: BEGIN
      DECLARE WC_CTYPE_PGMS CHAR(4) DEFAULT 'PGMS';
      DECLARE WC_CTYPE_PGMD CHAR(4) DEFAULT 'PGMD';
      IF (CASE
            WHEN PCTYPE IN ('PGMS', 'PGMM', WC_CTYPE_PGMD, 'PGNN')
            THEN 'PGMM'
            ELSE 'PGMN'
          END
         ) = 'PGMM' THEN
        SET PRC = 0;
      END IF;
      RETURN;
    END PTEST?
  
  Example#3. statement which issued ABEND04E.
  CREATE PROCEDURE SYSADM.CTEST(
    IN PCTYPE CHAR(4),
    OUT PRC INTEGER)
    LANGUAGE SQL
    DETERMINISTIC
    MODIFIES SQL DATA
    DYNAMIC RESULT SETS 1
    DISABLE DEBUG MODE
    PTEST: BEGIN
      DECLARE WC_CTYPE_PGMS CHAR(4) DEFAULT 'PGMS';
      DECLARE WC_CTYPE_PGMD CHAR(4) DEFAULT 'PGMD';
      IF PCTYPE IN ('PGMS', 'PGMN', WC_CTYPE_PGMD, 'PGMG') THEN
        SET PRC = 0;
      END IF;
      RETURN;
    END PTEST?
  
  DB2 does not process the SQL variables in the IN-LIST predicate
  properly which can result in the incorrect SQLCODE or unexpected
  ABEND.
  

Problem conclusion

• DB2 has been modified correctly to process SQL variables in the
  IN-LIST predicate to avoid the problem.
  
  Additional keywords: SQLCODE401 SQLIN SQLCREATE SQLSP
                       SQLSTOREDPROC SQLNATIVESQLPL SQLUDF
                       SQLPROCEDURE
  

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:

  UI20622

Modules/Macros

• DSNXOP0
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI20622

     UP14/09/03 P F409

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.