PH09665: DECIMAL INPUT PARAMETER IN THE NESTED SQLPL STORED PROCEDURE MAY HAVE UNEXPECTED NULL VALUE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A stored procedure input parameter is used as parameter to call
  a nested stored procedure in a signal handler.
  The input parameter in the nested stored procedure is unexpected
  null, which causes the logic to fail.
  
  A simplified test case follows here, the logic raises a signal
  990C1 if
  the unexpected NULL value is detected.
  The test logic is as follows:
  1) call TEST_UPDATE(1) to initiate the test
  2) TEST_UPDATE establishes a signal handler
  3) TEST_UPDATE calls TEST_CHECK_ROW.
  4) TEST_CHECK_ROW.raises a signal to simulate some error
  situation
  5) The signal handler in TEST_UPDATE calls  TEST_CHECK_UPD
  6) TEST_CHECK_UPD checks the value of it's input parameter which
  is
  unexpected NULL and raises a signal to indicate the NULL value
  
  --#SET TERMINATOR #
  CREATE PROCEDURE TWFNI1C.TEST_CHECK_ROW
    (
      IN srcRowId  DECIMAL(19 , 0)
    )
      VERSION TEST_VERSION_DEBUG
      LANGUAGE SQL
      DETERMINISTIC
      READS SQL DATA
      DISABLE DEBUG MODE
      DYNAMICRULES RUN
      APPLICATION ENCODING SCHEME EBCDIC
      VALIDATE BIND
      BEGIN
          SIGNAL SQLSTATE '990C0'
          SET MESSAGE_TEXT='RAISED EXCEPTION';
      END
      #
  
  CREATE PROCEDURE TWFNI1C.TEST_CHECK_UPD
    (
      IN srcRowId      DECIMAL(19 , 0)
    )
      VERSION TEST_VERSION_DEBUG
      LANGUAGE SQL
      DETERMINISTIC
      MODIFIES SQL DATA
      DISABLE DEBUG MODE
      DYNAMICRULES RUN
      APPLICATION ENCODING SCHEME EBCDIC
      VALIDATE BIND
      BEGIN
  
         -- wants to do something with the input parameter
          IF srcRowId IS NULL THEN
           SIGNAL SQLSTATE '990C1'
          SET MESSAGE_TEXT='UNEXPECTED ERROR VARIABLE IS NULL';
          END IF;
  
       END
       #
  
  CREATE PROCEDURE TWFNI1C.TEST_UPDATE
    (
      IN srcRowId      DECIMAL(19 , 0)
    )
      VERSION TEST_VERSION_DEBUG
      LANGUAGE SQL
      DETERMINISTIC
      MODIFIES SQL DATA
      DISABLE DEBUG MODE
      DYNAMICRULES RUN
      APPLICATION ENCODING SCHEME EBCDIC
      VALIDATE BIND
      BEGIN
           DECLARE EXIT HANDLER FOR SQLSTATE VALUE '990C0'
            BEGIN
              -- the variable srcRowId is here null - WHY ?
              CALL TWFNI1C.TEST_CHECK_UPD(srcRowId);
            END;
  
          -- RAISES A SQLSTATE
          CALL TWFNI1C.TEST_CHECK_ROW(srcRowId);
       END#
  
  -- SO THEN WE CALL THE TEST_UPDATE METHOD WITH a DECIMAL which
  is not
  NULL and get the unexpected error that srcRowId is NULL
  call TWFNI1C.TEST_UPDATE(1)#
  --SQL error: SQLCODE = -438, SQLSTATE = 990C1, SQLERRMC =
  UNEXPECTED
  ERROR VARIABLE IS NULL. SQLCODE=-438, SQLSTATE=990C1,
  DRIVER=4.18.60
  
  Keywords: SQLSP SQLSTOREDPROC
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 and Db2 12 for z/OS users of      *
  *                 native SQL procedures.                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: An SQL variable can be assigned a null  *
  *                      value when the variable is passed as    *
  *                      an input parameter to a stored          *
  *                      procedure.                              *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  An SQL variable can be incorrectly assigned a null value when
  the variable is passed as an input parameter to a stored
  procedure.  The problem occurs when both of the following
  conditions are met:
  
  - The data type of SQL Variable is decimal.
  - The CALL statement returns an error.
  
  
  The following example helps illustrate the problem.
  
  1. Create a native stored procedure PROC1 with one input
     parameter of type Decimal. The procedure returns with
     an error.
  
       CREATE PROCEDURE PROC1(IN pIN DECIMAL(19,0))
         SIGNAL SQLSTATE '75002'#
  
  2. Create a second native stored procedure PROC2 that invokes
     the first procedure.
  
       CREATE PROCEDURE PROC2()
         BEGIN
           DECLARE var1 DECIMAL(19,0) DEFAULT 0;
           CALL PROC1(var1);
         END#
  
  3. Invoke procedure PROC2.
  
       CALL PROC2()#
  
  Inside procedure PROC2, variable var1 is incorrectly assigned a
  null value after the invocation of procedure PROC1. The value
  of variable var1 should remained unchanged.
  

Problem conclusion

• Db2 has been modified to process the CALL statement such that
  variables that are passed as input parameters remain unchanged
  on the occurrence of error.
  
  Additional Keywords: SQLNATIVESQLPL SQLSP SQLSTOREDPROC SQLUDF
                       SQLTRIGGER ZSA2
  

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:

  UI62629 UI62634

Modules/Macros

•    DSNXRSPL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI62634

     UP19/05/09 P F905 Ž

• RC10 PSY UI62629

     UP19/05/09 P F905 Ž

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.