jchem cartridge update question

Hi,

I have added a number of new structures as smiles to a standard table which has the cartridge index on smiles column. After the SQL insertion, I thought the cartridge index should be updated automatically, therefore I should be able to do substructure search to find the structures I just inserted. I tried the sss in IJC and SQL using jc_compare, however I was not able to find the new structures. 

Did I miss something?

Thanks

Dong

Hi Dong,

You have to commit your changes, if you want them to be included in the search. Except for duplicate search with the requireCommit:n (http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_compare_requireCommit) option.

Peter

Hi Peter, Do you mind to do a quick webex/live meeting

Thanks

Dong

webex about what?

Hi Peter,

I do not quite understand what do you mean commit the change? I did sql insertion to add the smiles to the table and commit the insertion in Oracle. I did not specifically update the index.

Thanks

Dong 

Yes, that was what I meant. Commit the insert. You shouldn't have to do anything else.

You inserted and committed the structures using SQL*Plus or Toad? Can you retrieve them using the same tool? Can you retrieve them from the table simply by their ID without substructure search?

Peter

I did insertion in SQLPlus. Commit the change there as well.

I can retrieve the new smiles from the table.

I can see the the new smiles as structure in IJC through the grid view

And you can't retrieve the structure with jc_compare either using SQL*Plus nor IJC, correct?

Peter

right, none of it works.

struture index is OK since I can still do search return hits. I also checked the cartridge log did not see any error in indexing

Does jc_compare find the structure when you specify it as a literal target:

select jc_compare('Br', 'C') from dual;

? (Replace Br with your structure.) Getting 1 means "found", 0 means "not found".

Thanks

Peter

supposedly 'C1=NC=C2C=NC=NN12' is the new structure I added, following

SELECT

 

 

* FROM chemdata_single WHERE jc_compare(smiles, 'C1=NC=C2C=NC=NN12','t:s') = 1;

did not find the structure

The complete sequence is then:

insert into chemdata_single (..., smiles) values(..., 'C1=NC=C2C=NC=NN12');

commit;

select * from chemdata_single where jc_compare(smiles,  'C1=NC=C2C=NC=NN12', 't:s') = 1;

Correct?

And the same applies to a very minimalistic structure such a single carbon atom:

insert into chemdata_single (..., smiles) values(..., 'C');

commit;

select * from chemdata_single where jc_compare(smiles,  'C', 't:s') = 1;

correct?

Please, could you post the output of the following command:

select jchem_core_pkg.getenvironment() from dual;

?

Thanks,

Peter

right the insertion statement just like what you wrote

 

Here is the result with

select jchem_core_pkg.getenvironment() from dual;

 

Oracle environment:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

PL/SQL Release 11.1.0.7.0 - Production

CORE 11.1.0.7.0 Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

JChem owner: JCHEM

JChem Server environment:

Java VM vendor: Sun Microsystems Inc.

Java version: 1.6.0_32

Java VM version: 20.7-b02

JChem version: 5.9.3

JChem Index version: 5080100

JDBC driver version: 11.1.0.7.0-Production

Dong,

Are you able to retrieve structures with jc_compare which have been already in the table for some time (since as long as, say, last week)?

Another test could be to open a new database connection and see if you can find the structures (newly inserted in the other session) there using a simple SQL query (e.g. searching by id).

Another informative test could be to restart the JChem Cartridge server and see if you can retrieve the maverick structures with jc_compare after restart.

The only thing I can think of causing a similar behaviour is that the JChem Cartridge server may have been configured to connect back to a different but similarly structured database.

Peter

Hi Peter,

Afer restart jchem server I was able to retrive the newly added structures using both jc_compare and sss in IJC. I restarted jchem server on 9/25 after a server shutdown. A couple of days ago I updated IJC to 5.11, when I was in IJC as admin it prompt me to update the schema meta information, I did that. I have attched jchemproperty as csv file and jchem server configuration files. This is a single host server, both Oracle and JChem on the same box. Hope this information would help debug

Thanks

Dong

Hi Dong,

Many thanks for this information. We will work with the IJC team to figure out what the problem may be.

Thanks

Peter

Did inserts work before the IJC (schema) upgrade? (Did searches retrieve newly inserted structures before the upgrade?)

Thanks

Peter

after the initial built in July added two small batches of structures, I did not test on the first batch, but tested this time, then I found the problem ...

Insert may have never worked, correct?

Peter

base on recent insertion, I would say structures got inserted ( i can see them in the Oracle table, and in IJC), and fp generated ?, but structure cache was not refreshed when I did sss.

Dong,

I think we've now reached the point where a webex might be in order to help you obtain detailed diagnostic infomation potentially relevant to the issue. The following steps would need to be performed:

1. Add the following lines to jchem/cartridge/conf/logging.properties:

chemaxon.jchem.level = FINEST

2. Restart the JChem Cartridge server

3. Execute the following SQL:

call jcart_logger.set_log_level('chemaxon', 4);

4. Insert a structure into the table chemdata_single.

5. Obtain the content of the "update log" table associated with the JChem index created on chemdata_single.smiles:

select * from <index-name>_jcx_ul;

6. Obtain the content of the jchemproperties_cr table:

select * from jchemproperies_cr;

7. Commit the insert.

8. Execute a structure search which is supposed to retrieve the newly inserted structure.

9. Provide us the following data eventually through e-mail (pkovacs at chemaxon dot com):

9a. The content of the update log table obtained in step #5.

9b. The content of the jchemproperties_cr table obtained in step #6.

9c. The log files found in the jchem/cartridge/logs directory

9d. The corresponding Oracle session trace file from the Oracle trace directory

9e. The SQL used to create the JChem index on chemdata_single.smiles.

Thanks

Peter