T22-2020 Creating a Domain Index on ACD Data Mart for Direct 2018, or a previous representation needed for Direct 2020/2021

BIOVIA Direct

Program

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) Data Mart

Operating System

All operating systems supported with BIOVIA Direct

Description

BIOVIA currently provides ACD Data Mart in the formats for BIOVIA Direct 2017 R2, 2019, 2020 and 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 2018, you must first install the BIOVIA database in Direct 2017 R2 format and subsequently upgrade the chemistry domain index for your installed BIOVIA Direct version.

The database 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 you however are running BIOVIA Direct 2020 or 2021 but prefer the previous representation of haptic bonds and biological sequences, you must initially install the ACD Data Mart of the Direct 2019 format and then update the chemistry domain index for Direct 2020 or Direct 2021.

Resolution

1.    Download and Install ACD Data Mart of Direct 2017R2 format, if you have Direct 2018. Otherwise install Direct 2019 format for your Direct 2020 or 2021. For details on loading the database into Oracle, please consult install_datamart.pdf, which is included in the ACD Data Mart download zip file.

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 as the matching Direct version is not installed:

. . .

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

ORA-39083: Object type INDEX:"ACDDM"."MDM_CHEMISTRY_IDX" failed to create with error:

ORA-29833: indextype does not exist

Failing sql is:

CREATE INDEX "ACDDM"."MDM_CHEMISTRY_IDX" ON "ACDDM"."MDM_CHEMISTRY" ("CTAB")  INDEXTYPE IS "C$DIRECT2019"."MXIXMDL" ...

Additionally, you might see the following errors if the Data Mart schema is already enabled for your Direct version.

ORA-31684: Object type SYNONYM:"ACDDM"."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 statement below should return at least 13 rows. If it returns fewer than 13 rows, please contact BIOVIA Support for assistance with upgrading the database. (Your Data Mart schema name may be inv_datamart if you are using CISPro MSB.)

sqlplus acddm/******@your_aliasName

SQL> select table_name from user_tables where table_name like '%_IDX_%';

TABLE_NAME

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

MDM_CHEMISTRY_IDX_SKY2

MDM_CHEMISTRY_IDX_SGRP

MDM_CHEMISTRY_IDX_PROP

MDM_CHEMISTRY_IDX_NEC

MDM_CHEMISTRY_IDX_LOG

MDM_CHEMISTRY_IDX_IKY2

MDM_CHEMISTRY_IDX_FSUP

MDM_CHEMISTRY_IDX_FSIX

MDM_CHEMISTRY_IDX_FSDL

MDM_CHEMISTRY_IDX_FMLA

MDM_CHEMISTRY_IDX_FLEX

MDM_CHEMISTRY_IDX_CTAB

MDM_CHEMISTRY_IDX_CRCV

MDM_CHEMISTRY_IDX_CONV

MDM_CHEMISTRY_IDX_CCLK

MDM_CHEMISTRY_IDX_BSQ

16 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 2018, 2020 or 2021.

SQL> execute c$direct2021.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 2021 SP1 (Microsoft Windows Oracle12) (21.1.100.9)

Copyright (c) Dassault Systemes, 1999-2020

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: MDM_CHEMISTRY_IDX  Table: MDM_CHEMISTRY

SQL> select mdlaux.upgradeindexes_upgrade from dual;

UPGRADEINDEXES_UPGRADE

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

Created index: MDM_CHEMISTRY_IDX

Jump to Step 7.

Scenario 2)  If Direct 2019 is not installed ( this is the case you need to create Direct 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, for example:

SQL> create index mdm_chemistry_idx on mdm_chemistry(ctab) indextype is c$direct2018.mxixmdl parameters ('noaction');

Index created.

This completes the domain index creation for Direct 2018.  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 ACDDM_Direct2021_Update.sql that is included in your download package. The script will address the issue DIR-4107, that is explained in the BIOVIA Direct 2021 Product Release Document in more detail. The script may take a few hours to complete.

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

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 is 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.

SQL> 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

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

MDM_CHEMISTRY_IDX MDM_CHEMISTRY VALID    VALID        VALID  C$DIRECT2021

2.    Verify that there are no synonyms owned by the previous Direct version.

SQL> select distinct table_owner from user_synonyms;

TABLE_OWNER

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

C$DIRECT2021

3.    Run a search in the database and verify that it returns hits with no errors, for example:

SQL> SELECT COUNT(*) FROM MDM_CHEMISTRY 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.