Jchem 5.3.2 Upgrade - ChemAxon Forum Archive

User 9f6f294e9f

20-04-2010 10:53:13

Hi Peter

I'm running my first 5.3.2 upgrade and am being asked to supply:

The structure cache id of this JChem Server instance [JCC1]:

What is this ?

Are there any other changes in the upgrade that I need to be aware of ?

Thanks

Ant

ChemAxon aa7c50abf8

20-04-2010 12:13:00

Hi Ant,

The only thing you need to be aware of is that JChem Server must be assigned an id that must be unique across all ChemAxon servers (Web Services servers, IJC servers) which use JChem Structure caches accessing the same structure table.

The reason for introducing this property is that starting, with 5.3.2, synchronization of structure caches with the cached tables/indices is managed on a cache-by-cache basis for increased robustness. More specifically: under the new scheme, each structure cache will have its own log table entries as opposed to the old scheme where all structure caches shared the same log table entries. (Entries in log tables [tables with names ending with _UL] are used to record changes in the cached object. Database sessions making the changes to the cached object are the producers of the entries in the log tables whereas structure caches are the consumers.) Typically, one Java process has at most one JChem structure cache. Java processes which partition their process space into multiple application contexts (such as Web-containers like Tomcat) may have one JChem structure cache per hosted application context.

BTW, entering a question mark when prompted for the cache id by the JCC installer/upgrader is supposed to display the following description for this property:

> The structure cache id of the JChem Server instance currently being configured. The structure cache id must uniquely identify JChem structure caches across all JChem based application accessing the same structure tables. It must also be unique across JChem Cartridge Servers installed on nodes of an Oracle Real Application Cluster.

Peter

User c68322b4b7

22-04-2010 08:04:29

Hi Peter!

Using jc_insert in the last cartridge release (5.3.2) I got the following exception:

java.lang.IllegalStateException: The following cache ids are not registered for jc_test_properties: JCC1

The statement executed:

jchem_table_pkg.jc_insert('CCCCC', 'jc_test', 'jc_test_properties', 'false', 'false', '');

(Oracle release: 11.2.0.1.0)

Perhaps this error is related to the new caching mechanism.

Is there any way to avoid the exception?

Mark

pkovacs wrote:

Hi Ant,

The only thing you need to be aware of is that JChem Server must be assigned an id that must be unique across all ChemAxon servers (Web Services servers, IJC servers) which use JChem Structure caches accessing the same structure table.

The reason for introducing this property is that starting, with 5.3.2, synchronization of structure caches with the cached tables/indices is managed on a cache-by-cache basis for increased robustness. More specifically: under the new scheme, each structure cache will have its own log table entries as opposed to the old scheme where all structure caches shared the same log table entries. (Entries in log tables [tables with names ending with _UL] are used to record changes in the cached object. Database sessions making the changes to the cached object are the producers of the entries in the log tables whereas structure caches are the consumers.) Typically, one Java process has at most one JChem structure cache. Java processes which partition their process space into multiple application contexts (such as Web-containers like Tomcat) may have one JChem structure cache per hosted application context.

BTW, entering a question mark when prompted for the cache id by the JCC installer/upgrader is supposed to display the following description for this property:

> The structure cache id of the JChem Server instance currently being configured. The structure cache id must uniquely identify JChem structure caches across all JChem based application accessing the same structure tables. It must also be unique across JChem Cartridge Servers installed on nodes of an Oracle Real Application Cluster.

Peter

User 9f6f294e9f

22-04-2010 08:14:59

Hi Peter

I'm afraid that I'm still confused on this one. Can you confirm that it is safe to use the same cache id on different Jservers ? And can you also confirm whether it is ok to manually amend a cache id in jcart.properties and then stop and re-start the Jserver ?

If we have users or developers that use IJC, and that includes Jchem indexed queries, do we have to somehow specify a different cache id for each of the IJC users ? If no structure cache id's are set explicitly what would be the impact ?

Regards

Ant

ChemAxon aa7c50abf8

22-04-2010 12:13:44

Hi Mark,

> java.lang.IllegalStateException: The following cache ids are not registered for jc_test_properties: JCC1

Yes. This error is related to the new caching mechanism. In order to avoid this error, you have to create a jchem index on the table using the appropriate JChemProperties table name:

created index xjc_test on jc_test(cd_structure) indextype is jchem.jc_idxtype parameters('jchempropertiestable=jc_test_properties');

Peter

ChemAxon aa7c50abf8

22-04-2010 14:29:47

Hi Ant,

> Can you confirm that it is safe to use the same cache id on different Jservers ?

It is safe for one JChem Server to use the same cache id as another JChem Server as long as the sets of JChem indices (or JChem structure tables) they operate on don't share the same JChemProperties tables. By default, JChem indices created in the same schema on regular structure tables use the same JChemProperties table. (Those created in different schema will use by default a different JChemProperties table.)

As this is not trivial to follow, the safest is to name the cache id of each and every JChem Server differently.

And can you also confirm whether it is ok to manually amend a cache id in jcart.properties and then stop and re-start the Jserver ?

It is not sufficient. The structure.cache.id property must be set in the JC_IDX_PROPERTY table of the cartridge owner as well. In general, the install (or upgrade) procedure of JChem Cartridge should take care of this without the user having to do anything else than specify the id during installation or upgrade.

> If we have users or developers that use IJC, and that includes Jchem indexed queries, do we have to somehow specify a different cache id for each of the IJC users ? If no structure cache id's are set explicitly what would be the impact ?

The safest is to ask about IJC requirements on the IJC forum. I can present my halfway competent understanding here:

In the case of queries on JChem Cartridge-based IJC schemata (i.e. IJC executes the queries on Oracle through SQLs including JChem Cartridge operators -- thus having JChem Cartridge actually execute the queries) no structure cache in IJC is involved, hence no need for a separate cache id.

In the case of queries on JChem Base-based IJC schemata, IJC will use its own structure cache with an automatically generated cache id -- so no need to specify an id in this case either.

IJC will have (or already has?) a server component called IJC Server. I am not sure how it will handle cache ids.

Regards,

Peter

User 9f6f294e9f

22-04-2010 16:04:30

Thanks Peter, that makes things clearer.

User 9f6f294e9f

04-05-2010 08:40:26

Hi Peter

I had a great deal of difficulty when trying to upgrade to Jchem 5.3.2 on a production database over the weekend. I attempted an upgrade 4 times, using upgrade-evol.sh, but wasn't prompted for the cache id at all. I tried to set the cache id in the jcart.properties manually once when prompted to start the jserver, and on one occasion saw that it had been set to the default (JCC1), although I hadn't specified this. I also tried the upgrade with a Jserver already running, both with and without a cache id parameter. On each occasion the installation neared completion and then failed as follows:

INFO: No DBA login available, skipping staging schema removal
SEVERE: error
java.lang.IllegalArgumentException: Cache id must not be null
        at chemaxon.jchem.cartridge.rmi.impl.CacheIdManagement.registerCacheIdForJcc(CacheIdManagement.java:86)
        at chemaxon.jchem.cartridge.install.Upgrader.upgradeCacheId(Upgrader.java:153)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:65)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:51)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:293)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.interactiveUpgrade(UpgradeCmdLine.java:92)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:47)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:587)
+ set +x

I was on the point of attempting to revert to 5.3.0 when I wondered if the problem may be caused in part by the installation script always expecting the old installation directory to be named cartridge. So I dummied a cartridge directory under the path to old installation and created a copy of the jcart.properties there, including the cache id I wished to use. This time the installation did pick up the correct cache id and wrote it to jcart.properties, and the upgrade completed successfully - but didn't write the cache id to JCHEM.JC_IDX_PROPERTY (post upgrade there was no cache id entry). I hypothesised that the installation script only writes an entry to the table if it has prompted for the cache id during the upgrade, and inserted the correct entry via sql. I was then able to create indexes and run queries,and I can now see entries appearing in the JCX_UL tables with the correct cache id.

As you can imagine, the one time that I want upgrades to go smoothly is when working on production databases at a weekend ! Can you take a look at the upgrade process, and modify it to ensure that it always prompts for the cache id (showing the current value if it has found one) ?

Incidentally, I haven't out an installation from scratch in 5.3.2. Is the intention that the user will set the cache_id in jcart.properties themselves prior to running an installation, or will the installation always prompt for it and write it to jcart.properties and to the JC_IDX_PROPERTY table ?

One other 'nice to have'. if a DBA schema isn't specified removal of the content of the staging schema is skipped (see the first line of the copied output above). As the upgrade has the id and password of the staging schema it should be possible to remove the contents fairly easily. I can supply some sql that you can use if you'd like to consider this as a future enhancement.

Regards

Ant

User 9f6f294e9f

04-05-2010 12:55:08

Hi again

I tried a 5.3.2 installation from scratch. The installation errored if no cache id was included in the jcart.properties, but post completion of the installation there is no entry in JCHEM.JC_IDX_PROPERTY for the cache id.

Creating an index worked (eventually ! see below), but on querying the index I now receive:

ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: The url cannot be null
ORA-06512: at "JCHEM.JCHEM_CLOB_PKG", line 75
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 446

After manual insertion of the structure.cache.id entry in JCHEM.JC_IDX_PROPERTY the query now works.

As a side issue I also observed that, though I had called use_password successfully prior to creating an index, index creation attempts failed until I had granted the default access account the correct java privileges. This mystified me as I don't see why the default access account would be used. This is the error:

create index JCHEM_UCS_STRUCT_IDX1 on ant_structure(ISOMERIC_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: Oracle runtime returned null for default
connection!!!!!!!!!!!!
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 80
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 18

Regards

Ant

User 9f6f294e9f

04-05-2010 12:57:06

Sorry - I've just re-read my previous post and it isn't totally clear. To clarify - to get the installation to work I had to include the cache id in the jcart.properties and re-start the Jserver, then run the installation.

ChemAxon aa7c50abf8

04-05-2010 13:15:20

Hi Ant,

> I had a great deal of difficulty when trying to upgrade to Jchem 5.3.2 on a production database over the weekend.

I am truly sorry for this.

> I was on the point of attempting to revert to 5.3.0 when I wondered if the problem may be caused in part by the installation script always expecting the old installation directory to be named cartridge.

Your observation is correct. AFAIK, this is consistent with the documentation which refers to the "cartridge" directory in the uncompressed the jchem installation package. In the next minor version, when your current working directory is not called "cartridge" during upgrade, you'll be offered the option to specify the path to the parent directory of the jcart.properties file to be upgraded.

> I hypothesised that the installation script only writes an entry to the table if it has prompted for the cache id during the upgrade

This is not about having prompted or not. When you entered the cache id manually into the jcart.properties file, you made the upgrade script think that the cache id had already been properly set by a previous upgrade. (Properly set means here, as I mentioned in an earlier post, setting it both in the jcart.properties file and in the jchem.jc_idx_property table.)

> As you can imagine, the one time that I want upgrades to go smoothly is when working on production databases at a weekend !

I am truly sorry for the trouble we caused to you.

> Can you take a look at the upgrade process, and modify it to ensure that it always prompts for the cache id (showing the current value if it has found one) ?

We will try to make the upgrade process more robust. As I mentioned, this is not about prompting or not. (For example, I am not entirely comfortable with the possibility of users changing an already existing cache id. The new structure cache synchronization mechanism largely relies on the assumption that permanent cache ids are set once at either installation or the upgrade.) The way I see it can be made perhaps more robust in this respect is to check that the cache id is set in both the jcart.properties and the jchem.jc_idx_property table.

> Incidentally, I haven't out an installation from
scratch in 5.3.2. Is the intention that the user will set the cache_id
in jcart.properties themselves prior to running an installation, or
will the installation always prompt for it and write it to
jcart.properties and to the JC_IDX_PROPERTY table ?

Installation doesn't work for two-hosts setups, which is a bug. It will be fixed in the next minor version.

> One other 'nice to have'. if a DBA schema isn't
specified removal of the content of the staging schema is skipped (see
the first line of the copied output above). As the upgrade has the id
and password of the staging schema it should be possible to remove the
contents fairly easily. I can supply some sql that you can use if you'd
like to consider this as a future enhancement.

Yes. We would highly appreciate, if you supplied the SQLs required for this.

.

Thanks

Peter

ChemAxon aa7c50abf8

04-05-2010 13:18:05

Hi Ant,

> I tried a 5.3.2 installation from scratch

Installation doesn't work for two-hosts setups, which is a bug. It will be fixed in the next minor version.

Regards,

Peter

User 9f6f294e9f

04-05-2010 13:39:01

> This is not about having prompted or not. When you entered the cache id manually into the jcart.properties file, you made the upgrade script think that the cache id had already been properly set by a previous upgrade. (Properly set means here, as I mentioned in an earlier post, setting it both in the jcart.properties file and in the jchem.jc_idx_property table.)

Unfortunately, despite much blood, sweat and tears, I was unable to persuade the upgrade script to prompt me for a cache id. As I say I had four abortive attempts at the upgrade prior to using the method that worked. I appreciate that the earlier jcart.properties shouldn't have had a cache id entry but I was getting pretty desperate by that stage ! Maybe the upgrade should check, if it finds a cache id is set, that the entry is present in jchem.jc_idx_property. If not present the upgrade could prompt for confirmation that it is correct, and if so insert the entry in the table.

Here's the sql you can use to drop the contents of an Oracle staging schema (10g or higher):

DECLARE

CURSOR c_fks IS

select 'alter table '||table_name||' drop constraint '||constraint_name||';' drop_fk_text

from user_constraints where constraint_type = 'R';

CURSOR c_objects IS

select 'drop '||object_type||' '||object_name drop_text

from user_objects

where object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION',

'PACKAGE', 'TYPE', 'SYNONYM', 'TRIGGER','JAVA CLASS','INDEXTYPE','OPERATOR')

and object_name not like 'BIN$%';

BEGIN

FOR c_rows IN c_fks

LOOP

BEGIN

execute immediate(c_rows.drop_fk_text);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

END LOOP;

FOR i in 1 .. 10

LOOP

FOR c_rows IN c_objects

LOOP

BEGIN

execute immediate(c_rows.drop_text);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

END LOOP;

END;

/

DECLARE

v_count INTEGER := 0;

BEGIN

FOR r IN

(SELECT DISTINCT 'begin dbms_java.dropjava ('''

|| DBMS_JAVA.longname (object_name)

|| '''); end;' AS command,

DBMS_JAVA.longname (object_name) as classname

FROM user_objects

WHERE object_type like 'JAVA%')

LOOP

EXECUTE IMMEDIATE r.command;

v_count := v_count+1;

END LOOP;

-- DBMS_OUTPUT.put_line (v_count || ' object(s) were dropped.');

END;

/

purge recyclebin;

select count(*) from user_objects;

(the final count should return 0 - otherwise alert the user that the staging schema isn't totally empty)

Regards

Ant

ChemAxon aa7c50abf8

09-05-2010 20:13:21

Thank you, Ant, for the SQL that empties the staging schema!

Just out of curiosity: Is there a particular reason why the object types to drop are enumerated in the c_objects cursor? Are there object types which need to be excluded from the removal?

Thanks

Peter

User 9f6f294e9f

10-05-2010 08:10:37

hi Peter

The exclusion of BIN$% objects avoids attempts to drop items that are already in the recycle bin. The final sql statement purges the recycle bin.

The object_types are explicitly included to avoid unnecessary drops or drops that would error - such as an attempt to drop a package body when the package had already been dropped, or attempting to drop a constraint when the table that owned the constraint had already been dropped. The current list of object types, when run with the second pl/sql block that deals with the java objects, should remove all objects that are held in a staging schema.

With a little tweaking it should be possible to modify the sql to remove the contents of a Jchem schema. The main difference for Jchem is that there would be additional types such as 'OPERATOR' and 'TYPE', and that it would be necessary to append the keyword 'FORCE' to the drop statement for some of these object types.

Regards

Ant

User 9f6f294e9f

20-05-2010 09:59:50

Hi Peter

I've just carried out my first 5.3.3 upgrade and am pleased to say that it solves the great majority of issues that were previously encountered when trying to upgrade a Jserver on a server that had multiple Jservers configured under different cartridge_xxx directories.

Thanks for the very welcome enhancements.

Regards

Ant

ChemAxon aa7c50abf8

20-05-2010 10:22:14

Hi Ant,

Thank you for your feedback.

Apart from your request for the upgrade feature of emptying the staging schema, the implementation of which has been deferred to a future version, the installation- and upgrade-related issues reported under this topic are supposed to be solved in JChem version 5.3.3.

Regards,

Peter

User 9f6f294e9f

20-05-2010 10:27:26

Sorry - I edited my entry to remove the comment on the default user - it looks like it wasn't contained in the old conf file, but you presumably received a notification after I put the initial entry in. So, as far as I can tell, all the parameters present in the old file were correctly transferred.

Regarding the cleardown of the staging schema, whilst it's a 'nice to have' I don't regard it as being vital.

Regards

Ant

User 9f6f294e9f

27-05-2010 10:49:04

Hi Peter

I've just carried out a 5.3.3 upgrade and confirm that the default access account and password are transferred from the old jcart.properties file to the new one. Unfortunately there's an annoying bug when running the upgrade in Unix - where the encrypted password value includes two consecutive back slashes one of the two if being lost in the transfer. Please take a look at this when you have time.

Regards

Ant

ChemAxon aa7c50abf8

28-05-2010 17:32:44

Hi Ant,

Thank you for reporting this problem. It will be fixed in the next minor JChem version.

Sorry for the inconvenience.

Regards,

Peter

ChemAxon aa7c50abf8

19-07-2010 17:07:45

there's an annoying bug when running the upgrade in Unix - where the encrypted password value includes two consecutive back slashes one of the two if being lost in the transfer.

JChem 5.3.6 has been released with the fix for this problem.

Regards,

Peter

ChemAxon aa7c50abf8

24-07-2011 19:02:56

JChem 5.5.1 has been released with the upgrade feature of empting the staging schema when DBA credentials are not available.

Peter

User 7f33ec9a5c

16-10-2012 15:35:59

Hi,

It looks like my error is similar to the other errors in this thread.

When I try and insert to a JChem structure table:

a:= jchem.jchem_table_pkg.jc_insert(sSMI,'structure_jc1', 'jchem.jc_idx_property', 'true', 'false', 'haltOnBadFormat:y');

I get the error:

Exception in thread "Root Thread" java.lang.IllegalStateException: The following cache ids are not registered for jchem.jc_idx_property: JCC1

If I query:

select prop_value
from jchem.jc_idx_property
where PROP_NAME = 'structure.cache.id'

The query returns JCC1.

The Jchem table has many million structures in it that were inserted successfully about a month ago. The insert no longer seems to work.

~mike richards

ChemAxon aa7c50abf8

16-10-2012 15:53:47

Did you upgrade or something?

ChemAxon aa7c50abf8

16-10-2012 15:54:49

Also, why are you messing up with the JChemProperties table parameter? Isn't the default good enough?

User 7f33ec9a5c

16-10-2012 15:59:33

Hi Peter,

We have not touched anything. Just stopped working. The only reason I was querying the table parameter was to validate that it was present and correct. The only reason I posted to this forum and this thread was the error was Identical to what I was seeing.

Here is an SQL*Plus sesison displaying the behavior we are seeing:

(sorry about all the bad formatting, I'm cutting & pasting).

===============================================================

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 16 08:49:17 2012

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec jchem_core_pkg.use_password('password');

INFO: Using chemaxon.jchem.cartridge.util.cpool.Oracle10gDataSource

PL/SQL procedure successfully completed.

SQL> select jchem.jchem_table_pkg.jc_insert('c1ccccc1','structure_jc1', 'jchem.jc_idx_property', 'true', 'false', 'haltOnBadFormat:y')

from dual; 2

select jchem.jchem_table_pkg.jc_insert('c1ccccc1','structure_jc1', 'jchem.jc_idx_property', 'true', 'false', 'haltOnBadFormat:y')

*

ERROR at line 1:

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

java.lang.IllegalStateException: The following cache ids are not registered for

jchem.jc_idx_property: JCC1

ORA-06512: at "JCHEM.JCHEM_TABLE_PKG", line 30

ORA-06512: at "JCHEM.JCHEM_TABLE_PKG", line 20

ORA-06512: at line 1

SQL> select prop_value

from jchem.jc_idx_property

where PROP_NAME = 'structure.cache.id';

PROP_VALUE

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

JCC1

ChemAxon aa7c50abf8

16-10-2012 16:11:23

SQL> select jchem.jchem_table_pkg.jc_insert('c1ccccc1','structure_jc1', 'jchem.jc_idx_property', 'true', 'false', 'haltOnBadFormat:y')

This won't work like that. Something like that instead:

http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_insert

declare

    a cd_id_array;

begin

    a := jchem.jchem_table_pkg.jc_insert('Brc1ccccc1','sss_test');

end;

/

ChemAxon aa7c50abf8

16-10-2012 16:12:56

As to the cache id, I bet my next breakfast you didn't create this JCB table with jchem.jc_idx_property as the jchemproperties table.

User 7f33ec9a5c

16-10-2012 16:20:29

Hi Peter,

The table was created with this exact command, (I cut it from my original script that i used to create the table)

exec jchem_table_pkg.create_jctable('structure_jc1', 'jchem.jc_idx_property', 16, 2, 6, 'jc_structure_id number(15)', null, 1, 'tableType:molecules');

I get your concirn about the function nested in the select statement. This was an example used to trigger the error.

The actual function call that I am using, and that is causing the error, is nested in PL\SQL procedure, here is the whole thing:

declare

sSMI varchar2(4000);

sType varchar2 (100);

a cd_id_array;

new_cd_id integer;

is_first integer :=0;

sid integer := -1;

err_num NUMBER;

err_msg VARCHAR2(4000);

begin

eMail_from_DB('[email protected]', '[email protected]',null,'jChemACX inserts started','BEGIN LOAD');

for i in (select acxid, camsoft_smiles, chemaxon_smiles, daylight_smiles, sid

from MIKE.raw_smiles_to_acx_id

where acxid not in (select acx_id from acx_struct_log)

and acxid not in (select acx_id from acx_struct_oops)) loop

BEGIN

FOR J IN 1..3 LOOP

CASE J

WHEN 1 THEN

sSMI := i.camsoft_smiles;

sType := 'CAMSOFT';

WHEN 2 THEN

sSMI := i.chemaxon_smiles;

sType := 'CHEMAXON';

WHEN 3 THEN

sSMI := i.daylight_smiles;

sType := 'DAYLIGHT';

END Case;

a:= jchem.jchem_table_pkg.jc_insert(sSMI,'structure_jc1', 'jchem.jc_idx_property', 'true', 'false', 'haltOnBadFormat:y');

new_cd_id := a(1);

if (new_cd_id < 0) then

is_first := 0;

new_cd_id := -new_cd_id;

SELECT jc_structure_id

INTO sid

FROM Structure_JC1

WHERE cd_id = new_cd_id;

else

is_first :=1;

sid := -1;

update structure_jc1

set jc_structure_id = sid

where cd_id = new_cd_id;

end if;

insert

into acx_struct_log (acx_id, type, sid, new_cd_id, is_first)

values (i.acxid, sType, sid, new_cd_id, is_first);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

err_num := SQLCODE;

err_msg := SUBSTR(SQLERRM,1,4000);

eMail_from_DB('[email protected]', '[email protected]',null,'jChem took a crapper',err_msg);

INSERT INTO ACX_STRUCT_OOPS(acx_id, typ, err,msg) VALUES (i.acxid, sType, err_num, err_msg);

END;

COMMIT;

end loop;

eMail_from_DB('[email protected]', '[email protected]',null, 'Done dude','Jchem finished building table');

END;

ChemAxon aa7c50abf8

16-10-2012 16:43:20

Thanks for the detailed code sample!

Do I correctly understand that this code worked before, and today (or perhaps yesterday) it suddenly ceased to work -- for no apparent reason?

User 7f33ec9a5c

16-10-2012 19:14:15

HI Peter,

I think I am comiing closer to figuring this out on our end. This database was moved! The database itself was renamed and the JChem cartrige was re-installed underneath the existing tables & Indexes, and during that process, I think the structure ID cache was accidently renamed, after the index was created.

A log of the re-install from our DBA is shown below.

That structure table takes 17 hours to re-index. How can we figuure out what the previous structureID cache was named, and get all the names synched up so that the existing indexes start working again?

Thanks,

~mike

The following is because we already had the jchem database on db01 (freshly moved) with a jchem schema already populated...

bash install.sh --jcserver-only

Here is the session info:

# bash install.sh --jcserver-only

+ /jcode/11/jdk/jdk1.7/bin/java -classpath ../lib/jchem.jar -Djava.util.logging.config.file=conf/logging.properties chemaxon.jchem.cartridge.install.InstallCmdLine --jcserver-only

Connection type (sid, service_name, url) [sid]:

Name of the Oracle host [localhost]: db01

Oracle listener port [1521]: 1522

Name of the Oracle database (sid) [mydb]: jchem

The host name of the JChem Server [localhost]: db01

The port at which the JChem Server accepts connections [1099]:

maximum memory allocated to the JChem Server process [700m]: 2500m

The structure cache id of this JChem Server instance [JCC1]: