Try OpenEdge Now
skip to main content
Database Administration
Reference : PROUTIL Utility : PROUTIL MTIDXBUILD qualifier
 

PROUTIL MTIDXBUILD qualifier

For databases enabled for multi-tenancy, rebuild an index for a tenant or group, packing or consolidating index records to use disk space as efficiently as possible.
Caution: You must enable your database for multi-tenant index rebuild before executing the rebuild; see PROUTILENABLEMTIDXBLD qualifier.

Syntax

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

Parameters

db-name
Specifies the multi-tenant database you are using.
table [owner-name.]table-name
Specifies the table for the index or indexes to be rebuilt.
area area-name
Specifies that you want to rebuild the indexes for table-name defined in the named area.
index index-name
Specifies one named index for table-name to be rebuilt.
tenant tenant-name
Specifies that the MTIDXBUILD operation is to be performed only on the index partitions of tenant-name. If tenant-name does not own any partition of the specified indexes, MTIDXBUILD exits with an error.
group group-name
Specifies that the MTIDXBUILD operation is to be performed only on the index partitions of group-name. If group-name does not own any partition of the specified indexes, MTIDXBUILD exits with an error.
-T dir-name
Specifies the name of the directory where the temporary files are stored. If you do not use this parameter, MTIDXBUILD places temporary files in the current working directory.
-SS sort-file-directory-specification
Identifies the location of a multi-volume sort file specification. If you use the Sort Directory Specification (-SS) parameter, MTIDXBUILD does not use the Temporary Directory (-T) parameter.
-TB n
Specifies that the multi-tenant 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.
-TM n
Specifies the merge number. n indicates the number of blocks or streams to be merged during the sort process.
-B n
Specifies the number of blocks in the database buffers.
-SG n
Specifies that the multi-tenant index rebuild uses index grouping. n must be a value between 8 and 64, and indicates the number of index groups used by MTIDXBUILD. 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.
-pfactor n
Specifies that MTIDXBUILD 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.
refresh n
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 MTIDXBUILD from building the index until they are disconnected.

Notes

*If you do not specify an area or a specific index, all indexes associated with the table, for the specified tenant or group, are rebuilt.
*You can run multiple instances of MTIDXBUILD at the same time.
*To avoid name collisions with the .srt file when running multiple instances of MTIDXBUILD, the naming convention used for the .srt file is databasename.tenantname.srt or databasename.groupname.srt. If a file with that name is not located, MTIDXBUILD then looks for a file named databasename.srt.
*Index partitions that are not allocated can not be rebuilt.
*Use the Temporary Directory (-T) startup parameter to identify or redirect temporary files created by MTIDXBUILD 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.
*MTIDXBUILD prompts you to confirm you have enough disk space for sorting. If you answer "no", MTIDXBUILD executes without sorting.
*MTIDXBUILD does not repair corrupted record data.
*For differences between MTIDXBUILD and IDXBUILD, see Multi-tenantindex rebuild.
*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 multi-tenant index rebuild, the _index records for the indexes to be rebuilt are first changed to "active", and the _storageObject record for the tenant or group partition, is set to "inactive". This enables an index to be in different states for different partitions.
*If auditing is enabled on your database:
*If there is an audit policy defined to record updates on _index records for the indexes to be rebuilt, auditing does not record updates.
*MTIDXBUILD uses the same audit event as IDXBUILD.
*To prevent users from accessing a rebuilt index with a stale cursor, ABL clients, whose login tenant or group matches the table MTIDXBUILD is working on, or whose tenant id is a super tenant, with a timestamp older than a current timestamp from MTIDXBUILD, must update their cache or log out.
If MTIDXBUILD 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), MTIDXBUILD 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, for SQL clients to start a new transaction, or forcibly disconnect them with PROSHUT.