The Administration Guide of JChem provides background information about the administration of structure tables in relational databases using JChemManager. It contains information about table creation, importing and exporting structure files.
In order to run JChemManager the following software need to be installed:
You can find information about the hardware requirements here.
JChemManager is a tool for creating, deleting structure tables, and importing, exporting structure files into and out of these tables. The program is a two-tier Java application that can be run under operating systems supplied with Java (see for more on the System requirements).
.jchemfile located in the
.chemaxon(UNIX / Linux) or
chemaxon(Windows) directory in the user's home directory.
Prepare the usage of the
jcman script as described in . Run
JChemManager by entering
To avoid the conflict of different versions of classes,
jchem.jar should not be included in the system's class path (CLASSPATH). See
jcman.bat in the
bin subdirectory as examples. Avoid using directory names with spaces (e.g. use
PROGRA~1 instead of
Program Files in Win32) For example, such problem might occur when you view a HTML page using a browser, which contains a Marvin applet and, at the same time, the system's class path contains
Many operations of
jcman can also be invoked without graphic interface. The command line usage makes the administration easier from a remote machine
In the above cases connect to the server using
ssh and invoke
jcman. The list of options (as listed with
After starting JChemManager the "Connecting to a Database" dialog box appears. The dialog box can also be displayed by selecting the Connect icon on the tool bar of JChemManager. Tooltips, linked documents under ? icons, and warning messages in case of wrong or incomplete input can help the data entry in this dialog.
JChem uses the JDBC protocol to connect to relational databases. Before trying to connect, make sure that the appropriate JDBC driver is included in the CLASSPATH environmental variable. To establish a JDBC connection the following parameters have to be set:
A Java class name, the entry point of the name of the JDBC driver. The appropriate driver of the selected database appears in this field. In case of Other database the driver must be specified in this textbox. If you would like to use an ODBC driver, enter
sun.jdbc.odbc.JdbcOdbcDriver. See FAQ for more details on JDBC and ODBC drivers.
URL of database:
A JDBC URL provides a way of identifying a database so that the appropriate driver will recognize it and establishes a connection with it. At the first connection the URL format of the selected database system appears here. The parts highlighted by yellow color must be modified according to the local conditions. Some examples can be seen here .
In the case of an ODBC driver, the full syntax is
where each attribute has the following form
For other common URL formats please click here.
Enter the name of the property table. See JChem database concepts for the principles of JChem property table.
The default value of Property table is
JChemProperties. Since version 1.6 default value can be changed by the user to support flexible multiuser capabilities and to support the same user to work within the same databse with more than one environment defined by different Property tables.
Enter a user ID needed to connect to the database. If a login name is not needed, then leave the field empty.
Enter the password for the login name. If you want the system to save the password in the
.jchem file for later use, check Remember password .
After filling the form and selecting Connect , the system attempts to connect to a database using the settings entered.
The above settings are stored in the
.jchem file located under
".chemaxon" in the home directory of the user who connected to the database. At the first connection, a new table called
(or the name you specify) is generated in the database, which contains parameters of structure tables.
Structure tables contain chemical structures and associated data, including both data used by the JChem system internally and custom, user defined data (static/imported or calculated). For more information about JChem table structure, see JChem database concepts.
To create a structure table select the Create icon. The Create a Table dialog box appears.
Parameters to be specified:
Name of new table:
The name of the table to be created in the database that was specified at the Connecting to a Database dialog box.
Specify the number of
INTEGER columns that will contain the of the molecules. Higher number provides better screening performance for substructure searching, but too many columns may significantly increase the size of the structure cache.
Bits to be set for patterns*:
The number of bits to be set for each substructure pattern in the. 1 or 2 bits for a pattern are the best choice.
Maximum pattern length*:
The maximum length of linear substructure patterns used for.
Assume absolute stereo flag:
If checked, all query and target structures are treated as absolute stereo. This setting can be changed later without recalculating the table.
You can specify here a fix set of structures in a file that will be used as structural keys. The fingerprint will be extended with the appropriate number of integer columns to provide 1 bit for each structure.
Filter out duplicate structures:
If checked, JChem will filter out the structures which are already imported to the database with the same topology.
Warning: with a slight possibility concurrent import sessions may insert duplicated structures. See note about commit interval at Setting options.
Duplicate search uses tautomers:
If checked, tautomers are considered during duplicate search. Enabling this feature increases import time. This option is described in detail in the JChem Database Concepts.
You may specify a custom standardization XML for the structure table. You have to recalculate the table if you want to change the configuration. You can either select a standardization XML by clicking the 'Browse' button, or you can create your own with Standardizer Editor. Loading or saving a configuration in Standardizer Editor will set the target XML file being used as the custom standardization for the newly created table.
See more information at JChem Database concepts.
Compatibility notes : Tables created before JChem version 3.2 will be treated as "Any structures" to maintain previous behaviour. The default type for new tables is "Molecules".
*See theabout the optimization of these parameters. Statistics about fingerprint darkness of existing tables can also be obtained via running jcman s <table_name>. If you haven't made any previous testing, use the defaults that are optimized for typical compounds of pharmaceutical interest. The default values differ according to table type.
After pressing Ok in the Create a Table dialog box, the SQL statement for creating the structure table is displayed in the Create Table Statement dialog box.
If you would like to add more columns, modify the SQL statement (though this can also be done later in most RDBMS-s).
There is a default limit on the length of the field
cd_smiles for most RDBMS-s. If the majority of your molecules' SMILES representation is longer than this limit (in case of HUGE molecules), the search process can become slower. In this case you may try to increase the limit.
After selecting Ok, the SQL statement is executed.
For each structure table, the fingerprint properties are stored in the
JChemProperties table. If the RDBMS supports schemata, username is also attached to the table name in the
name column of the property table, like in the following example:
Import formats in JChemManager:
When the Import icon is selected, the "Import" dialog box appears.
Specify the database table and the input file. The data fields in the file can be imported into the columns of the table.
To support connecting the corresponding field names and column names, the program will detect field names in the file. If the file is too big, checking may be time consuming. Use the Check whole file for field names in selected file check box and the Number of lines to check input box to decide whether you want the whole file or just a given number of lines to be searched for field names.
If an error occurs during import, the error message and the corresponding stack trace information is written to the standard error. Check the Halt if an error occurs box if you would like the system to stop if a molecule can not be imported.
If Allow empty structures is unchecked, JChemManager will not import empty structures (structures where the atom count is zero).
If Set chiral flag for MDF formats is checked, JChemManager will set the chiral flag (absolute stereo flag) for the imported structures.
In case of SMILES*, InChI, Mol2, Molfiles, RDfiles and Marvin Documents importing starts after selecting the Ok button.
For SDfiles, the "Connecting Fields" window appears, where you can connect the corresponding field names and column names.
*Some SMILES may contain additional data separated by whitespace from the structure string. The additional data columns are separated by tabulators ("\t"). In this case, the "Connecting Fields" dialog will also appear, the fields will be named as "field_0", "field_1" etc.
Clicking on a cell in the Field in file column of the window, a list box appears with the alternative field names. In the case of the
cd_id column, Auto-incrementing can also be selected, which means that the value is increased by one after each new record. This works even if the RDBMS does not support auto-incrementing, because in this case JChemManager will take care of incrementing the value. (Some RDBMS-s that have the auto-incrementing feature for columns do not allow the explicit setting of
Importing starts after selecting the Ok button. A progress window displays the progress of the import.
Export formats in JChemManager:
When the Export icon is selected, the "Export" dialog box appears.
Specify the database table and the output file.
File format is determined from the extension:
extension starts with "sdf"
extension starts with "mol"
extension starts with "rd"
extension starts with "rxn"
extension starts with "smi"
extension is "inchi"
extension is "mol2"
extension is "mrv"
After pressing the Ok button, the next dialog appears.
On the left panel, you can specify which fields to export in the case of formats that support additional data. By default,
cd_id and the additional fields are selected (fields not beginning with "cd_"). You can add more fields by pressing the Add button. To remove one ore more fields, select them and press the Remove button. You can restore the default setting by pressing Reset. The Sort button arranges fields according to the original order in the database rows.
On the right panel you can specify a criteria for molecules to be exported in the form of an SQL WHERE statement .
Exporting starts after selecting the Ok button. A progress window displays the progress of the export.
When the Delete icon is selected, the "Delete" dialog box appears.
In this dialog you can
Custom (non-cd) columns, Chemical Terms and Molecular Descriptors can be added to and deleted from a chosen structure table.
The structure table can be specified by the drop-down list found on the top of the pane. In the center there is a table which contains the custom field name and the type currently defined in the chosen structure table. To delete a custom field, select the row and click on the 'Delete selected columns' button. Molecular Descriptors and their configurations can also be seen on a separate pane. A custom column can be created by clicking on 'Add column' button.
After pressing the "Add column" button, the next dialog appears. In this pane, the column name, column data type and its parameters can be defined with an optional chemical terms expression. Index can also be created for the column. When finished, click on the 'Ok' button to add the database field and after that you can add other fields by following the same process. Finally, click on the 'Done' button in the Modify dialog.
If any new columns containing chemical terms expression were added, a message dialog will appear to ask whether you want to generate the values of the newly created fields. If the 'No' button is selected, the columns will remain empty. If the 'Yes' button is selected, a progress bar will be displayed showing the status of the generation until the process is done. Chemical Terms values can be calculated or recalculated later as described here.
To add a Molecular descriptor, select the root element of the 'Molecular Descriptors' tree and click on the 'Add' button. Then the following dialog will pop up.
Here you must type the descriptor name, which can be anything. Then you should select the descriptor type and the descriptor XML file, which contains information about the custom fingerprint generation rules. You can define some additional configuration by clicking on the descriptor's name in the Molecular Descriptor tree. Give a name to the configuration and specify the XML file. By clicking on the 'Remove' button you can delete the currently selected descriptor or configuration in the Molecular Descriptor tree.
Updating and inserting rows in structure tables is not yet supported in JChemManager. However, custom applications built using the can perform these operations. See the included with the package.
The table options dialog can be reached from the File -> Table Options dialog of JChemManager.
Change the table name in the combo box on the top to view / edit settings for other tables.
Changing some settings (e.g. standardization, tautomer duplicate search) requires the recalculation of the table. This will be performed after pressing the "Ok" button. The recalculation can take considerable amount of time depending on the size of the structure tables and other factors.
NOTE: you can make changes for multiple tables, your changes will be stored when selecting other tables. The actual changes in the database and the recalculation (if needed) will take place for all tables after pressing the "OK" button.
If "Assume absolute stereo flag" is set for a table, all query and target structures are treated as absolute stereo ("chiral flag" in MDL files).
Changing this setting does not require recalculation.
You can specify whether duplicate structures are filtered out during the import process or not.
Changing this setting does not require recalculation.
You can also specify if tautomers are considered during duplicate search. Enabling this feature increases import time.
Changing this setting requires recalculation.
You can also change the standardization of the tables.
Changing this setting requires recalculation.
When the Options icon is selected, the "Options" dialog box appears. The options set here are stored in the
NOTE: If you are using multiple property tables these options should be set for each property table individually.
You can set advanced options in the second tab.
cd_structurefield in the database. This has the following benefits:
Sometimes forced compression can be harmful, for example, when third-party software which cannot interpret compressed ChemAxon format need access to the structures. The compression is disabled by default, so all new structures are inserted without compression.
After the installation of every major version of JChem and in certain cases after the installation of minor versions of JChem, you have to upgrade your database in order to make the database consistent with the new software version. Modification of database structure and of calculated data are executed during the upgrade process. There are two possible ways to upgrade database.
When a new version of JChem is released usually the calculated data in the structure tables have to be refreshed to be consistent with the new version. Normally this is offered by JChem Manager when connecting, see above, but in some cases one may want to initiate recalculation by hand.
To recalculate fix columns, select the File -> Recalculate menu option, the "Recalculate" dialog will appear:
Three recalculation options exist. It is possible to recalculate either the standard JChem table fields, or the Chemical Terms fields of the JChem table, or the Molecular Descriptors which are stored in different tables, or even all of them. The recalculation options can be chosen with checkboxes, which are disabled if a given option is not possible for the selected table(s), e.g if there are no Chemical Terms defined. You can select one table, or recalculate all tables.
You can also recalculate the tables from command-line.
When upgrading to a new JChem version, either in JChem Manager or in command line mode, the user will be asked whether to recalculate some of the existing structure tables or not. In general the following rules are applied to decide whether to require recalculation or not when a new JChem release is coming.
Chemical Terms columns can be recalculated manually using the API, or the jcman in command line mode. In the API recalculateCTColumns method can be used, which is part of the UpdateHandler. The following line should be executed in command line to recalculate all Chemical Terms columns for a structure table.
For table version number one rule is applied: they must follow each other in an ascendant order which makes the recalculation check simpler. The version of one particular table can be viewed using the
command for all structure tables, or
for one particular table. Number 5020105 could be an example for table version. The first five numbers here define the JChem version 5.2.1 while the rest of this sequence is reserved for the possibility of further identification. The current JChem and table version can be retrieved executing the
from 5.3. In older versions, type
command in the lib directory, where jchem.jar can be found.
Recalculation process can be long sometimes, and since it is running on the live system it is not recommended to modify the molecules or the table structure until the recalculation has not been finished. Precalculation is implemented to make this pending time as short as possible. Precalculation process runs in the background, and precalculated data are stored in temporary tables. When finished, you can copy these data to the structure tables with one step. Meanwhile some molecules or the table structure may still change of course. In first case modified molecules should be recalculated after precalculated data have been applied (copied) but it will be automatic and will take much less time than recalculating the entire table. Structure change of the precalculated JChem table will invalidate the former precalculation so it has to be restarted again.
Note: When updating from a JChem version (older than 5.3) which doesn't implement precalculation, you should only use precalculation, if you are able to ensure that the precalculated table won't change between the precalculation process and the data copying.
You can precalculate those tables which need recalculation according to the current version of JChem. You can get information about the need of precalculation, the status and the valdity of a previously started precalculation process by using
If precalculation is recommended for a table but there is not precalculated data available with the corresponding version of JChem, or the data are not valid or there are too many changed (invalid) rows since the last precalculation, we suggest you to run the precalculation process on that table.
You will need precalculation when you are upgrading your JChem from a previous version to a newer one. If you want to run precalculation you will need to install the new version because you have to use the precalculation process of this. During and after the execution of this process the older version can be used so the work does not need to be stopped on JChem databases. To run precalculation you should type
command for all structure tables, or
for one particular table.
Note: Precalculation is only possible on tables which need recalculation according to the current version of JChem. Precalculation is not possible on tables which need upgrade (e.g., because of changes in database structure).
It is also possible that for some reason you would like to remove a temporary table created by the precalculation process. This operation can be perfored using the
command where tableName is the structure table which the temporary table belongs to.
When you are starting JChem from GUI or using the command line jcman u option, JChem will automatically detect if there are complete and valid precalculated data available. In this case it will ask whether to apply these to structure tables or not. This will happen before JChem requires table recalculation and you can skip this step by answering No.
Otherwise precalculated data will be copied from temporary table to JChem table. It is possible that some molecules have been changed since the last precalculation. In this case a recalculation process is started for only these structures after precalculated data have been successfully copied. However, usually recalculation time of these rows must be much shorter than recalculation of the whole table. Of course, structure tables which already have the appropriate data gained by the way of precalculation won't appear later in the list of tables that need to be recalculated according to JChem.
Precalculation is supported on the following DBMSs now:
MSSQL Server 2005+