DB2 - Problem description
Problem IC68316 | Status: Closed |
EXECUTION OF A QUERY CONTAINING SCALAR SUBQUERY OUTPUT COLUMN REFERENCE MAY FAIL WITH SQL0901N IN A DPF ENVIRONMENT | |
product: | |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 | |
Problem description: | |
Execution of a query may fail with SQL0901N if the following conditions are satisfied: - Database is Data Partitioning Feature (DPF) enabled - Query or a view it references or one of its nested subselects contains a scalar subquery (SSQ) reference in the output column list - SSQ contains an equi-join predicate that directly references a local column and a column from the query that references SSQ - An SSQ output column (SSQOC) is from a base table or view (on at least one base table) that is referenced inside SSQ - SSQOC is used for output in the query column that references SSQ - SSQOC is not used in any predicates in the query that references SSQ The SQL0901N will look something like: SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "probe 030 in sqlkt_pack_tuple(), msg=cold=NULLABLE zvalue=NONULLS col(".) SQLSTATE=58004 Corresponding entries in the db2diag.log may look something like: YYYY-MM-dd-hh.mm.SS.ssssss+sss xxxxxxxxxxx LEVEL: Severe PID : nnnnnnn TID : nnnn PROC : db2sysc n INSTANCE: xxxxxxx NODE : nnn DB : xxxxxxxx APPHDL : nnn-nnnnn APPID: *xxxx.xxxxxxx.nnnnnnnnnnnn AUTHID : xxxxxxx EDUID : nnnn EDUNAME: db2agntp (xxxxxxxx) n FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:300 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 70 sqlerrmc: probe 030 in sqlkt_pack_tuple(), msg=cold=NULLABLE zvalue=NONULLS col( sqlerrp : SQLRI088 sqlerrd : (1) 0xhhhhhhhh (2) 0xhhhhhhhh (3) 0xhhhhhhhh (4) 0xhhhhhhhh (5) 0xhhhhhhhh (6) 0xhhhhhhhh sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * EXECUTION OF A QUERY CONTAINING SCALAR SUBQUERY OUTPUT * * COLUMN * * REFERENCE MAY FAIL WITH SQL0901N IN A DPF ENVIRONMENT * * * * Execution of a query may fail with SQL0901N if the following * * * * conditions are satisfied: * * * * - Database is Data Partitioning Feature (DPF) enabled * * * * - Query or a view it references or one of its nested * * subselects * * contains a scalar subquery (SSQ) reference in the output * * column * * list * * * * - SSQ contains an equi-join predicate that directly * * references a * * local column and a column from the query that references SSQ * * * * - An SSQ output column (SSQOC) is from a base table or view * * (on * * at least one base table) that is referenced inside SSQ * * * * - SSQOC is used for output in the query column that * * references * * SSQ * * * * - SSQOC is not used in any predicates in the query that * * * * references SSQ * * * * * * * * The SQL0901N will look something like: * * * * SQL0901N The SQL statement failed because of a non-severe * * * * system error. * * * * Subsequent SQL statements can be processed. (Reason "probe * * 030 * * in * * * * sqlkt_pack_tuple(), msg=cold=NULLABLE zvalue=NONULLS col(".) * * * * SQLSTATE=58004 * * * * * * * * Corresponding entries in the db2diag.log may look something * * * * like: * * * * YYYY-MM-dd-hh.mm.SS.ssssss+sss xxxxxxxxxxx LEVEL: * * Severe * * PID : nnnnnnn TID : nnnn PROC : * * db2sysc * * n * * * * INSTANCE: xxxxxxx NODE : nnn DB : * * * * xxxxxxxx * * * * APPHDL : nnn-nnnnn APPID: * * *xxxx.xxxxxxx.nnnnnnnnnnnn * * AUTHID : xxxxxxx * * * * EDUID : nnnn EDUNAME: db2agntp (xxxxxxxx) n * * FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, * * * * probe:300 * * * * DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes * * * * sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 70 * * sqlerrmc: probe 030 in sqlkt_pack_tuple(), msg=cold=NULLABLE * * * * zvalue=NONULLS col( * * * * sqlerrp : SQLRI088 * * * * sqlerrd : (1) 0xhhhhhhhh (2) 0xhhhhhhhh (3) * * * * 0xhhhhhhhh * * * * (4) 0xhhhhhhhh (5) 0xhhhhhhhh (6) * * * * 0xhhhhhhhh * * * * sqlwarn : (1) (2) (3) (4) (5) (6) * * * * (7) (8) (9) (10) (11) * * * * sqlstate: * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.8 Fix Pack 3. * **************************************************************** | |
Local Fix: | |
None exists apart from manually rewriting the query in question to preclude the conditions described | |
available fix packs: | |
DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows | |
Solution | |
Problem was first fixed in DB2 Version 9.8 Fix Pack 3. | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 30.04.2010 10.01.2011 10.01.2011 |
Problem solved at the following versions (IBM BugInfos) | |
9.8.FP3 | |
Problem solved according to the fixlist(s) of the following version(s) | |
9.8.0.3 |