|
Problem IC63463
|
Status: Closed |
SQL1224N from select count() using XMLTABLE with COLUMN PATH that uses FLWR statement |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A select count() query is crashing the database because we are
marking the "elements" node for extraction but not allocating a
sqlz value for binding out the result. During navigation tuple
construction we try to initialize the sqlz value but trap
because it was never allocated previously.
Sample Query:
select count(1)
FROM networkmessage n,
XMLTABLE ('$n/NetworkMessage' passing n.value as "n"
COLUMNS
cardId VARCHAR(100) PATH
'*:cardId',
groupId VARCHAR(100) PATH
'*:groupId',
res_p3_processing_cd_blast VARCHAR(100) PATH 'for $i
in (*:response/*:elements) where $i/*:id = "3" return
$i/*:value',
res_p3_processing_cd_visa VARCHAR(100) PATH 'for $i
in (*:response/*:elements/*:elements) where $i/../*:id = "3" and
$i/*:id = "1" return $i/*:value'
) AS X
LEFT JOIN Card c ON X.cardId = c.id;
SQL ERROR:
SQL1224N The database manager is not able to accept new
requests, has
terminated all requests in progress, or has terminated your
particular request
due to an error or a force interrupt. SQLSTATE=55032
STACK:
00002ADCF95E1BBC _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm + 0x00b4
00002ADCF95E1C83 _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x0009
00002ADCF5B4FA35 sqlo_trce + 0x03f3
00002ADCF5B8E45D sqloEDUCodeTrapHandler + 0x0107
0000003FA320DE70 address: 0x0000003FA320DE70 ; dladdress:
0x0000003FA3200000 ; offset in lib: 0x000000000000DE70 ;
00002ADCF4B3667F
_ZN21XmlnvTupleConstructor12m_initZValueEP10sqlz_valueP16XmlnvBu
fferArrayi + 0x0011
00002ADCF65FBED8
_ZN21XmlnvTupleConstructor22m_initTupleConstructorEP12sqlriXNavO
bjP14XmlnvAutomaton + 0x03be
00002ADCF65ED981
_ZN14XmlnvAutomaton15m_initAutomatonEP14XmlnvNavigatorP12sqlriXN
avObj + 0x02a1
00002ADCF65F729B
_ZN14XmlnvNavigator15m_initNavigatorEP13SQLO_MEM_POOLP11XMLSTORE
_CBP12sqlriXNavObjP12XmlrnNodeMgrP8sqlrr_cb + 0x02d5
00002ADCF65F6EC9
_ZN14XmlnvNavigatorC9EP13SQLO_MEM_POOLP11XMLSTORE_CBP12sqlriXNav
ObjP8sqlrr_cbPi + 0x0065
00002ADCF65F6FC4
_ZN14XmlnvNavigatorC1EP13SQLO_MEM_POOLP11XMLSTORE_CBP12sqlriXNav
ObjP8sqlrr_cbPi + 0x0006
00002ADCF5F45FD0 _Z12sqlriXMLScanP8sqlrr_cb + 0x0350
00002ADCF675562C _Z14sqlriNljnPipedP8sqlrr_cb + 0x0230
00002ADCF674E338 _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm +
0x00b2
00002ADCF498394E
_Z10sqlrr_openP14db2UCinterfaceP15db2UCCursorInfo + 0x076a
00002ADCF46D00B4
_Z16sqljs_ddm_opnqryP14db2UCinterfaceP13sqljDDMObject + 0x015c
00002ADCF46C7202
_Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U
Cinterface + 0x03b2
00002ADCF46C757D _Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterface
+ 0x030d
00002ADCF46C5244 address: 0x00002ADCF46C5244 ; dladdress:
0x00002ADCF35E3000 ; offset in lib: 0x00000000010E2244 ;
00002ADCF46C4FD6 address: 0x00002ADCF46C4FD6 ; dladdress:
0x00002ADCF35E3000 ; offset in lib: 0x00000000010E1FD6 ;
00002ADCF46C2DF5 address: 0x00002ADCF46C2DF5 ; dladdress:
0x00002ADCF35E3000 ; offset in lib: 0x00000000010DFDF5 ;
00002ADCF46C2C9B _Z17sqljsDrdaAsDriverP18SQLCC_INITSTRUCT_T +
0x0051
00002ADCF462947A _ZN8sqeAgent6RunEDUEv + 0x00c2
00002ADCF4B28291 _ZN9sqzEDUObj9EDUDriverEv + 0x006d
00002ADCF4B28221 _Z10sqlzRunEDUPcj + 0x0009
00002ADCF4913B04 sqloEDUEntry + 0x02d0
0000003FA32062F7 address: 0x0000003FA32062F7 ; dladdress:
0x0000003FA3200000 ; offset in lib: 0x00000000000062F7 ;
0000003FA26D1B6D clone + 0x006d
(/lib64/libc.so.6) |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 LUW All Platforms *
****************************************************************
* PROBLEM DESCRIPTION: *
* A select count() query is crashing the database because we *
* are *
* marking the "elements" node for extraction but not *
* allocating a *
* sqlz value for binding out the result. During navigation *
* tuple *
* construction we try to initialize the sqlz value but trap *
* *
* because it was never allocated previously. *
* *
* *
* *
* Sample Query: *
* *
* *
* *
* select count(1) *
* *
* FROM networkmessage n, *
* *
* XMLTABLE ('$n/NetworkMessage' passing n.value as "n" *
* *
* COLUMNS *
* *
* cardId VARCHAR(100) PATH *
* *
* '*:cardId', *
* *
* groupId VARCHAR(100) PATH *
* *
* '*:groupId', *
* *
* res_p3_processing_cd_blast VARCHAR(100) PATH *
* 'for $i *
* in (*:response/*:elements) where $i/*:id = "3" return *
* *
* $i/*:value', *
* *
* res_p3_processing_cd_visa VARCHAR(100) PATH *
* 'for $i *
* in (*:response/*:elements/*:elements) where $i/../*:id = "3" *
* and *
* $i/*:id = "1" return $i/*:value' *
* *
* ) AS X *
* *
* LEFT JOIN Card c ON X.cardId = c.id; *
* *
* *
* *
* SQL ERROR: *
* *
* *
* *
* SQL1224N The database manager is not able to accept new *
* *
* requests, has *
* *
* terminated all requests in progress, or has terminated your *
* *
* particular request *
* *
* due to an error or a force interrupt. SQLSTATE=55032 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v97 Fixpack 1 *
**************************************************************** |
| Local Fix: |
Workaround query, replace "for" with "let" to avoid bad
codepath.
select count(1)
FROM networkmessage n,
XMLTABLE ('$n/NetworkMessage' passing n.value as "n"
COLUMNS
cardId VARCHAR(100)
PATH '*:cardId',
groupId VARCHAR(100)
PATH '*:groupId',
res_p3_processing_cd_blast VARCHAR(100) PATH
'*:response/*:elements[*:id = "3"]/*:value',
res_p3_processing_cd_visa VARCHAR(100) PATH
'*:response/*:elements/*:elements[../*:id = "3" and *:id =
"1"]/*:value'
) AS X
LEFT JOIN Card c ON X.cardId = c.id; |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
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 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
The fix allocates the sqlz value used for binding out results
during navigation. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 29.09.2009
23.02.2010
23.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
