If you have a license for the JChem Cartridge for Oracle, then this can be used in conjunction with Instant JChem (from version 2.4 onwards). The key benefits of this are as follows:
Searching is performed directly in an Oracle process, or more correctly the JChem cartridge server cache and not in client side IJC memory, so that intensive memory and CPU requirements are transferred from the IJC client to the Oracle server. This allows very large databases (tables containing tens of millions of compounds) to be searched using cartridge SQL sent from IJC with modest client side memory and CPU requirements.
This allows molecular structure data to be managed and viewed in IJC and then accessed and manipulated through other routes using the SQL language.
Your organisation can access existing legacy data already in Oracle tables.
Your organisation can migrate from other vendors cartridges to the JChem cartridge.
There are two different types of cartridge table in IJC, in addition to the normal JChemBase table. Each of these is treated as a different Entity type in IJC. When you create a new Entity for handling structures you will choose which of the three types to choose. Without the cartridge service available, only the standard JChemBase table type will be available. With a cartridge install detected the other two table types are also available.
| Table/Entity type | Description |
|---|---|
| JChemBase table | These are the normal JChemBase tables that are available if you are not using the JChem Cartridge, or are using a database other than Oracle (Derby, MySql). All operations on this type of table are completed using the JChem API and are performed directly in IJC memory. Thus, the CPU and memory requirements needed for structure searching are imposed on and limited by the IJC client machine. For very large tables this can be a problem unless you can allocate large amounts of memory to IJC. |
| Cartridge index on a JChemBase table | This type of index uses the JChemBase table directly for its fingerprint and associated information. The management functions (creation, deletion, applying standardizer, adding/removing chemical terms columns) for this type of table are performed by IJC using the JChem API whilst the searching and updating functions are performed by the cartridge using SQL. This means that the heavy CPU and memory intensive task of searching is transferred from IJC to the Oracle server. However, as this table type is accessed both from IJC and from the cartridge the JChem versions (more correctly the JChem table versions - see below) must be identical in IJC and the cartridge. It is recommended that the CD_STRUCTURE (BLOB) column is indexed but it is possible also possible to index the CD_SMILES (VARCHAR2) column. |
| Cartridge index on a standard (non-JChemBase) table | For this type of index, chemical structures in a column (VARCHAR, BLOB or CLOB) of a standard (non-JChem) table can have a JChem index added to them to allow searching and related operations. This enables access to existing legacy data to JChem functionality. Behind the scenes this index uses a separately created JChem table (known as the index table). This is used for the fingerprint and related information and so do ensure enough space is available for this. All management, searching and updating functions are performed by the cartridge using SQL, and IJC only accesses this table type through SQL, not the JChem API. This means that the heavy CPU and memory intensive task of searching is transferred from IJC to Oracle and, as this table type is accessed from IJC only using SQL, the JChem versions (more correctly the JChem table versions - see below) in IJC and the cartridge do NOT have to be identical, but ideally it is still recommended to keep them similar, if not the same. For this reason this is the currently recommended table type to use when using the cartridge. |
Please see the Introduction, the JChem chemical database concepts and the High Level Overview of the JChem Cartridge architecture pages for a more complete description of JChem Base and JChem cartridge.
The JChem cartridge is tested for both Oracle 10g and 11g. Oracle 10g with the latest patch sets is recommended as this has been found to be more stable than earlier 11g releases, but the latest 11g releases are probably as stable as the latest 10g releases now. The JChem cartridge does not run in the XE version of Oracle. The most commonly used configuration is where the JChem Cartridge functions have been installed into an Oracle schema that owns these functions (e.g. a 'JCHEM' user) and access to those functions can be be granted to a second user schema (e.g. 'IJC_IDXOWNER') using the JCC_BASIC_ROLE. This will allow this user to administer tables and indexes. Then tables/indexes owned by this user can be accessed by a further 'IJC_IDXUSER' via another configured "read only" role (JCC_IDX_ROLE). See the JChem Cartridge installation guide for full details of how to set up the JChem Cartridge. Once these environments have been established you can connect to the Oracle database as the IJC tertiary user 'IJC_IDXUSER' and the JChem Cartridge functionality in the priamry user 'JCHEM' will be automatically enabled on a per index basis via the secondary user 'IJC_IDXOWNER'.
As the JChem Cartridge and IJC instances are related but separate products, each can use different versions of JChem which if unmanaged can potentially lead to incompatibilities. The best solution is to ensure that the versions of JChem running in IJC and in the JChem Cartridge are the same, but some differences can be tolerated, as described above and below. To establish the version of JChem and the version of the JChem tables used by that version of JChem can be determined by:
Instant JChem:
Help -> About Instant JChem. Look for the following lines (note: the JChem table version is only present since IJC 2.4)
Instant JChem Version: 5.7.0 (build: 111121)
JChem Version: 5.7.0
JChem Table Version: 5070000
JChem Cartridge
Execute the following SQL as the cartridge owner and look for the 'JChem version' and 'JChem Index version' properties (note: the JChem table version is only present since JChem 5.1.2)
SQL> select jchem_core_pkg.getenvironment() from dual;
JChem version: 5.7.0
JChem Index version: 5070000
JDBC driver version: 11.1.0.7.0-Production
SQL>
You should try to ensure that the JChem table versions that are being used by the JChem Cartridge and by IJC are the same. For cartridge indexes on JChemBase tables it is a strict requirement that table versions are identical. For cartridge indexes on standard tables some differences in the table version is possible, but it is recommended to synchronise the same major JChem version if possible.
NOTE: the cartridge upgrade process has been gradually improved over time. These instructions are for the current version (5.6 at time of writing). If you are using an older version then please consult the cartridge documentation for that version.
When a new version of JChem or Instant JChem becomes available you will most likely want to upgrade. Because of the need to keep the JChem version used in IJC and the cartridge the same, or at least similar (see previous section) then IJC and the cartridge should ideally be upgraded at the same time.
IJC is updated using the normal mechanisms (uninstall/install, update center or Java Web Start - Java web start is recommended if you have lots of users who need updating as this ensures that they are all updated at the same time).
To be safe, before you start the cartridge upgrade process you might want to make a record of:
All cartridge indexes that are present, and whether they are an index on a JChemBase table or a standard table.
Standardizer configurations for each table/index.
Chemical terms column names, column definitions and chemical terms expression for each table/index.
Any additional custom parameters that were used when creating the index.
To be 100% cautious you could take a backup of each table/index, prior to upgrading.
Full instructions for upgrading the cartridge are described in the JChem cartridge installation guide and should be done by your JChem Cartridge administrator. But in brief you need to:
Shut down the old JChem server
Run the upgrade.sh (or .bat on Windows) script in the cartridge directory
Answer the questions, including choosing to upgrade all cartridge indexes
Once the cartridge upgrade is complete you should test the new cartridge environment like this (exact version info will of course differ):
sqlplus jchem/jchem
SQL> select jchem_core_pkg.getenvironment() from dual;
JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
Oracle environment:
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
JChem owner: JCHEM
JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
Java version: 1.6.0_14
Java VM version: 14.0-b16
JChem version: 5.9.1
JChem Index version: 5080100
JDBC driver version: 11.1.0.7.0-Production
SQL>A key part of the JChem Cartridge upgrade process is to regenerate the JChem Cartridge indexes so that they are of the correct format for the new JChem version. This needs to be completed as part of the cartridge upgrade process. During the upgrade process an option to re-generate all indexes is given. If this option is not selected, then you must re-generate each index manually after the upgrade. Without regeneration of the indexes the JChem cartridge functions will fail as the index version will not be recognised by Oracle. If you try to connect to the Oracle schema in this state the JChem Cartridge tables (IJC entities) will be recognised and you will see a series of errors on connecting to the schema that look like this:
Warning: Failed to create Entity 3CCBA4E7776177703112E5272B0E9ED3 for Schema DA2B1B32A827FDB879E51505D2B536D6 [ CARTIDXOWNER ]"
An item required by this Entity was missing.
The cause was: TableDescription not found for cartridge (std) table NCI_aug00 [ID = 3CCBA4E7776177703112E5272B0E9ED3]
Please look for earlier errors that could have caused this.When you open the schema editor in IJC all your cartridge based tables will be missing. This is because the indexes have not been regenerated. If you see this close IJC and regenerate the indexes manually. Once this is has been performed you can restart IJC and the tables will re-appear.
Note that IJC does not mind if the name of the index is changed if it is dropped and re-created, though generally this should be avoided.
If you are using cartridge indexes on JChemBase tables then these tables will be regenerated when IJC connects to the oracle schema. The user will be asked whether the tables should be regenerated and if the user chooses to do this then they will be regenerated along with any plain JChemBase tables that are present.
If you are using the manual upgrade process you will however need to rebuild the cartridge index using an approach similar to this:
ALTER INDEX <index-name> REBUILD PARAMETERS('upgradeOnly:y')This process will be virtually instantaneous as the fingerprint data and other descriptors are already present in the JChemBase table.
If you are using cartridge indexes on standard tables then these indexes will NOT be regenerated when IJC connects to the oracle schema and you must regenerate these as part of the cartridge upgrade process. The upgrade approach will optionally do this, but if doing a manual upgrade then this must be done separately. If indexes are dropped and re-created this must be done carefully as IJC needs the updated state to be the same as the previous one. You must ensure that any chemical terms columns (the name, column type and the chemical terms expression) are the same as before the upgrade otherwise IJC will not be able to identify the items it was using. Again, this is a major benefit of the software-guided upgrade process. In the simplest case re-creating the index looks like this:
SQL> drop index idx_struc_table;
Index dropped.
SQL> CREATE INDEX idx_struc_table ON struc_table(structure) INDEXTYPE IS jchem.jc_idxtype;
Index created.This process of adding the index may take some considerable time as the fingerprint data and other descriptors will need to be generated as part of the index creation.
If you are using chemical terms columns and/or a standardizer then the situation is more complex as these have to be specified when re-creating the index.
You can either specify the standardizer when re-creating the index, or you can create the index without a standardizer and then set the standardizer again within IJC once the index has been regenerated and you have re-connected with IJC. In either case you will need to save the standardizer configuration before you commence the upgrade process.
Setting up correct privileges for JChem cartridge to operate can be complex. The above guide, plus the JChem cartridge administrators documentation provides a general overview. There are typically two types of problem, that are described briefly here.
The simplest case does not involve an index. In order for a user to see another user's JChem base table, then SELECT,INSERT,UPDATE and DELETE need to be granted for the table and it's '_UL' sibling, from the owner to the intended user. Also, the owner JCHEMPROPERTIES and JCHEMPROPERTIES_UL must also be visible and accessible to the user.
This could be your 'IJC_IDXOWNER' user if you want them to be able to create and use JChem cartridge indexes on tables and thus effectively 'own' the index. To make this clear we call this user 'IJC_IDXOWNER':
Create an Oracle user 'IJC_IDXOWNER'.
Grant CONNECT, RESOURCE and JCC_BASIC_ROLE roles to this user in Oracle as DBA.
On JChem cartridge server, in cartridge directory run the config-util.sh (or .bat) script with the list-sqls-for-jcc-user-privs argument.
Answer the questions correctly, at the prompt.
Perform the operations described as the DBA and JChem users.
The user 'IJC_IDXOWNER', is now able to create the JChem and Standard tables with associated cartridge indexes, by right click in the entities tab, select Structures table and use the drop down of table types.
An example of this config-util.sh script being called, with the 'list-sqls-for-jcc-user-privs' option is below:
{primary} Once the script has run, you need to perform the grant operations described between the CUT HERE parts of the output, to give the 'IJC_IDXOWNER' user the necessary access to the JChem cartridge functions.
$ ./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 +xIJC 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 here for details. For this to work with JChem cartridge indexes the user must be granted extra privileges on the index. To make this clear we call this user 'IJC_IDXUSER':
Create an Oracle user 'IJC_IDXUSER'.
Grant CONNECT, RESOURCE and JCC_BASIC_ROLE roles to this user in Oracle as the DBA.
Grant SELECT,INSERT, UPDATE and DELETE privileges on the JCHEMPROPERTIES and JCHEMPROPERTIES_CR tables in the index owner's schema ('IJC_IDXOWNER') to the 'IJC_IDXUSER' schema.
Grant SELECT privileges on the JC_IDX_PROPERTY table in the index owner's schema to the 'IJC_IDXUSER'.
On JChem cartridge server run the config-util.sh (or .bat) script with the config-index-user argument
Answer the questions correctly, at the prompt.
Perform the operations described as the DBA and JChem users
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_IDXUSER', 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.
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_idxuser
The name of the index (IDX_BUILDING_BLOCKS_GB): IDX_BUILDING_BLOCKS_GB -- 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_idxuser', 'ijc_idxowner', 'IDX_BUILDING_BLOCKS_GB', 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
Grant the privileges as described (unless you chose to let the script do this for you). Don't forget, you will need to repeat the above calls for each index you wish to grant access to!
Some notes on this:
Your JChem user may not have privileges to grant the JCC_BASIC_ROLE. You may need to do this as a privileged user.
You index owner may not see the privman_pgk 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.
This is usually a privileges problem when accessing tables from the non-default schema. IJC recognises tables with an approach similar to this:
select * from all_tables
where table_name = 'TABLE_NAME' and owner = 'SCHEMA_NAME'Try running this SQL as the Oracle user that IJC is using and see if the table is seem. You will need to substitute the values of TABLE_NAME and SCHEMA_NAME accordingly. If the table is not seen then you probably need to grant SELECT privilege (and maybe other privileges) on the table.
Alternatively, maybe privileges have not been set up on the JChem property tables that the cartridge needs. At the moment, SELECT privilege is needed on the JC_IDX_PROPERTY tables. Additionally any JChem property tables that are used and the associated cache registration tables need to be accessible also (usually called JCHEMPROPERTIES & JCHEM_PROPERTIES_CR). You must grant SELECT, INSERT, UPDATE and DELETE on these tables in order to avoid the error 'Error occurred: Register to JChem cache failed: ORA-01031: insufficient priviliges' To test this try the above command for each of these tables connected as your user.
This is usually a privileges problem. IJC recognises cartridge indexes like this:
select i.index_name, i.table_owner, i.table_name, c.column_name
from all_indexes i, all_ind_columns c
where i.ityp_owner = 'JCHEM'
and i.owner = 'SCHEMA_NAME'
and i.ityp_name='JC_IDXTYPE'
and i.index_name = c.index_nameTry running this SQL as the Oracle user that IJC is using and see if the index is visible. You will need to substitute the values of JCHEM and SCHEMA_NAME accordingly.
Another problem that can cause the index to become "invisible" is if it has been made invalid as a result of a failed operation on the index. You can rebuild the index quickly like this:
ALTER INDEX INDEX_NAME REBUILD PARAMETERS ('skipRecalc=y')This will quickly reconstitute the index. If it fails change the 'skipRecalc=y' to 'skipRecalc=n' which will perform a complete index rebuild (recalculating the fingerprints etc.) which can be a slow operation.
You have probably got the basic grants correct but have failed to set the privileges on the index to allow searching (or inserting, updating or deleting). See the section above on granting privileged access to the index.
You may need to use a fresh database session for all the newly granted privileges to take effect. Depending on which tool you use to execute structure searches, this may involve restarting IJC or SQL*Plus – or even restarting the JChem Cartridge server, in case the user already did unsuccessful searches with an incomplete privilege set. Also, rarely, you may need to commit (or close) the SQL sessions used to grant the privileges.
If this information doesn't solve your problem using the JChem Cartridge with IJC then please report the problem to the IJC forum .
Questions about the JChem Cartridge itself should be reported to the Structure search and chemical database forum .