DB2 - Problembeschreibung
Problem IT02618 | Status: Geschlossen |
AUTO REORG MAY NOT RECLAIM SPACE FOR COLUMN-ORGANIZED TABLES | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
If maxOfflineReorgTableSize is set (> 0) in auto reorg policy like below, the reclaimable space may not be automatically reclaimed by db2acd for column-organized tables. <?xml version="1.0" encoding="UTF-8"?> <DB2AutoReorgPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" > <ReorgOptions dictionaryOption="Rebuild" indexReorgMode="Online" useSystemTempTableSpace="true" reclaimExtentsSizeForTables="100000" reclaimE xtentsSizeForIndexObjects="50000" /> <ReorgTableScope maxOfflineReorgTableSize="1000000"> <FilterClause>TABSCHEMA NOT LIKE 'SYS%' AND (TABSCHEMA,TABNAME) NOT IN (SELECT TABSCHEMA, TABNAME FROM SYSCAT.EVENTTABLES)</FilterClause> </ReorgTableScope> </DB2AutoReorgPolicy> select T.RECLAIMABLE_SPACE FROM TABLE (ADMIN_GET_TAB_INFO('SAPD01','/BIC/B0000444000')) T RECLAIMABLE_SPACE -------------------- 13522624 You can check all the tables to be evaluated by running the query below: -> db2 "WITH DP (SCHEMA, NAME, TBSPID) AS (SELECT DISTINCT dp.tabschema, dp.tabname, dp.tbspaceid FROM syscat.datapartitions as dp), DBPG (TBSPID, TBSPACETYPE, N UMPARTS) AS (SELECT tbsp.tbspaceid, tbsp.tbspacetype, count(dbpg.dbpartitionnum) FROM syscat.dbpartitiongroupdef as dbpg, syscat.tablespaces as tbsp WHERE dbp g.dbpgname = tbsp.dbpgname GROUP BY tbsp.tbspaceid, tbsp.tbspacetype) SELECT DISTINCT a.schema, a.name, a.reorg_state, a.reorg_history, a.reorg_index_schema, a.reorg_index_name, a.reorg_index_online, b.clustered, a.reorg_flag, b.stats_time, a.reorg_time, d.tbspacetype, b.tableorg, ((BIGINT(b.card) * BIGINT(b.avgrowsize))/(BIGINT(d.numparts))) as table_size FROM SYSTOOLS.HMON_ATM_INFO a, SYSCAT.TABLES b, DP c, DBPG d WHERE a.schema =b.tabschema AND b.tabschema = c.schema AND a.name = b.tabname AND b.tabname = c.name AND c.tbspid = d.tbspid AND a.stats_lock != 'Y' AND a.reorg_flag = 'Y' AND (((b.clustered = 'Y') OR (b.clustered = 'T') OR (b.tableorg = 'C')) OR ((b.clustered is NULL) AND ((b.stats_time > a.reorg_time) OR (a.reorg_state > 4) OR (b.stats_time is not null AND a.reorg_time is null) ) ) ) with ur" If any table has a negative table_size value, then all tables after this one will not be evaluated by db2acd including the one with the negative table_size value. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * User who uses auto reorg to reclaim spaces for * * column-organized table * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 V10.5 Fix Pack 4. * **************************************************************** | |
Local-Fix: | |
There are two options: 1) one is to update the FilterClause in auto reorg policy like the below: <FilterClause>TABSCHEMA NOT LIKE 'SYS%' AND (TABSCHEMA,TABNAME) NOT IN (SELECT TABSCHEMA, TABNAME FROM SYSCAT.EVENTTABLES) AND (TABLEORG = 'R' OR (TABLEORG='C' AND CARD = 0)) </FilterClause> And for those non-empty column-organized tables, manually run "reorg table <tabname> reclaim extents ALLOW WRITE ACCESS'" 2) another option is to set maxOfflineReorgTableSize to 0 so that ATM will not check the table size. And to avoid performing offline table reorg against large tables when offline window is specified, user can exclude those large tables in reorg policy explicitly. | |
verfügbare FixPacks: | |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows | |
Lösung | |
Problem first fixed in DB2 V10.5 Fix Pack 4. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 18.06.2014 10.09.2014 10.09.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.4 |