[JChem 3.2.3] Configuring JChem users

Hi,





I installed the JChem cartridge in a JCHEM schema, and now I am trying to configure users (several users will have to use the cartridge).





First I created a role ACCESS_JCHEM_CARTRIDGE and modified the SAMPLE procedure in the jcart_user_hlp.sql script. But when I called the sample procedure, the role seemed not to be recognized :

Code:

call jcartuserhlp.sample()      * ERREUR à la ligne 1 : ORA-01917: l'utilisateur ou le rôle '' n'existe pas ORA-06512: à "SYS.JCARTUSERHLP", ligne 6 ORA-06512: à "SYS.JCARTUSERHLP", ligne 76 ORA-06512: à "SYS.JCARTUSERHLP", ligne 20 ORA-06512: à "SYS.JCARTUSERHLP", ligne 184

So I tried to give one of my users' name, TESTJCHEM, and it seemed to work.


Any idea why the role was rejected ?





Then I tried to create an index of jc_idxtype in TESTJCHEM schema. I got this error, whereas the index had been created :

Code:

ORA-29855: erreur d'exécution de la routine ODCIINDEXCREATE ORA-29532: appel Java arrêté par une exception Java non interceptée : java.lang.Exception: The following exception has been thrown by the servlet: Exception: ORA-01031: insufficient privileges ORA-06512: à "JCHEM.JCHEM_CORE_PKG", ligne 47 ORA-06512: à "JCHEM.JC_IDXTYPE_IM", ligne 17

and when trying to run a query using the index, we get the following message :

Code:

ORA-20101: Please, create domain index on the column referenced in the operator jc_molconvertb of the table with DATA_OBJECT_ID=12184. You can find out the name of the table by executing: SELECT owner, object_name INTO schema_name, table_name FROM sys.dba_objects WHERE DATA_OBJECT_ID = 12184 ORA-06512: à "JCHEM.JCHEM_CORE_PKG", ligne 49 ORA-06512: à "JCHEM.MOLCONVERTB_FUNC", ligne 7

Can you help us to find what we made wrong ?

Quote:

Any idea why the role was rejected ?

I could not reproduce this one. Which JChem version are you using? Earlier versions of the script did not like too long user/role names.

Quote:

ORA-01031: insufficient privileges

This is either related to your first problem (setting privileges) or you did not call jchem_core_pkg.use_password at the beginning of the database session. Do have a jdbc user configured in Tomcat?

Quote:

when trying to run a query using the index

This problem is close related to the previous one (insufficient privileges somewhere along the way).





Thanks


Peter

Hi Peter,





Thank you for your reply. I am not sure my problem is solved. I used the jchem_core_pkg.use_password procedure but what password must I give, the JCHEM one or the one of my user schema ? (TESTJCHEM).


I seem not to get insufficient privileges errors anymore, but we still have errors when creating or using indexes, so it is difficult to find the origin of the problem.





Anyway, meanwhile I tried to reinstall the JCHEM cartridge in the same schema that will have to use it (so that developers can begin tests).


But when I tried to create an index, I got this new error :

Code:

SQL> CREATE INDEX jc_compound_idx ON COMPOUND(molstructure) INDEXTYPE IS hitdbjchem.jc_idxtype; CREATE INDEX jc_compound_idx ON COMPOUND(molstructure) INDEXTYPE IS hitdbjchem.jc_idxtype * ERREUR à la ligne 1 : ORA-29855: erreur d'exécution de la routine ODCIINDEXCREATE ORA-29532: appel Java arrêté par une exception Java non interceptée : java.lang.Exception: The following exception has been thrown by the servlet: Exception: An error occurred while processing the structure (ROWID=AAAOLLAAGAAAh0YAAI) ' -ISIS-  01100509522D 29 32  0  0  0  0  0  0  0  0999 V2000 -2.1625   -1.3000    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0 ............

Please help me ! Thanks.





Estelle

Quote:

I used the jchem_core_pkg.use_password procedure but what password must I give, the JCHEM one or the one of my user schema ? (TESTJCHEM).

You have to specify the password of the user who opened the database session. The password is required so that it can be sent along with the user id to the JChem Server (Tomcat) to make it possible to open a database connection from there with the same identity and privileges as the initial database session.

Quote:

An error occurred while processing the structure (ROWID=AAAOLLAAGAAAh0YAAI)

This typically indicates that a problem occurred during the import of the molecule. Please, could you post the corresponding Java stack trace from the <tomcat-home>/logs directory. (Do a 'grep ROWID=AAAOLLAAGAAAh0YAAI *' in this directory to find out in which file the stack trace has been output.) We may eventually also need to analyze the molecule which caused this problem. Please, check with your researches how confidential this compound is. (You can identify it based on the ROWID.)





Thanks


Peter

Hi Peter,





So, this is the Java stack trace we got :

Code:

2007-02-01 10:55:15.727 [null]  [http-10.0.0.36-8367-Processor25]: JDBC driver version: 10.2.0.2.0 chemaxon.jchem.db.UpdateHandlerException: Inserting a query or Markush structure is not allowed for table: "HITDBJCHEM.JC_COMPOUND_IDX_JCX"         at chemaxon.jchem.db.UpdateHandler.readMolecule(UpdateHandler.java:1013)         at chemaxon.jchem.db.UpdateHandler.init(UpdateHandler.java:866)         at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1888)         at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1866)         at chemaxon.jchem.cartridge.servlets.indexing.IndexCreationHandler.execute(IndexCreationHandler.java:70)         at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run0(IndexingThread.java:72)         at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run(IndexingThread.java:40) java.sql.SQLException: Inserting a query or Markush structure is not allowed for table: "HITDBJCHEM.JC_COMPOUND_IDX_JCX"         at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1891)         at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1866)         at chemaxon.jchem.cartridge.servlets.indexing.IndexCreationHandler.execute(IndexCreationHandler.java:70)         at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run0(IndexingThread.java:72)         at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run(IndexingThread.java:40) java.lang.Exception: An error occurred while processing the structure (ROWID=AAAOLLAAGAAAh0YAAI) '

Hi Estelle,





Please, read the following thread: http://www.chemaxon.com/forum/ftopic2514.html .





Please, also checkout the new index parameter tableType (http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#index)





Basically, you have to set tableType to anyStructures.





Peter

OK, I understand.





Just a little problem, I get this message while trying to drop the bad-created index :

Code:

SQL> DROP INDEX jc_compound_idx ; DROP INDEX jc_compound_idx * ERREUR à la ligne 1 : ORA-29856: erreur d'exécution de la routine ODCIINDEXDROP ORA-29532: appel Java arrêté par une exception Java non interceptée : oracle.jdbc.driver.OracleSQLException: ORA-00054: ressource occupée et acquisition avec NOWAIT (pas d'attente) indiquée ORA-06512: à "HITDBJCHEM.JCHEM_CORE_PKG", ligne 64 ORA-06512: à "HITDBJCHEM.JC_IDXTYPE_IM", ligne 43

What can I do ?

Try this one:

Code:

DROP INDEX jc_compound_idx FORCE

Thank you, the dropping worked better ! I thought it was a more critical problem.





So, while re-creating the index with the parameter you suggested, I got this new error :

Code:

SQL> CREATE INDEX jc_compound_idx ON COMPOUND(molstructure) INDEXTYPE IS hitdbjchem.jc_idxtype PARAMETERS('tableType=anyStructures');  2 CREATE INDEX jc_compound_idx ON COMPOUND(molstructure) INDEXTYPE IS hitdbjchem.jc_idxtype * ERREUR à la ligne 1 : ORA-29855: erreur d'exécution de la routine ODCIINDEXCREATE ORA-29532: appel Java arrêté par une exception Java non interceptée : java.io.IOException: Server returned HTTP response code: 502 for URL: http://10.0.0.36:80/jchem/tunnel ORA-06512: à "HITDBJCHEM.JCHEM_CORE_PKG", ligne 47 ORA-06512: à "HITDBJCHEM.JC_IDXTYPE_IM", ligne 17

I am going on reading the whole doc on creating indexes.

Quote:

HTTP response code: 502

This won't be a trivial one.





http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html says:

Quote:

10.5.3 502 Bad Gateway The server, while acting as a gateway or proxy, received an invalid response from the upstream server it accessed in attempting to fulfill the request.

Which Java version are you using to run Tomcat?





Thanks


Peter

pkovacs wrote:

Which Java version are you using to run Tomcat?

It is version 1.5, can it be a compatibility issue ?

No it was just a blind shot. I found by googling a hint that Tomcat with JDKs prior to 1.4.2_07 might have similar issues. But apparently this doesn't apply to your case.

Are you sure Tomcat is running on port 80 (at 10.0.0.36). This is an unusual port number for Tomcat. Is it integrated with the Apache http server? When used with JChem Cartridge, Oracle should be communicating directly with Tomcat without the intermediary of Apache (or other http server).





Peter

Peter,





So we have reinstalled Tomcat and the cartridge using another port number, and we finally succeeding in creating the index !!!





We are now testing queries using it...





Thank you for your precious help.





Estelle

Edited by error

Hi Estelle,





Have you upgraded from an earlier JChem Cartridge installation or is this a pristine installation?





Thanks,


Peter

Hi Peter,





In fact, I uninstalled a previous version (jchem3.0.5) but some objects were not well removed. So I finally dropped the entire owner schema (JCHEM) and restarted a new installation of jchem3.2.3 in a clean one.





Estelle

Hi Peter,





We tried to reinstall from the beginning the JChem Cartridge in its own schema with users in different schemas using it. So I created a new database on another server for test (because on the previous one, we had made lots of installations/desinstallations...).


But I still get the same errors when configuring my user schema and creating the index.





I did following steps :


- run the jcart_user_hlp.sql script in SYS


- run the test.sh script -> OK


- run the JCartUserHlp.config_user() -> OK


- called the Jchem_Core_Pkg.use_password('<TESTJCHEM_password'>), I got this error :

Code:

SQL> CALL jchem.Jchem_Core_Pkg.use_password('***'); CALL jchem.Jchem_Core_Pkg.use_password('***') * ERREUR à la ligne 1 : ORA-29532: appel Java arrêté par une exception Java non interceptée : java.lang.ExceptionInInitializerError

- run the JCartUserHlp.grants_on_jcidx()


I got this error :

Code:

ORA-01403: aucune donnée trouvée ORA-06512: à "SYS.JCARTUSERHLP", ligne 93 ORA-06512: à ligne 16

And of course when I try to create the index, I get an "insufficient privilege" error, because of the previous errors I guess.





Please can you help me configure this user ? Thank you !

Hi Estelle,





What does the following statement give, when executed as TESTJCHEM:

Code:

SELECT jchem.jchem_sessionid_sq.nextval FROM dual

?





Thanks


Peter

Hi Peter,





Thanks for the quick response.





I got this :

Code:

SQL> SELECT jchem.jchem_sessionid_sq.NEXTVAL FROM dual ;    NEXTVAL ----------          7

Hmm...I thought TESTJCHEM cannot see this sequence in JCHEM's schema, but obviously this is not the problem.





Please, could you post the Java stack trace from the Oracle session trace (in the udump directory) containing the string "java.lang.ExceptionInInitializerError"?





Thanks


Peter

Hi Peter,





This is the Java stack trace from the Oracle session trace :

Code:

oracle.jdbc.driver.OracleSQLException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA" : identificateur non valide         at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java:153)         at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java:92)         at oracle.jdbc.driver.T2SPreparedStatement.describe_internal(T2SPreparedStatement.java:163)         at oracle.jdbc.driver.T2SPreparedStatement.executeForDescribe(T2SPreparedStatement.java:395)         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1136)         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1258)         at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1439)         at chemaxon.jchem.cartridge.dbsession.JCSession.<init>(JCSession.java:80)         at chemaxon.jchem.cartridge.JFunctions.<clinit>(JFunctions.java:150) java.lang.ExceptionInInitializerError Caused by: java.lang.RuntimeException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA" : identificateur non valide         at chemaxon.jchem.cartridge.dbsession.JCSession.<init>(JCSession.java:93)         at chemaxon.jchem.cartridge.JFunctions.<clinit>(JFunctions.java:150)

Estelle,





Please, could you execute the following SQL statement as TESTJCHEM:

Code:

select jchem_core_pkg.get_cartowner_schema from dual;

What does it return?





Thanks


Peter

I got this :

Code:

SQL> select jchem_core_pkg.get_cartowner_schema from dual; GET_CARTOWNER_SCHEMA -------------------------------------------------------------------------------- JCHEM

Hm...there appears to be some inconsistency here... Based on the Oracle session trace, the problem is that the caller cannot see (or cannot execute?) the

Code:

jchem_core_pkg.get_cartowner_schema

function. But TESTJCHEM is able to execute it.





Are you really calling

Code:

jchem.Jchem_Core_Pkg.use_password('***');

as TESTJCHEM? If so, are you sure you are connected to the right database instance?





Thanks


Peter

Yes, I'm doing this :

Code:

SQL> connect testjchem/password_testjchem Connecté. SQL> call jchem.Jchem_Core_Pkg.use_password('password_jchem'); Appel terminé.

Earlier (Mon Mar 12, 2007 4:44 pm), the

Code:

jchem.Jchem_Core_Pkg.use_password('***')

appeared to fail with the ExceptionInInitializerError error message. Maybe you get the error only in a freshly started database session. Please, could you retry it in a new database session? Is this call still working?





Note that you should be able to call jchem_core_pkg without the schema prefix: in addition to the required privileges, the jcart_user_hlp.sql is supposed to create the necessary synonyms in the TESTJCHEM schema as well -- if TESTJCHEM is a user (see the syns_for_jcobjs member procedure). If TESTJCHEM is a role, no synonyms will be created, I believe.





Thanks


Peter

OK, all of this is beginning to be really unclear for me !


So let's sum up what I did since I reinstalled all JChem cartridge on my new base :





- creation of a new database, with JVM


- installation of jchem 3.2.3 on /home-system/oracle


- installation of the Tomcat server


- installation of the JChem cartridge in a new schema JCHEM (with calling of the dbms_java.grant_permisson to JCHEM)


- setting of the connexion informations on Tomcat with JCHEM login, and test -> OK


- test of the cartridge installation on JCHEM -> OK


- connection to JCHEM from JChem Manager, and creation of the JChemProperties table, then setting of the JChem Licence Key and Cartridge Licence Key in the options


- creation of a TESTJCHEM schema, that will be the user of the JChem cartridge


- creation of the object type JCartUserHlp on SYS by running script jcart_user_hlp.sql


- creation of a table LOGTBL with STMT column on JCHEM


- creation of a JCartUserHlp object on SYS, with TESTJCHEM informations, and calling of the procedures config_user() and grants_on_jcidx() -> not OK


- creation of a JCartUserHlp object on SYS, with TESTJCHEM informations, and calling of the procedure config_user() only -> OK


- test of the cartridge access with TESTJCHEM -> OK


- creation of a JCartUserHlp object on SYS, with TESTJCHEM informations, and calling of the procedure grants_on_jcidx() -> not OK


- then I tried to call the Jchem_Core_Pkg.use_password('password_jchem') from SYS -> not declared. Then prefixed by jchem -> java.lang.ExceptionInInitializerError





I hope all these steps will help you finding out where I made mistake.


Thanks for your help !





Estelle

Thank you for the detailed information. It is very helpful indeed.





The next to last step ("creation of a JCartUserHlp object on SYS, with TESTJCHEM informations, and calling of the procedure grants_on_jcidx()") makes sense only, if you apply it to an already existing index of type jc_idxtype. By the way, this step is required only, if the index has been created in a schema different from that of the user (not in TESTJCHEM's schema).





I think there is a misunderstanding regarding the purpose of the Jchem_Core_Pkg.use_password(...) procedure. Its purpose is to enable the JChem Server (running in Tomcat) to open a database connection of the jcart user, which TESTJCHEM in your test case. You should call the jchem_core_pkg.use_password(...) procedure from within a database session opened by TESTJCHEM with the password of TESTJCHEM as parameter. The procedure will send the username of the current user (TESTJCHEM) and the password specified (should be the password of TESTJCHEM) to the JChem Server, which will store the username-password pair transiently and use it whenever a (secondary) connection should be opened in Tomcat on behalf of TESTJCHEM. The primary connection is the one opened "directly" by TESTJCHEM. JChem Cartridge operations are started in the primary session and will be sent for processing to JChem Server, where a secondary session will be opened on behalf of TESTJCHEM for technical reasons (partly for performance, partly for code reuse reasons) in support of the JChem Cartridge operation started by TESTJCHEM (in its primary database session).





I hope this helps.





Peter

Thanks !





You're right, the using of use_password() procedure was not clear for me.


So, if I understood, it should be used only when we will have to access the JChem server, connecting on TESTJCHEM schema, from our java application for example ?


I don't need to call it while trying to create the index and attribute grants to TESTJCHEM ?





So, regarding the jc_idxtype index I have to create on the COMPOUND table in TESTJCHEM, should I better create it in the TESTJCHEM schema (user of the cartridge), or in the JCHEM schema (owner) ? Should creating it in TESTJCHEM later cause problems while trying to export the TESTJCHEM schema for example ? (because of grants issues)





Then actually, I tried to create my jc_idxtype in TESTJCHEM yet. But the creation never ends, and I have to kill the session...

Quote:

You're right, the using of use_password() procedure was not clear for me. So, if I understood, it should be used only when we will have to access the JChem server, connecting on TESTJCHEM schema, from our java application for example ? I don't need to call it while trying to create the index and attribute grants to TESTJCHEM ?

You have to call it once for each database session, at the beginning of each database session: call it immediately after logging in with sqlplus for example. It is required for indexing, for searching, for almost everything -- even if you're working with sqlplus.

Quote:

So, regarding the jc_idxtype index I have to create on the COMPOUND table in TESTJCHEM, should I better create it in the TESTJCHEM schema (user of the cartridge), or in the JCHEM schema (owner) ? Should creating it in TESTJCHEM later cause problems while trying to export the TESTJCHEM schema for example ? (because of grants issues)

For simplicity, I suggest to create the index in the same schema where the table being indexed has been created: in the TESTJCHEM schema.

Quote:

Then actually, I tried to create my jc_idxtype in TESTJCHEM yet. But the creation never ends, and I have to kill the session...

You mean creating jc_idxtype index. How many compounds are stored in the table?





Thanks


Peter

OK, so :


- I connected to TOAD as TESTJCHEM


- called jchem.Jchem_Core_Pkg.use_password('password_testjchem');


- ran the following :

Code:

CREATE INDEX jc_compound_idx ON COMPOUND(molstructure) INDEXTYPE IS jchem.jc_idxtype PARAMETERS('tableType=anyStructures');

that keeps hanging...





We have around 100 000 compounds.

Depending on the kind of structures and the horse power of your machine, the indexing takes about 10 minutes. Indexing the NCI SMILES data set (250k) takes about 5 minutes on an otherwise not loaded 3GHz dual Xeon (Netburst) machine.





Is perhaps the table locked by another user (or by another session in your TOAD like in http://www.chemaxon.com/forum/viewpost9670.html#9670)?





Thanks


Peter

Hi Peter,





I'm affraid it is not the problem. I waited until 15 minutes yet, without success. I checked that there was not any lock.


And when we had installed the cartridge in the same schema as the table COMPOUND, the index creation had taken a few seconds only.

Hi Estelle,





Please, could you turn on SQL Trace immediately before starting the CREATE INDEX and post or send me (to pkovacs at chemaxon dot com) the trace file.





Thanks


Peter

Peter, I let the creation run longer this time, and the index was finally created !! I think it took more than 20 minutes. Do you think the creation is longer due to the access to the cartridge from a user schema ?





So, I try to do the following steps, and let you know if it works !


Thanks for your help !





Estelle

Estelle,





Normally, it should make no difference which schema holds the structure table.





One factor that might make a big difference in index creation time is whether the structure table is a JChem table (created with jcman for example) or a regular structure table (created with CREATE TABLE...). Creating structure index on JChem tables is instantaneous because the index information is computed as you insert structures into the table (the info is already there at the moment you start creating the index) whereas the index information is computed during index creation in the case of regular structure tables.





The few seconds indexing time you mentioned is typical for JChem-tables -- it would be too fast for a regular structure table containing 100k structures.





Still, 20 minutes appears to be too long -- but whether it is actually slow or not depends on the horse power of your machine, on how busy your machine is processing other tasks simultaneously and what kind of structures are being indexed. If, based on these factors, you also feel that it is too slow, I suggest addressing this performance problem separately (perhaps in a separate topic?).





Thanks


Peter

Hi Peter,





Thanks for your precious help, it really helped !


I tried to drop and recreate the index several times, in fact the creation seems to be closer to around 10 minutes in average.





I'm actually trying to recreate a new user schema and reapply all the steps.





Meanwhile, I'm doing export tests, but I'm going to open a new thread for this.





Estelle