Comment Finding Inactive Indexes

Rob Fitzpatrick Sponsor
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. :rolleyes:
The following code shows inactive indexes. It works in v10 and v11.

/*  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.

    for more details.
    In 10.2B and below, we still use _index._active.

    Rob Fitzpatrick

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           
  /* 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 ) ) ).

    if v-q:query-off-end then leave.

    if v-b::_object-state <> 0 then      /* this index is not active */
      find dictdb._index no-lock where _index._idx-num = v-b::_object-number no-error.
      find dictdb._file no-lock of _index no-error.
        v-b::_object-state  column-label "Object state"
  delete object v-q.

  /* 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

Cringer Moderator
Staff member
What's new in 11.7.1 on this Rob? As I understand it they've made some changes to the online activation of indexes?

Rob Fitzpatrick Sponsor
Sorry, I should have mentioned releases. I tested on 10.2B08 and 11.6.3. Haven't been hands-on with 11.7.x yet.

From the 11.7 NeRF:
Database Client Notification is a communication mechanism between clients and the database, informing
clients of the need to refresh their schema cache. Notification enables a more timely completion of certain
database administrative actions, such as activating an inactive index. Prior to OpenEdge Release 11.7,
certain database utilities required clients to disconnect, even though the utility can execute while the database
is online. The disconnect of the clients is required because these utilities update index schema. Database
Client Notification allows clients to remain connected and refresh their schema in step with the utility
execution. Notification enables the index activation processes to move to completion more rapidly.

So this should help with the first point above, i.e. clients having their schema caches updated to reflect the new index. I supposed that would at least make it available for dynamic queries. This is speculation, I haven't tested it yet.