Adding custom criteria

This topic describes how to apply object-specific criteria after you have started to use the BMC AMI Utility Manager product.

Although the criteria indicating disorganization are usually consistent across objects, some table spaces require customized thresholds. You can adjust the reorganization frequency for individual objects by adding criteria for specific table spaces. The following scenarios describe some of these cases.

Hot spots

If the majority of inserts to a table space are in specific locations, these "hot spots" become unclustered when their free space is exhausted. Even if RTS reports unclustered inserts below the threshold for the entire table space, exhaustion of free space in hot spots occurs. This causes localized high unclustered data, which can affect readers.

For table spaces with hot spots and sequential access, insert an object-specific record with a lower TS_REORG_UNCLUST threshold.

Preventing reorganization simply to reclaim space

You are less likely to need to reorganize a table space with randomly inserted and deleted rows. For such table spaces, set the threshold for INSERT and DELETE criteria to a higher or NULL value. Doing this prevents unnecessary reorganizations simply to reclaim space.

If you always insert a table space in key sequence and reclaim space by rotating partitions or a similar technique, set the TS_REORG_INSERTS value to NULL.

Increasing the reorganization frequency to reclaim space

The default value for the TS_REORG_INSERTS and TS_REORG_DELETES criteria is 25. The default value for the TS_REORG_INSERTSMIN and TS_REORG_DELETESMIN criteria is 100000. These default values mean that BMC AMI Utility Manager does not trigger a reorganization unless both of following conditions exist:

At least 25 percent of the rows have been inserted or deleted.
There are over 100,000 inserted or deleted rows.

Assume that a table space has an ascending key, inserts are always ascending, and deletes primarily target the oldest keys. The cluster will always be good, but you will need to reorganize to reclaim space freed up by the deletions. The default values cited in this section can sufficiently handle this scenario.

You might also want to reduce the criteria values for very large table spaces. For example, in a billion-row table space, a value of 25 is probably too high.

Over-allocating

If you want to trigger reorganization because of over-allocation, use the TS_REORG_ALLOUSED criterion. Specify a TS_REORG_ALLOUSED threshold in the %.% record. Subsequently, add object-specific criteria records without this criterion for any object that you deliberately over-allocate.

If you use BMC AMI Utilities embedded automation (XSUTALLO) to allocate a Db2 data set of the correct size, you should not need this criterion.

Reorganization criteria for table spaces

This section describes the reorganization criteria applicable to table spaces.

Table spaces usually reach reorganization thresholds before they reach space reclamation thresholds.

When you set your INDREF criterion, consider whether you are configuring a data sharing Db2 subsystem when you select the minimum values.

The initial settings for minimum values are 100,000. The initial settings for insert and delete thresholds are 25%. Consequently, when these settings are applied, the product does not reorganize table spaces that have fewer than 400,000 rows if only to reclaim a little space.

Important

The maximum threshold setting for all percentage criteria is 100.

Table space reorganization criteria that indicate disorganized data

The following table describes the reorganization criteria that apply to disorganized table spaces:

Criterion	Triggers a reorganization when	Starting (recommended) threshold
TS_REORG_INDREF	(REORGNEARINDREF + REORGFARINDREF) * 100 / TOTALROWS is greater than TS_REORG_INDREF	10 (5 if data sharing)
TS_REORG_UNCLUST	REORGCLUSTERSENS > 0 AND REORGUNCLUSTINS * 100 / TOTALROWS is greater than TS_REORG_UNCLUST	10
TS_REORG_MASSDEL	REORGMASSDELETE	0
TS_REORG_DISORG	(REORGDISORGLOB * 100) / TOTALROWS is greater than TS_REORG_DISORG	10
TS_REORG_CHKAREO	Advisory Reorg Status Default: N	Y

Table space reorganization criteria that indicate the need for space reclamation

The following table describes the table space reorganization criteria that indicate the need for space reclamation:

Criterion	Triggers a reorganization when	Starting (recommended) threshold
TS_REORG_INSERTS	(REORGINSERTS * 100 / TOTALROWS) is greater than TS_REORG_INSERTS	25
TS_REORG_INSERTSMIN	The minimum number of inserts is reached	100000
TS_REORG_DELETES	(REORGDELETES * 100 / TOTALROWS) is greater than TS_REORG_DELETES	25
TS_REORG_DELETESMIN	The minimum number of deletes is reached	100000
TS_REORG_UPDATES	(REORGUPDATES * 100 / TOTALROWS) is greater than TS_REORG_UPDATES	NULL
TS_REORG_UPDATESMIN	The minimum number of updates is reached	100000
TS_REORG_INSDELUPD	((REORGINSERTS + REORGDELETES + REORGUPDATES) * 100 /TOTALROWS) is greater than TS_REORG_INSDELUPD	20
TS_REORG_INSDELUPDMIN	The minimum number of INSDELUPD is reached	100000
TS_REORG_EXTENTS	EXTENTS	50
TS_REORG_ALLOUSED	(SPACE * 1024 / DATASIZE) is greater than TS_REORG_ALLOUSED	2

Index reorganization criteria that indicate a disorganized index

The following table describes the reorganization criteria that applies to disorganized indexes.

When you set the PSEUDODEL threshold, consider whether you are configuring a data sharing Db2 subsystem.

Criterion	Triggers a reorganization when	Starting (recommended) threshold
IX_REORG_PSEUDODEL	(REORGPSEUDODELETES * 100 / TOTALENTRIES) is greater than IX_REORG_PSEUDODEL	10 (5 if data sharing)
IX_REORG_LEAFLIMIT	(REORGLEAFFAR * 100 / NACTIVE) is greater than IX_REORG_LEAFLIMIT	10
IX_REORG_APPENDINS	(REORGAPPENDINSERT * 100 / TOTALENTRIES) is greater than IX_REORG_APPENDINS	10
IX_REORG_NLEVELS	REORGNUMLEVELS	0
IX_REORG_MASSDEL	REORGMASSDELETE	0
IX_REORG_CHKAREO	Advisory Reorg Status	Y

Index reorganization criteria that indicate the need for space reclamation

The following table describes the index reorganization that indicate the need for space reclamation:

Criterion	Triggers a reorganization when	Starting (recommended) threshold
IX_REORG_EMPTY	(NPAGES > 5 AND (NPAGES * 100) / NLEAF) is greater than IX_REORG_EMPTY	10
IX_REORG_INSERTS	(REORGINSERTS * 100 / TOTALENTRIES) is greater than IX_REORG_INSERTS	25
IX_REORG_INSERTSMIN	The minimum number of inserts is reached	100000
IX_REORG_DELETES	(REORGDELETES * 100 / TOTAL ENTRIES) is greater than IX_REORG_DELETES	25
IX_REORG_DELETESMIN	The minimum number of deletes is reached	100000
IX_REORG_INSDEL	((REORGINSERTS + REORGDELETES) * 100) / TOTALENTRIES	20
IX_REORG_INSDELMIN	The minimum number of changes is reached	100000
IX_REORG_EXTENTS	EXTENTS	200

Custom criteria and thresholds

Use the WHERE clause to specify custom thresholds on one, several, or all criteria records. The following examples show how you can select or exclude an object for processing by using the WHERE clause.

Example 1

To perform the following operation, update the TS_REORG_WHERE statement for all of your criteria records:

Skip reorganization for objects with fewer than 100 pages.
Reorganize objects smaller than 100 cylinders that are also in extents.

The following WHERE clause performs this operation:

DO_WHERE (A.NACTIVE < 18000 AND A.EXTENTS > 1)
OR SKIP_WHERE (A.NACTIVE < 100)

Example 2

To trigger an index for reorganization based on both the RTS.REORGLEAFNEAR value and the standard (IX_REORG_LEAFLIMIT) REORGLEAFFAR criteria, create a criteria record for the object. Specify the following WHERE clause in the record:

DO_WHERE((A.REORGLEAFNEAR * 100) / A.TOTALENTRIES > 30