Just passing along a couple of things I learned recently.
- When you add an index online for an existing table with the "add new objects on-line" option, it will appear to be active but it won't actually be available until it is activated with proutil idxactivate or idxbuild. Until it is activated, clients trying to use the new index will get a 16488 error.
- In 11.0+, _index._active is no longer reliable. In the case above, _active is true but the index is not active. Due to multi-tenancy and table partitioning, index activation status is now handled at the storage object level. For index records in _storageobject, when _storageobject._object-state = 0 the index is active and when it equals 1 the index is inactive. I don't know if this field can have other values or if it's a bitmap like _object-attrib. For now I'm assuming not. Maybe someday PSC will document the system tables.
/* dispinactive.p Display inactive indexes in the database with the dictdb alias. In 11.0+, index activation status is stored in _storageobject._object-state; 0 means active, 1 means inactive. _index._active is not reliable. It may show an index active when it is effectively inactive (and _object-state = 1), e.g. after the index has been added online via the Dictionary and before it is activated with idxactivate or idxbuild. See http://knowledgebase.progress.com/articles/Article/000051550/p for more details. In 10.2B and below, we still use _index._active. Rob Fitzpatrick 09/12/2017 */ define variable v-q as handle no-undo. define variable v-b as handle no-undo. find first dictdb._db. /* test _storageobject for an _object-state field */ find dictdb._file no-lock where _file-name = '_storageobject'. find dictdb._field no-lock of _file where _field-name = '_object-state' no-error. if available( _field ) then do: /* this _storageobject table has a field called '_object-state' we rely on _object-state to determine whether an index is active (0) or inactive (1) */ create query v-q. v-b = buffer dictdb._storageobject:handle. v-q:set-buffers( v-b ). v-q:query-prepare ( substitute( "for each dictdb._storageobject where _storageobject._db-recid = &1 and _storageobject._object-type = 2", recid( _db ) ) ). v-q:query-open(). repeat: v-q:get-next(). if v-q:query-off-end then leave. if v-b::_object-state <> 0 then /* this index is not active */ do: find dictdb._index no-lock where _index._idx-num = v-b::_object-number no-error. find dictdb._file no-lock of _index no-error. display _file._file-name _index._index-name v-b::_object-state column-label "Object state" . end. end. v-q:query-close. delete object v-q. end. else /* we rely on _index._active to determine index activation state */ for each dictdb._storageobject where _storageobject._db-recid = recid( _db ) and _storageobject._object-type = 2: find dictdb._index no-lock where _index._idx-num = _storageobject._object-number no-error. find dictdb._file no-lock of _index no-error. if not _index._active then display _file._file-name _index._index-name _index._active . end.