Installation And Administration of JChem Cartridge for Oracle


Software requirements

  • JDK 6 or later is supported.
  • Oracle 10gR1 or later (including 11gR2)
    • Oracle Express Edition is not supported.
    • With Oracle 10gR1, patch level 10.1.0.5 or higher is required
    • With Oracle 10gR2, patch level 10.2.0.3 or higher is required; with Oracle Real Application Cluster patch level 10.2.0.4 is required.
    • 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 (for example: http://updates.oracle.com/download/7713193.html). These patches require Oracle Server to be already at patch-level 11.1.0.7.0.

A large number of operating systems is supported — all operating systems which are transitively supported by the required tools listed above (including Microsoft Windows, RedHat Linux, Oracle Linux 5.5, Oracle Solaris, etc.).

Support in JChem Cartridge for Oracle 9.2 ceases with JChem 5.4. (Support in JChem Base for Oracle 9.2 is continued.) Please, contact us (support at chemaxon dot com), if you would like to see support for Oracle 9.2 continued.

Before you begin

Before you begin installing JChem Cartridge, please, read the Introduction and the High Level Overview of the JChem Cartridge architecture.

If you encounter any problem during installation, do not hesitate to contact our technical support for assistance.

Installation of JChem Cartridge for Oracle

Default (single-host) installation

Deprecation notice: install-interact.sh and install-interact.bat have been deprecated in favor of install.sh and install.bat

Pre-installation tasks

  1. Enable JVM for the Oracle database Make sure that your Oracle database is JVM-enabled and properly configured for use of Java Stored Procedures. A database created with default settings typically meets this requirement.
    (If in doubt, please, read the sections Initializing a Java-Enabled Database and Configuring Oracle JVM in the chapter Java Installation and Configuration of Oracle's Java Developer's Guide.)
  2. Set the JAVA_HOME environment variable Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.6 or newer.
  3. Set the ORACLE_HOME environment variable Set the ORACLE_HOME environment variable to the home of the Oracle installation.
  4. Download the installation package Download the JChem Cartridge installation package and unpack it. You will want to uncompress the installation package in a permanent location (such as /opt/chemaxon/jchem-cartridge on Unix/Linux or C:\ChemAxon\JChem-Cartridge on Windows), because in addition to being used to install JChem Cartridge, the executables and configuration files of the uncompressed package will also back the operation of the JChem Cartrige server associated with the JChem Cartridge instance to be installed.
  5. (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.
    Some of the installation steps which are executed by the JChem Cartridge installation application by default, may need to be executed manually.
    • Some steps of the installation require elevated (DBA) privileges. In organizations where the function of the Application Administrator and Database Administrator is separated, DBA privileges may not be available during installation. In such cases, the JChem Cartridge owner and a basic JChem Cartridge user role has to be set up by the DBA prior the installation. The commands required elevated privileges can be listed by executing 
      [/opt/chemaxon/jchem/cartridge]$ config-util.sh list-dba-sqls [options]
      on Unix or
      C:\chemaxon\jchem\cartridge> config-util.bat list-dba-sqls [options]
      on Windows.
    • You may want to have more control over details of creation of the JChem Cartridge owner's schema than is provided by the installation application — for example, you want to assign a specific default table space to the JChem Cartridge owner. In such cases, the JChem Cartridge owner has to be created in advance. The JChem Cartridge installer application will check the availability of the JChem Owner's schema and if finds it (empty and equipped with the appropriate privileges), will use it, instead of trying to create and configure one.
      The SQL statements needed to grant the required privileges to the JChem Cartirdge owner can be listed by executing 
      [/opt/chemaxon/jchem/cartridge]$ config-util.sh list-jcc-owner-privs [options]
      on Unix or
      C:\chemaxon\jchem\cartridge> config-util.bat list-jcc-owner-privs [options]
      on Windows.

    The config-util program accepts the following options for both listing commands:

    --jcc-owner <jchem-owner>
    --jchem-server-host <jchem-server-host>
    --jchem-server-port <jchem-server-port>
    --oracle-version <target-db-major-version>.<target-db-minor-version>.

    The options which are not specified on the command line will be prompted for interactively.

Installation

  1. Start a command interpreter Start the command interpreter available on your platform (console/terminal on Unix/Linux, Command Prompt on Windows) and make the cartridge directory your current working directory.
  2. Start the installer Start the install.sh shell script or install.bat batch file.
  3. Provide the input requested by the script
    • If you answer to a prompt for a property with a question mark (?), more detailed description of the property will be displayed.
    • When prompted for the DBA login, enter exclamation mark (!), if you don't want the installer program to use DBA login. See this pre-installation task for steps required before installation in such cases.
    • After all required input have been provided, you will be asked to confirm them and only after your confirmation will database and configuration files be changed.

Post-installation tasks

  1. (Linux/Unix users) Create system service control script 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. (Please, see the following Forum post: http://www.chemaxon.com/forum/viewpost16398.html#16398)
  2. Start JChem Server After the installation program start JChem Server
  3. Register your license file(s)
  4. Enable Oracle users to use JChem Cartridge

Installing JChem Cartridge on two hosts

JChem Cartridge can be set up so that the stand-alone Java computation server (JChem Server) and the Oracle server are on two different hosts.

It is important to note that host names specified during the two-hosts installation must be "real" host names (or real IP address)as opposed to the default localhost/127.0.0.1.

Deprecation notice: install-interact.sh and install-interact.bat have been deprecated in favor of install.sh and install.bat

  1. Installing the JChem Server
  2. Installing the Oracle-resident part

Installing the JChem Server

This part of the installation must be executed on the host where JChem Server is to be installed.

Pre-installation tasks

  1. Download the installation package Download the JChem Cartridge installation package and unpack it on the machine which is to host the JChem Server.You will want to uncompress the installation package in a permanent location (such as /opt/chemaxon/jchem-cartridge on Unix/Linux or C:\ChemAxon\JChem-Cartridge on Windows), because in addition to being used to install the JChem Cartridge server, the executables and configuration files of the uncompressed package will also back the operation of the JChem Cartrige server associated with the JChem Cartridge instance to be installed.
  2. Set the JAVA_HOME environment variable Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.6 or newer.

Installation

  1. Start a command interpreter on the host where JChem Server is to be installed Start the command interpreter available on your platform (console/terminal on Unix/Linux, Command Prompt on Windows) and make the cartridge directory your current working directory.
  2. Start the installer Start the install.sh shell script or install.bat batch file with the --jcserver-only argument. E.g: 
       bash install.sh --jcserver-only
  3. Provide the input requested by the script
    • If you answer to a prompt for a configuration parameter with a question mark (?), more detailed description of the property will be displayed.
    • After all required input have been provided, you will be asked to confirm them and only after your confirmation will configuration files be changed.

Post-installation tasks

  1. (Linux/Unix users) Create system service control script 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. (Please, see the following Forum post: http://www.chemaxon.com/forum/viewpost16398.html#16398)
  2. Start JChem ServerAfter the installation program start JChem Server
  3. Register your license file(s)

Installing the Oracle-resident part

This part of the installation has to be executed on a host with Java installed on it.

Pre-installation tasks

  1. Enable JVM for the Oracle database Make sure that your Oracle database is JVM-enabled and properly configured for use of Java Stored Procedures. A database created with default settings typically meets this requirement.
    (If in doubt, please, read the sections Initializing a Java-Enabled Database and Configuring Oracle JVM in the chapter Java Installation and Configuration of Oracle's Java Developer's Guide.)
  2. Set the JAVA_HOME environment variableSet the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.6 or newer.
  3. Set the ORACLE_HOME environment variableSet the ORACLE_HOME environment variable to the home of the Oracle installation.
  4. Download the installation packageDownload the JChem Cartridge installation package and unpack it on the host where it will be executed (preferably on the Oracle server).
  5. (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.
    Some of the installation steps which are executed by the JChem Cartridge installation application by default, may need to be executed manually.
    • Some steps of the installation require elevated (DBA) privileges. In organizations where the function of the Application Administrator and Database Administrator is separated, DBA privileges may not be available during installation. In such cases, the JChem Cartridge owner and a basic JChem Cartridge user role has to be set up by the DBA prior the installation. The commands required elevated privileges can be listed by executing 
      [/opt/chemaxon/jchem/cartridge]$ config-util.sh list-dba-sqls [options]
      on Unix or
      C:\chemaxon\jchem\cartridge> config-util.bat list-dba-sqls [options]
      on Windows.
    • You may want to have more control over details of creation of the JChem Cartridge owner's schema than is provided by the installation application — for example, you want to assign a specific default table space to the JChem Cartridge owner. In such cases, the JChem Cartridge owner has to be created in advance. The JChem Cartridge installer application will check the availability of the JChem Owner's schema and if finds it (empty and equipped with the appropriate privileges), will use it, instead of trying to create and configure one.
      The SQL statements needed to grant the required privileges to the JChem Cartirdge owner can be listed by executing 
      [/opt/chemaxon/jchem/cartridge]$ config-util.sh list-jcc-owner-privs [options]
      on Unix or
      C:\chemaxon\jchem\cartridge> config-util.bat list-jcc-owner-privs [options]
      on Windows. The following options are accepted:

      --jcc-owner <jchem-owner>
      --jchem-server-host <jchem-server-host>
      --jchem-server-port <jchem-server-port>
      --oracle-version <target-db-major-version>.<target-db-minor-version>.

      The options which are not specified on the command line will be prompted for interactively.

Installation

  1. Start a command interpreter Start the command interpreter available on your platform (console/terminal on Unix/Linux, Command Prompt on Windows) and make the cartridge directory your current working directory.
  2. Start the installer Start the install.sh shell script or install.bat batch file.
  3. Provide the input requested by the script
    • If you answer to a prompt for a configuration parameter with a question mark (?), more detailed description of the property will be displayed.
    • When prompted for the DBA login, enter exclamation mark (!), if you don't want the installer program to use DBA login. See this pre-installation task for steps required before installation in such cases.

Post-installation tasks

Two-host installation example:

Assume that the Oracle Server is hosted on the machine called negev and the JChem Server will be hosted on shiraz .
  1. Make sure the JAVA_HOME environment variable points to a Sun Microsystems Java Development Kit 1.6 or later on shiraz. (You won't need to set ORACLE_HOME on shiraz.)
  2. Start the install.sh shell script on shiraz: 
       bash install.sh --jcserver-only
  3. Enter negev, when prompted for Name of the Oracle host.
  4. Enter shiraz, when prompted for The host name of the JChem Server.
  5. Answer any further questions as appropriate.
  6. After the installation on shiraz has been completed, start the JChem Server on shiraz:
       bash server.sh start
  7. Execute the steps on negev described for the default installation making sure to
    1. enter negev, when prompted for Name of the Oracle host;
    2. enter shiraz, when prompted for The host name of the JChem Server;

Installing JChem Server with Oracle Real Application Cluster (RAC)

JChem Cartridge has been tested on Oracle RAC with

  1. Clusterware 10.2.0.4
  2. Oracle 10.2.0.4
  3. the JChem Server running on a host "outside" the RAC and connecting to the RAC's nodes using their public virtual IP addresses.
Installation is similar to installing JChem Server and Oracle on two different hosts with the following difference:
  1. You must pick one of the RAC nodes and use its host name and Oracle instance name during installation.
  2. After the installation has been completed, the JChem Server configuration must be modified to make it a client of the RAC instead of only the selected node.

Example:

Assume that the cluster has two nodes with

  • management interfaces reachable as clu0 and clu1
  • virtual IP addresses of 10.0.0.81 and 10.0.0.83
  • one Oracle instance each called mydb1 and mydb2 respectively
  • a clustered service called mydbs
. Assume that you pick clu0 to act on behalf of the cluster during installation. Assume further that JChem Server will be hosted by a computer called shiraz .
  1. Make sure the JAVA_HOME environment variable points to a Sun Microsystems Java Development Kit 1.6 or later on shiraz. (You won't need to set ORACLE_HOME on shiraz.)
  2. Start the install.sh shell script on shiraz: 
       bash install.sh --jcserver-only
  3. Enter clu0, when prompted for Name of the Oracle host.
  4. Enter mydb1, when prompted for Oracle instance name.
  5. Enter shiraz, when prompted for The host name of the JChem Server.
  6. Answer any further questions as appropriate.
  7. After the installation on shiraz has been completed, start the JChem Server on shiraz:
       bash server.sh start
  8. Execute the steps on negev described for the default installation making sure to
    1. enter clu0, when prompted for Name of the Oracle host;
    2. enter mydb1, when prompted for Oracle instance name;
    3. enter shiraz, when prompted for The host name of the JChem Server;
  9. After JChem Cartridge installation is complete and can be used with JChem Server talking to clu0, modify JChem Server configuration to have its traffic load-balanced across then entire RAC:
    Add or edit the url property in the cartridge/conf/jcart.properties file with the following value: 
    url=jdbc\:oracle\:thin\:@(DESCRIPTION=(LOAD_BALANCE=on)\
(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.81)(PORT=1521))\
(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.83)(PORT=1521))\
(CONNECT_DATA=(SERVICE_NAME=mydbs)))

Administrative tasks

Starting and stopping JChem Server

The preferred way to start and stop JChem Server is through the system service facility provided on the host operating system. On Windows, a service for JChem Cartridge is created during installation. Linux/Unix users may want to look at the following Forum post: http://www.chemaxon.com/forum/viewpost16398.html#16398) for a way to create a system service.

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

Purging connection caches

Executing the following command in the jchem/cartridge directory on the JChem Server host 

$ ./server.sh purge-connection-caches
will result in the connection caches in the JChem Server being purged so that new connections will be opened by JChem Server on the next JChem Cartridge operations (without having to re-execute jchem_core_pkg.use_password for the users ).

Clearing server references in client connections

JChem Cartridge caches, in the client connections, references to RMI server objects in the JChem Cartridge server. If JChem Cartridge is restarted, these RMI references become stale. If closing and reopening the client connections is not an option, the following SQL command can be executed (in each client connection affected) to force refetching new server references: 

call rmi.clear_directory_cache();

Configuring the JChem Server

Setting the maximum size of the Java heap memory

  1. Detemining the maximum amount of memory required by the JChem Cartridge server application
  2. Detemining the maximum size of the Java heap memory
  3. Setting the maximum size of the Java heap memory
  4. Example

Detemining the maximum amount of memory required by the JChem Cartridge server application

For best performance, the amount of free physical memory on the JChemServer host should be large enough to accommodate the Java heap memory.

The amount of Java heap memory, in turn, should be large enough to accommodate

  • the Structure Cache of the search engine and
  • the objects associated with individual (concurrent) searches and other user operations (such as indexing).

  1. Structure Cache memory

    The rule of thumb is that the structure cache needs about 130 MB of memory for 1 million molecular structures with 512 bit fingerprint length. The longer the fingerprint length, the larger the required memory. (For a detailed description of how to calculate an estimation of the memory requirement of the search cache, see the FAQ. Multiply the result of the formula provided in the referenced FAQ item by about 1.3 on 64-bit systems.)

    The Structure Cache will not use the entire Java heap size. It will try to "spare" the amount of memory specified by the value (in megabytes) of the reservedMemory property set in the jchem/cartridge/conf/jcart.properties file.

  2. Operation-dependent memory

    The operation dependent memory requirement can be further subdivided into two categories:

    1. Temporary memory for the search hits

      The memory overhead for each concurrent search is about 1.7MB per 100k hits. For example: if a particular JChem Cartridge installation is supposed to support 10 truly concurrent searches returning each (at the same time) a maximum of 1 million hits, JChem Server needs 170MB memory in addition to what is required for the structure cache.

    2. "Everything else"

      A difficult to quantify component which basically reflects the usage pattern of JChem Cartridge observable at the specific site. Still, assuming that user activity is evenly spread over time, it can be typically regarded fairly constant. A good initial value could be 200MB. It can be further adjusted depending on the number of concurrent users, the type of operations they do and the kind of (input) structures they operate on.

    Althought not always effective (and therefore not strictly necessary), it is a good practice to specify the total expected amount of the operation-dependent memory (in megabytes) through the reservedMemory property in the jchem/cartridge/conf/jcart.properties file.

Detemining the maximum size of the Java heap memory

The Java runtime process in which the JChem Cartridge server application is executing works with a memory overhead of about 12.5% over the JChem Cartridge server's calculated total memory requirement. This means that the JChem Cartridge server's calculated total memory requirement has to be multiplied by 1.125 to obtain the value for the jcserver.maxmem property in jchem/cartridge/conf/jcart.properties, which property is used to set the maximum heap space of the JVM process.

Setting the maximum size of the Java heap memory

Set the jcserver.maxmem property in the jchem/cartrdige/conf/jcart.properties file: 

jcserver.maxmem=700m
. Make sure the letter m or M is appended to the value to denote the megabyte measurement unit. JCC server restart is required for the new value to take effect.

Example:

Assuming that the jc_idxtype index called JCXCDDR_0 has been created

  • on 10 million structures
  • with default fingerprint properties (512 bit long fingerprints)
  • and an average cd_smiles length of 46.3 (obtained by executing: select jcc_idx_stats.get_avg_cdsmiles_len('CCDR_OWNER.JCXCDDR_0') from dual; )
, you estimate the structure cache memory requirement (10000000*(0.5*46.3+512/8+13.5)) at 1,006 megabyte. (Repeat this calculation for all indexes which this particular JChem Cartridge instance has to service and add the results to get the total structure cache memory requirement. We assume for this example that we have a single JChem index for this JChem Cartridge instance to service, so the total memory required by the structure cache is 1,006 megabyte.) You further estimate
  • the temporary memory required for search hits at 200 megabyte
  • the general memory overhead of the JChem Cartridge server application at 200 megabyte
which gives 1,406 megabyte memory required by the application, which gives a memory requirement of roughly 1,6 gigabyte by the Java runtime (1406*1.125). The jcserver.maxmem property in jchem/cartridge/conf/jcart.properties must therefore be set to 1600m.

Disabling screening during Markush search

With current JChem Cartridge versions, screening during Markush search may be counter-productive on two-host installations with relatively slow network connections (rule of thumb: 0.5 ms ping time or longer). In such cases add the following line to jchem/cartridge/conf/jcart.properties:

markush.screening.enabled = false

Setting Oracle cache properties

If JChem Server is using the default data source implementation ( chemaxon.jchem.cartridge.util.cpool.Oracle10gDataSource with 10g or 11g JDBC drivers), the connection cache properties can be specified in the jchem/jchemsite/cartridge/conf/jcart.properties file, by prefixing their names with the oracle.connection.cache. character string. For example, in order to close idle connections after 10 seconds you have to specify in the jchem/jchemsite/cartridge/conf/jcart.properties file, the following: 

oracle.connection.cache.InactivityTimeout = 10
oracle.connection.cache.PropertyCheckInterval = 10
(The name of the PropertyCheckInterval property is somewhat misleading: in addition to the interval between checks on property values, it also specifies the interval for enforcing the cache properties' settings. Its default value is 900 seconds, so it should be adjusted for any Oracle-connection-cache-related time-out settings specifying a time-out less then 900 seconds.)

Ignoring JChem Server properties for specific commands

Some custom properties you might have set for a running JChem Server might be interfering with other JChem Server related tasks which are executed through the server.sh or server.bat scripts. For example, you might fancy to enable remote JMX management by setting the sysprop.com.sun.management.jmxremote.port property in jchem/conf/jcart.properties . When you execute 

server.sh stop
to stop the running server, the command will fail complaining about the already used JMX port. You can prevent this from happening by adding the following line to the jchem/conf/jcart.properties file: 
ignore.properties.for.stop=sysprop.com.sun.management.jmxremote.port
More generally, specifying a comma-separated list of properties (which are found in the jcart.properties file) as the value of the ignore.properties.for.<command-string> property allows you to exclude that list of properties from evaluation for a specific server.sh command. So if you also use the 
server.sh reload
command (for reloading certain configuration parameters of JChem Server without having to stop it), you might also consider adding:

ignore.properties.for.reload=sysprop.com.sun.management.jmxremote.port

Testing the JChem Cartridge installation

Connect to the database as the JChem-owner (or the JChem-user) to be tested and execute the following: 

  1. call jchem_core_pkg.use_password('<passwd>');
    where <passwd> is JChem-owner's (or the JChem-user's) password; 
  2.  select jchem_core_pkg.getenvironment() from dual;
If JChem Cartridge is correctly working, the last call returns an output similar to the following: 
    JCHEM_CORE_PKG.GETENVIRONMENT()
    --------------------------------------------------------------------------------
    Oracle environment:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE    10.2.0.3.0      Production
    TNS for Linux: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production

    JChem Server environment:
    Java VM vendor: Sun Microsystems Inc.
    Java version: 1.6.0_23
    Java VM version: 1.6.0_23-b03
    JChem version: 5.0.0alpha14

    JCHEM_CORE_PKG.GETENVIRONMENT()
    --------------------------------------------------------------------------------
    JDBC driver version: 10.2.0.3.0

Registering the license file(s)

Create a directory licenses in the cartridge directory (in the working directory of the JChem Server) and drop the license files received from ChemAxon's Sales into the licenses directory. If you want JChem Server to retrieve the license files from a different directory (e.g. because multiple applications or hosts share a common license "repository"), set the license.dir property in the jcart.properties file to the path of that directory.

Multiple license files:

  • If your licenses are stored in multiple license files (i.e. you receive an additional license file from ChemAxon Sales with, for example, a renewed license or an evaluation license for an additional feature), you have to drop all license files into the licenses directory (wherever it is configured to be in the conf/jcart.properties file). This means that you may have to store them under different name -- for example as license0.cxl, license1.cxl, license2.cxl, etc. -- to avoid overwriting each other.
  • There should be only one entry for any licensed feature in the license files. For example, if you receive a license file with a renewed license, remove the entry for the corresponding expired license from the old license file, so that only the renewed, valid license entry is found for the given feature in the files.

Important note:
On October the 1st, 2008, the old license files (issued for JChem versions prior to 5.0.0) will become unusable with releases JChem 5.0.0 and above. In case you have such files, you need to request ChemAxon Sales to re-issue the licenses in the new, XML-based format.

Configuring JChem Cartridge users

It is recommended that the Oracle user created during JChem Cartridge installation is exlusively dedicated to owning the core database objects of JChem Cartrdige (e.g. jchem_core_pkg ), so that these objects are cleanly separated from users' objects.

Types of JCC users

Users need to have appropriate system and object privileges as well as synonyms in order to successfully use JChem Cartridge.

From an administrative prespective, users can be divided into two categories:

  1. those who own the jc_idxtype indexes they use as well as those who don't need such indexes at all because they use functions and operators with literal (string constant) input structures;
  2. those who use jc_idxtype indexes owned by a different user.

Users of both categories need a basic set of privileges in order to execute JChem Cartridge functions and operators. In addition to this basic set of privileges, users of the second category also need privileges on the objects associated with the index not owned by them. The following figure shows the various groups of privileges.

Certain permissions/roles (CONNECT, RESOURCE) must be granted directly to users. Others may be granted both directly indirectly through roles which already have been granted the necessary privileges. (The benefit of roles is obvious with multiple/large number of users.) One single role is sufficient for the basic privileges that all users must have (represented by the JCC_BASIC_ROLE role on the figure). In addition to the basic role, zero or more futher roles are required for members of the second user category depending on how many groups of such users there are in your organization/project. Each group of such users will need a set of extra privileges (specific to the given group) corresponding to the set of not-owned indexes which members of the group need to access. The roles for these groups of "secondary" users are represented by the JCC_IDXxxxx_ROLE role.

User credentials and privileges for JChem Server

Resuing the parent session's identity

The structure tables and domain indexes of a given user initiating a given cartridge operation must be accessible via the JDBC connection(s) opened in the JChem Server for use by the fingerprint caching engine. Beginning with JChem version 3.0.12 the recommended way to achieve this is for users to call the PL/SQL procedure jchem_core_pkg.use_password(password VARCHAR2) with their password as the parameter at the beginning of each database session (see a brief description of the mechanism at work here). The function jchem_core_pkg.set_password(password VARCHAR2) has a similar effect; it returns 0, if the password is valid for the user, -1 otherwise.

The two-argument variant of jchem_core_pkg.use_password(login VARCHAR2, password VARCHAR2) can be used to instruct JChem Server to use a different identity to connect back to Oracle – different from the real user. This may be useful for example in definer's-authorization stored procuderes where the real user is transfered the privileges of the definer of the stored procedure for the time the stored procedure's code is executed.

The passwords specified through jchem_core_pkg.use_password are, by default, cached in JChem Server and reused for subquent requests. For heightened security, the password.always.required property can be specified. When set to true , this property will instruct JChem Server to require a password (in addition to the database login) for each RMI request involving database access. In this non-default case, the password is cached in the parent Oracle session and passed along with each request to JChem Server. This mode of operation thus requires the call to jchem_core_pkg.use_password at the beginning of each database session. (As opposed to the default behaviour, which requires this call only once between two restarts of the JChem Server.)

Defaulting to a pre-configured identity

If the password of a given user has not been set for JChem Server via the use_password PL/SQL procedure, the oracle.server.login and oracle.server.password JChem Cartridge properties will be used to open database connections in JChem Server.

If the use of parent sessions' identities is combined with the use of the default identity, the need may arise for preventing users who are supposed to use the default credentials in JChem Server for their database operations from accidently using their parent session identities. The allowed.to.specify.credentials property in jchem/cartridge/conf/jcart.properties can be used for this purpose. When it is specified, only users are allowed to set their parent sessions' identities for use in JChem Server whose database login names appears in the comma separated list that this property can be set to.

If the jchem_core_pkg.use_password(password VARCHAR2) call cannot be used by users or applications for some unlikely reason, the user specified by the oracle.server.login property must have the following privileges:

  • SELECT ANY TABLE
  • SELECT ANY SEQUENCE
  • DELETE ANY TABLE
  • CREATE ANY TABLE
  • CREATE ANY INDEX
  • CREATE ANY SEQUENCE
  • CREATE ANY TRIGGER
  • INSERT ANY TABLE
  • UPDATE ANY TABLE
  • DROP ANY TABLE
  • DROP ANY SEQUENCE
  • DROP ANY TRIGGER

Please, note that the JChem Cartridge components which are executed in Oracle currently communicate with JChem Server using an open RMI protocol. This means that some kind of protection (e.g. a simple firewall) must be setup on the machine which hosts JChem Server to prevent unauthorized searches in the structure tables.

Accessing JChem Cartridge functionality through PL/SQL procedures defined by other users (with definer's right):

For users who need to access JChem Cartridge functionality from within PL/SQL programs, the same privileges that have been granted to the JCC_BASIC_ROLE (described here) must be also directly granted to these users. The reason is that privileges obtained through a role are ignored by Oracle for access control excercised in a PL/SQL program. The following command can be used to list the SQL statements for granting the required privileges: 

[/opt/chemaxon/jchem/cartridge]$ config-util.sh list-sqls-for-jcc-user-privs [options]
on Unix or 
C:\chemaxon\jchem\cartridge> config-util.bat list-sqls-for-jcc-user-privs [options]
on Windows. The config-util program accepts the following options for this listing command:

--jcc-owner <jchem-owner>
--jchem-server-host <jchem-server-host>
--jchem-server-port <jchem-server-port>
--oracle-version <target-db-major-version>.<target-db-minor-version>
--user-to-configure <the-user-to-configure>

In addition, if users are also to create jc_idxtype indices from within PL/SQL programs, they will need to have CREATE TABLE and CREATE SEQUENCE privileges directly granted to them. (Privileges associated with RESOURCE role will be ignored as well.) Symetrically, users will need DROP TABLE and DROP SEQUENCE privileges directly granted to them in order to successfully drop indices in their schemata.

Setting up users to access JChem index(es) created by other users

If applicable, create privileges for those users who need to use jc_idxtype index(es) owned by another user:
Depending on how JChem Server is set up to connect back to Oracle, this step can be as simple as granting the appropriate privilege (the required combination of SELECT, INSERT, UPDATE and DELETE privileges on the structure table to the non-owner user.

JChem Cartridge "super user" configured in JChem Server

One possible configuration is to set the "oracle.server.login" and "oracle.server.password" in the cartridge/conf/jcart.properties configuration file to the credentials of a user whose privileges cover the required privileges of all JChem Cartridge users. The jcart-util.sh script can be used with the the encrypt argument to convert login names and passwords into an encrypted for which JChem Server can subsequently decrypt: 

     ./jcart-util.sh encrypt

With this configuration, the appropriate privilege (the required combination of SELECT, INSERT, UPDATE and DELETE privileges) must be granted on the structure table to the non-owner user.

This approach (let's call it "the JChem Cartridge super-user" approach) has the advantage of a simpler privilege configuration for "second-level" JChem Cartridge users (those who use other users' indexes such as GSK_DATA_READER), but has the disadvantage of "having around" a relatively highly privileged user. Some organizations perceive this disadvantage as prohibitive. (The JChem Cartridge super-user approach is also recommended, if you want to use Oracle's built-in fine grained (row-level) access control mechanism.)

JChem Server uses initiators' identities

The other configuration consists of JChem Server using the initiator user's identity to connect back to Oracle. With this configuration you have to perform the following steps to let non-owner users access JChem structure indexes:
  1. As the JChem Cartridge owner execute: 
    1. privman_pkg.grants_on_jcobjs('<jchem-owner>', '<index-owner>');
      to make sure the index owner is directly granted privileges on objects in the JChem Cartridge owner's schema (as opposed granted through a role); 
    2. privman_pkg.grants_on_jcobjs('<jchem-owner>', '<index-user>');
  2. As the index owner execute the privman_pkg.grants_on_jcidx procedure in the JChem Cartridge owner's schema to grant the desired privilege on the index. This procedure accepts the following parameters:
    1. The name of the role (or user) which needs to access the given index
    2. The name of the index owner
    3. The name of the index
    4. "search" flag, 1 if the user needs to access the index for searching, 0 otherwise.
    5. "insert" flag, 1 if the user needs to access the index for inserting new structures, 0 otherwise.
    6. "update" flag, 1 if the user needs to access the index for updating structures, 0 otherwise.
    7. "delete" flag, 1 if the user needs to access the index for deleting structures, 0 otherwise.
Note:
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.
Command-line tool

A command-line tool is provided to help you execute the steps required. In the jchem/cartridge directory , execute the following command and enter the input you're prompted for: 

config-util.sh config-index-user

(If you enter a question mark a more detailed help messages will be displayed on the input parameter required.)

Consider using roles

If multiple users need to use the same index with the same access privileges, you will want to create a role for the particular privilege set on the particular index and execute the above steps on the role (instead of concrete users) and grant the role to the users.

Example:

The following example grants the CCDR19_READER Oracle user "search permission" on the index CCDR19 owned by the user CCDR19_OWNER : 

    -- As JCHEM:
    privman_pkg.grants_on_jcobjs('JCHEM', 'CCDR19_OWNER');
    privman_pkg.grants_on_jcobjs('JCHEM', 'CCDR19_READER');
    -- ----------------------------------------------------
    -- As CCDR19_OWNER:
    call jchem.privman_pkg.grants_on_jcidx('CCDR19_READER', 'CCDR19_OWNER', 'CCDR19', 1, 0, 0, 0);

Tracking JChem Server activity

Tracking memory utilization

The jcf.t_get_gmem_util() function can be used to obtain global memory utilization data. The call

select * from table(jcf.t_get_gmem_util())
will return two columns:
  1. DESCRIPTION describing the memory resources and
  2. UTIL_MB giving the value for the memory resource in megabytes.
The memory resources are:
  1. Total JVM memory: the amount of memory currently reserved by the JChem Server process.
  2. Free JVM memory: the amount of memory currently reserved but not allocated to Java program objects.
  3. Max JVM memory: the total amount of memory which the JChem Server process is allowed to eventually reserve.
  4. <index-owner-name>.<index-name>: the amount of memory used for caching data associated with the specified index.

Tracking tasks in progress

The jcf.t_get_taskinfo() function can be used to list outstanding tasks in the JChem Server. The call

select * from table(jcf.t_get_taskinfo());
will return the following columns:
  1. ID specifying the internal identifier of the task;
  2. DESCRIPTION specifying various attributes of the task such as the kind of operation, the options specified for the operation, the index being worked on;
  3. START_TIME specifying the time when the task has been submitted;
  4. USER_NAME specifying the user who submitted the tasks (in whose session the operation has been started)
  5. ESTIM_MEMORY_USE specifying an approximation of the memory (in bytes) used by task;
  6. TIMEOUT specifying the amount of time (in seconds) which the task can be sitting idle. (Idle timeout can be specified for domain index scans and are useful in cases where hits found during search are fetched piece-meal by the Oracle resident JCC parts for early retrieval without waiting for all the hits to be retrieved. The task is normally completed when all hits have been fed to Oracle. On completion, the resources associated with the task are released. The idle time out makes resource release time-bound if the database session which submitted the tasks has crashed, for example);
  7. LAST_RESCHEDULED specifying the time when the task last was accessed by the Oracle-resident JCC part.

When executed by the jchem owner, jcf.t_get_taskinfo() returns all outstanding tasks. Non-jchem owner get listed only the tasks which they have started.

Currently only index scans are tracked by the jcf.t_get_taskinfo() function.

Producing thread dump

If the JCC server appears to hang (the server in general or any of the outstanding tasks in particular), samples of the execution state might be helpful to ChemAxon's technical support for diagnosing the problem. Executing the following command in the jchem/cartridge directory on the JChem Server host 
$ ./server.sh thread-dump
will dump the current execution stack of the JCC server's threads to the standard error.

Using JChem Cartridge

SQL operators of the Cartridge can be used to search for chemical data. SQL procedures are also defined to perform DML operations on chemical tables. To increase the speed of the searching process, the jc_idxtype index type has to be applied.

See using JChem Cartridge.

Removing JChem Cartridge

To remove JChem Cartridge

  1. drop all jc_idxtype indexes and other PL/SQL objects that have been created after installation (including user defined operators added in support of user defined functions and supporting PL/SQL functions).
  2. drop the JChem Cartride owner's schema (the schema where JChem Cartridge has been installed)
  3. Windows users: remove the Windows service for the JChem Cartridge server by executing in the jchem\cartridge directory: 
    .\prunsrv.exe //DS//JChemCartridgeService

Upgrading JChem Cartridge

The following upgrade instructions apply to JChem Cartridge version 5.0 or later. With JChem Cartridge versions prior to 5.0, first remove the current version of JChem Streams and JChem Cartridge, then install the new version. Note that you have to obtain new 5.0-compatible XML-based license file and install it.

Deprecation notice: upgrade-evol.sh and upgrade-evol.bat have been deprecated in favor of upgrade.sh and upgrade.bat

  1. Single-host upgrade
  2. Two-host upgrade

Upgrading sigle-host installations

Pre-upgrade tasks

  1. Set the JAVA_HOME environment variable Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.6 or newer.
  2. Download the installation package of the new JChem Cartridge version Download the installation package of the new JChem Cartridge version and unpack it on the machine hosting the JChem Cartridge server.
  3. Unpack the new JChem installation package Typcially, you will want to unpack the new version next to the location where the old version was unpacked (i.e. in a sibling directory of the old version).

  4. (Optional) Make sure an empty schema for staging the upgrade exists For the upgrade of Oracle objects in the JChem Cartridge owner's schema, an additional, empty schema will be required where the objects of the new version will be loaded and used as a blue-print for the upgrade of the JChem Cartridge owner's schema. If you provide DBA credentials for the upgrade program, the staging schema will be created for you with the required privileges.

    If, for some reason, DBA privileges cannot be entered to the upgrade program (for example, because the upgrade is essentially performed by an application adminstrator, who is a distinct person from the database administrator), the staging schema with the required privileges must be created manually. The privileges required by the staging schema can be listed by executing the config-util program in the jchem/cartridge directory with the list-sqls-for-staging-schema argument: 
    config-util list-sqls-for-staging-schema
    . Starting with JChem Cartridge version 5.5.1, the staging schema will be emptied by the upgrade program after it is no longer needed, so it is there ready for use during subsequent upgrades. With prior version the schema can be emptied by executing the script published under this forum post.

Upgrade

  1. Stop users from using JChem Cartridge Restrict access to the Oracle instance and/or to the applications accessing JChem Cartridge so you can safely upgrade JChem Cartridge without user requests and the installation procedure interfering with each other.
  2. Stop the old JChem Server version
    1. If you use your operating system's service facility for automatically starting and stopping JChem Server, use that facility to stop it first
    2. Execute server.sh stop (or server.bat stop) in the cartridge directory of old JChem version
    The second step must be performed even, if the service appeared to stop correctly in the first step.
  3. Navigate to the cartridge directory of the new JChem version in a command interpreter Change your current working directory to the cartridge directory of the new JChem version. (Users having multiple JChem Cartridge instances reusing the same JChem package, may want to look at this forum topic at this point.)
  4. Start the upgrade application Start the script upgrade.sh (or upgrade.bat on Windows). (The main purpose of the script is to upgrade the PL/SQL objects associated with the JChem Cartridge implementation. But it will also try migrating JChem Server settings, license files, rebuilding indexes, etc.)
  5. Provide the input requested by the script
    • If you answer to a prompt for a property with a question mark (?), more detailed description of the property will be displayed.
    • When prompted for the DBA login, enter exclamation mark (!), if you don't want the installer program to use DBA login. See this pre-upgrade task for steps required before installation in such cases.

Post-upgrade tasks

  1. On Windows, the upgrade application will offer to upgrade the Windows service for JChem Cartridge server. On other operating systems, you must make sure to update the service configuration to point to the new JChem package.
  2. Start JChem Server.
  3. If you ran the upgrader application without DBA privileges, you have to
    1. rebuild JChem indexes on plain structure tables by executing the following SQL statement: 
      alter index >index-name< rebuild parameters('upgradeOnly=y'))
    2. upgrade JChem Base structure tables (if you use any) using JChemManager.

Upgrading two-host installations

In order to minimize JChem Cartridge down-time, it is recommended that you execute Pre-upgrade tasks for the JChem Cartridge server on the machin where the JChem Cartridge server is running and Pre-upgrade tasks for the JChem Cartridge owner on the server you selected for executing the Oracle side upgrade (the Oracle server or the JChem Cartridge server host is recommended). Execute the respective upgrade and post-upgrade tasks only after the preparations have been completed on both machines.

Upgrading JChem Cartridge server

This part of this installation has to be performed on the machine hosting the JChem Cartridge server.

Pre-upgrade tasks

  1. Set the JAVA_HOME environment variable Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.6 or newer.
  2. Download the installation package of the new JChem Cartridge version Download the installation package of the new JChem Cartridge version.
  3. Unpack the new JChem installation package Typcially, you will want to unpack the new version next to the location where the old version was unpacked (i.e. in a sibling directory of the old version).

Upgrade

  1. Stop users from using JChem Cartridge Restrict access to the Oracle instance and/or to the applications accessing JChem Cartridge so you can safely upgrade JChem Cartridge without user requests and the installation procedure interfering with each other.
  2. Stop the old JChem Server version
    1. If you use your operating system's service facility for automatically starting and stopping JChem Server, use that facility to stop it first
    2. Execute server.sh stop (or server.bat stop) in the cartridge directory of old JChem version
    The second step must be performed even, if the service appeared to stop correctly in the first step.
  3. Navigate to the cartridge directory of the new JChem version in a command interpreter Change your current working directory to the cartridge directory of the new JChem version. (Users having multiple JChem Cartridge instances reusing the same JChem package, may want to look at this forum topic at this point.)
  4. Execute the following command on the JChem Server host: 
    bash upgrade.sh --jcserver-only
    As the last part of this step, Windows user will be offered the option to upgrade the JChem Cartridge windows service. Select this option only if the windows service was created by the JChem Cartridge installer during the original installation.
  5. Provide the input requested by the script
    • If you answer to a prompt for a property with a question mark (?), more detailed description of the property will be displayed.

Post-upgrade tasks

  1. On Windows, the upgrade application will offer to upgrade the Windows service for JChem Cartridge server. On other operating systems, you must make sure to update the service configuration to point to the new JChem package.
  2. Start JChem Server.

Upgrading the JChem Cartridge owner schema

This part of the installation has to be executed on a host with (at least) Java installed on it. During this part you also should be able to access the configuration data of the old installation. You typically execute this part either on the same host where the Oracle-side installation (or the previous Oracle-side upgrade) was executed or on the JChem Cartridge server host.

Pre-upgrade tasks

  1. Set the JAVA_HOME environment variable Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.6 or newer. upgrade program.
  2. Download the installation package of the new JChem Cartridge version Download the installation package of the new JChem Cartridge version and unpack it on the machine where you will execute the
  3. Unpack the new JChem installation package Typcially, you will want to unpack the new version next to the location where the old version was unpacked (i.e. in a sibling directory of the old version).

  4. (Optional) Make sure an empty schema for staging the upgrade exists For the upgrade of Oracle objects in the JChem Cartridge owner's schema, an additional, empty schema will be required where the objects of the new version will be loaded and used as a blue-print for the upgrade of the JChem Cartridge owner's schema. If you provide DBA credentials for the upgrade program, the staging schema will be created for you with the required privileges.

    If, for some reason, DBA privileges cannot be entered to the upgrade program (for example, because the upgrade is essentially performed by an application adminstrator, who is a distinct person from the database administrator), the staging schema with the required privileges must be created manually. The privileges required by the staging schema can be listed by executing the config-util program in the jchem/cartridge directory with the list-sqls-for-staging-schema argument: 

    config-util list-sqls-for-staging-schema
    . Starting with JChem Cartridge version 5.5.1, the staging schema will be emptied by the upgrade program after it is no longer needed, so it is there ready for use during subsequent upgrades. With prior version the schema can be emptied by executing the script published under this forum post.

    Upgrade

    1. Navigate to the cartridge directory of the new JChem version in a command interpreter Change your current working directory to the cartridge directory of the new JChem version. (Users having multiple JChem Cartridge instances reusing the same JChem package, may want to look at this forum topic at this point.)
    2. Execute the following command on the JChem Server host: 
      bash upgrade.sh
    3. Provide the input requested by the script
      • If you answer to a prompt for a property with a question mark (?), more detailed description of the property will be displayed.
      • When prompted for the DBA login, enter exclamation mark (!), if you don't want the installer program to use DBA login. See this pre-upgrade task for steps required before installation in such cases.

    Post-upgrade tasks

    If you ran the upgrader application without DBA privileges, you have to
    1. rebuild JChem indexes on plain structure tables by executing the following SQL statement: 
      alter index >index-name< rebuild parameters('upgradeOnly=y'))
    2. upgrade JChem Base structure tables (if you use any) using JChemManager.

    Do you have a question? Would you like to learn more? Please browse among the related topics on our support forum or search the website. If you want to suggest modifications or improvements to our documentation email our support directly!