OpenEdge 10.1A introduced a new idxactivate feature to PROUTIL to activate an index online or offline.
Command syntax:
proutil db-name -C idxactivate [owner-name.]table-name.index-name [useindex index-name] [recs n] [refresh t] [ tenant tenant-name | group group-name]
In order to use IDXACTIVATE the primary index must be active and is used by default to build inactive indexes unless "useindex" is specified, in which case:
- The "useindex" specified needs to be a valid, active index
- A useindex cannot be a schema, VST or word index
- The value provided after the useindex parameter must only be the index name, not the table-name.index-name pairing.
- If the useindex is not active or cannot be found, the Primary index is used instead and needs to be active
- An INACTIVE PRIMARY index can be built with another index specified with useindex
- The useindex should ideally be a UNIQUE index otherwise there are additional overhead necessary to operate on a non unique index
IDXACTIVATE should only be used when certain that the index used to build the inactive index is in a good state. It is recommended to run IDXCHECK against the index that will be used to activate the inactive index before-hand.
Provided that the inactive index is not the only index, IDXACTIVATE is a useful feature for Replication enabled databases, where previously replication had to be disabled in order to activate inactive indexes. For further information refer to Article:
Prior to activating the index, IDXACTIVATE checks to ensure there are no users with a schema timestamp that is earlier than the schema timestamp of the index.
If any such users are connected to the database, IDXACTIVATE cannot proceed, unless the OpenEdge 11.7 Database Client Notification (-usernotifytime) is enabled. The Database Client Notification poll time (-usernotifytime) can be updated online in PROMON or with the _DbParams VST. Refer to the example provided in Article How to query Database Startup Parameters that can be modified with VST's?
IDXACTIVATE waits until all connected clients respond to the notification, and then proceeds.This is because a schema lock is necessary in order to activate an index. When there are any users connected to the database that have accessed the earlier version of the schema this lock cannot be achieved.
An option prompt to wait or cancel the index activation with a list of the current users blocking the activation procedure.
Example:
Activating Index <name> at Database timestamp 1429005492 . (12811)
Usr Name Type Pid Timestamp
5 uname1 ABL 3124 1429005484
Connections with timestamps older than 1429005492 exist. (13246)
Do you wish to continue waiting..... Type y to continue (y/n). (13247)
If the option to wait is chosen, the IDXACTIVATE session will wait until all the users with an earlier schema timestamp refresh their sessions, re-prompting at the -refresh interval (default 60 seconds), which specifies the number of seconds between displays of blocking users, as needed.
Prior to OpenEdge 11.7 where the database can be started with -usernotifytime, in a production environment user sessions should be refreshing regularly. By 'refresh' a simple "FIND FIRST <tablename>." would suffice. However if connected users don't refresh their session to update their timestamp within an acceptable time, during this waiting period a separate "PROSHUT -C disconnect" command could be used in a different shell session to forcibly disconnect these users inactive on the database server side.
Only one index can be activated at a time, if multiple indexes are being activated with IDXACTIVATE in separate command lines, the schema timestamp check will follow for each activation.
When IDXACTIVATE activates and builds the index, it bundles [recs n] number of records into transactions. By default, 100 records are bundled into one transaction. The number specified for the [recs n] value will redefine the number of records to bundle into one transaction.
While IDXACTIVATE is in the key building phase, user sessions cannot perform CUD activity against this table:
Table is locked. Record create failed. (328) (6527)
The IDXACTIVATE utility always turns on table locking. If the use-index is unique, it releases the lock for the remainder of its operations. If the use-index is non-unique, it re-acquires a lock on the table/partition. Regardless of the use-index, it acquires a table/partition lock while deleting any existing index blocks (if this index were previously active for example).
Once the IDXACTIVATE command completes, the index is active and all users can access it again.
While the IDXACTIVATE feature was introduced in OpenEdge 10.1A, OpenEdge 10.2B08, 11.2.1, 11.3.0 or later is recommended. Previously there were issues where interrupting an IDXACTIVATE during the key build phase, leaves the index as active (but it's not built) and another that left admin locks behind when interrupted. Otherwise please refer to the Articles referenced below for additional considerations.
