When applying Schema Changes with inactive indexes, use the PROUTIL -C IDXACTIVATE feature introduced in OpenEdge 10.1A.
- Unlike IDXBUILD, this method will not require After-Imaging to be disabled and can be run both online and offline. Article Why does After-Imaging have to be disabled to run an index rebuild?
- While Offline IDXFIX enables inactive indexes, which does not require AI to be disabled; it necessitates downtime. For Replication enabled databases however, while AI remains enabled, replication will first have to be disabled in order for IDXFIX offline to run and therefore necessitates re-baselining replication.
Once the IDXACTIVATE command completes, the index is active and all users can access it. Updates are recorded in the AI notes and will be populated to the target database when the associated AI transaction notes are applied (roll forward against hotspare or through AI Block replication to the target database(s).
Example:
A new index "idx01" was added to the Customer table of a sports2000 database.
Either the Primary index CustNum (by default) or another non-word index can be used to build the new inactive index as long as they are active
To activate this index either online or offline without having to first disable after-imaging run:
$ proutil sports2000 -C idxactivate Customer.idx01 useindex name
A non-unique index has been selected as the useindex index.
Additional locking is required with the use of this index name.
Do you want to continue? (y/n)
y
Index Activate: BEGIN activation of idx01. (13258)
Index Activate: recs value: 100 refresh rate: 60. (13259)
Activating Index idx01 at Database timestamp 1391432926 . (12811)
Index Activate: Index idx01 No. 63 - 1117 keys added. (13244)
Index Activate: Index idx01 No. 63 - 1117 keys processed. (13245)
Index Activate: Index idx01 has been activated.
Considerations:
1. By not disabling After-Imaging, introduces an additional overhead in doing so, depending on what the associated schema updates entail.
While field-level schema changes cause no big activity on AI/BI files, only deleting or adding an active index will potentially generate a lot of bi/ai activity; indexes eventually need to be activated and in doing so generate a lot BI/AI of activity. This is generally why AI is disabled before doing schema updates, as it's often faster to re-baseline replication target(s) / hotspare baselines than apply all the related ai files that are generated by the schema update. It is not however a requirement as this Article outlines.
2. IDXACTIVATE should only be used when certain that the index used to build the inactive index is in a good state. IDXCHECK should be run against this index before-hand to confirm it's integrity.
3. In order to use IDXACTIVATE the Primary index must be active and is used by default unless "
useindex" is specified for another active index on the same table. The parameter after
useindex should only be the index name, it should not contain the table-name.index-name pairing (see example above).
4. IDXACTIVATE turns on table locking, which means that no-one can execute CUD activity against this table until it completes:
- If the use-index is a unique index, 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,
IDXACTIVATE acquires a table/partition lock while in the process of deleting existing index blocks (if this index were previously active for example).
Therefore, consider specifying a
unique index to build the inactive index.
If there are currently no unique indexes on that particular table, the ' application downtime while the table is locked can be minimised by running "
proutil -C idxfix", Option 3 for the new index online. IDXFIX online will not activate the index, however it will pre-build the index key entries. Running IDXACTIVATE afterwards will then be faster.
5. While the IDXACTIVATE feature was introduced in OpenEdge 10.1A, OpenEdge 10.2B08, 11.2.1, 11.3.0 or later is recommended. Otherwise please refer to the Articles referenced below for additional considerations.
For Progress versions pre OpenEdge 10.1A:
Irrespective of version, the fact remains that when AI is enabled, (which Replication relies on) IDXBUILD cannot be run without first disabling After-imaging, which means having to disable replication if indexes are added as INACTIVE.
In previous versions, if the database is not enabled for replication, but does have AI enabled, an alternative offline option is IDXFIX in 'report and fix' mode, which will activate the inactive indexes added in the schema update without having to disable after-imaging.
In previous versions, the only alternative for replication enabled databases is to add the new indexes ACTIVE, ensuring sufficient AI and BI space. In a replication environment, before adding the ACTIVE index consider stopping the replication server after synchronisation with target has completed and restart the RPLS after the new index has been applied and built against the source database.
Specific to the replication environment, for online schema changes to source:
- When the source database has been started, but the Replication Agent or target database are not running, the schema is locked by the Replication server for recovery purposes preventing schema updates.
- When the RPLS and RPLA then establish contact, Schema updates are not allowed while the Replication server is performing synchronization. If schema updates are being performed when failure recovery synchronization begins, all source database updates will block until failure recovery is complete.
- Only once replication has synchronised and with the "schema-Lock-Action=force" parameter defined in the source.repl.properties configuration, as long as the schema updates adhere to the online schema change rules, they will apply online with active indexes.
- Irrespective of replication, (apart from the online schema rules that can be applied), user sessions timestamps prevent the online schema update - therefore disconnect all user sessions prior to starting the schema update. They can reconnect as soon as it has started.
