IBM Support

PM85786: SQLCODE901 RETURNED IN ERROR ON A SIMPLE SELECT FROM A VIEW BASED ON A COMPLEX SELECT USING IDAA

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • SQL error: SQLCODE = -901, SQLSTATE = 58004, SQLERRMC = 42S02:
    ERROR:  Relation 'ACC004' does not exist. SQLCODE=-901,
    SQLSTATE=58004, DRIVER=4.13.111
    
    was returned for a simple select:
    
    select * from USER1.COMPLEX_VIEW
    
    when run through IDAA.
    

Local fix

  • Run the simple select in DB2 for z/OS or run the full select the
    view is based on in IDAA or DB2 for z/OS.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 9 for z/OS and DB2 10 for   *
    *                 z/OS who use IBM DB2 Analytics Accelerator   *
    ****************************************************************
    * PROBLEM DESCRIPTION: Offload of a query fails with           *
    *                      SQLCODE -901 with a message similar to  *
    *                      "relation 'acc004' does not exist"      *
    *                      where 'acc004' will vary depending on   *
    *                      the SQL statement.                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Offload of a query fails with SQLCODE -901 with a message
    similar to "relation 'acc004' does not exist" where 'acc004'
    will vary depending on the SQL statement.
    This specific failure can occur when a view is specified in the
    query and the view has nested table expressions with
    OUTER JOIN.
    For example:
    
    CREATE VIEW TESTVIEW AS(
    SELECT TX1.C1 AS TX1C1,TX2.C1 AS TX2C1
    FROM
                        (SELECT * FROM (
                            SELECT C2.C_NATIONKEY,
                            R_REGIONKEY,C_COMMENT,C_NAME
                            FROM CUSTOMER C2,
                            REGION WHERE C2.C_NATIONKEY =
                            R_REGIONKEY
                            ORDER BY C2.C_NATIONKEY, R_REGIONKEY
                            ) AS TXIN1 WHERE ((TXIN1.R_REGIONKEY>1
                                         OR TXIN1.C_NATIONKEY=3)
                                         AND TXIN1.C_COMMENT ='TEST'
                                         AND TXIN1.C_NAME='TEST2'
                                              )
                         ) AS TX1(C1,C2,C3,C4)
                         LEFT JOIN
                         (SELECT * FROM (
                            SELECT C1.C_NATIONKEY,
                            R_REGIONKEY,C_ACCTBAL
                            FROM CUSTOMER C1,
                            REGION WHERE C_NATIONKEY =
                            R_REGIONKEY
                            AND  C1.C_CUSTKEY >1
                            ORDER BY C1.C_NATIONKEY, R_REGIONKEY
                            ) AS TXIN2  WHERE TXIN2.R_REGIONKEY=2
                                              AND TXIN2.C_ACCTBAL>0
                         ) AS TX2(C1,C2,C3)
                           ON TX1.C1 = TX2.C1
    );
    
    
    A query that references TESTVIEW and is offloaded will return
    the SQLCODE -901 error.
    
    Additional keywords:
    SQLCODE901 IDAAV2R1/K IDAAV3R1/K SQLVIEW SQLOUTERJOIN
    

Problem conclusion

  • DB2 code is fixed to resolve the SQLCODE -901 so that the
    query offloads successfully.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM85786

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-03-27

  • Closed date

    2013-04-25

  • Last modified date

    2013-06-04

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

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

    UK93816 UK93817

Modules/Macros

  • DSNXONZC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK93816

       UP13/05/05 P F305

  • R910 PSY UK93817

       UP13/05/05 P F305

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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
04 June 2013