DB2 - Problem description
Problem IT16877 | Status: Closed |
YOU MAY ENCOUNTER FODC APPLICATION ERROR WHEN EXECUTING QUERIES WITH OUTER JOIN AND COALESCE EXPRESSION. | |
product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problem description: | |
When using DB2 10.1 FP4 or FP5 you may encounter FODC_AppErr or -901 error when the following conditions are present: 1. Query has OUTER JOIN 2. The OUTER JOIN has join predicate whose one operand is column from row preserving side and the other operand is a COALESCE expression over column from NULL producing side. A sample query is shown: select * from t2 left outer join (select * from t1 union select * from t1 ) as tt on t2.c1 = COALESCE(tt.c2, 1) Typical symptoms are : 1. A -901 error and messages in the db2diag.log like 2016-06-06-16.37.39.724351-240 I4633528A647 LEVEL: Error PID : 7668074 TID : 23392 PROC : db2sysc 4 INSTANCE: db2inst NODE : 004 DB : DBNAME APPHDL : 0-10749 APPID: 162.103.180.14.37369.160606201208 AUTHID : X416216 HOSTNAME: test.torolab.ibm.com EDUID : 23392 EDUNAME: db2agntp (DBNAME) 4 FUNCTION: DB2 UDB, table Q services, sqlkt_pack_tuple, probe:30 MESSAGE : DIA0001E An internal error occurred. Report the following error code : "Line=02441, rc1=0x82170001, rc2=0x00000000, rc3=0, msg=cold=NULLABLE zvalue=NONULLS col(0)". 2016-06-06-16.37.39.758067-240 I4645078A942 LEVEL: Severe PID : 7668074 TID : 23392 PROC : db2sysc 4 INSTANCE: db2inst NODE : 004 DB : DBNAME APPHDL : 0-10749 APPID: 162.103.180.14.37369.160606201208 AUTHID : X416216 HOSTNAME: test.torolab.ibm.com EDUID : 23392 EDUNAME: db2agntp (DBNAME) 4 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) 0x82170001 (2) 0x00000001 (3) 0x00000000 (4) 0x00000000 (5) 0xFFFFFE0C (6) 0x00000004 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 2. The stack file will have a signature similar to : -------Frame------ ------Function + Offset------ 0x090000000052CF14 pthread_kill + 0xD4 0x090000000B1B20D0 sqloDumpEDU + 0xD0 0x090000000A1CB050 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 + 0x104 0x090000000C8EB5E0 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x534 0x090000000C8EC1AC sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28 0x090000000C8EBB58 sqlzeMapZrc__FP8sqeAgentUiUlT2P5sqlcaiPC12sqlzeContextb + 0x114 0x090000000DC46774 sqlritqb__FP8sqlrr_cb + 0x304 0x090000000C51CC68 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x28 0x090000000B8DDE58 sqlrr_dss_router__FP8sqlrr_cb + 0xC3C 0x090000000CCEA6B0 sqlrr_subagent_router__FP8sqeAgentP12SQLE_DB2RA_T + 0x428 0x090000000CCEB928 @83@sqleSubRequestRouter__FP8sqeAgentPUiPUl + 0x3F8 0x090000000CCFF87C sqleProcessSubRequest__FP8sqeAgent + 0x580 0x090000000C8C0EF4 RunEDU__8sqeAgentFv + 0x67C 0x090000000C8BD150 EDUDriver__9sqzEDUObjFv + 0x13C 0x090000000C8BCFD8 sqlzRunEDU__FPcUi + 0x10 0x090000000C8D3848 sqloEDUEntry + 0x264 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * See SYSROUTE APARs to see where this APAR is addressed? * **************************************************************** | |
Local Fix: | |
A possible workaround is lower optimization level from default 5 to 0,1 or 2. | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT16934 IT16936 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 01.09.2016 08.05.2017 08.05.2017 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |