ChemAxon Forum Archive » Storage & search: JChem Base and JChem Oracle Cartridge

error creating jc_idxtype

User 8688ffe688

24-02-2005 19:25:24

Here is the trace of my sqlplus session. I'm unable to create jc_idxtype.





SQL> select jchem_core_pkg.getenvironment() from dual;





JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------


Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production


PL/SQL Release 9.2.0.4.0 - Production


CORE 9.2.0.3.0 Production


TNS for Solaris: Version 9.2.0.4.0 - Production


NLSRTL Version 9.2.0.4.0 - Production


NLSRTL Version 9.2.0.4.0 - Production


JChem version in the database: 3.0.8


JChem version in the Tomcat server: 3.0.8


java.vm.version: 1.4.2_07-b05


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/4.1.30








1 row selected.





Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production


PL/SQL Release


9.2.0.4.0 - Production


CORE 9.2.0.3.0 Production


TNS for Solaris: Version


9.2.0.4.0 - Production


NLSRTL Version 9.2.0.4.0 - Production


NLSRTL Version


9.2.0.4.0 - Production





JChem version in the database: 3.0.8


JChem version in the Tomcat server:


3.0.8


java.vm.version: 1.4.2_07-b05


java.vm.vendor: Sun Microsystems Inc.


Apache


Tomcat/4.1.30


SQL> select object_name, status from user_objects where object_type =


'SYNONYM';


OBJECT_NAME STATUS


------------------------------ -------


CD_ID_ARRAY VALID


COMPARE_FUNC VALID


CONTAINS_FUNC VALID


DISSIMILARITY_FUNC VALID


EQUALS_FUNC VALID


EVALUATE_FUNC VALID


FORMULA_FUNC VALID


FORMULA_FUNC_EQ VALID


JCF_COMPARE VALID


JCF_CONTAINS VALID


JCF_DISSIMILARITY VALID


JCF_EQUALS VALID


JCF_EVALUATE VALID


JCF_FORMULA VALID


JCF_FORMULA_EQ VALID


JCF_MATCHCOUNT VALID


JCF_MOLCONVERT VALID


JCF_MOLCONVERTB VALID


JCF_MOLWEIGHT VALID


JCF_REACT VALID


JCF_TANIMOTO VALID


JCF_TRANSFORM VALID


JCHEM_CORE_PKG VALID


JCHEM_MISC_PKG VALID


JCHEM_REFCUR_PKG VALID


JC_COMPARE VALID


JC_CONTAINS VALID


JC_DISSIMILARITY VALID


JC_EQUALS VALID


JC_EVALUATE VALID


JC_FORMULA VALID


JC_FORMULA_EQ VALID


JC_IDXTYPE VALID


JC_IDXTYPE_IM VALID


JC_INSERT VALID


JC_LOGD VALID


JC_LOGP VALID


JC_MATCHCOUNT VALID


JC_MOLCONVERT VALID


JC_MOLCONVERTB VALID


JC_MOLWEIGHT VALID


JC_PKA VALID


JC_REACT VALID


JC_TANIMOTO VALID


JC_TPSA VALID


JC_TRANSFORM VALID


LOGD_FUNC VALID


LOGP_FUNC VALID


MATCHCOUNT_FUNC VALID


MOLCONVERTB_FUNC VALID


MOLCONVERT_FUNC VALID


MOLPROPS_ARRAY VALID


MOLPROPS_ARRAY_ARRAY VALID


MOLWEIGHT_FUNC VALID


PKA_FUNC VALID


REACT_FUNC VALID


RESARRAY VALID


RIDARRAY VALID


TANIMOTO_FUNC VALID


TPSA_FUNC VALID


TRANSFORM_FUNC VALID


USER_DEF_FUNC VALID





62 rows selected.





SQL> desc acd_chemstruct;


Name Null? Type


----------------------------------------- --------


----------------------------


ACD_ID NOT NULL VARCHAR2(12)


CASNO VARCHAR2(12)


SMILES VARCHAR2(512)


CHIME VARCHAR2(4000)


MOL_NAME VARCHAR2(600)


MOL_WEIGHT NUMBER(8,1)


PRODUCT_COUNT NUMBER(8)


CIS_ID VARCHAR2(9)


CREATED_DATE DATE


MODIFIED_DATE DATE





SQL> @tmp1


create index jc_idx on acd_chemstruct(smiles) indextype is jc_idxtype


parameters('TABLESPACE=ACD_INDEX,STORAGE=INITIAL


200M,std_config=aromatize:d')


*


ERROR at line 1:


ORA-29833: indextype does not exist





Any ideas about what may be misconfigured?

ChemAxon aa7c50abf8

25-02-2005 08:15:34

In "user" schemata, the index type name must be qualified with the name of "index owner's" schema. This is a known issue (not yet listed as such in the docs).

User 8688ffe688

25-02-2005 23:19:48

I included the owner of jc_idxtype but now the index creation is frozen (running 3 hrs for 300K structures). SQL Plus no longer responds. I killed my terminal. When I select indexes from user_indexes the index status is 'INPROGRS' The tomcat server that is configured has not changed in processing or memory usage.





I tried changing to the OCI oracle driver here is the results. Tomcat started to do something, I saw the java process go to 95-99% but then error:





create index jc_idx on acd_chemstruct(smiles)


*


ERROR at line 1:


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: null


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 16








Any thoughts anybody?

ChemAxon aa7c50abf8

28-02-2005 10:13:20

Regarding the hanging index creation with the recommended thin JDBC driver, I can only guess. There is a documented known issue consisting of an Oracle call hanging indefinitely when Tomcat is brought down during the call. If this applies to your case, then you should see no database sessions started from Tomcat. (Provided that no other JChem Cartridge operations are running.) You can check using (for example) Enterprise Manager to see if there is an open database session with the attributes configured for Tomcat. You can spot this session by looking at the user name, the connection type (should be JDBC), and eventually (if Tomcat is running on another host) the client host name. If you find the session initiated in Tomcat, the problem might be in the database session itself. You can check what kind of SQL statement is currently executing on behalf of this session.





JChem Cartridge has not been test with the OCI driver. Do you have a particular reason why you tried using the OCI driver? If you send me the output in the catalina.sh file in the <tomcat-home>/logs directory, I can have a look.





Checking the catalina.sh file is also a good idea for the hanging index creation problem.

User 8688ffe688

28-02-2005 18:27:36

I've started over with install of tomcat-4.1.30. Tomcat is now with default configuration with the exception of the following:





Startup parameters for JVM 1.4.2_07:


-server


-Xms256m


-Xmx512m


-Djava.awt.headless-true


-Dlog4j.configuration=file:$CATALINA_HOME/conf/log4j.prop


-Djdbc.drivers=oracle.jdbc.driver.OracleDriver





* jchemstreams configured with thin driver and connection test successful.





Oracle client 9.2.0.4, Redhat 9.0 Linux, Dual 1.7Ghz Intel, 2GB RAM, Database Server is E420 Dual Ultra Sparc II, Solaris 9 64bit, 4GB RAM, Oracle 9.2.0.4 64bit server.





SQL Plus Error:


create index jc_idx on acd_chemstruct(smiles) indextype is jchem.jc_idxtype parameters('TABLESPACE=ACD_INDEX,STORAGE=INITIAL 200M,std_config=aromatize:d')


*


ERROR at line 1:


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: null


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0





Tomcat Error:


java.lang.NullPointerException


at chemaxon.jchem.db.UpdateHandler.setMolecule(UpdateHandler.java:783)


at chemaxon.jchem.db.UpdateHandler.setValuesForFixColumns(UpdateHandler.java:939)


at chemaxon.jchem.cartridge.servlets.JChemCartTunnel.buildIndex(JChemCartTunnel.java:93)


at chemaxon.jchem.cartridge.servlets.JChemCartTunnel.process(JChemCartTunnel.java:48)


at chemaxon.jchem.cartridge.servlets.JChemServletBase.doPost(JChemServletBase.java:221)


at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)


at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)


at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)


at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)


at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)


at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)


at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)


at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)


at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)


at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)


at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)


at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)


at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)


at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)


at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)


at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)


at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)


at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)


at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)


at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)


at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)


at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)


at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)


at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)


at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)


at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)


at java.lang.Thread.run(Thread.java:534)








Observed behavior after initiating index creation:


1. Tomcat Java process 99% cpu usage 10-15 sec


2. Oracle error


3. Tomcat error





SQL Plus responses for verification:





SQL> select jchem_core_pkg.getenvironment() from dual


*


ERROR at line 1:


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Read timed out


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 108





SQL> select jchem.jchem_core_pkg.getenvironment() from dual;





JCHEM.JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------


Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production


PL/SQL Release 9.2.0.4.0 - Production


CORE 9.2.0.3.0 Production


TNS for Solaris: Version 9.2.0.4.0 - Production


NLSRTL Version 9.2.0.4.0 - Production


NLSRTL Version 9.2.0.4.0 - Production


JChem version in the database: 3.0.8


JChem version in the Tomcat server: 3.0.8


java.vm.version: 1.4.2_07-b05


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/4.1.30





Any tips would be helpful.

ChemAxon aa7c50abf8

28-02-2005 19:15:24

You most probably have a NULL value in the 'smiles' column.

User 8688ffe688

28-02-2005 19:37:29

Anyway in the future that index creation can consider null and empty smiles strings?





BTW After throwing the above exception, tomcat jchemstreams no longer responds to subsequent calls. I must reboot tomcat to jchemstreams to respond.





Error message on subsequent calls:





create index jc_idx on acd_chemstruct(smiles) indextype is jchem.jc_idxtype parameters('TABLESPACE=ACD_INDEX,STORAGE=INITIAL 200M,std_config=aromatize:d')


*


ERROR at line 1:


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Read timed out


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 16





Daylight woes:


Be very careful if you are indexing smiles that were created with Daylight chemical cartridge. I recommend regenerating all smiles using JChem.





Major progress today. We now have the latest version of MDL's Available Chemical Directory running in parallel with Daylight and ChemAxon's Oracle cartridge. The initial test results favor ChemAxon.

ChemAxon aa7c50abf8

01-03-2005 10:26:10

Quote:
Anyway in the future that index creation can consider null and empty smiles strings?
Yes, we plan to support NULL and empty smiles strings in JChem Cartridge.
Quote:
BTW After throwing the above exception, tomcat jchemstreams no longer responds to subsequent calls. I must reboot tomcat to jchemstreams to respond.





Error message on subsequent calls:





create index jc_idx on acd_chemstruct(smiles) indextype is jchem.jc_idxtype parameters('TABLESPACE=ACD_INDEX,STORAGE=INITIAL 200M,std_config=aromatize:d')


*


ERROR at line 1:


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Read timed out


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 16
We will investigate this problem.