Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
APAR status
Closed as program error.
Error description
A table may become corrupted and unusable after loading data when the table is a multidimensional clustering (MDC) table with row compression enabled. You will only encounter this problem is all of the following are true: 1. The table is an MDC table. 2. The table has a row compression dictionary. 3. The load action is INSERT and the INDEXING MODE is REBUILD or AUTOSELECT. 4. The table space PAGESIZE * EXTENSIZE for this table is less than approximately 150KB, and the table's compression dictionary spans more than one table space extent. You can determine if a table's compression dictionary spans more than one table space extent by dividing its size by the size of the extent. For example : page_size_in_bytes = "db2 select PAGESIZE from syscat.tablespaces where TBSPACE='<tablespace>' ". extent_size_in_pages = "db2 select EXTENTSIZE from syscat.tablespaces where TBSPACE='<tablespace>' ". extent_size_in_bytes = page_size_in_bytes * extent_size_in_pages. dictionary_size_in_bytes = "db2 select dictionary_size from table(sysproc.admin_get_tab_info('<schema>','<tabname>')) as t". num_extents_spanned = dictionary_size_in_bytes / extent_size_in_bytes. The DB2 instance will ultimately crash (trap, end abormally, ABEND) with function SPExpandRec or sqldLoadTCB at the top of the stack. Other eye catcher functions in the stack could be: sqldFetchDictionary. The call stack will contain functions similar to this: sqldExpandRec... sqldExpandAndSetup... sqldFetchDirect... sqldFetchDictionary... sqldLoadTCB... sqldFixTCB... You will also see db2diag.log entries similar to the following: 008-05-15-15.27.31.143122-240 I1220A417 LEVEL: Severe PID : 529192 TID : 1 PROC : db2agent (FOO) INSTANCE: db2inst1 NODE : 000 DB : FOO APPHDL : 0-7 APPID: ... AUTHID : ... FUNCTION: DB2 UDB, data management, sqldLoadTCB, probe:5681 RETCODE : ZRC=0x8704007A=-2029780870=SQLD_REC_DELETED "DELETED RECORD ID"
Local fix
It is possible that this problem will prevent you from accessing and restarting the database if the INDEXREC database configuration parameter is set to RESTART. This is because at the end of crash recovery the database will check if indexes need to be rebuilt and this could result in accessing the corrupted table, leading to the database being marked as bad. To circumvent this and allow crash recovery to complete so you can access other tables, set the database configuration parameter INDEXREC to ACCESS instead. There is no known way of recovering the data from the corrupted table apart from performing a point in time restore operation to just prior to the load that corrupted the table. A DROP TABLE statement will also fail on this table. The table can be dropped if it is marked as bad by the db2dart /MT command, however DB2 service needs to be engaged prior to executing this. An alternative to dropping the table is to drop the table space that contains the corrupted table. To reactively determine if a load operation has already marked SYSTEM extent as FREE, and overwritten them, perform the following steps. Only perform this if your database has already been deactivated. For each MDC table with compression enabled, whose compression dictionary spans more than one tablespace extent (see note above), do this: Run db2dart inspection on the table (/T) [or entire table space (/TS) or entire database (/DB)], this will report an error like "DartFetchDictionary: could not read/allocate dictionary or expansion buffer". If this is the case, the only recourse is to restore the tablespace or database from a previous backup image, or to manually recreate and repopulate the table. Be sure to change INDEXREC cfg parameter to ACCESS before next database activation. To proactively determine if a load operation has already marked any SYSTEM extents FREE and/or overwritten them, perform the following steps: [Special note, for compression dictionaries built using the INSPECT utility, those table^space extents that were flaged as SYSTEM for the compression dictionary may not start from extent 0, so it is not possible to determine if a load operation marked these extents FREE and/or overwrote them]. 1. For each MDC table with compression enabled, whose compression dictionary spans more than one table space extent (see note above), do this: i)Determine the table_OI using "db2 select TABLEID from SYSCAT.TABLES where TABNAME='<tabname>' AND TABSCHEMA='<tabschema>' ". ii)Determine the table_TSI using "db2 select TBSPACEID from SYSCAT.TABLES where TABNAME='<tabname>' AND TABSCHEMA='<tabschema>' ". iii)Perform the command: db2dart <database name> /dm /tsi <table_TSI> /oi <table_OI> /ps 0 /np 100 /v y /scr n /rptn MYREPORT.txt iv)Examine the MYREPORT.txt file, and search for the "Block Map Entries". For example: Block Map Entries: blockNum bits 00000000 IN_USE SYSTEM 00000001 FREE Each blockNum 00000000 through 0000000m (where m equals the number of extents spanned by the table's dictionary minus 1) should say "IN_USE SYSTEM" since you already determined that this table's compression dictionary spans more than one table space extent. If it says FREE then a previous load operation has incorrectly marked it FREE, and if it says IN_USE but not SYSTEM then a subsequent load or insert operation has already overwritten it. You should immediately (before a database deactivation)change the INDEXREC database configuration parameter to "ACCESS" and export the contents of this table so that it can be rebuilt later.
Problem summary
Users affected : Users having MDC tables which have row compression Problem description : Load can corrupt the table leaving the data unrecoverable. Problem Summary : This apar fix prevents the corruption.
Problem conclusion
First fixed in DB2 UDB Version 9, Fixpak 5
Temporary fix
Comments
APAR Information
APAR number
IZ21943
Reported component name
DB2 DPF
Reported component ID
5724N7400
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2008-05-07
Closed date
2008-06-27
Last modified date
2008-10-20
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 DPF
Fixed component ID
5724N7400
Applicable component levels
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]
Document Information
Modified date:
03 October 2021