Setting up the JChem Cartridge for Oracle and its use with Instant JChem

    Introduction

    The performance of Instant JChem searches can be significantly improved by using the JChem cartridge for Oracle, as the computational requirements are load-balanced from the client side to the server. In this document, we will guide you through the process of installation and setup of JChem cartridge for Oracle (JCC), for the use with Instant JChem (IJC)/Plexus Connect.

    The recommended layout of the database is shown in the figure below [fig. 1]. Three database schemas are recommended. The first one is the data warehouse (DWH) which contains all the structural and analytical data (you can use existing data in your Oracle database), indexes and tables containing JChem data. The data in the DWH can be accessed through the IJC DB schema which itself contains IJC metadata only. The JChem cartridge for Oracle is installed in the JCHEM schema and is used by the data warehouse for data processing (all the stored procedures needed for JCC operation are there).

    images/download/attachments/1806815/jccSchema.png

    [1]

    Installation of JChem Cartridge for Oracle (JCC)

    We will assume that your environment is set up according to the JCC software requirements:

    Oracle 11gR1 users have to apply the fix for Oracle bug 7713193. For Windows 32-bit, the fix is currently available in patch 8260294. A single patch is also available for Linux x86_64. These patches require Oracle Server to be already at patch-level 11.1.0.7.0.

    One JChem server and one JChem cartridge for Oracle instance is usually sufficient for most use cases. If more than one cartridge instance is needed, please follow the instructions f or installation with Oracle Real Application Cluster.

    Pre-installation tasks (a more detailed version can be found here)

    1. Make sure that your Oracle database is JVM-enabled and properly configured to use Java Stored Procedures. A database created with default settings typically meets this requirement. (If in doubt, refer to the chapter Java Installation and Configuration of your Oracle version's Java Developer's Guide.)

    1. Set the environment variable JAVA_HOME to the home directory of a Oracle Java Development Kit version 1.6 or newer.

    > {primary}
> Please keep in mind that Plexus supports only Java 8 since the version 15.6.22.0 - If the Plexus server will be running on the same machine, Java 8 is necessary.
>

    1. Download the JChem Cartridge installation package and unpack into a permanent location (such as /opt/chemaxon/jchem-cartridge on Unix/Linux or C:\Chemaxon\JChem-Cartridge on Windows), because its files will not be used exclusively during the installation process of JChem Cartridge, but they will also back the operation of the JChem Cartridge server (associated with the JChem Cartridge instance to be installed). The location of the uncompressed package will be the location of the JChem Server. This can be the same server computer where the database resides or a separate hardware.

    1. (Advanced use) Have your DBA set up schema and role. For Data Vault users and for organizations where the roles of the DBA and the application administrator are strictly separated (if this is your case, please follow the steps in the JCC documentation)

    1. Collect information to be prepared to answer the questions that will be asked during the next installation process You will be asked for the

     1.
    Oracle hostname and port;

 1.
    Oracle database name;

 1.
    Host name, port, and maximum allocated memory of the JChem Server.

    > {primary}
    > localhost as hostname can only be applied if JChem Cartridge for Oracle and JChem Server will be located on the same host.
    >

 1.
    DBA login name and password;

 1.
    Name and password of the Cartridge owner;

 1.
    Name of the role for basic JChem Cartridge user privileges (optional);

 1.
    Name for JChemServer as an operating system service (optional).

    Installation (a detailed version can be found here)

    The following steps have to be performed on the host where the JChem Cartridge server will be running:

    1. Start the command interpreter available on your platform and navigate to the cartridge directory.

    > {primary}
> Note for Windows users
>
> Creating a Windows Service requires Administrator (or equivalent) privileges; therefore, the a Command Prompt with Administrator privileges is necessary.
>

    1. Start the installer Start the install.sh shell script or install.bat batch file.

    1. Provide the input requested by the script

    1. If you answer a prompt for a property with a question mark (?), a more detailed description of the property will be displayed.

    1. The connection to the database can be given multiple ways.

     1.
    If you choose sid connection type, you will be asked for host, port and Oracle sid to identify the connection.

 1.
    If you choose service_name connection type, you will be asked for host, port and the service name to identify the connection.

 1.
    If you choose URL connection type, you can enter the whole connection string of the connection.

    1. Set the maximum memory allocated to the JChem server instance, 700m is the default value.

    1. Set the structure cache id of this JChem server instance - JCC1 is the default value. If more than one JChem server instances are to be used, please refer to the installation guide using Oracle RAC.

    1. When prompted for the DBA login, enter an exclamation mark (!), if you don't want the installer program to use DBA account. See this pre-installation task for steps required before installation in such cases. Installation using non-DBA account is not recommended, please, apply it only in compulsory cases.

    1. Decide whether you want a role for the basic JChem Cartridge user privileges to be created. As default, the JCC_BASIC_ROLE with all the necessary privileges is created. If you select “no” you have to define this role manually.

    1. Define the name for the role with the basic JChem Cartridge user privileges, the default name is JCC_BASIC_ROLE.

    1. We recommend to use public synonyms. If they are not created during installation, they have to be granted durin JChem user configuration

    1. After all required input has been provided, you will be asked to confirm them and only after your confirmation will the database and configuration files be changed.

    > {primary}
> Note for Windows users
>
> You will be asked whether you want to install JChemServer as an operating system service, and if you answer y, you will be prompted for the name of the service.
>

    Post-installation tasks

    1. (Linux/Unix users) Create system service control script

    1. The installer (optionally) creates a Windows service on Windows. Linux/Unix users may want to create a system service for JChem Server after the installation program completes successfully.

    1. After the installation of JChem Cartridge is completed, the license file(s) should be copied into a dedicated directory

    The license file(s) should be dropped into the directory  **licenses ** under  **cartridge ** directory (the working directory of the JChem Server). You can view the available licenses using the following query, which returns the list of licensed products and features:

```
select * from table(jchem_core_pkg.getLicenses());
```

> {primary}
> The JChem server must be restarted after moving the license files into the dedicated directory while it is running.
>

    1. After the installation program start JChem Server

    The preferred way to start and stop JChem Server is through the system service facility provided on the host operating system. You also can start and stop using a command interpreter available on your platform (terminal on Unix/Linux, Command Prompt on Windows) in the following way:

    1. Make the cartridge directory your current working directory

    1. call the server script (server.sh on Unix/Linux server.bat on Windows) with the start or stop parameter:

    ```
bash server.sh start
```

or

```
bash server.sh stop
```

or

```
nohup ./server.sh start &
```

(this will prevent process termination on user’s logout)

    Creating index on an existing table

    To be able to use JChem cartridge for Oracle functionalities on tables already existing in the database, creating an cartridge index on a column containing molecular structures in one of the supported representations is necessary. More details on the jc_idxtype can be found in the online documentation. Creation of cartridge index is possible on both standard and JChem tables.

    {primary} Only database user with the JCC_BASIC_ROLE is able to create JCC indexes (jchem by default).

    Before index creation you need to run the following command:

    CALL jchem_core_pkg.use_password(<jchem user password>)

    The index can be created using the following general statement:

    CREATE INDEX <index name> ON <table> (<structure-column>) INDEXTYPE IS jc_idxtype PARAMETERS('param1=paramvalue1,param2=paramvalue2,...');

    {primary} The index name cannot be longer than 22 characters.

    {primary} Make sure you create your index using the data warehouse schema owner, so the related tables are created in the data warehouse database schema.

    {primary} When creating index on JChem tables, please keep in mind that both JChem and JCC has to be the same version. More details on different table types available when using JChem Cartridge for Oracle can be found here.

    There are multiple parameters available when creating an index. Detailed description of the parameters can be found in the documentation. The list can be found below.

    {warning} Before using any of the options, please consult the JChem Cartridge documentation. If no options are selected, the default is applied.

    The following option can be specified only for JChem tables:

    • RegenerateTable: specify this option if you want to create index on a structure table generated by an earlier, incompatible version of JChem.

    In addition to the preceding parameter the following can be used for indices on regular structure tables:

    • idxSubType [ structures | fp ]

    • fp_size

    • fp_bit

    • pat_length

    • structureType

    • structuralfp _config

    • std_config

    • std_conf:sql

    • absoluteStereo [y|n]

    • haltOnError [y|n|nf|nfp]

    • duplicateFiltering [y|n]

    • TDF [y|n]

    • exclusiveDF [y|n]

    • autoCalcCt=<column-type-definition>;<ct-expression>

    • TABLESPACE=<tablespace-name>

    • STORAGE=<storage-clause-elements>

    • threadCnt=<number-of-indexing-threads>

    • insSessCnt=1

    • errorTableName

    Here are some examples to create an index, more can be found in the JCC documentation

    1. Create index with default parameters:

    `CREATE INDEX jc_idx ON jchemtest(structure_col) INDEXTYPE IS jc_idxtype;`

    1. Create index with default parameters on a JChem table for use with BLOB operators:

     `CREATE INDEX jc_idx ON jchemtest(cd_structure) INDEXTYPE IS jc_idxtype;`

    1. Create an index with structural keys stored in the stkey column of the stkeys table:

     `CREATE INDEX jc_idx ON jchemtest(cd_structure) INDEXTYPE IS jc_idxtype PARAMETERS('sep=! structuralfp_config=SELECT stkey FROM stkeys');`

    1. Create index with Daylight-style aromatization:

     `CREATE INDEX jc_idx_2 ON jchemtest2(structure_col) INDEXTYPE IS jc_idxtype PARAMETERS('std_config=aromatize');`

    1. Specify that query and target structures must be treated as absolute stereo by default:

     `CREATE INDEX jc_idx_2 ON jchemtest2(structure_col) INDEXTYPE IS jc_idxtype PARAMETERS('absoluteStereo=y');`

    1. Create index so that JChem stores metainformation about the index in a table called jchemprops:

     `CREATE INDEX jc_idx_2 ON jchemtest2(structure_col) INDEXTYPE IS jc_idxtype PARAMETERS('JChemPropertiesTable=JChemProps');`

    1. Create an index on a structure table generated by an earlier, incompatible version of JChem so that the table will be upgraded to the current JChem table version: CREATE INDEX jc_idx_2 ON jchemtest2(structure_col) INDEXTYPE IS jc_idxtype PARAMETERS('RegenerateTable=true');

    Cartridge/database setup for the use with Instant JChem/Plexus Suite

    At this point, we have installed the JChem Server including the JChem cartridge into an Oracle schema that owns these functions (e.g. a 'JCHEM' user). The following section will guide you through the process of setting up the access to the cartridge functions to the second user schema (data warehouse, e.g. 'IJC_IDXOWNER') which allows this user to administer the tables and indexes. Then tables/indexes owned by this user can be accessed by a further 'IJC' (which owns the IJC database schema) via another configured "read only" role (JCC_IDX_ROLE).

    The privileges necessary for each of the remaining Oracle users are described in a separate topic below and the setup of tables is also discussed in this part of tips and tricks as well as in this document i n JChem Cartridge for Oracle documentation

    Data warehouse access to JChem Cartridge for Oracle

    In our example, the data will be stored in a database schema belonging to the IJC_IDXOWNER user, which will have access to the cartridge functions and thus effectively 'own' the index.

    {primary} You will need the access rights as both DBA and JCHEM user to set up the IJC_IDXOWNER.

    1. Create an Oracle user 'IJC_IDXOWNER'.

    `CREATE USER ijc_idxowner IDENTIFIED BY ijc_idxowner_password QUOTA unlimited ON users;`

 It is recommend to not limit the tablespace for the IJC_IDXOWNER user.

    1. Grant CONNECT, RESOURCE and JCC_BASIC_ROLE roles to this user in Oracle as DBA.

    `GRANT connect, resource TO ijc_idxowner;`

`GRANT JCC_BASIC_ROLE TO ijc_idxowner;`

    1. On JChem cartridge server, in cartridge directory run the config-util.sh (or .bat) script with the list-sqls-for-jcc-user-privs argument.

    `./config-util.sh list-sqls-for-jcc-user-privs`

    1. Answer the questions correctly, at the prompt. An example of the config-util.sh script being called, with the 'list-sqls-for-jcc-user-privs' option is below:

    ```

$ ./config-util.sh list-sqls-for-jcc-user-privs
Name of the cartridge owner [jchem]:
The host name of the JChem Server [localhost]:
The port at which the JChem Server accepts connections [1099]:
Target Oracle version [11.2]: 10.2
The target Oracle user [zazie]: ijc_idxowner
-------------------------- CUT HERE ----------------------------
-- To be executed as DBA:
call dbms_java.grant_permission('IJC_IDXOWNER', 'SYS:java.net.SocketPermission', 'localhost:1099', 'resolve,connect');
-- To be executed as jchem:
call jchem.privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner');
-------------------------- CUT HERE ----------------------------
+ set +x
```

    1. Perform the operations present in the script between the CUT HERE parts as the DBA and JChem users.

     *     as DBA:

    `call dbms_java.grant_permission('IJC_IDXOWNER', 'SYS:java.net.SocketPermission', 'localhost:1099', 'resolve,connect');`

 *     as jchem:

    `call jchem.privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner');`

    1. The user 'IJC_IDXOWNER' is now able to create JCC indexes with cartridge owner prefix(jchem in example) before INDEXTYPE.

     `create index jc_idx ON testtable(structure_column) INDEXTYPE IS jchem.jc_idxtype`

    Instant JChem access to data warehouse

    IJC can access tables from Oracle schemas other than its own. To do this add the required schemas in the Database schemas property of the schema section of the schema editor. See the screenshot below and the details on using multiple database schemas can be found here. For this to work with JChem cartridge indexes the user must be granted extra privileges on the index. In our case, this user is called ‘IJC'.

    1. Create an Oracle user 'IJC'.

    `CREATE USER ijc IDENTIFIED BY ijc_password QUOTA unlimited ON users; `

 It is recommend to not limit the tablespace for the IJC user.

    1. Grant CONNECT, RESOURCE and JCC_BASIC_ROLE roles to this user in Oracle as the DBA.

    `GRANT connect, resource TO ijc; `

`GRANT JCC_BASIC_ROLE TO ijc;`

    1. Grant SELECT on all tables present in the data warehouse. It is possible to grant to the IJC user privileges to select any table present in the database except for system tables:

    > {primary}
> Please keep in mind that in this case, the ijc user will be able to select tables anywhere in the database, please use this command very carefully.
>

`GRANT SELECT ANY TABLE TO ijc;`

 Alternatively, it is possible to grant the privileges only for database tables present only in data warehouse, in the presented example the ijc_idxowner schema:

```

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='ijc_idxowner') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to ijc';
END LOOP;
END;
```

> {primary}
> When a new table is introduced in the data warehouse, the grants need to be performed manually.
>

    1. Grant INSERT, UPDATE and DELETE privileges on the JCHEMPROPERTIES and JCHEMPROPERTIES_CR tables in the index owner's schema ('IJC_IDXOWNER') to the 'IJC' schema.

    `GRANT INSERT, UPDATE, DELETE ON JCHEMPROPERTIES TO ijc; `

`GRANT INSERT, UPDATE, DELETE ON JCHEMPROPERTIES_CR TO ijc;`

    1. Grant SELECT, INSERT, UPDATE and DELETE privileges on the update logs (tablename_UL) and JCC index helper tables (tablename_JCX) tables in the index owner's schema ('IJC_IDXOWNER' which is the data warehouse) to the 'IJC' schema.

    `GRANT INSERT, UPDATE, DELETE ON tablename_UL TO ijc; `

`GRANT INSERT, UPDATE, DELETE ON tablename_JCX TO ijc;`

 This step has to be repeated for each table.

> {primary}
> The grant commands present in steps 4 and 5 have to be run as the data warehouse user, in these examples ijc_idxowner.
>

    1. On JChem cartridge server run the config-util.sh (or .bat) script with the config-index-user argument

    `./config-util.sh config-index-user`

    1. Answer the questions correctly, at the prompt. An example of using the config-util.sh script with the config-index-user option is given below. Please note the index name prompt expects a single index name. If you have multiple names you will need to repeat the final privman_pkg call for each index that you wish to use.

    ```

[oracle@fedora10 cartridge]$ ./config-util.sh config-index-user
Name of the Oracle host. [localhost]:
Oracle listener port [1521]:
Name of the Oracle instance [mydb]: orcl
Name of the cartridge owner [jchem]:
Password of the cartridge owner:
The role for basic JChem Cartridge user privileges [JCC_BASIC_ROLE]:
The owner of the index: ijc_idxowner
The password of the index owner:
The user of the index: ijc
The name of the index (IDX_BUILDING_BLOCKS_GB): <jcc_index_name> -- if more than one index, need to specify one and repeat from script.
Give search permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: y
Give insert permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: n
Give update permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: n
Give delete permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: n
-- ---- SQLs to execute as jchem:
call privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner')
grant JCC_BASIC_ROLE to ijc
-- ---- SQLs to execute as ijc_idxowner:
call privman_pkg.grants_on_jcidx('ijc', 'ijc_idxowner', '<jcc_index_name>', 1, 0, 0, 0) -- need to qualify with jchem. & repeat for each index...
Do you want to execute the SQL statements (y, n) [y]: n
+ set +x
```

    1. Perform the operations below as the DBA and JChem users

     *     SQLs to execute as jchem:

    `call privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner')`

    `grant JCC_BASIC_ROLE to ijc`

 *     SQLs to execute as ijc_idxowner:

    `call jchem.privman_pkg.grants_on_jcidx('ijc', 'ijc_idxowner', 'IDX_BUILDING_BLOCKS_GB', 1, 0, 0, 0)`

    This call needs to be qualified with JChem and repeated for each index you wish to grant access to.

    1. Grant SELECT privileges on the sequence used for generation of IDs in the structure table (optional, you can use the Null value generator if you are not wanting to insert new rows). This step is really only relevant for Standard tables + index which have a potentially unknown sequence name.

    The user 'IJC', is now able promote (Schema editor, tables tab) either JChem or Standard tables that have associated cartridge indexes that are owned by another user, for example 'IJC_IDXOWNER'. The access level of each depends upon the previous configuration options.

    Some notes on this:

    1. Your JChem user may not have privileges to grant the JCC_BASIC_ROLE. You may need to do this as a privileged user.

    1. You index owner may not see the privman_pkg package. You may need to qualify this with the schema name of the jchem owner. e.g. jchem.privman_pkg.

    A detailed example is provided in the administrators tips section.

    A final point about privileges is that due to the quirks or Oracle it is sometimes necessary to grant the privileges directly to the user rather than using a role. The times when this is necessary are not easy to predict, but if you are using the role based approach and having difficulties then try granting privileges directly to the user.

    Accessing the database using Instant JChem

    After opening Instant JChem, go to the projects window (Window > Projects). Using right click and selecting Create New IJC Schema… option (alternatively File > Create New IJC Schema…) you can open the wizard.

    In the first step, select Oracle database. In the second step, fill in the credentials - use the database URL and ‘IJC’ user credentials as used previously through this guide. By pressing Finish you will create a new IJC schema and the IJC metadata tables are created in the IJC database schema. More details on creating a new IJC schema can be found in the online documentation.

    Adding non-default schema in Instant JChem

    We assume you have set up the database access as described in the previous parts of this guide and admin access to your IJC schema (no security template is applied).

    Using double-click on the IJC schema node in the Projects window, alternatively right-click and selecting the Edit Schema option, you can open the schema editor. By selecting the Schema > Database schemas in the schema editor window, you can see all the database schemas in your Oracle database your Instant JChem user has access to. Select your data warehouse schema and add it to the selected schemas using the buttons in the middle of the screen. After applying the changes, you have to disconnect from the IJC schema and reconnect it so IJC schema can pick up all the changes. More information on working with multiple database schemas can be found in the online documentation.

    Promoting existing database tables

    Selecting the Database Tables tab in the schema editor will expand the window to display all the database tables available in the database schemas available for the database user which is used to connect IJC. You can notice the database table names are identified by the database schema name. Using the right-click on the table name and the Promote to Entity option you can open the promotion wizard. In the first step, you can review the entity settings while in the second step you can select the database columns to be included in the entity. In the case of JChem structure tables, there is no need to select additional database columns. In the case of data tables, select the columns as appropriate. More details on promoting existing database tables and using them is available online.