PK55145: UNEXPECTED SQLCODE407 ISSUED WITH NULL VALUE HOSTVAR IN MINUS EXPRESSION OF A PREDICATE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Unexpected sqlcode407 issued when Null value is passed to the
  hostvar followed by a minus expression in predicate.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V910 Native SQL procedure users.     *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected SQLCODE407 could be issued   *
  *                      from DSNXRBND at location 100 when a    *
  *                      native SQL procedure is invoked.This    *
  *                      happens if the procedure contains a     *
  *                      query with a predicate which compares   *
  *                      a column that is defined as NOT NULL    *
  *                      against an expression with a unary      *
  *                      minus applied to an SQL variable, and   *
  *                      at execution time this SQL variable     *
  *                      contains the NULL value.                *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Unexpected SQLCODE407 could be issued from DSNXRBND at
  location 100 when a native SQL procedure is invoked.
  This happens if the procedure contains a query with a
  predicate which compares a column that is defined as
  NOT NULL against an expression with a unary minus
  applied to an SQL variable, and at execution time this
  SQL variable contains the NULL value.
  
  The following example could recreate the problem:
  CREATE TABLE T1(C1 INT NOT NULL);
  
  CREATE PROCEDURE MYTEST1 (INOUT P1 INT)
    BEGIN
       DECLARE A INTEGER;
       SELECT 11 INTO P1 FROM T1 WHERE C1=-A;
    END
  
  SQLCODE -407 is issued when A is NULL.
  

Problem conclusion

• DB2 is fixed to handle the minus expression correctly.
  Additional Keywords: SQLNATIVESQLPL SQLMINUS SQLPREDICATE
                       SQLNULL
  

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:

  UK31415

Modules/Macros

• DSNXOEXD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK31415

     UP07/12/05 P F712

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.