LI74989: SQL0901N IS RETURNED WHEN RUNNING MERGE STATEMENT WITH INSERT CLAUSE THAT USES A SCALAR SQL FUNCTION IN A DPF ENVIRONMENT.

DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• When using MERGE statement with INSERT clause that uses scalar
  sql function in a DPF environment, SQL0901n error is returned.
  
  EX:
  
  create table <target_table> (tc1 int not null, tc2 int, tc3 int)
  partitioning key (tc1);
  create table <source_table> (sc1 int not null, sc2 int, sc3
  int);
  
  create function double1(inparm1 int) returns int
  language sql deterministic no external action reads sql data
  begin atomic
    declare var1 int;--
    set var1 = inparm1 * 2;--
    return (var1);--
  end;
  
  merge into <target_table> using (select sc1, sc2, sc3 from
  source_table) as src (ssc1, ssc2, ssc3) on (ssc1 = tc1)
  when matched then
       update set tc2 = ssc2
  when not matched then
       insert values (ssc1, ssc2, double1(ssc3));
  --SQL0901N  The SQL statement failed because of a non-severe
  system error.
  --Subsequent SQL statements can be processed.  (Reason "Not
  exactly one
  --non-subquery input qun: ambiguous nextQun".)  SQLSTATE=58004
  
  
  
  THE ISSUE CRITERIA:
  
  -- DPF
  -- Merge with INSERT clause
  -- INSERT clause references a scalar SQL function
  

Local fix

• Modify each partitioning key column in the INSERT clause to a
  non-trivial expression like the following:
  
  
  
  merge into <target_table> using (select sc1, sc2, sc3 from
  <source_table>) as src (ssc1, ssc2, ssc3) on (ssc1 = tc1)
  when matched then
       update set tc2 = ssc2
  when not matched then
       insert values (case when (1=1) then ssc1 end, ssc2,
  double1(ssc3));
  

Problem summary

• When using MERGE statement with INSERT clause that uses scalar
  sql function in a DPF environment, SQL0901n error is returned.
  

Problem conclusion

• First Fixed in Db2 v9.1 FP9
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  LI74971

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP

• R950 PSY

     UP