|
Problem IC68785
|
Status: Closed |
SELECT FROM SYSIBMADM.ADMINTABINFO CRASHES DATABASE WITH BFIX DETECTED AN ERROR |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
If we have a table that has a very big size, we might get into a
situation where a query on SYSIBMADM.ADMINTABINFO will crash the
database.
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
SUBSTR(TABNAME,1,30) TABNAME,
SUM(DATA_OBJECT_P_SIZE) DATA_OBJECT_P_SIZE,
SUM(INDEX_OBJECT_P_SIZE) INDEX_OBJECT_P_SIZE,
SUM(LONG_OBJECT_P_SIZE) LONG_OBJECT_P_SIZE,
SUM(LOB_OBJECT_P_SIZE) LOB_OBJECT_P_SIZE,
SUM(XML_OBJECT_P_SIZE) XML_OBJECT_P_SIZE
FROM SYSIBMADM.ADMINTABINFO
WHERE TABNAME LIKE 'TEST%'
GROUP BY TABSCHEMA, TABNAME
order by 3 desc, 4 desc
;
The db2diag.log will report the following messages:
2010-03-16-12.07.40.909000-240 I46298553F4655 LEVEL: Severe
PID : 8172 TID : 1444 PROC :
db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : TEST
APPHDL : 0-23826 APPID: *LOCAL.DB2.100316155850
AUTHID : DB2ADMIN
EDUID : 1444 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, Common Trace API, sqlbfix, probe:104
DATA #1 : String, 45 bytes
bfix detected an error - Current Descriptor =
DATA #2 : Buffer page descriptor, PD_TYPE_SQLB_BPD, 160 bytes
Pagekey: {pool:27;obj:4;type:64} PPNum:3972
objectPageNum: 5971
Note when hitting this issue, there will always be the number
3972 and 5971 as shown above. This is not a table corruption as
it could be interpreted from the db2diag.log messages, but an
error on the calculations when transversing the EMP (extent Map
Pages) for the table.
The stack of the crash will show the following entries:
sqloDumpEDU
sqle_panic
sqlb_panic
sqlbufix
sqlbNumExtentsMappedBySingleIndirect
sqlbNumExtentsMappedByDoubleIndirect
sqlbGetExtentCount
sqlbDMSGetObjActualSize
sqlbGetObjActualSize
sqldGetTableData
sqlrlAdminGetTabInfo
sqlerTrustedRtnCallbackRouter
admin_get_tab_info_v97
This problem only occurs when doing the following
Select DATA_OBJECT_P_SIZE / INDEX_OBJECT_P_SIZE /
LONG_OBJECT_P_SIZE / LOB_OBJECT_P_SIZE / XML_OBJECT_P_SIZE from
sysibmadm.admintabinfo on a table that is very big.
We might also see a crash when dropping such table, due to the
purging transversing the EMP pages to calculate the physical
size of the table. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* If we have a table that has a very big size, we might get *
* into a *
* situation where a query on SYSIBMADM.ADMINTABINFO will crash *
* the *
* database. *
* *
* *
* *
* SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA, *
* *
* SUBSTR(TABNAME,1,30) TABNAME, *
* *
* SUM(DATA_OBJECT_P_SIZE) DATA_OBJECT_P_SIZE, *
* *
* SUM(INDEX_OBJECT_P_SIZE) INDEX_OBJECT_P_SIZE, *
* *
* SUM(LONG_OBJECT_P_SIZE) LONG_OBJECT_P_SIZE, *
* *
* SUM(LOB_OBJECT_P_SIZE) LOB_OBJECT_P_SIZE, *
* *
* SUM(XML_OBJECT_P_SIZE) XML_OBJECT_P_SIZE *
* *
* FROM SYSIBMADM.ADMINTABINFO *
* *
* WHERE TABNAME LIKE 'TEST%' *
* *
* GROUP BY TABSCHEMA, TABNAME *
* *
* order by 3 desc, 4 desc *
* *
* ; *
* *
* *
* *
* The db2diag.log will report the following messages: *
* *
* *
* *
* 2010-03-16-12.07.40.909000-240 I46298553F4655 LEVEL: *
* Severe *
* PID : 8172 TID : 1444 PROC : *
* *
* db2syscs.exe *
* *
* INSTANCE: DB2 NODE : 000 DB : TEST *
* APPHDL : 0-23826 APPID: *
* *LOCAL.DB2.100316155850 *
* AUTHID : DB2ADMIN *
* *
* EDUID : 1444 EDUNAME: db2agent (TEST) 0 *
* FUNCTION: DB2 UDB, Common Trace API, sqlbfix, probe:104 *
* *
* DATA #1 : String, 45 bytes *
* *
* bfix detected an error - Current Descriptor = *
* *
* DATA #2 : Buffer page descriptor, PD_TYPE_SQLB_BPD, 160 *
* bytes *
* Pagekey: {pool:27;obj:4;type:64} PPNum:3972 *
* *
* objectPageNum: 5971 *
* *
* *
* *
* *
* *
* Note when hitting this issue, there will always be the *
* number *
* 3972 and 5971 as shown above. This is not a table corruption *
* as *
* it could be interpreted from the db2diag.log messages, but *
* an *
* error on the calculations when transversing the EMP (extent *
* Map *
* Pages) for the table. *
* *
* *
* *
* The stack of the crash will show the following entries: *
* *
* *
* *
* sqloDumpEDU *
* *
* sqle_panic *
* *
* sqlb_panic *
* *
* sqlbufix *
* *
* sqlbNumExtentsMappedBySingleIndirect *
* *
* sqlbNumExtentsMappedByDoubleIndirect *
* *
* sqlbGetExtentCount *
* *
* sqlbDMSGetObjActualSize *
* *
* sqlbGetObjActualSize *
* *
* sqldGetTableData *
* *
* sqlrlAdminGetTabInfo *
* *
* sqlerTrustedRtnCallbackRouter *
* *
* admin_get_tab_info_v97 *
* *
* *
* *
* This problem only occurs when doing the following *
* *
* Select DATA_OBJECT_P_SIZE / INDEX_OBJECT_P_SIZE / *
* *
* LONG_OBJECT_P_SIZE / LOB_OBJECT_P_SIZE / XML_OBJECT_P_SIZE *
* from *
* sysibmadm.admintabinfo on a table that is very big. *
* *
* *
* *
* We might also see a crash when dropping such table, due to *
* the *
* purging transversing the EMP pages to calculate the physical *
* *
* size of the table. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7 and Fixpack 3 *
**************************************************************** |
| Local Fix: |
Do not run a query on sysibmadm.admintabinfo if there are very
big tables on the system.
Avoid dropping very big tables. |
| available fix packs: |
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 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 |
Problem was first fixed in DB2 version 9.7 and Fixpack 3 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC69320
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.05.2010
29.06.2010
29.06.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
