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