|
Problem IC79679
|
Status: Closed |
QUERY REFERENCING A TABLE CONTAINING A GENERATED COLUMN MIGHT PRODUCE A -901 |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Under rare scenarios, DB2 might produce a SQL0901N if the
following conditions are true:
1. The query references a table with one or more generated
columns
2. The same table is part of one or more MQTs
3. The generated column(s) are not referenced in the MQT
In the db2diag.log, the issue appears as
2011-08-22-14.59.17.413828-240 I107409736E862 LEVEL: Severe
PID : 25891 TID : 192766011744 PROC :
db2sysc 1
INSTANCE: nypxt280 NODE : 001 DB : IMRSDM
APPHDL : 1-5889 APPID:
10.184.115.183.24068.1108221859
AUTHID : IMRSDB
EDUID : 93402 EDUNAME: db2agent (IMRSDM) 1
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: 30
sqlerrmc: Bad Plan; Unresolved QNC found
sqlerrp : SQLNG032
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x000000B9 (5) 0xFFFFFC1E (6)
0x00000001
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
<dumping into FODC>
2011-08-22-14.59.17.659189-240 E107459750E1259 LEVEL: Info
(Origin)
PID : 25891 TID : 192766011744 PROC :
db2sysc 1
INSTANCE: nypxt280 NODE : 001 DB : IMRSDM
APPHDL : 1-5889 APPID:
10.184.115.183.24068.1108221859
AUTHID : IMRSDB
EDUID : 93402 EDUNAME: db2agent (IMRSDM) 1
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
MESSAGE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 723 bytes
Compiler error stack for rc = -2145779603:
sqlnn_cmpl[420]
sqlng_main[2790]
sqlng_main[1656]
sqlng_build_thread[10]
sqlng_process_return_op[50]
sqlng_process_pipe_op[30]
sqlng_process_mate_op[1735]
sqlng_process_TQ_op[30]
sqlng_build_thread[40]
sqlng_scan_derived_table[40]
sqlng_build_thread[10]
sqlng_process_sort_op[20]
sqlng_process_nljn_op[50]
sqlng_build_thread[10]
sqlng_process_hsjn_op[60]
sqlng_build_thread[10]
sqlng_process_nljn_op[50]
sqlng_build_thread[10]
sqlng_process_TQ_op[30]
sqlng_build_thread[40]
sqlng_process_nljn_op[140]
sqlng_build_thread[10]
sqlng_process_f_iscan[110]
sqlng_build_TA_op[110]
sqlng_build_thread[100]
sqlng_walk_BF_chain[25]
sqlng_process_BF_node[20]
sqlng_process_parse_tree_n[994]
<followed by ADM5570W> |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V9.7 Fixpack 6 *
**************************************************************** |
| Local Fix: |
Create another MQT which references the generated column in the
select list. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.11.2011
20.07.2012
20.07.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
