T21-2020 Creating a Domain Index on BIOVIA Databases for an older version of Direct, or a previous representation needed for Direct 2020/2021

BIOVIA Direct

(Revised June 2021, Original Technical Note T103-2015)

Program

BIOVIA Direct 2017 R2

BIOVIA Direct 2018

BIOVIA Direct 2020 (only when you want previous representation of Haptics and SCSR)

BIOVIA Direct 2021 (only when you want previous representation of Haptics and SCSR)

Database

BIOVIA Available Chemicals Directory (ACD)

BIOVIA Screening Compounds Directory (SCD)

MDDR

BIOVIA Toxicity

Operating System

All operating systems supported with BIOVIA Direct

Description

BIOVIA currently provides its chemical sourcing databases (ACD, SCD) and its bioactivity databases (MDDR, Toxicity) in the formats to work with BIOVIA Direct 2019, 2020 or 2021SP1. If you are running any of these BIOVIA Direct versions, you can install the BIOVIA databases in their native formats (see further notes on Direct 2020/2021 below).

If you however are running BIOVIA Direct 2017R2 or 2018, you must first install the BIOVIA database in Direct 2019 format and subsequently create the chemistry domain index for your installed BIOVIA Direct version.

The databases in Direct 2020 and 2021 formats apply a new representation for Haptic Bonds and use the Self Contained Sequence Representation (SCSR) for biological sequences. For more information on these representations, see the BIOVIA Chemical Representation document from the BIOVIA Direct documentation package. If you are running BIOVIA Direct 2020 or 2021 and prefer the updated representation of these chemical entities, then just download and install the database version that matches your installed Direct 2020 or 2021 version. There is no need to follow the steps in this Technical Note. If, however, you are running BIOVIA Direct 2020 or 2021 but prefer the previous representation of haptic bonds and biological sequences, you must initially install the BIOVIA database in Direct 2019 format and then update the chemistry domain index for Direct 2020 or Direct 2021.

Resolution

The following steps are for the BIOVIA ACD database as an example.

1.    Install the BIOVIA database of the Direct 2019 format in your environment. For details on loading the database, please read BIOVIA_DirectCompatibleDatabases_InstallationGuide.pdf file provided with the BIOVIA database.

The import may return some errors, when your Oracle instance does not have the matching Direct version for the dump files. All objects will basically import successfully, but the domain index creation step will fail with errors similar to those below, when the 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$DIRECT2019"."MXIXMDL" ...

Additionally, you might see the following error if the Oracle schema is already enabled for your target BIOVIA 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.    Drop existing synonyms for BIOVIA Direct (If you do not have Direct 2019, It returns ORA-06550 errors. Ignore the error and go to step 4).

SQL> execute mdlaux.unsetup

PL/SQL procedure successfully completed.

4.    Create the BIOVIA Direct synonyms for the installed target Direct version. Your version can be 2017R2, 2018, 2020 or 2021.

SQL> execute c$direct2018.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 2018 (Microsoft Windows Oracle12) (18.1.0.365)

             Copyright (c) Dassault Systemes, 1999-2017

6.    Create or upgrade the domain index:

Scenario 1)  Direct 2019 is installed, and either Direct 2020 or 2021 also installed on the same instance. Domain index was created for Direct 2019 successfully. Then run these commands to upgrade the domain index. Otherwise, go to Scenario 2).

SQL> select mdlaux.upgradeindexes_prepare from dual;

UPGRADEINDEXES_PREPARE

-----------------------------------------------------------------------

Prepared index for upgrade/recreate: ACD2D_MDLIX  Table: ACD2D_MOLTABLE

SQL> select mdlaux.upgradeindexes_upgrade from dual;

UPGRADEINDEXES_UPGRADE

-----------------------------------------------------------------------

Created index: ACD2D_MDLIX

Jump to Step 7.

Scenario 2)  If Direct 2019 is not installed ( this is the case where you need to create Direct 2017 R2 or 2018 format domain index, or 2020/2021 format with previous representation) and you had ORA-39083 and CREATE INDEX errors during the datapump import.

Create the domain index using the ‘NOACTION’ parameter:

SQL> create index dbname_mdlix on dbname_moltable(ctab) indextype is c$direct2018.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$direct2018.mxixmdl parameters ('noaction');

Index created.

This completes the domain index creation of your target Direct version, if you have Direct 2018 or 2017 R2.  If you created the domain index for Direct 2020 or 2021, continue to the next step.

7.    (After upgrading or creating domain index for Direct 2021 only. Skip this and go to Step 8, if your domain index is for Direct 2020.)

Run the <dbname>_Direct2021_Update.sql script that is included in your download package, where <dbname> is your database name. The script will address the issue DIR-4107, that is explained in the BIOVIA Direct 2021 Product Release Document in more detail.

8.    Recreate search keys and fastsearch indexing data. These commands take some time to complete.

SQL> execute mdlaux.recreatekeys('dbname_mdlix');

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

where dbname_mdlix is the domain index name. For example,

SQL> execute mdlaux.recreatekeys('ACD2D_MDLIX');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

This completes the upgrade of the domain index to Direct 2020 or 2021.

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 2021. 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$DIRECT2021

2.    Verify that there are no synonyms owned by the previous Direct version. If you have Direct 2021 (or 2020 or 2018 or 2017R2), TABLE_OWNER includes C$DIRECT2021 (or C$DIRECT2020 or C$DIRECT2018 or C$DIRECT2017R2).

SQL> select distinct table_owner from user_synonyms;

TABLE_OWNER

------------------------------

C$DIRECT2021

ACD

3.    Run a search in the database, for example:

SELECT COUNT(*) FROM ACD2D_MOLTABLE WHERE SSS(CTAB,'c:\BIOVIA\direct2021\examples\molfiles\didehydroalanine.mol')=1;

        COUNT(*)

----------

             280

How to contact BIOVIA Support

If you have any questions, please contact BIOVIA Support.