A fix is available
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
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