PI30369: AN EXPECTED ERROR TOKEN OF *N IN THE SQLCA MESSAGE OF SQLCODE407AT EXECUTION TIME NOT ISSUED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• AN EXPECTED ERROR TOKEN OF *N IN THE SQLCA MESSAGE OF SQLCODE407
  AT EXECUTION TIME NOT ISSUED
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of SQL  *
  *                 statements that fail with an SQLCODE -407.   *
  ****************************************************************
  * PROBLEM DESCRIPTION: No expected column name or *N is        *
  *                      returned in the SQLCODE -407            *
  *                      (SQLCODE407) execution error message.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When an SQL statement fails with an SQLCODE -407 and the failure
  is caused by an execution error during the host variable bind-in
  process, no column name or expected error token *N is returned.
  
  
  The following example helps to illustrate the problem.
  
  1. Create a Table T1 with one of the columns defined as
     NOT NULL.
  
     CREATE TABLE T1 (C1 INTEGER, C2 VARCHAR(100) NOT NULL);
  
  
  2. Insert a NULL value for C2, the column defined as NOT NULL.
  
     HOSTVARS = (NULLIF('A','A'));
  
     INSERT INTO T1 VALUES(1, ?);
  
  
  3. Check the result.
  
    SQLCODE = -407,
    ERROR:  AN UPDATE, INSERT, OR SET VALUE IS NULL,
    BUT THE OBJECT COLUMN  CANNOT CONTAIN NULL VALUES
  
  Note the blank space following 'COLUMN'.
  
  A NULL insert value is specified for a column defined as NOT
  NULL, so an SQLCODE -407 will be issued due to this execution
  time error. However, DB2 does not return the column name or the
  error token *N as expected.
  
  For this example, the expected SQLCODE -407 error message is as
  follows.
  
    SQLCODE = -407,
    ERROR:  AN UPDATE, INSERT, OR SET VALUE IS NULL,
    BUT THE OBJECT COLUMN *N CANNOT CONTAIN NULL VALUES
  

Problem conclusion

• The code in DB2 is modified to return *N for an SQL statement
  that terminates during the host variable bind-in process with an
  SQLCODE -407 execution error.
  
  Additional Keywords: SQLCODE407 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:

  UI24644 UI24645

Modules/Macros

•    DSNXRIB9 DSNXRID9 DSNXRIHB DSNXRIHD DSNXRIHL
  DSNXRIHR DSNXRIHS DSNXRIS9 DSNXRIVR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI24644

     UP15/02/10 P F502

• RB10 PSY UI24645

     UP15/02/10 P F502

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.