PI35998: SQLCODE -199 WHEN USING KEYWORD NULL IN A MERGE STATEMENT UNDER THE VALUES CLAUSE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Running a MERGE statement with the NULL keyword in the VALUES
  clause will cause a SQLCODE -199 SQLCODE199 in DB2 11 for z/OS
  while it works fine in DB2 10 for z/OS.
  

Local fix

• Change the NULL keyword to CAST(NULL AS data_type)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of MERGE           *
  *                 statement with NULL keyword in the USING     *
  *                 VALUES clause.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected SQLCODE -199 is issued       *
  *                      when a MERGE statement references the   *
  *                      NULL keyword in its USING VALUES        *
  *                      clause.  Now we allow this              *
  *                      scenario instead of issuing             *
  *                      SQLCODE -199.                           *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following example helps to illustrate the problem.
  
  CREATE TABLE T1(C1 CHAR(10), C2 CHAR(10));
  
  SQLCODE -199 will be issued for the following statement:
  
  MERGE INTO T1 T
  USING (VALUES (NULL)) AS S(C2)
  ON T.C2 = S.C2
  WHEN MATCHED THEN
    UPDATE SET T.C1 = S.C2
  WHEN NOT MATCHED THEN
    INSERT (T.C1) VALUES (S.C2)
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;
  
  The NULL keyword is not allowed in DB2 11 as an expression in
  the source-table of a MERGE statement.
  
  After applying this fix, the above example works without
  DB2 issuing a negative SQLCODE.
  

Problem conclusion

• DB2 is updated to process the MERGE statement with NULL
  keyword in its USING VALUES clause.
  
  Additional keywords: SQLMERGE SQLCODE199 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:

  UI28623

Modules/Macros

•    DSNHSM1A
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI28623

     UP15/07/15 P F507

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.