T103-2015 (Revised November 2019) Upgrading Domain Index on BIOVIA Databases to Direct 2016, 2017 R2, 2018 or 2020

BIOVIA Direct 2016

Program

• BIOVIA Direct 2016
• BIOVIA Direct 2017 R2
• BIOVIA Direct 2018
• BIOVIA Direct 2020

Database

• BIOVIA Available Chemicals Directory (ACD)
• BIOVIA Screening Compounds Directory (SCD)
• MDDR
• BIOVIA Toxicity

Operating System

All operating systems supported for BIOVIA Direct.

Description

BIOVIA currently provides chemical sourcing databases (ACD, SCD) and bioactivity databases (MDDR, Toxicity) formatted for BIOVIA Direct 9.5, 2017 and 2019.

If you have installed the BIOVIA Direct 2016, 2017 R2, 2018, or 2020, and want to use a BIOVIA database in one of these formats, you must initially install the database in one of the provided BIOVIA Direct formats and then upgrade it to the desired format, for example, install a 2017 database and upgrade it to 2018. This Technical Note describes the workflow to perform the required domain index upgrade.

Note: Releases of BIOVIA databases of a given Direct format end when, for the corresponding version of Direct, BIOVIA mainstream support in a regulated environment ends.  After February 29, 2020, Direct 9.5 format database releases will end and Direct 2020 format database releases will start. The BIOVIA Direct Product Release document of your Direct version has the end-of-support date.

Resolution

Depending on the BIOVIA Direct version(s) installed in your Oracle database you must follow one of two scenarios. Which scenario to follow is described in the decision matrix below. Based on the decision then follow the detailed steps for either Scenario 1 or Scenario 2.

Table 1: Decision matrix for determining which Scenario to use.

 Scenario 1)

The workflow basically follows the steps as described in the chapter “Upgrading Indexes” in the BIOVIA Direct Administration Guide. The steps 2 through 6 should complete very fast in just seconds on most servers. The steps 1 and 7 may take some time to complete.
The following steps upgrade the BIOVIA ACD database as an example.

1. Install the BIOVIA database in the format that matches the secondary Direct installation in your environment. For details on loading the BIOVIA database into Oracle please consult the BIOVIA_DirectCompatibleDatabases_InstallationGuide.pdf file provided with the BIOVIA database.
2. Log in as your database schema owner:
   sqlplus acd/*********
3. Drop the old synonyms for Direct:
   SQL> execute mdlaux.unsetup
   PL/SQL procedure successfully completed
4. Create the Direct synonyms for the installed target Direct version (2016, 2017 R2, 2018, or 2020). For instance for Direct 2020
   SQL> execute c$direct2020.mdlauxop.setup
   PL/SQL procedure successfully completed.
5. Verify the version number for the target Direct version by executing the following SQL:
   SQL> select mdlaux.version from dual;
   VERSION
   -----------------------------------------------------------------------
   BIOVIA Direct
   Revision 2020 (Microsoft Windows Oracle12) (20.1.0.462)
   Copyright (c) Dassault Systemes, 1999-2019
6. Upgrade the domain index:
   SQL> select mdlaux.upgradeindexes_prepare from dual;

UPGRADEINDEXES_PREPARE-----------------------------------------------------------------------Prepared index for upgrade/recreate: ACD2D_MDLIX  Table: ACD2D_MOLTABLESQL> select mdlaux.upgradeindexes_upgrade from dual;UPGRADEINDEXES_UPGRADE-----------------------------------------------------------------------Created index: ACD2D_MDLIX

7.    (When upgrading to Direct 2020 only) Recreate Fastsearch. This step may take some time to complete.

SQL> execute mdlaux.recreatefastsearch('dbname_mdlix');
where dbname_mdlix is the domain index name. For example,
SQL> execute mdlaux.recreatefastsearch('ACD2D_MDLIX');
PL/SQL procedure successfully completed.

Note: There is a known issue in Direct 2016 on Oracle with non-American NLS session parameters, for example German. With these NLS settings the mdlaux.upgradeindexes_prepare function may fail (issue and resolution on 3DS Support > Knowledge Base at https://support.3ds.com/knowledge-base/ and search for a bug report “BR10000264204”).  This issue is fixed in the Direct 2017 R2 version and later.

This completes the upgrade of the domain index from the Direct 9.5, 2017 or 2019 format.

Scenario 2)

The following steps upgrade the BIOVIA ACD database as an example.

1.    Install the BIOVIA database in the format which best matches your installed Direct version(s). Please see the above decision matrix to determine the database version to load in your environment; it should be either the Direct 9.5, 2017 or 2019 format. For details on loading the BIOVIA database into Oracle please consult the BIOVIA_DirectCompatibleDatabases_InstallationGuide.pdf file provided with the BIOVIA database.
As none of the database formats provided by BIOVIA will match your installed Direct version(s) you will notice errors during the datapump import of the data into Oracle. With this, the datapump call may look like this:

C:\temp_acd>impdp userid=acd/******** parfile=acddirect_dp.par directory=dpimp

All objects will import successfully, but the domain index creation will fail with errors similar to those below as a matching Direct version is not installed:

. . .
. . .

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX:"ACD"."ACD2D_MDLIX" failed to create with error:
ORA-29833: indextype does not exist
Failing sql is:
CREATE INDEX "ACD"."ACD2D_MDLIX" ON "ACD"."ACD2D_MOLTABLE" ("CTAB")  INDEXTYPE IS "C$DIRECT2017"."MXIXMDL" ...

Additionally, you might see the following error if the Oracle schema is already enabled for your target Direct version.
ORA-31684: Object type SYNONYM:"ACD"."MDLAUX" already exists

2.    After the import, verify that the tables with the chemistry index data exist, per the chapter ‘Upgrading Indexes’ in the Direct Administration Guide. The following statement should return at least 13 rows. If it returns less than 13 rows please contact BIOVIA Support for assistance with upgrading the database.

sqlplus acd/********
SQL> select table_name from user_tables where upper(table_name) like upper('%_MDLIX_%');
TABLE_NAME
---------------------------------------------------
ACD2D_MDLIX_BSQ
ACD2D_MDLIX_CCLK
ACD2D_MDLIX_CONV
ACD2D_MDLIX_CTAB
ACD2D_MDLIX_FLEX
ACD2D_MDLIX_FMLA
ACD2D_MDLIX_FSDL
ACD2D_MDLIX_FSIX
ACD2D_MDLIX_FSUP
ACD2D_MDLIX_IKY2
ACD2D_MDLIX_LOG
ACD2D_MDLIX_NEC
ACD2D_MDLIX_PROP
ACD2D_MDLIX_SGRP
ACD2D_MDLIX_SKY2
15 rows selected.

3.    If the import created a domain index, it must be dropped with the FORCE option before you continue:
SQL> drop index dbname_mdlix force;
where dbname_mdlix is the domain index name. For example,
SQL> drop index acd2d_mdlix force;
drop index acd2d_mdlix force
           *
ERROR at line 1:
ORA-01418: specified index does not exist
Note: The drop index command will fail if the import did not create the domain index; this is normal and the error can be ignored.

4.    Create the synonyms for the target Direct version, for example if you have Direct 2020:
SQL> execute c$direct2020.mdlauxop.setup
PL/SQL procedure successfully completed.

5.    Verify the version number for the target Direct version by executing the following SQL:
SQL> select mdlaux.version from dual;
VERSION
--------------------------------------------------------------------
                                 BIOVIA Direct
            Revision 2020 (Microsoft Windows Oracle12) (20.1.0.462)
                   Copyright (c) Dassault Systemes, 1999-2019

6.    Create the domain index using the ‘NOACTION’ parameter:
SQL> create index dbname_mdlix on dbname_moltable(ctab) indextype is c$direct2020.mxixmdl parameters ('noaction');
where dbname_mdlix is the domain index name and dbname_moltable is the molecule table name. For example,
SQL> create index acd2d_mdlix on acd2d_moltable(ctab) indextype is c$direct2020.mxixmdl parameters ('noaction');
Index created.

7.    (When upgrading to Direct 2020 only) Recreate Fastsearch. This step may take some time to complete.
SQL> execute mdlaux.recreatefastsearch('dbname_mdlix');
where dbname_mdlix is the domain index name. For example,
SQL> execute mdlaux.recreatefastsearch('ACD2D_MDLIX');
PL/SQL procedure successfully completed.

This completes the upgrade of the domain index from the Direct 9.5, 2017 or 2019 format.
 
Note: Molecule table names for BIOVIA databases are:

Optional: You can run queries such as the following if you want to further verify the status of your upgrade or to help troubleshoot any issues:
1.    Verify that the status of your domain index(es) is/are VALID, and the ITYP_OWNER is your BIOVIA Direct version for your database. The following section is an example with Direct 2020. Note: All three ‘STATUS’ columns must be VALID:

select index_name, table_name, status, domidx_status, domidx_opstatus, ityp_owner from user_indexes where index_type = 'DOMAIN';
index_name   table_name      STATUS   DOMIDX_STATU DOMIDX ityp_owner
------------ --------------  -------- ------------ ------ --------
ACD2D_MDLIX  ACD2D_MOLTABLE  VALID    VALID        VALID  C$DIRECT2020

2.    Verify that there are no synonyms owned by the previous Direct version. If you have Direct 2020 (or 2018 or 2017R2 or 2016), TABLE_OWNER includes C$DIRECT2020 (or C$DIRECT2018 or C$DIRECT2017R2 or C$DIRECT2016).
SQL> select distinct table_owner from user_synonyms;
TABLE_OWNER
------------------------------
ACD
C$DIRECT2020

3.    Run a search in the database, for example:
SELECT COUNT(*) FROM ACD2D_MOLTABLE WHERE SSS(CTAB,'c:\BIOVIA\direct2020\examples\molfiles\didehydroalanine.mol')=1;
        COUNT(*)
----------
           272

How to contact BIOVIA Support

If you have any questions, please contact BIOVIA Support.