DB2 - Problem description
Problem IT36327 | Status: Closed |
AUTO REORG..RECOMPRESS AND ONLINE BACKUP CANNOT BE RUN CONCURRENTLY | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
Auto REORG..RECOMPRESS and online backup cannot be run concurrently. Thus, if online backup is running, auto REORG..RECOMPRESS will be interrupted. However, there is currently an issue if auto REORG..RECOMPRESS is interrupted by online backup, most commonly found in the case where online backup is already running and auto REORG..RECOMPRESS is attempted on the table. If this occurs, it is possible that auto REORG..RECOMPRESS will not run again on this table, even if it still has data that requires recompression. Additionally, this prevents reclaim from releasing pages that were deleted by processes such as logical truncate. These problems will persist until the database is restarted. This can cause frequently truncated tables to grow in size because their deleted pages are not reclaimed. db2level: Informational tokens are "DB2 v11.5.4.0" example issue: ---- A sign that this issue may be occurring can be if RECLAIMABLE SPACE is 0 after LOGICAL TRUNCATE and RUNSTATS on a table: SELECT SUM(RECLAIMABLE_SPACE) as RECLAIMABLE_SPACE FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('SCHEMA', 'TABLENAME')) RECLAIMABLE_SPACE -------------------- 0 1 record(s) selected. Additional signs that this issue may be happening is if messages similar to the following are found in the db2diag.log for the same table: ============ 2021-03-18-15.57.02.504505-420 I200238E752 LEVEL: Info PID : 30481 TID : 139990201788160 PROC : db2sysc INSTANCE: xxxx NODE : 000 DB : USID APPHDL : 0-73 APPID: *LOCAL.DB2.210318225654 AUTHID : aaaa HOSTNAME: db2i-xxxx-ss9r6-x86 EDUID : 217 EDUNAME: db2taskp (USID) FUNCTION: DB2 UDB, CDE Data, TableAction::recompress, probe:2009 MESSAGE : ZRC=0x870F0035=-2029060043=SQLO_NOTAVAIL "Cannot honor the conditional request" DIA8531C A memory access error has occurred. DATA #1 : String, 122 bytes REORG TABLE "SCHEMA.TABLENAME" RECOMPRESS did not get OLB latch. OLB is in progress. Daemon will relaunch recompress. 2021-03-18-15.57.04.877573-420 I201664E668 LEVEL: Event PID : 30481 TID : 140011164919552 PROC : db2sysc INSTANCE: xxxx NODE : 000 DB : USID APPHDL : 0-38 APPID: *LOCAL.xxxx.210318225313 UOWID : 4 ACTID: 1 AUTHID : aaaa HOSTNAME: db2i-xxxx-ss9r6-x86 EDUID : 146 EDUNAME: db2agent (USID) FUNCTION: DB2 UDB, CDE Data, TableTruncater::TruncateData::getOLBLock, probe:100 DATA #1 : String, 53 bytes Backup is running. Downgrading to logical truncation. DATA #2 : Table name with schema, 21 bytes SCHEMA.TABLENAME ============= | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5.6 * **************************************************************** | |
Local Fix: | |
A workaround to reclaim the space from any frequently truncated tables that are exhibiting increases in size would be to run "TRUNCATE TABLE DROP STORAGE IMMEDIATE" when online backup is not running to release those pages back into the tablespace. A sign that this issue may be occurring can be if RECLAIMABLE SPACE is 0 after LOGICAL TRUNCATE and RUNSTATS on a table. Also fixed permanently in DB2 v11.5.6 | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5.6 * **************************************************************** | |
Comment | |
Upgrade to 11.5.6 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 23.03.2021 10.06.2021 10.06.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |