1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Comment Finding Inactive Indexes

Discussion in 'Database Admin' started by Rob Fitzpatrick, Sep 12, 2017.

  1. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com 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.

    Code (text):
    1. /*  dispinactive.p
    2.    
    3.     Display inactive indexes in the database with the
    4.     dictdb alias.
    5.    
    6.     In 11.0+, index activation status is stored in
    7.     _storageobject._object-state; 0 means active,
    8.     1 means inactive.  _index._active is not reliable.
    9.     It may show an index active when it is effectively
    10.     inactive (and _object-state = 1), e.g. after the
    11.     index has been added online via the Dictionary and
    12.     before it is activated with idxactivate or idxbuild.
    13.  
    14.     See http://knowledgebase.progress.com/articles/Article/000051550/p
    15.     for more details.
    16.    
    17.     In 10.2B and below, we still use _index._active.
    18.  
    19.     Rob Fitzpatrick
    20.     09/12/2017
    21.    
    22.  */
    23.  
    24. define variable v-q as handle no-undo.
    25. define variable v-b as handle no-undo.
    26.  
    27. find first dictdb._db.
    28.  
    29. /* test _storageobject for an _object-state field */
    30. find dictdb._file no-lock where _file-name = '_storageobject'.
    31. find dictdb._field no-lock of _file where _field-name = '_object-state' no-error.
    32.  
    33. if available( _field ) then          
    34. do:
    35.   /* this _storageobject table has a field called '_object-state'
    36.      we rely on _object-state to determine whether an index is active (0) or inactive (1)
    37.   */  
    38.  
    39.   create query v-q.
    40.  
    41.   v-b = buffer dictdb._storageobject:handle.
    42.   v-q:set-buffers( v-b ).
    43.   v-q:query-prepare ( substitute( "for each dictdb._storageobject
    44.                                      where _storageobject._db-recid    = &1
    45.                                        and _storageobject._object-type = 2", recid( _db ) ) ).
    46.   v-q:query-open().
    47.  
    48.   repeat:
    49.     v-q:get-next().
    50.     if v-q:query-off-end then leave.
    51.  
    52.     if v-b::_object-state <> 0 then      /* this index is not active */
    53.     do:
    54.       find dictdb._index no-lock where _index._idx-num = v-b::_object-number no-error.
    55.       find dictdb._file no-lock of _index no-error.
    56.       display
    57.         _file._file-name
    58.         _index._index-name
    59.         v-b::_object-state  column-label "Object state"
    60.       .
    61.     end.
    62.    
    63.   end.
    64.  
    65.   v-q:query-close.
    66.   delete object v-q.
    67. end.
    68.  
    69. else                          
    70.   /* we rely on _index._active to determine index activation state */
    71.  
    72.   for each dictdb._storageobject
    73.     where _storageobject._db-recid    = recid( _db )
    74.       and _storageobject._object-type = 2:
    75.     find dictdb._index no-lock where _index._idx-num = _storageobject._object-number no-error.
    76.     find dictdb._file no-lock of _index no-error.
    77.    
    78.     if not _index._active then
    79.       display
    80.         _file._file-name
    81.         _index._index-name
    82.         _index._active
    83.       .
    84.  
    85.   end.
     
    Marco Mendoza and LarryD like this.
  2.  
  3. Cringer

    Cringer ProgressTalk.com 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?
     
  4. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com 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:
    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.
     

Share This Page