IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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

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

Problem conclusion

  • Fixed in DB2 v95FP6.
    

Temporary fix

  • See Local Fix.
    

Comments

APAR Information

  • APAR number

    LI74971

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-10-29

  • Closed date

    2010-09-14

  • Last modified date

    2010-09-14

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

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

    IC64191 LI74989

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R910 PSY

       UP

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
14 September 2010