Applying object-specific criteria

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.

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_INSERTSMINand 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. 

Ensuring reorganization

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

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.

Table space reorganization criteria that indicate disorganized data

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

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:

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.

Index reorganization criteria that indicate the need for space reclamation

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

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:

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: