|
Problem IC74489
|
Status: Closed |
DB2 CRASHED DURING "INSERT FROM SELECT" STATEMENT IN DPF MODE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In similar queries where we are doing an INSERT FROM SELECT
during DPF we might encounter an instance crash because temp
records are prematurely cleaned up in coordinator node.
Sample Query:
select
case when exists
( select prj.PROJECT_ID from project prj
INNER JOIN irb_protocol irbp on irbp.PROJECT_ID =
prj.PROJECT_ID AND prj.PROJECT_ID = ?
INNER JOIN RULE_IRB_PROTOCOL rirbp on
rirbp.IRB_PROTOCOL_ID = irbp.IRB_PROTOCOL
INNER JOIN RULE r on rirbp.RULE_ID = r.RULE_ID AND
r.FILTER = 'Filter 5'
where 'Y' =
( Select Coalesce(researchOptOut, 'N')
from Demographics d,
xmltable('$d/Person/demographicsCollection/demographics
[(effectiveStartTimeStamp <=
$curruntT) and (effectiveEndTimeStamp >= $curruntT)]'
passing current timestamp as
"curruntT", d."XMLDATA" as "d"
columns researchOptOut
VARCHAR(24) path './../../researchOptOut' ) ex
where upi = (Select ex.upi
from
xmltable('$d/child::node()/personUpi'
passing cast(? as
XML) as "d"
columns upi
VARCHAR(24) path '.') ex )
)
)
then 1 else 0 end from sysibm.sysdummy1
Stack:
<StackTrace>
-------Frame------ ------Function + Offset------
0x09000000006316BC pthread_kill + 0x88
0x09000000086BAA04 sqloDumpEDU + 0x34
0x09000000079F77BC sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
0xFC
0x0900000007EB4BCC
sqldcsl_dumpBadPage__FP8sqeAgentP8SQLD_TCBP16SQLB_OBJECT_DESCP11
SQLB_FIX_CBUiP16SQLDCSL_PAGESLOTiT7CUle
+ 0xBF0
0x090000000A512848
sqldcsl_fetchDirect__FP8sqeAgentP8SQLD_TCBP12SQLD_TCB_CSLP16SQLD
CSL_FETCH_CB
+ 0x294
0x090000000851E43C
sqldcsl_fetchRow__FP8sqeAgentP8SQLD_TCBP12SQLD_TCB_CSLP16SQLDCSL
_FETCH_CB
+ 0x470
0x0900000009A71D34
xmls_FetchRecord__FP11XMLSTORE_CBR8XMLS_XIDPPcPUsT4UcP13XMLS_WA_
TOKENPP8SQLD_TCB
+ 0xBB0
0x090000000A10FE20
collectXMLValueRidsInternal__12XmlrnRidListF8XMLS_XIDP17xmlDataD
escriptor
+ 0x4E4
0x090000000A146148
addXTempsFromDD__19SqlriXmlTempManagerFP17xmlDataDescriptorP8sql
rr_cb
+ 0x174
0x090000000A144C7C sqlritqa__FP8sqlrr_cb + 0x384
0x090000000740F84C sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
0x658
0x090000000A34278C sqlrr_dss_router__FP8sqlrr_cb + 0x34
0x09000000086F3324
sqlrr_subagent_router__FP8sqeAgentP12SQLE_DB2RA_T + 0x2C
0x0900000006EDEE44 sqleSubRequestRouter__FP8sqeAgentPUiT2 +
0x5F0
0x0900000007134EF8 sqleProcessSubRequest__FP8sqeAgent + 0xA14
0x0900000008B2F154 RunEDU__8sqeAgentFv + 0x2F4
0x090000000759C6B0 EDUDriver__9sqzEDUObjFv + 0xD8
0x09000000075A2264 sqloEDUEntry + 0x264
</StackTrace>
Example db2diag.log entries:
2011-01-03-11.17.07.817772-360 I51257335A6807 LEVEL: Severe
PID : 1417450 TID : 26736 PROC : db2sysc
0
INSTANCE: ciderdev NODE : 000 DB : CIDERWU
APPHDL : 0-131 APPID:
10.39.153.8.62676.110103171659
AUTHID : RENGB
EDUID : 26736 EDUNAME: db2agntp (CIDERWU) 0
FUNCTION: DB2 UDB, Common Storage Layer, sqldcsl_fetchRow,
probe:611
MESSAGE : ZRC=0x87A80001=-2019033087=SQLDCSL_BADPAGE "Bad Data
Page"
DIA8500C A data file error has occurred, record id is
"".
DATA #1 : String, 16 bytes
SQLDCSL_FETCH_CB
DATA #2 : SQLDCSL_FETCH_CB, PD_TYPE_CSL_FETCHCB, 56 bytes
SQLDCSL_FETCH_CB: Size:56
x0000 fixCB 0x07000000704a4c68
accessMethod: SQLB_POOL_RELATIVE
fixMode: 2 SQLBOLD/SQLBOLDS
buffptr: 0x0000000000000000
bpdPtr: 0x0000000000000000
dmDebugHdl: 0
objectPageNum: 9
empDiskPageNum: 4294967295
unfixFlags: 0
dirtyState: SQLBCLEAN
fixInfoFlags:
regEDUid: 0
Pagekey: {pool:5;obj:2;type:134} PPNum:9
Obj: {pool:5;obj:2;type:134} Parent={5;2}
==== |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 LUW ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Refer to APAR Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v9.7.4 or newer release. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
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 |
Fix provided in DB2 v9.7.4 and newer releases. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.02.2011
28.04.2011
28.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
