JChem Cartridge for Oracle

• Introduction
• Architecture
• Installation
• SQL operators
• Extensible Index, jc_idxtype
• User defined operator
• JChem-table functions
• Miscellaneous functions
• Cost estimation for the Oracle Optimizer
• Examples and demos
• Known issues
• Future plans

Introduction

JChem Cartridge adds chemical knowledge to your Oracle platform and you automatically take advantage of Oracle's security, scalability, and replication. You can search data by structure, substructure and similarity through extensions to Oracle's native SQL language. Chemical data can be easily inserted and modified using SQL procedures of the JChem Cartridge.

SQL operators of the Cartridge can be used to search for chemical data (molecules or reactions) more efficiently than it is possible using traditional mechanisms such as PL/SQL Stored Procedures. To increase the speed of the searching process, the jc_idxtype index type has to be applied.

Architecture

High Level Overview

The chemical knowledge for JChem Cartridge comes from ChemAxon's core Java libraries. Oracle offers the possibility to use existing Java libraries by calling them from Java Stored Procedures. Java Stored Procedures run in a special, in many respects unconventional Java runtime environment which is tightly integrated with the Oracle RDBMS. One of the most important characteristics of the Oracle Java runtime environment is that Java libraries are run in a byte-code-interpreted mode. This aspect puts Java Stored Procedures at a serious disadvantage in terms of performance compared to modern Java runtime environments. These latter use sophisticated techniques to compile Java byte-code into native binary executable at runtime. Native executables can then be run much faster than Java byte-code.

The solution used for JChem Cartridge to compensate for the performance disadvantage with Oracle Java Stored Procedures is to perform the computation intensive functions in an external Java runtime environment capable of runtime byte-code compilation ("external" meaning: external to Oracle). We will call this external Java runtime environment (as well as the JChem Cartridge components running in it) JChem Server.

At an abstract level, the JChem Server comprises the following components:

• JChem Base providing
  • the core chemical functions such as atom-by-atom graph search algorithms(JChem Core);
  • implementing advanced database search and update functions. (Search and Update engines which add an RDBMS-aware layer to the core JChem functions).
• JChem Cartridge Adapter which translates JChem Base's external interfaces to the needs of JChem Cartridge.

For structured database access, the Search and Update engines use JDBC connection(s) (how to configure the JDBC connection for the Search Engine).

To reduce hard disk access and network round trip, the Search Engine extensively caches the chemical information stored in database tables. (How to configure memory for the search cache.)

The JChem Cartridge components (PL/SQL and Java Stored Procedures) which are located within Oracle, perform the following functions:

• Interfacing with the Oracle Data Cartridge framework,
• Performing simple tasks involving mainly pure database access,
• Managing communication with the JChem Server.

Database Access Modes

The standard way for an Oracle Java Stored Procedure to access database objects is to use a JDBC-connection which has been pre-opened by Oracle in the context of the database session, in which invocation of a given stored procedure has been initiated.

However, as JChem Cartridge stored procedures make extensive use of the JChem Server for all kind of tasks to speed up computation, there are cases where it is much more efficient to access the database (as part of some sub-tasks) directly from within the JChem Server rather than relying entirely on stored procedures for database access. Relying entirely on stored procedures for database access would require moving (in some cases) a large amount of data back and forth between the JChem Server and the stored procedures which would have an obvious impact on performance (and in the case of earlier Oracle versions also on stability).

Direct database access from within the JChem Server thus involves opening a new database session (within JChem Server). This results in certain cartridge operations using two database sessions: the "original" (initiator) database session and an auxiliary database session. INSERT operations into regular Oracle tables as well as inserts into JChem tables using the jchem_table_pkg.jc_insert function are currently performed exclusively using the initiator session. A single session is also used for updates and deletes.

The dual-session access mode is used for searches that use the jc_idxtype operators in domain index scan mode (where the table is scanned using the jc_idxtype domain index). The functional equivalents of operators as well as the operators executed in "non-domain-index-scan" mode use one singe database session.

Dual-Session Database Access Example

When you use a search operator (such as jc_contains ) in the WHERE clause of a SQL query, the following execution path is traversed:

1. The application sends Oracle Server a SQL statement involving substructure search.
2. The Oracle Execution Engine processes the statement and the substructure search operator of JChem Cartridge is called with the appropriate parameters.
3. The parameters of the search are passed onto the JChem Server over RMI.
4. The Search Engine checks (over the JDBC connection) to see if the database table involved in the search has been modified since the corresponding structure cache was loaded the last time.
5. The structure cache is refreshed (over the JDBC connection), if necessary.
6. A pre-screening is used on fingerprints in the cache, then an atom-by-atom graph search on the structures from the cache.
7. The results are communicated over RMI back to the cartridge components located in Oracle.
8. The ROWIDs of the matching structures are returned to the Oracle Execution engine.
9. The Oracle Execution engine further processes the original SQL statement, if necessary.

Single-Session Database Access Example

1. The application sends Oracle Server a SQL statement involving an INSERT (in the case of regular structure tables) or jc_insert (in the case of JChem structure tables).
2. The Oracle Execution Engine processes the statement and calls JChem Cartridge to either update the index table for the newly inserted structure (in the case of regular SQL INSERT) or directly execute the jc_insert function. (jc_insert includes both inserting the structure and updating the index information in the same JChem structure table.)
3. Additional information (Standardizer configuration, fingerprint generation rules) is fetched from the index table.
4. The molecular structure is passed onto the JChem Server over RMI along with the additional information.
5. JChem Base's Update Handler computes the chemical information to update the index table with.
6. The computed chemical information is communicated over RMI back to the JChem Cartridge components located in Oracle.
7. The chemical information is inserted into the index table.

Implications of the Dual-Session Database Access Mode

Using two separate JDBC connections for searching has the following implications:

• User authentication information must also be maintained in the JChem Server.
• Searching is performed in a separate database session and thus in a separate transaction context. The consequence is that modifications in the database are not reflected in search results until they are committed.

User Credentials

While it is currently impossible to retain "normal" transaction semantics with dual-session database access, beginning with JChem version 3.0.12 a simple mechanism is provided to allow to retain security semantics: i. e. to execute all database operations with the credentials of the initiating database session. The mechanism consists of

1. the JChem Server maintaining in transient form (in system memory) the user credentials (user name, and password);
2. propagate the user name of the initiating user for each call to JChem Server, so the proper credentials can be used to open the JDBC connection in the JChem Server on behalf of the user action "in Oracle".

A PL/SQL procedure jchem_core_pkg.use_password(password VARCHAR2) is made available for users to set their transient password in JChem Server. While the password is kept in JChem Server's main memory, it will be remembered and used as long as JChem Server is shut down. It is therefore recommended but not strictly necessary for users (or applications on their behalves) to call the use_password procedure at the beginning of each database session. (It must called once after each time the JChem Server is (re)started.)

If the password of a given user has not been set for JChem Server via the use_password PL/SQL procedure, the user credentials (if any) that were set as part of the JChem Server JDBC configuration will be used to open database connections in JChem Server (how to configure the JDBC connection for the Search Engine).

jchem_core_pkg.use_password(password VARCHAR2) transmits the password to JChem Server via in plain text via a regular RMI call. If JChem Server and Oracle are running on different machines, malicious users may be able to sniff the passwords as it travels on the network. The necessary steps must be taken to prevent this (e.g. by limiting user's privileges on client machines so that they are unable to put their Ethernet card in promiscuous mode, or using a dedicated/closed network connection between the Oracle host and the JChem Server host).

Support For JChem tables

In addition to regular structure tables containing molecular structures, structure tables that have been created and populated by tools included in JChem Base can also be used with JChem Cartridge. The advantages of JChem tables over plain Oracle tables are:

• inserts into JChem tables are faster than into plain tables,
• searching in JChem tables are faster than in plain tables,
• certain features (such as chiral flag setting for MDL files or duplication filtering) can be customized at a structure level for JChem table imports, while the same features can be customized at an index level granularity with plain Oracle tables.

• You have to use JChemManager to create and drop JChem tables.
• You have to use either JChemManager or the jchem_table_pkg.jc_insert function provided with JChem Cartridge to insert into a JChem table.
• You have to use either JChemManager or the jchem_table_pkg.jc_update, procedure provided with JChem Cartridge to update structures in a JChem table.
• You have to use either JChemManager or the jchem_table_pkg.jc_delete procedure provided with JChem Cartridge to remove structures from a JChem table.

From an architectural perspective, the main difference between using/indexing regular structure tables and using/indexing JChem tables is the following:

When you create a JChem Cartridge domain index on a regular table, the chemical information on the structures found in the table (called in this context base table) is stored in a separate table (called index table). Since the same chemical information which is stored in an index table is already present in a JChem table, the base table and index table are physically the same table in case of a JChem table. (Thus creating a jc_idxtype index on a JChem table is an instantaneous operation.)

Parameters representing target structures can be either a structure string in any format recognized by JChem or a column containing such structures.

Some of the parameters representing target structures can only be a structure string (literal-only target structures).

Parameters representing query structures can be in any format supported by JChem (like SMARTS, Molfile, Rxnfile, etc.). Starting with JChem 3.1.1, multiple query structures can also be specified for a single search operator at a time. The individual query structures are to be separated as specified by the given format (e.g. in the case of SMILES/SMARTS, SDFiles with a newline character). The search is performed for each individual query structure and the union of the hits is returned. (See also our Future Plans.) Multiple query structures are accepted only for operators evaluated in domain index scans. See the example for jc_compare.

Some of the operators and functions have an option list parameter. Option list parameters are of type VARCHAR2 and accept a list of options (specific to the given operator or function). The default separator between the individual options is the space character. In case the space character is not appropriate as a separator (because, for example, the value of any of the options itself contains space(s)), a custom separator string can be used by specifying the special option "sep=" as the first option followed by a string that will be used as the custom separator. The custom separator string must be delimited by a space at the end (the space will not be part of the separator string). If a custom separator string is specified (always at the beginning of the options string), each option following option must be delimited using the custom separator string.

Without creating a jc_idxtype index of a chemical table, operators occurring in expressions are evaluated for each row of the result. If the value of the smiles column of the current row is NULL then a NULL value will be returned by the operator.

SELECT cd_id, jc_contains(cd_smiles, smiles) FROM scott.jchemtable WHERE cd_id < 10;

SELECT cd_id, jc_tanimoto(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') FROM scott.jchemtable WHERE cd_id < 10;

SELECT cd_id FROM scott.jchemtable WHERE jc_contains(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

SELECT cd_id FROM scott.jchemtable WHERE jc_evaluate(smiles, 'logd( "7.4" )' ) > 2;

NOTE: Operators appearing in the WHERE clause are evaluated much faster with the use of the JChem index (jc_idxtype).

Example:

SELECT cd_id FROM scott.jchemtable WHERE jc_contains(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

Functional equivalents

Each operator supported by the jc_idxtype index type have a functional equivalent in the jcf package. The name of the functional equivalents have the 'jc_' prefix of their operator peers removed. E.g. the functional equivalent of jc_molconvert is jcf.molconvert.

One possible use of functional equivalents is in SQL constructs where operators cannot be used such as embedded in another operator:

select jc_compare('c1ccccc1', jcf.molconvert('C1C=CC=CC=1', 'smiles:au'), 't:s') from dual;

Default properties

The behavior of operators and functions can be configured by using default properties and index parameters. Currently, two global properties are supported:

• the Standardizer configuration called standardizerConfig, whose value can be a list of actions.
• the absolute stereo flag calledabsoluteStereo, whose value can be false and true.

call jc_set_default_property('standardizerConfig','aromatize:b');

• Its value will be used to define the behavior in contexts where no index information is available (e.g. select ... from dual).
• Its value will be used as the default when creating an index of jc_idxtype. You can override the default value for a given index by specifying the corresponding index parameter with a different value (the std_config index parameter for the standardizerConfig default property).

Default properties provide default values only to indexes that are created in the current user's schema. The scope of default properties is the schema. Each user requiring daylight style aromatization with dehydroganization must call

call jc_set_default_property('standardizerConfig','dehydrogenize:optional..aromatize');

Extensible Index, jc_idxtype

CREATE INDEX

The index can be created on a column of any table, provided that the column contains molecular structures in any of the supported representation. Use the following general statement to create the index:

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

The following optional parameters can be specified for both JChem and non-JChem tables:

• JChemPropertiesTable is required for determining the properties of fingerprints during the queries. These properties, that are stored in the JChemProperties table, can be different for each JChem molecule table. If the JChemPropertiesTable parameter is not defined, the method uses the default property table name in the user's schema: JChemProperties.

• RegenerateTable: specify this option if you want to create index on a structure table generated by an earlier, incompatible version of JChem. Trying to create an index on an incompatible JChem-table version without specifying this option will result in an error message. This option accepts no parameter value.
  

• tableType: the type of the structure table being indexed. The following table types are currently valid:
  • anyStructures: All types of structures are allowed, but no structure type-specific searching.
  • molecules: Specific structures, like single molecules, mixtures, salts, polymers. Other structures (e.g. with query or Markush features or reactions) are not allowed in database (exception is thrown).
  • reactions: Table for storing single step reactions.
  • queries: Table for storing queries. Only superstructure, exact and perfect search are allowed.
• fp_size: the number of INTEGER columns that will contain the chemical hashed fingerprints of the molecules. (For more details see the JChem Administration Guide.)
• fp_bit: the number of bits to be set for each pattern in the structure.(For more details see the JChem Administration Guide.)
• pat_length: the number of edges in a pattern.(For more details see the JChem Administration Guide.)
• structuralfp_config: a select statement returning the structural keys.
• std_config: the configuration for custom standardization. The standardizer configuration is used during structure search. See here how. When not specified, the standardization configuration set as a default property is used. When not specified and no default standardization property is configured, the built-in default standardizer configuration is used. For details see the Standardizer documentation. You can use either the XML format or the action string variant of the Standardizer configuration. Special care has to be taken when custom standardization (other than the build-in default) is used on the database table. The "aromatize" action should be present in the configuration, and it is safest to put it first. See also the examples below.
• absoluteStereo: if set to "y" all query and target structures are treated as absolute stereo. This setting can be changed later with ALTER INDEX. This setting can be overridden for individual searches with the corresponding option of the jc_compare operator.
• autoCalcCt=<column-type-definition>;<ct-expression> specifies that a Chemical Terms expression should be automatically evaluated on structures being indexed with the value being stored in a column of the specified type. The expression will be evaluated during CREATE INDEX for the structures already inserted and automatic calculation of the values during future structure inserts is arranged. The precalculated values will be used by jc_evaluate and jc_evaluate_x functions. (See example)
• TABLESPACE to specify the table space for the index. Use: TABLESPACE=<tablespace-name>.
• STORAGE to specify any of the elements of the standard Oracle storage_clause. Use: STORAGE=<storage-clause-elements>.

1. Create index with default parameters:
   

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

2. Create index with default parameters on a JChem table for use with VARCHAR2 operators:
   

   CREATE INDEX jc_idx ON jchemtest(cd_smiles) INDEXTYPE IS jc_idxtype;

3. 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;

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

   CREATE INDEX jc_idx ON jchemtest(cd_smiles) INDEXTYPE IS jc_idxtype PARAMETERS('structuralfp_config:SELECT stkey FROM stkeys');

5. Create index with Daylight-style aromatization:
   

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

6. 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');

7. Specify that
   1. the LogP of the structures being indexed be stored in a column of type numeric(30,15)
   2. the rotatableBondCount be stored in a column of type numeric(10,0)
   3. the pKa in a column of type numeric(30,15):

   CREATE INDEX jcxautocalccttest ON autocalccttest(structure)
           INDEXTYPE IS jchem_cc.jc_idxtype
           PARAMETERS('sep=! autoCalcCt=numeric(30,15);logp()!autoCalcCt=numeric(10,0);rotatableBondCount()!autoCalcCt=numeric(30,15);pKa("acidic","2")')

8. 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');

9. 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');

10. Create an index on the USERS tablespace with the storage_clause 'INITIAL 2M' using Daylight style aromatization: CREATE INDEX jc_idx_2 ON jchemtest2(structure_col) INDEXTYPE IS jc_idxtype PARAMETERS('TABLESPACE=USERS,STORAGE=INITIAL 2M,std_config=aromatize:d');

ALTER INDEX

ALTER INDEX <index-name> REBUILD PARAMETERS( 'param1=paramvalue1,param2=paramvalue2,...');

• absoluteStereo to specify/change default chirality.
• addAutoCalcCt=<column-type-definition>;<ct-expression> adds a Chemical Terms expression which should be automatically evaluated on structures being indexed with the value being stored in a column of the specified type. The expression will be evaluated during ALTER INDEX for the structures already inserted and automatic calculation of the values during future structure inserts is arranged. The precalculated values will be used by jc_evaluate and jc_evaluate_x functions. (See example)
• delAutoCalcCt=<<ct-expression> stops automatically evaluating the given Chemical Terms expression on structures being indexed and drops the column holding the already calculated values. (See example)
• TABLESPACE to specify the table space for the index. Use: TABLESPACE=<tablespace-name>.
• STORAGE to specify any of the elements of the standard Oracle storage_clause. Use: STORAGE=<storage-clause-elements>.

Examples

1. Move the index table for a jc_idxtype index to the SYSTEM tablespace, with storage_clause 'INITIAL 3M BUFFER_POOL KEEP':

   ALTER INDEX jcidx_nci_1k REBUILD PARAMETERS('TABLESPACE=SYSTEM,STORAGE=INITIAL 3M BUFFER_POOL KEEP');

2. Adds the following Chemical Terms expressions to the list of precalcuated expressions:
   1. the LogP of to be stored in a column of type numeric(30,15)
   2. the rotatableBondCount to be stored in a column of type numeric(10,0)
   3. the pKa to be stored in a column of type numeric(30,15):

   alter index jcxautocalccttest
   parameters('sep=! addAutoCalcCt=numeric(30,15);logp()!addAutoCalcCt=numeric(10,0);rotatableBondCount()!addAutoCalcCt=numeric(30,15);pKa("acidic","2")')

3. Adds the following Chemical Terms expressions to the list of precalcuated expressions:
   1. the LogP of to be stored in a column of type numeric(30,15)
   2. the rotatableBondCount to be stored in a column of type numeric(10,0)
   3. the pKa to be stored in a column of type numeric(30,15):

   alter index jcxautocalccttest
   parameters('sep=! delAutoCalcCt=logp()!delAutoCalcCt=rotatableBondCount()!delAutoCalcCt=pKa("acidic","2")')

Index operators

The valid operator comparison using the index are:

• jc_contains(...) = 0
• jc_contains(...) = 1
• jc_equals(...) = 0
• jc_equals(...) = 1
• jc_compare(...) = 0
• jc_compare(...) = 1

jc_matchcount, jc_tanimoto, jc_dissimilarity, jc_molweight, jc_evaluate operators return real values. These index operators support the full range of comparison functions: ">", ">=", "<", "<=", and "=". Note that the "!=" comparison does not use the index and will use the functional form.

The jc_contains and jc_equals operators perform substructure search and perfect structure search respectively. These operators take two parameters, the first is the smiles column, the second is the query structure in any format supported by JChem (like SMARTS, Molfile, Rxnfile, etc.). jc_contains returns the structures from the table, which contain the query as a substructure, jc_equals returns the ones that are equal to the query structure.

NOTE: In case of the jc_contains and jc_matchcount operators, if the query structure is given in SMILES format it will be converted to SMARTS.

SELECT id FROM JCHEMTEST WHERE jc_contains(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

SELECT id FROM JCHEMTEST WHERE jc_equals(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

The jc_matchcount operator performs substructure search and returns the occurrence of the query structure in the target structure.

If, for some reason, you need a search behavior other than provided with jc_contains or jc_equals (e.g. you want to perform perfect search with query imported as SMARTS, or you want to perform substructure search with the query imported as SMILES), have a look at jc_compare.

Similarity search using Tanimoto formula can be performed by using the jc_tanimoto and jc_dissimilarity operators. The return values of these operators are in the range of 0.0 - 1.0, inclusive.

SELECT id FROM jchemtest WHERE jc_tanimoto(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') < 0.6;

SELECT id FROM jchemtest WHERE jc_dissimilarity(smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') > 0.4;

Molecular weights are calculated by the jc_molweight operator.

Select id from JCHEMTEST where jc_molweight(smiles) < 23;

Calculations can be performed by using the jc_evaluate, jc_compare operators.

SELECT id FROM jchemtest WHERE jc_evaluate(smiles, 'logp()') < 11;

SELECT id FROM jchemtest WHERE jc_evaluate(smiles, 'logd( "7.4" )' ) > 4.2;

SELECT id FROM jchemtest WHERE jc_evaluate(smiles, 'psa()') > 3;

If two or more operators are used in the where clause of one select statement it is more efficient to use the jc_compare operator with a filterQuery.

    select jchem_core_pkg.get_idx_stats('jchemuser', 'jcxnci_10k', null) from dual

Statistics for table: JCHEMUSER.JC_NCI_10K
--------------------
Row count: 10000
NULL SMILES count: 0
Average SMILES length: 27.66
Average compressed SMILES length: 12.96
Markush structure count: 0 (0.0%)
Min. CFP bits: 0.78%      cd_id: 2110
Max. CFP bits: 68.35%     cd_id: 3035
Avg. CFP bits: 21.48%

Chemical Fingerpint distribution:
--------------------------------
0% - 10% : 15.38 %
10% - 20% : 34.04 %
20% - 30% : 29.55 %
30% - 40% : 14.69 %
40% - 50% : 4.91 %
50% - 60% : 1.21 %
60% - 70% : 0.22 %
70% - 80% : 0.0 %
80% - 90% : 0.0 %
90% - 100% : 0.0 %

     public class MyClass implements chemaxon.jchem.cartridge.JChemCartModul
    {
        public Object doFunc(String[] args) throws Exception
            String query = args[0];
            String param = args[1];
            Object result = null;
            ...
            result = ...
            return result;
        }
    }

sqlplus jchem/tiger@mydb @assoc_stats.sql

sqlplus jchem/tiger@mydb @disassoc_stats.sql

    execute immediate 'select count(*) from ' || struct_table
                        || ' where jc_compare(' || struct_col
                        || ', :a, :b) = 1'
            using search_queries(1), search_options;

    select count(*) from plain_nci where jc_compare(structure, 'c1ccccc1', 't:s') = 1;
    

    execute immediate 'select count(*) from (select ' || struct_col
                        || ' from ' || struct_table || ' where ' ||  pk_col 
                        || ' < :a) a where jc_compare(' || struct_col
                        || ', :b, :c) = 1'
            using less_than_ids(1), search_queries(1), search_options;

    select count(*) from (
        select structure from plain_nci where id < 10000
        ) a
        where jc_compare(structure, 'c1ccccc1', 't:s') = 1;
    

calibrate.bat calibrator.properties

bash calibrate.sh calibrator.properties