On a DPF environment, database might crash during insert from an
XMLTABLE when XMLCAST with input from a parameter marker is
involved.
In db2diag.log, you will see the following entries:
2009-12-08-16.43.46.870142-300 I116523A1787 LEVEL: Severe
(Origin)
PID : 3064396 TID : 51713 PROC : db2sysc 0
INSTANCE: kxie NODE : 000 DB : TPOX_DSS
APPHDL : 0-289 APPID: 9.26.119.196.3521.091208214346
AUTHID : KXIE
EDUID : 51713 EDUNAME: db2agntp (TPOX_DSS) 0
FUNCTION: DB2 UDB, Common Storage Layer,
sqldcsl_getRecDataAndLength, probe:2414
MESSAGE : ZRC=0x87A80001=-2019033087=SQLDCSL_BADPAGE "Bad Data
Page"
DIA8500C A data file error has occurred, record id is "".
DATA #1 : signed integer, 8 bytes
0
DATA #2 : Hexdump, 20 bytes
0x070000004B300038 : 0000 0000 0000 0000 7FB4 0000 7FB3 0000
................
0x070000004B300048 : 0000 0014 ....
CALLSTCK:
[0] 0x09000000906BA3DC pdLog + 0xF8
[1] 0x09000000921F7CA8
sqldcsl_fetchDirect__FP8sqeAgentP8SQLD_TCBP12SQLD_TCB_CSLP16SQLD
CSL_FETCH_CB
+
0x25C
[2] 0x09000000916CB680
sqldcsl_fetchRow__FP8sqeAgentP8SQLD_TCBP12SQLD_TCB_CSLP16SQLDCSL
_FETCH_CB
+ 0x4
78
.......
Below is an example INSERT statement that might fail:
INSERT INTO ORDER (CUSTID, SECSYM, ODOC)
SELECT CT.CUSTID, T.SECSYM, T.ODOC
FROM XMLTABLE('declare default element namespace
"http://www.fixprotocol.org/FIXML-4-4";$ODOC'
passing xmlcast(? as xml) as "ODOC"
COLUMNS
"ACCTID" VARCHAR(12) PATH '*:FIXML/*:Order/@Acct',
"SECSYM" VARCHAR(15) PATH '*:FIXML/*:Instrmt/@Sym',
"ODOC" XML PATH '.') AS T,
CUSTACC AS CT
WHERE XMLEXISTS('declare default element namespace
"http://tpox-benchmark.com/custacc";
$CADOC/Customer[Accounts/Account/@id = $ACCTID]'
passing CT.CADOC as "CADOC", T.ACCTID as "ACCTID"
)
And below is the DDLs for the tables used in the above example:
CREATE DATABASE TPOX_DSS AUTOMATIC STORAGE YES ON /notnfs/kxie
DBPATH ON /home/kxie PAGESIZE 32 K
create table custacc(custid integer not null, cadoc xml inline
length 32000,primary key(custid)) distribute by hash(custid)
compress yes
create table order(custid integer, secsym varchar(15), odoc xml
inline length 32000) distribute by hash(custid) compress yes
create table security(secsym varchar(15) not null, sdoc xml
inline length 32000, primary key(secsym)) distribute by
hash(secsym) compress yes
create index custacc_accountid on custacc(CADOC) generate key
using xmlpattern 'declare namespace
c="http://tpox-benchmark.com/custacc";/c:Customer/c:Accounts/c:A
ccount/@id'
as sql varchar(12) |
