Automatic cleanup of pseudo-empty index pages
In Version 11, DB2® automatically cleans up pseudo-deleted index entries and pseudo-empty index pages. This enhancement can reduce the size of some indexes, which can improve SQL performance and reduce the need to run the REORG INDEX utility.
When rows are deleted, index entries are not physically deleted unless the delete operation has exclusive control over the index page set. These index entries are called pseudo-deleted index entries. Subsequent searches continue to access these pseudo-deleted entries, which can gradually degrade performance as more rows are deleted. These pseudo-deleted index entries can also result in timeouts and deadlocks for applications that insert data into tables with unique indexes.
Before Version 11, DB2 attempted to clean up pseudo-empty index pages as part of SQL DELETE processing. Pseudo-empty index pages are pages that contain only pseudo-deleted index entries. However, if some of the pseudo-deleted entries in the page were not committed during the SQL DELETE processing, cleanup could not be performed. Therefore, some pseudo-empty pages were likely not cleaned up.
In Version 11, in addition to the cleanup that was previously done, DB2 automatically deletes pseudo-empty index pages independently of the SQL DELETE transaction. DB2 also automatically deletes individual pseudo-deleted index entries.
Automated cleanup of pseudo-empty index pages and pseudo-deleted index entries is enabled for all indexes by default when the value of the INDEX_CLEANUP_THREADS subsystem parameter is set to a non-zero value. However, you can specify time windows to enable or disable the index cleanup for the entire subsystem, for indexes in specific databases, or for specific indexes.
When the system is zIIP-enabled, this automated clean-up function runs under enclave service request blocks (SRBs) that are zIIP-enabled. However, the system does not need to be zIIP-enabled to use this new function.
This new function is designed to run with minimal or no disruption to other concurrent DB2 work in the system.
DB2 Subsystem Parameter INDEX_CLEANUP_THREADS
Specifies the maximum number of threads that can be created to process the cleanup of pseudo-deleted index entries on this subsystem or data sharing member. Pseudo-deleted entries in an index are entries that are logically deleted but still physically present in the index.
- Acceptable values: 0 to 128
- Default: 10
- Data sharing scope: Members are not required to use the same setting
- Online changeable: Yes
- Update: option 15 on panel DSNTIPB
- DSNZPxxx: DSN6SPRM INDEX_CLEANUP_THREADS
This parameter works in conjunction with the SYSIBM.SYSINDEXCLEANUP catalog table, which controls cleanup processing of pseudo-deleted index entries.
The default setting is appropriate for most situations. However, a larger setting might be appropriate in cases where large indexed tables or large numbers of indexed tables are constantly modified. A smaller value might be appropriate in cases where thread count is severely constrained.
When INDEX_CLEANUP_THREADS is set to 0, no cleanup is performed by the subsystem or data sharing member regardless of the entries in the SYSIBM.SYSINDEXCLEANUP catalog table.
DB2 Tablle SYSIBM.SYSINDEXCLEANUP
The rows in the SYSIBM.SYSINDEXCLEANUP table specify time windows to control index cleanup processing. Each row specifies a time window to enable or disable the cleanup of pseudo-deleted index entries for specific database objects.
DBNAME VARCHAR(24) The name of the database that contains the index space. INDEXSPACE VARCHAR(24) The name of the index space. ENABLE_DISABLE CHAR(1) NOT NULL Specifies whether the row enables or disables cleanup for the specified index space.'E'=Enabled, 'D'=Disabled MONTH_WEEK CHAR(1) NOT NULL Indicates the meaning of the value of the DAY column: 'M'The value indicates the day of the month. 'W'The value indicates a day of the week. MONTH SMALLINT The month in which the time window applies. For example a 1 value indicates January and a 12 value indicates December. If this column contains NULL, the time window applies to all months. If the value of the MONTH_WEEK column is 'W', this value must be NULL. DAY SMALLINT The day of the month or the day of the week for which the time window applies, as specified by the value of the MONTH_WEEK column. For example, if MONTH_WEEK='W', a 1 value indicates Monday and 7 indicates Sunday. If the value of this column is NULL, the time window applies to every day of the month or every day of the week. START_TIME TIME The local time at the beginning of the time window specified by the row. When this column contains a null value, the row applies at all times on the specified days. This column must contain NULL if the END_TIME column contains NULL. END_TIME TIME The local time at the end of the time window specified by the row. When this column contains a null value, the row applies at all times on the specified days. This column must contain NULL if the START_TIME column contains NULL.
Probleme mit Automatic Cleanup
Nach der Installation von Version 11 kam es – auch im CM Mode – zur einer überraschend hohen Zahl von DSNT501I und DSNT376I Meldungen, wie z.B. …
13.45.35 STC11045 DSNT376I -DB2T PLAN=IDAEMK WITH 261 261 CORRELATION-ID=014.IDAEMK02 261 CONNECTION-ID=DB2T 261 LUW-ID=DESUBM0N.IPDB2T01.CEE394BE87CE 261 THREAD-INFO=SYSOPR:*:*:*:*:* :*:* 261 IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=PPLN4711 261 CORRELATION-ID=JOB47112 261 CONNECTION-ID=BATCH 261 LUW-ID=DESUBM0N.IPDB2T01.CEE3C3ECF217=37071 261 THREAD-INFO=SCHEDL2T:BATCH:SCHEDL2T:JOB47112:STATIC:146 261 ON MEMBER DB2T 13.45.35 STC11045 DSNT501I -DB2T DSNILMCL RESOURCE UNAVAILABLE 262 262 CORRELATION-ID=014.IDAEMK02 262 CONNECTION-ID=DB2T 262 LUW-ID=DESUBM0N.IPDB2T01.CEE394BE87CE=0 262 REASON 00C9008E 262 TYPE 00000D01 262 NAME 00000182.00001062
Problem mit IBM DB2 Korrektur PI31459/UI26612 gefixt
An index pseudo delete cleanup thread needs to acquire DBD lock and table lock before updating the index to remove the pseudo deleted entries. If the required locks have already been held by other applications, index pseudo delete cleanup thread could timeout and issue many DSNT376I and DSNT501I messages. Because index pseudo delete cleanup threads tend to give other applications higher priority, normally they are the victims in the timeout situation. Index pseudo delete cleanup threads are designed to run in the background and to not block other applications, issuing a large amount of timeout messages. This can be alarming to the users while there is no real contention in the system.
DB2 code has been changed to not issue DSNT376I and DSNT501I messages if an index pseudo delete cleanup thread timed out while waiting for the necessary locks in order to remove the pseudo deleted entries from an index. If, however, the index pseudo delete cleanup threads are the lock holders and caused other applications to timeout, DSNT376I and DSNT501I messages will continue to be issued.
Quelle: IBM DB2 11 for z/OS Literatur, IBM DB2 Support