PROUTIL TPIDXBUILD qualifier

For databases enabled for table partitioning, rebuild an index or indexes for a single partition of a partitioned table, packing or consolidating index records to use disk space as efficiently as possible.

Note: You must enable your database for table-partitioned index rebuild before executing the rebuild; see PROUTILENABLETPIDXBLD qualifier.

Syntax

proutil db-name -C tpidxbuild table [owner-name.]table-name
[ area area-name | index index-name ]
{ partition partition-name | composite initial }
[ -T dir-name | -SS sort-file-directory-specification ]
[ -TB blocksize ][ -TM n ] [ -B n][ -SG n ]
[ -pfactor n ][ refresh n ]

Parameters

Specifies the table-partitioned database you are using.

Specifies the table for the index or indexes to be rebuilt.

Specifies that you want to rebuild the local indexes for the partition-name partition of table-name, defined in the named area.

Specifies one named index for the partition-name partition of table-name to be rebuilt. The index must be a local index.

Specifies that the TPIDXBUILD operation is to be performed only on the indexes of the partition-name table partition. If partition-name is not a valid partition for the specified table, or if the partition is in a rebuilding state, TPIDXBUILD exits with an error.

Specifies that the TPIDXBUILD operation is to be performed only on the composite initial partition of the table. If the partition is in a rebuilding state, TPIDXBUILD exits with an error.

Specifies the name of the directory where the temporary files are stored. If you do not use this parameter, TPIDXBUILD places temporary files in the current working directory.

Identifies the location of a multi-volume sort file specification. If you use the Sort Directory Specification (-SS) parameter, TPIDXBUILD does not use the Temporary Directory (-T) parameter.

Specifies that the table-partition index rebuild will be performed using Speed Sort. n indicates the allocated block size, in kilobytes. The maximum value is 64; if not specified, the default is 8.

Specifies the merge number. n indicates the number of blocks or streams to be merged during the sort process.

Specifies the number of blocks in the database buffers.

Specifies that the table-partition index rebuild uses index grouping. n must be a value between 8 and 64, and indicates the number of index groups used by TPIDXBUILD. The default value is 48. Note that a temporary file is created for each index group.

A large -SG value requires more memory allocation and more file handles. To determine the amount of memory (in kilobytes) needed for each index group, add 1 to the merge number (the value of -TM) and multiply the sum by the speed sort block size (the value of -TB). Memory consumption for each index group equals (-TM + 1) * -TB.

Specifies that TPIDXBUILD will use a packing factor. n must be a value between 60 and 100, and indicates the maximum percentage of space used in an index block. The default value is 100.

Specifies the frequency in seconds to update the display of clients that are blocking the build of an index. The default refresh rate is 60 seconds.You can set it as high as 300 seconds. Connected clients with a schema timestamp earlier than the index's schema timestamp will prevent TPIDXBUILD from building the index until they are disconnected.

Notes

If you do not specify an area or a specific index, all local indexes associated with the table, for the specified partition, are rebuilt. Global indexes are skipped without error.

To avoid name collisions with the .srt file when running multiple instances of TPIDXBUILD, the naming convention used for the .srt file is databasename.partitionname.srt. If a file with that name is not located, TPIDXBUILD then looks for a file named databasename.srt.

Use the Temporary Directory (-T) startup parameter to identify or redirect temporary files created by TPIDXBUILD to a specified directory when sorting and handling space issues.

Use the Speed Sort (-TB), Merge Number (-TM), Sort Grouping (-SG) and Blocks in Database Buffers (-B) startup parameters to improve index rebuild performance.

TPIDXBUILD prompts you to confirm you have enough disk space for sorting. If you answer “no”, TPIDXBUILD executes without sorting.

To determine whether you have enough free space to sort the indexes, consider that when rebuilding an individual index, sorting that index requires free space that can be as large as three times the size of one index entry times the number of records in the file.

During the table-partitioned index rebuild, the _index records for the indexes to be rebuilt are first changed to “active”, and the _storageObject record for the partition of the partitioned table, is set to “inactive”. This enables an index to be in different states for different partitions.

If there is an audit policy defined to record updates on _index records for the indexes to be rebuilt, auditing does not record updates.

To prevent users from accessing a rebuilt index with a stale cursor, all ABL clients, with a timestamp older than a current timestamp of TPIDXBUILD, must update their cache or log out.

If TPIDXBUILD detects one or more these clients, there are two possible outcomes:

For OpenEdge Release 11.7 and later, if you have enabled Database Client Notification (-usernotifytime), TPIDXBUILD waits until the clients respond to the notification, and then proceeds.

For releases prior to Release 11.7, or if you have not enabled Database Client Notification (-usernotifytime 0), you may wait for those clients to disconnect or forcibly disconnect them with PROSHUT.

TPIDXBUILD acquires an exclusive table partition lock. Once the build begins, all other processes are blocked from creating or deleting index objects, and clients cannot use the index in any query (locked or no-lock).

If db-name is a UTF-8 database, you must specify -cpinternal UTF-8 on the command line. If not specified, an error message is generated and TPIDXBUILD exits.