JChem table vs standard table with structure with cartridge

User 55ffa2f197

23-02-2012 17:11:57

Hi,


Now it seems the prefered way of indexing molecules using cartridge is to start with standard table instead of the JChem table. However using JChem table does come with advantage where we can use jcman. This is paritcularly convinent if we are loading reaction file (RD file) where we need to store the reaction part of te RD file as blob, otherwise we would have to write our own sqlldr script, and seperate out the reactions from other fields, and save them into files, or write a java function which insert the reaction into blob field in the standard table.


However if the standard table becomes neccessary for cartridge indexing, after loading the structures into JChem table using jcman do we need to remove the fp fields and turn this table into a standard table?


Advise please


Thanks


Dong

ChemAxon 9c0afc9aaf

23-02-2012 18:04:22

Hi Dong,


You can index a JChem table too with the cartridge.


You just have to use certain fucntions instead of plain SQL insert/delete/update to access them:


http://www.chemaxon.com/jchem/doc/dev/cartridge/index.html#jctable


A plain table is neither necessary nor preferred to a JChem table.


In fact a JChem table can give you slightly better performance in some cases.


Best regards,


Szilard


PS: Directly modifying any JChem database objects is not recommended or supported.

User 55ffa2f197

23-02-2012 18:47:49

Hi Szilard,


I read this in the manual. A month ago I used JChem table and put the cartridge index on it I can do structure search in SQLPlus using jchem operator. However the index cannot be searched in IJC. At the time the suggestion from you guys (Timm) is to drop the JChem table and use the standard table. Once we did that we can do substructure search through IJC as well.


I am confused with conflicting information. My concern is to have IJC be able to search the index. I have no doubt jchem operator based SQL search would work regardless the structures are from JChem table or  standard table.


Thanks


Dong 

ChemAxon 9c0afc9aaf

23-02-2012 19:07:16

Dong,


This seems more like an IJC issue than a Cartridge issue.


You did not mention IJC in the first post.


I'm not sure what is the problem with Searching cartridge-indexed JCB tables in IJC,


but since you use IJC maybe you could also populate the plain tables with IJC instead of jcman ?


Tim should comment on this - I move this to the IJC section of the forum.


Best,


Szilard


 

ChemAxon fa971619eb

23-02-2012 19:10:09

Dong,


Both types are supported in IJC, but indexes on JChemBase tables are less flexible as the version of JChem in IJC and the cartridge need to be the same, but for indexes on standard tables the versions are independent (within reason).


The situation you had was that the version of IJC was newer than the version in the cartridge, so indexes on JChemBase cartridges could not be used. So I suggested to switch to standard tables.


Generally, unless you have very particular reasons to use JChemBase tables then I would suggest to use standard tables. The differences in performance should be very minor.


Tim

User 55ffa2f197

23-02-2012 19:37:43

Hi Tim,


This is what I thought.


I still like jcman's loading function for hadling RD file. I guess if I drop jchemproperties table come with jcman loading, literally the jchem table would turn into a standard table. I do agree with Szilard usually users should not mess with the jchem table in this fashion.


 


Thanks


Dong

ChemAxon 9c0afc9aaf

23-02-2012 21:13:33

Hi Dong,


I defintely do not recommend deleting the property table: it may contain important information (e.g. on other tables) and you would also be left with some other database objects which would create a problem later.


The good news is that IJC has a nice import wizard, which can import your RD files into plain cartridge tables.


Please see:


http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/moving_data/importing_data.html


If you select "New Structure entity (using standard table with cartridge index)" then the table will be created, the index will be added and the structures will be imported in one step. Could not get easier.


You can right-click on existing entities if you want to add data to an existing table later.


I hope this is an acceptable solution ?


Best regards,


Szilard

User 55ffa2f197

28-02-2012 18:08:06

Hi Szilard,


Thanks for the enlightening. This function hides so deep unless you have comb through entire manual carefully or did not fall to sleep in the training session. Have this been mentioned in training I doubt. Anyway it works fine except the DBA has to grant the db schema with cartridge permission, this requires DBA to run two dbms commands to grant the shchema sucket connections to jchem server I suppose. Otherwise the standard table with cartridge index will not appear at all. Of course jchem has to grant jchem_core_pkg.grant.jcorb for the schema. Since this has to be done through UI over the network if you have millions of the moles to process this may not be feasible. Using jcman on the server might be faster but then you have to using command line in SQL to make cartridge index, and the thing you do not suggest to mess around with jchem table.... Well it all depends on the dimention of the data you are dealing with and how brave you are.


Thanks


Dong

ChemAxon 9c0afc9aaf

28-02-2012 19:06:59

Hi Dong,


Granting the appropriate rights to each cartridge user is essential in any case, does not depend on Instant JChem.


Usually granting JCC_BASIC_ROLE should cover all the minor things like socket permission.


Inserting into a cartridge-indexed plain table is somewhat slower than jcman, but the speed should be acceptable even for millions - let us know otherwise.


Of course you can also consider using an IJC version that matches the Cartridge version, and then you can use JChem Base tables (and jcman) as well.


Best,


Szilard