Customizing after installation

This topic describes how to apply object-specific criteria after you have started to use the NGT 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.

Note

Since the introduction of UNCLUSTERSENS in IBM DB2 Version 10, the cluster criterion takes the access method (random or sequential) into account. This simplifies the required unique key.

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_INSERTSMIN and TS_REORG_DELETESMIN criteria is 100000. These default values mean that NGT 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_INSERTS value 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 NGT 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.

Note

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
Related topic



Was this page helpful? Yes No Submitting... Thank you

Comments