IBM Support

IT16112: A CORRELATED SCALAR SUBQUERY IN AN UPDATE STATEMENT MAY NOT CORRECTLY RETURN SQL0811N

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A correlated scalar subquery in an UPDATE statement may not
    correctly return SQL0811N.
    
       SQL0811N   The result of a scalar fullselect, SELECT INTO
    statement, or VALUES INTO statement is more than one row.
    
    Assuming 2 rows in table B will match the same row of A:
    
    Case 1)	Expects success but SQL0811N is returned
       UPDATE TEST.A A SET COLUMN_B = (SELECT COLUMN_B FROM TEST.B B
    WHERE
      A.COLUMN_A=B.COLUMN_A FETCH FIRST 1 ROWS ONLY) ;
    
    Case 2) Expects SQL0811N but it succeeds
      UPDATE TEST.A_COL A SET COLUMN_B = (SELECT COLUMN_B FROM
    TEST.B_COL B WHERE
      A.COLUMN_A=B.COLUMN_A ORDER BY COLUMN_A FETCH FIRST 2 ROWS
    ONLY) ;
    

Local fix

  • Workaround:
    db2set -im DB2COMPOPT=NO_UPDATE_COMP
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 11.1 Fix Pack 1                               *
    ****************************************************************
    

Problem conclusion

  • Problem fixed in DB2 11.1 FP1
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT16112

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-07-13

  • Closed date

    2016-12-20

  • Last modified date

    2016-12-20

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

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

    IT18022

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
14 December 2020