Using ODE with ORACLE and Geronimo

[Raja Balasubramanian/MAXIMUS <Ra...@maximus.com>]

Hi All,

I'm trying to use ODE in Geronimo with ORACLE 10g as back end.
ODE in Geronimo with MySQL works like a charm.

I have created ORACLE schema and created a connection pool for that and
mapped in web.xml and geronimo-web.xml.

ODE deployment went fine and BPEL deployments went fine. 
But when I invoke a process (which calls another web service), it just
hangs. No exceptions, but waiting for something indefinitely.

Anybody faced this problem?
Could somebody show me how to overcome this?

Thank you

Raja

Re: Using ODE with ORACLE and Geronimo

[Matthieu Riou <ma...@gmail.com>]

Yep, tracking this with an issue would be better.

You can also try to remove ALL foreign key constraints and add the indexes
pasted below. That should solve most database related deadlocks.

create index IDX_XMLDATA_IID on BPEL_XML_DATA (PIID);
create index IDX_XMLDATA_SID on BPEL_XML_DATA (SCOPE_ID);
create index IDX_XMLDATA_NAME on BPEL_XML_DATA (NAME);
create index IDX_XMLDATA_NAME_SID on BPEL_XML_DATA (NAME, SCOPE_ID);

create index IDX_EVENT_IID on BPEL_EVENT (IID);
create index IDX_EVENT_PID on BPEL_EVENT (PID);

create index IDX_QRTZ_SIMTRIG_NAME on QRTZ_SIMPLE_TRIGGERS (TRIGGER_NAME);
create index IDX_QRTZ_SIMTRIG_GRP on QRTZ_SIMPLE_TRIGGERS (TRIGGER_GROUP);
create index IDX_QRTZ_FIRTRIG_NAME on QRTZ_FIRED_TRIGGERS (TRIGGER_NAME);
create index IDX_QRTZ_FIRTRIG_GRP on QRTZ_FIRED_TRIGGERS (TRIGGER_GROUP);
create index IDX_QRTZ_FIRTRIG_INST on QRTZ_FIRED_TRIGGERS (INSTANCE_NAME);
create index IDX_QRTZ_FIRTRIG_ENTRY on QRTZ_FIRED_TRIGGERS (ENTRY_ID);
create index IDX_QRTZ_TRIG_NAME on QRTZ_TRIGGERS (TRIGGER_NAME);
create index IDX_QRTZ_TRIG_GRP on QRTZ_TRIGGERS (TRIGGER_GROUP);
create index IDX_QRTZ_TRIG_NAME_GRP on QRTZ_TRIGGERS (TRIGGER_NAME,
TRIGGER_GROUP);
create index IDX_QRTZ_SIMTRIG_NAME_GRP on QRTZ_SIMPLE_TRIGGERS
(TRIGGER_NAME, TRIGGER_GROUP);
create index IDX_QRTZ_FIRTRIG_NAME_GRP on QRTZ_FIRED_TRIGGERS (TRIGGER_NAME,
TRIGGER_GROUP);

create index idx_qrtz_t_next_fire_time on QRTZ_TRIGGERS(NEXT_FIRE_TIME);
create index idx_qrtz_t_state on QRTZ_TRIGGERS(TRIGGER_STATE);
create index idx_qrtz_t_nf_st on
QRTZ_TRIGGERS(TRIGGER_STATE,NEXT_FIRE_TIME);
create index idx_qrtz_ft_job_name on QRTZ_FIRED_TRIGGERS(JOB_NAME);
create index idx_qrtz_ft_job_group on QRTZ_FIRED_TRIGGERS(JOB_GROUP);

create index IDX_CORR_SET_NAME on BPEL_CORRELATION_SET (CORR_SET_NAME);
create index IDX_CORR_SET_SCOPE_ID on BPEL_CORRELATION_SET (SCOPE_ID);
create index IDX_BPEL_INSTANCE_PROCESS_ID on BPEL_INSTANCE (PROCESS_ID);
create index IDX_BPEL_INSTANCE_STATE on BPEL_INSTANCE (STATE);
create index IDX_BPEL_PROCESS_PROCID on BPEL_PROCESS (PROCID);
create index IDX_BPEL_PROCESS_TYPE_NAME on BPEL_PROCESS (type_name);
create index IDX_BPEL_PROCESS_TYPE_NS on BPEL_PROCESS (type_ns);
create index IDX_BPEL_CORRELATOR_PROCESS_ID on BPEL_CORRELATOR (PROCESS_ID);
create index IDX_BPEL_CORRELATOR_CID on BPEL_CORRELATOR (CID);
create index IDX_BPEL_CORRELATOR_MESSAGE_CKEY on
BPEL_CORRELATOR_MESSAGE_CKEY (CKEY);
create index IDX_SELECTOR_CORRELATOR on BPEL_SELECTORS
(CORRELATOR,CORRELATION_KEY);
create index IDX_SELECTOR_CKEY on BPEL_SELECTORS (CORRELATION_KEY);
create index IDX_SELECTOR_SELGRPID on BPEL_SELECTORS (SELGRPID);
create index IDX_UNMATCHED_CKEY on BPEL_UNMATCHED (CORRELATION_KEY);
create index IDX_UNMATCHED_CORRELATOR_CKEY on BPEL_UNMATCHED
(CORRELATOR,CORRELATION_KEY);
create index IDX_RECOVERY_LDATAID on BPEL_ACTIVITY_RECOVERY (LDATA_ID);
create index IDX_EVENT_LDATAID on BPEL_EVENT (LDATA_ID);
create index IDX_FAULT_LDATAID on BPEL_FAULT (LDATA_ID);
create index IDX_MESSAGE_LDATAID on BPEL_MESSAGE (DATA);
create index IDX_INSTANCE_LDATAID on BPEL_INSTANCE (JACOB_STATE);
create index IDX_MEXCEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_CEPR_ID);
create index IDX_MEXEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_EPR_ID);
create index IDX_PLINK_VAL_PROCESS_IDX on BPEL_PLINK_VAL (PROCESS);
create index IDX_PLINK_VAL_SCOPE on BPEL_PLINK_VAL (SCOPE);
create index IDX_PLINKMYR_LDATAID on BPEL_PLINK_VAL (MYROLE_EPR);
create index IDX_PLINKPR_LDATAID on BPEL_PLINK_VAL (PARTNERROLE_EPR);
create index IDX_XMLDATA_LDATAID on BPEL_PLINK_VAL (PARTNERROLE_EPR);
create index IDX_PLINK_VAL_MODELID on BPEL_PLINK_VAL (MODELID);
create index IDX_UNMATCHED_CORRELATOR on BPEL_UNMATCHED (CORRELATOR);
create index IDX_XML_DATA_SCOPE on BPEL_XML_DATA (SCOPE_ID);
create index IDX_SELECTOR_INSTANCE on BPEL_SELECTORS (PIID);
create index IDX_VARPROP_XMLDATA on VAR_PROPERTY (XML_DATA_ID);
create index IDX_VARPROP_NAME on VAR_PROPERTY (PROP_NAME);
create index IDX_VARPROP_VALUE on VAR_PROPERTY (PROP_VALUE);

Cheers,

Matthieu

On 2/2/07, Maciej Szefler <mb...@intalio.com> wrote:
>
> Raja,
>
> This looks like a deadlock issue. Also if you could, (a) set oracle's
> deadlock timeout to some short period (5 sec) and run to see what happens,
> or (b) query oracle's lock table once the system hangs and include the
> output.  Also if you run with logging at the debug level (esp wrt to
> hibernate SQL calls), the output could be very helpful. Also, it'd be best
> if you file an issue for this.
>
> Thanks,
> -mbs
>
> On 2/2/07, Raja Balasubramanian/MAXIMUS <Ra...@maximus.com>
> wrote:
> >
> > Hi All,
> >
> > I'm trying to use ODE in Geronimo with ORACLE 10g as back end.
> > ODE in Geronimo with MySQL works like a charm.
> >
> > I have created ORACLE schema and created a connection pool for that and
> > mapped in web.xml and geronimo-web.xml.
> >
> > ODE deployment went fine and BPEL deployments went fine.
> > But when I invoke a process (which calls another web service), it just
> > hangs. No exceptions, but waiting for something indefinitely.
> >
> > Anybody faced this problem?
> > Could somebody show me how to overcome this?
> >
> > Thank you
> >
> > Raja
> >
>

Re: Using ODE with ORACLE and Geronimo

[Maciej Szefler <mb...@intalio.com>]

Raja,

This looks like a deadlock issue. Also if you could, (a) set oracle's
deadlock timeout to some short period (5 sec) and run to see what happens,
or (b) query oracle's lock table once the system hangs and include the
output.  Also if you run with logging at the debug level (esp wrt to
hibernate SQL calls), the output could be very helpful. Also, it'd be best
if you file an issue for this.

Thanks,
-mbs

On 2/2/07, Raja Balasubramanian/MAXIMUS <Ra...@maximus.com>
wrote:
>
> Hi All,
>
> I'm trying to use ODE in Geronimo with ORACLE 10g as back end.
> ODE in Geronimo with MySQL works like a charm.
>
> I have created ORACLE schema and created a connection pool for that and
> mapped in web.xml and geronimo-web.xml.
>
> ODE deployment went fine and BPEL deployments went fine.
> But when I invoke a process (which calls another web service), it just
> hangs. No exceptions, but waiting for something indefinitely.
>
> Anybody faced this problem?
> Could somebody show me how to overcome this?
>
> Thank you
>
> Raja
>