BIOVIA Direct: How can I check on the progress of running MDLAUX.RECREATEFASTSEARCH for my domain index?

We are upgrading our database to a later version of Direct, using the steps in Chapter 6, ‘Upgrading Indexes’ in the BIOVIA Direct Admin Guide.

We are running the step below:

SQL> execute mdlaux.recreatefastsearch('moltable_ix');

where ‘moltable_ix’ is the name of our domain index.

Is there a way I can check on the progress of this procedure?

Solution:

You can open a separate SQL session to the database and use the MDLAUX.LOGTABLE function to check on the progress.

For example, if the domain index name is TOX_STR_MDLIX, you can run a query such as the following to check on the status:

select msg, dattim from table(mdlaux.logtable('tox_str_mdlix', 'asc'));

where the MSG column will include the rows processed, and the DATTIM column includes the date and time.

If you would like to include minutes and seconds from the DATTIM column, you can use the Oracle TO_CHAR function, for example:

select msg, to_char(dattim, 'DD-MON-YYYY:HH24:MI:SS')
from table(mdlaux.logtable('tox_str_mdlix', 'asc'));

You can sort descending (‘DESC’) if you prefer to see the most recent rows processed first.

Here is an example of the first few rows processed:

​​​​​​​​​​​​​​





This shows the procedure has completed:

​​​​​​​


The MDLAUX.LOGTABLE procedure allows you to view information from the index log table indexname_LOG that logs status information about these commands or procedures:

CREATE INDEX
ALTER INDEX
MDLAUX.SCANINDEX
MDLAUX.UPDATEPENDINGINVERSIONS
MDLAUX.RECREATEKEYS
MDLAUX.UPDATEPENDINGFASTSEARCH
MDLAUX.RECREATEFASTSEARCH

For more information, see MDLAUX.LOGTABLE in the BIOVIA Direct Administration Guide.