A fix is available
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 number
PI35998
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2015-02-27
Closed date
2015-06-17
Last modified date
2015-08-03
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
06 May 2020