Standardizer

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 a user to be able to use a standard JChem table owned by another user

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.

Setting up a user to be able to own a JChem cartridge index/table

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':

1. Create an Oracle user 'IJC_IDXOWNER'.

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

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

4. Answer the questions correctly, at the prompt.

5. Perform the operations described as the DBA and JChem users.

6. 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:

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

Name of the cartridge owner [jchem]:

The host name of the JChem Server [localhost]:

The port at which the JChem Server accepts connections [1099]:

Target Oracle version [11.2]: 10.2

The target Oracle user [zazie]: ijc_idxowner

-------------------------- CUT HERE ----------------------------
-- To be executed as DBA:
call dbms_java.grant_permission('IJC_IDXOWNER', 'SYS:java.net.SocketPermission', 'localhost:1099', 'resolve,connect');

-- To be executed as jchem:
call jchem.privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner');
-------------------------- CUT HERE ----------------------------

+ set +x

Setting up a user to be able to use a cartridge index/table owned by another user

IJC can access tables from Oracle schemas other than its own. To do this add the required schemas in the Database schemas property of the schema section of the schema editor. See 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':

1. Create an Oracle user 'IJC_IDXUSER'.

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

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

4. Grant SELECT privileges on the JC_IDX_PROPERTY table in the index owner's schema to the 'IJC_IDXUSER'.

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

6. Answer the questions correctly, at the prompt.

7. Perform the operations described as the DBA and JChem users

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

9. 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:

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

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

1. IJC does not see my cartridge table

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.

2. IJC recognises my cartridge table as a standard table, not a cartridge table

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_name

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

3. IJC sees my cartridge table, but structure searches fail

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.

I changed the privileges/settings/etc, but it still doesn't work!

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.

Other problems

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 .