Controlling index cleanup processing

Db2 automatically cleans up pseudo-deleted index entries. You can specify time windows when the cleanup processing is enabled or disabled for particular database objects, and for an entire subsystem.

Before you begin

Your authorization ID has privileges to modify data in the SYSIBM.SYSINDEXCLEANUP catalog table.

About this task

When data rows are deleted, index entries are not physically deleted unless the delete operation has exclusive control over the index page set. The index entries that remain 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.

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.

Recommendation: Use rows in the SYSIBM.SYSINDEXCLEANUP catalog table only to define exceptions when the default index cleanup behavior is unacceptable. The performance of index cleanup processing might be reduced if the number of rows in this catalog table becomes too large.

To minimize the performance impact:

  • Whenever possible, define time windows at system or database levels, rather than for specific indexes.
  • Remove unneeded or conflicting rows from the catalog table.

In data sharing, the rows in the SYSIBM.SYSINDEXCLEANUP catalog table apply to all Db2 members.

Procedure

To control cleanup processing for pseudo-empty index pages and pseudo-deleted index entries:

Insert rows the SYSIBM.SYSINDEXCLEANUP catalog table for each time window that you want to create.
Begin general-use programming interface information.For example, you might issue the following statements:
Disable the cleanup for all indexes
Issue the following statement:
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
				ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
							values(NULL,NULL,'D', 'W', NULL, NULL, NULL , NULL );

You can also disable all cleanup processing for a subsystem by setting the value of the INDEX_CLEANUP_THREADS subsystem parameter to 0.

Disable the cleanup for all indexes, except on every Saturday and Sunday
Issue the following statements:
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values(NULL,NULL,'D', 'W', NULL, 1, NULL , NULL );
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values(NULL,NULL,'D', 'W', NULL, 2, NULL , NULL );
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values(NULL,NULL,'D', 'W', NULL, 3, NULL , NULL );
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values(NULL,NULL,'D', 'W', NULL, 4, NULL , NULL );
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values(NULL,NULL,'D', 'W', NULL, 5, NULL , NULL );
Disable cleanup on all indexes every day from 8 a.m. to 6 p.m. local time
Issue the following statement:
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values(NULL,NULL,'D', 'W', NULL, NULL,'08:00:00' , '18:00:00' );
Disable cleanup for index IX1 on the RMCDB00 database on 1 June
Issue the following statement:
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values('RMCDB00','IX1','D', 'M', 6, 1,NULL,NULL);
Disable cleanup for all indexes in the RMCDB00 database on every Monday from 8 a.m. to 5 p.m. local time
Issue the following statement:
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values('RMCDB00',NULL,'D', 'W', NULL, 1,'08:00:00','17:00:00');
Disable cleanup for all indexes in the RMCDB00 database but enable cleanup for the IX1 index in the same database
Issue the following statements:
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values('RMCDB00',NULL,'D', 'W', NULL, NULL,NULL,NULL);
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values('RMCDB00','IX1','E', 'W', NULL, NULL,NULL,NULL);
End general-use programming interface information.

Results

Db2 checks the data in the SYSIBM.SYSINDEXCLEANUP catalog table at 10 minute intervals. So, the enforcement of any new row that you insert might be delayed for as long as 10 minutes. To stop index cleanup processing immediately, set the value of the INDEX_CLEANUP_THREADS subsystem parameter to 0. This subsystem parameter is online changeable.

When multiple rows in the SYSIBM.SYSINDEXCLEANUP catalog table specify overlapping time windows and conflicting values in the ENABLE_DISABLE column, the most specifically defined row overrides the others. So, rows that specify database-level time windows override any conflicting system-level time windows. Similarly, rows that specify index-level time windows override conflicting database-level time windows.

Begin general-use programming interface information.

If two otherwise matching rows contain conflicting ENABLE_DISABLE values, index cleanup processing is disabled for the specified context. For example, consider the following statements:

INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE, 
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values('RMCDB00',NULL,'D', 'W', NULL, 1,NULL,NULL);
INSERT INTO SYSIBM.SYSINDEXCLEANUP(DBNAME, INDEXSPACE,
		ENABLE_DISABLE, MONTH_WEEK, MONTH, DAY, START_TIME, END_TIME)
				values('RMCDB00',NULL,'E', 'W', NULL, 1,NULL,NULL);

The result of the rows inserted by these statements is that cleanup is disabled on Monday for the RMCDB00 database.

End general-use programming interface information.

What to do next

Begin program-specific programming interface information.You can activate IFCID 0377 to monitor the cleanup processing for pseudo-empty index pages and pseudo-deleted index entries.End program-specific programming interface information.