Domain index and table regeneration problems

Hi,

as a possible solution for the problem described here I updated JChem Cartridge and tried to regenerate a couple of tables. The tables were created either by JCMan with following manual creation of jc_idxtype index on cd_structure field or by IJC.

The tables created with 'Allow duplicate structures' set on ('Duplicate filtering' set off in IJC) where regenerated without any problems, on contrary, the tables created with duplicate filtering failed with an error during regeneration.

java.sql.SQLException: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8 (T4CPreparedStatement.java:216)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2227)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2130)
        at chemaxon.jchem.db.Regenerator$RegenThread.run(Regenerator.java:82)

Does it mean that the domain indices on the tables with unique structures were not meant to be regenerated? I have noticed that there is no way to 'alter rebuid' the domain indices, but it comes to all of them, including non-unique ones.

Thank you in advance,

Valery

 

Hi Valery,

This means that the index got invalid for some reason.

ALTER INDEX REBUILD is supported for indexes on regular structure tables: http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#alter_index_rebuild . A jc_idxtype index on a JChem structure table is not a true index in the sense that it doesn't involve creating/storing any index data: the index data is part of the table is already there with or without a jc_idxtype index.

In order to repair the index on a JChem structure table, you can simply drop and recreate the index. Both will be an almost instantaneous operation, the only activity involved being Oracle doing some bookkeeping in the data dictionary.

Regards,

Peter

Hi Peter,

Dropping and recreating the index was the first thing I've done. Besides, I tried to create new table, using IJC as well as JCMan, reimport SD file with duplicates filtering and creating the index (on tables created with JCMan). The regeneration failed on fresh tables as well. As I meantioned before, it happenes only if 'Duplicates filtering' table option is on. It's quite odd, unless there is something special about duplicates filtering option.

Another thing, maybe not related and should be posted on IJC forum, I couldn't find a way to create the domain index inside IJC on the cd_structure column on the tables created in JCMan and inported into IJC project scheme.

Thank you,

Valery

Hi Valery,

I am perplexed. I can only see a "Duplicate search uses tautomers" check box in the "Table Options" dialog box using JChem Manager. Where can you set the "Duplicates filtering" option? (I am testing with JChem 5.2.2.)

Thanks

Peter

Hi Peter,

I pointed out both option in my original message, due to different naming in your products.

When I use JCMan to create the tables and inport data, the option I mean is 'Allow duplicate structures', which is unchecked on the tables that fail to regenerate. On the other hand, when I use IJC for the same purpose, the option is called 'Duplicate filtering', which is checked on the tables that fail to regenerate.

Thank you,

Valery

Sorry, Valery! Your first description of the problem was, indeed, absolutely correct in this respect. Apparently, I am much more overwhelmed by the plethora of products, productchens and options here than you.

So here I have a fresh JChem 5.2.2 installation.

1. I am creating a table with the JChem Manager GUI with the default options.


2. I am importing 1000 smiles into the table with the "Allow duplicate structures" option turned off.


3. I am creating a jc_idxtype index on the table


4. I am regenerating the table with JChem Manager GUI.


5. I am searching using the jc_compare operator


6. I am importing again the same structures (all are rejected now as duplicates)


7. I am regenerating again.

Everything works as expected. Is there here anything you did differently?

Thanks

Peter

Hi Peter,

Although you didn't specify it in your last post and I can only guess, but the only thing differs in my case is that the data I import containes duplicate structures.

1. I'm creating the table.
2. I'm importing SD file with duplicate structures in it, "Allow duplicate structures" option turned off, and JCMan filters out the duplicates.
3. I'm creating a jc_idxtype index on cd_structure field.
4. I'm trying to regenerate the table in JCMan and it fails with the exception.

Thanks,

Valery

Valery,

Have you tried to regenerate the JChem table without a jc_idxtype index? (After dropping the index, but before recreating it again?) Are you getting the same error during regeneration?

Thanks

Peter

Valery,

Could you please write

1. the exact version or our Oracle installation (down to the last digit)

2. the exact version of the JDBC driver (generally the latest is recommended, have you tried with that ?). Please note that later versions of JChem come with JDBC drivers already in the CLASSPATH (inside the lib directory), please make sure you only use wither the embedded or the one ins the system CLASSPATH, but not both

3. the output of the "java -version" command

4. could you confirm you are using JChem 5.2.2 ? ("jcman --help" , or  "Help -> About" in the GUI)

 

I ask for these because this may help us to reproduce / track down this problem sooner.

 

Best regards,

 

Szilard

 

Hi Piter, Szilard

I checked ojdbc version of all instances I have in the system and, indeed, I had different version for Oracle and JChem. I updated every instance of it to 'Oracle 11.1.0.6.0-Production+ JDBC 3.0 compiled with JDK5' to match my Oracle version and now, after dropping and recreating the indices, regeneration works fine.

Thank you very much indeed,
Val