T77-2017 (Revised November 2019): Upgrading Domain Index on ACD Data Mart to Direct 2018 or 2020

Program

BIOVIA Direct 2018
BIOVIA Direct 2020

Database

BIOVIA Available Chemicals Directory (ACD) Data Mart

Operating System

All operating systems supported for BIOVIA Direct 2018 or 2020

Description

BIOVIA currently provides ACD Data Mart formatted for BIOVIA Direct 2017 R2 and 2019 only.

If you have installed BIOVIA Direct 2018 or 2020 and want to use ACD Data Mart with one of those 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 R2 database and upgrade it to 2018, or install a 2019 database format and upgrade to 2020.

Resolution

Scenario 1) Both Direct 2019 and Direct 2020 (or Direct 2017 R2 and Direct 2018) are installed on your Oracle instance

The workflow basically follows the steps as described in the chapter “Upgrading Indexes” in the BIOVIA Direct Administration Guide. Steps 2 through 6 should complete quickly, in just seconds on most servers.

1. Install ACD Data Mart. For details on loading the database into Oracle, please consult install_datamart.pdf, which is included in the ACD Data Mart download zip file.

2. Log in as the ACD Data Mart schema owner (yours may be inv_datamart if you are using CISPro MSB).

sqlplus acddm/******@your_aliasName

3. Drop the old synonyms for Direct:

SQL> execute mdlaux.unsetup

PL/SQL procedure successfully completed.

4. Create the Direct synonyms (replace 2020 with 2018, if you are upgrading to 2018).

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)

6. Upgrade the domain index:

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

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

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

PL/SQL procedure successfully completed.

This completes the upgrade of the ACD Data Mart domain index from Direct 2019 to 2020 (or from 2017 R2 to 2018) format.

Scenario 2) Direct 2020 (or 2018) is installed and it is the only Direct version.

The following steps upgrade the ACD Data Mart domain index to the Direct 2020 (or 2018) format.

1. Install ACD Data Mart. For details on loading the database into Oracle, please consult install_datamart.pdf, which is contained within the ACD Data Mart download zip file.

Since the Direct version in the ACD Data Mart does not match the installed Direct version, the datapump import will produce errors. That is, all objects will import successfully, but the domain index creation will fail with errors similar to these below.

. . .

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

ORA-29833: indextype does not exist

. . .

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. If the import created a domain index, it must be dropped with the FORCE option before you continue:

SQL> drop index MDM_CHEMISTRY_IDX force;

drop index MDM_CHEMISTY_IDX 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 Direct synonyms (replace 2020 with 2018, if you are upgrading to 2018).

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)

6. Create the domain index using the ‘NOACTION’ parameter:

SQL> create index mdm_chemistry_idx on mdm_chemistry(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.

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

PL/SQL procedure successfully completed.

This completes the upgrade of the ACD Data Mart domain index from Direct 2019 to 2020 (or from 2017 R2 to 2018) format.

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

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$DIRECT2020

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

SQL> select distinct table_owner from user_synonyms;

TABLE_OWNER

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

C$DIRECT2020

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\direct2020\examples\molfiles\didehydroalanine.mol')=1;

COUNT(*)

----------

272

How to contact BIOVIA Support

If you have any questions, please contact BIOVIA Support.