IBM Support

PI29102: SQLCODE4742 RC1 FOR RUNNING STATIC QUERY WITH INSERT INTO SELECT

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • sqlcode4742 rc1 for running a static query with insert into
    select even the explain of the package shows that the query gets
    properly accelerated.
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 for z/OS who use query      *
    *                 acceleration.                                *
    ****************************************************************
    * PROBLEM DESCRIPTION: When the query is a self-referencing    *
    *                      INSERT with sub-select,                 *
    *                      SQLCODE -4742 RC1 is issued during      *
    *                      bind.  This APAR will fix it.           *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    SQLCODE -4742 RC1 will be issued when the query to be offloaded
    is a self-referencing INSERT with sub-select.
    
    For example,
    INSERT INTO   T1
    (SELECT * FROM T2 A
       WHERE C1 IN (3,2,3,4,5,6,7)
       AND NOT EXISTS (SELECT 1 FROM T1 B
                         WHERE A.C1=B.C1 )
       );
    
    In the above query, T1 is the INSERT target table, and it is
    also referenced in the sub-select. Thus, SQLCODE4742 RC1 will
    be issued.  PI29102 will fix this problem.
    
    Keywords:
    IDAAV4R1/K
    

Problem conclusion

  • This APAR will fix the SQLCODE4742 RC1 error
    message issued during bind of a self-referencing
    INSERT with sub-select.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI29102

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-11-06

  • Closed date

    2015-01-20

  • Last modified date

    2015-03-03

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

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

    UI24527 UI24528

Modules/Macros

  •    DSNXONZA
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI24527

       UP15/02/05 P F502

  • RB10 PSY UI24528

       UP15/02/05 P F502

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"}],"Version":"A10","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 March 2024