LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: michaelslowey

Hi all,

Just wondering if its possible to not commit until *all* records written, while using the JDBC Execute Node?

Cheers
Mick

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hey Mick,

I could be wrong, but after looking at the node (and checking the code quickly) it doesn't look like this is possible to do this nicely at the moment.

The only way I can see to do this is either if you know the number of rows input upfront, or use a very large number that will always be larger than the number of rows input to the node.

Note that if the CommitFrequency specified is greater than the number of input rows, the node will commit after all rows have been processed.

An example of how to do this would be to set CommitFrequency to 9223372036854775807 (the maximum java Long value).

It's pretty unlikely that you would be processing more than 9 quintillion records, so you should be right there.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: michaelslowey

Thanks Tim,

I'm sure I wont be processing 9Q Records!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Team,

Is it possible to commit a DB Exec node based on the success or failure of another set of DB Exec nodes?
I would like this to happen without using clocks.

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

At this time, each DbExecute opens and closes it's own session with the database. Oracle treats each session independently, and you would be unable affect one session from a different one.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

You could set things up so that the DB Exec node gets zero records when you don't want it to do anything. And when you want it to do something, give it the required records or at least one that will cause it to do something.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Tony and Jones,

Thanks for your inputs. We need a solution for below situation.
6 DB Execute nodes need to be treated as a single transaction and be committed or rolled back as a whole.
First 4 update table1's records with different status values.
The 5th one is clock synced with the 4 nodes and it updates another status in table1 once the 4 nodes complete.
The 6th node runs in parallel to these 5 and updates another table2.

Issue: In a normal situation this runs fine, but when one of the 4 nodes fail, the 6th node which runs in parallel could update table2 which is not desirable.

For now we are thinking of 2 options:
1. Clock sync 5th node with the 6th node so that node6 executes only after node5.
2. Have all the DML in a single node and handle them with a PL/SQL approach

Both have their own drawbacks.

Can you please provide an optimal solution that is also good from a performance perspective? Because clock syncs will serialize the insertions.

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

At this time there is no possibility of having multiple nodes run inside a single database transaction.

Using the DB Execute node you can execute multiple SQL insert and update statements which might be part of the answer for what you are needing.

Based on what you are describing, I would consider creating a PL/SQL function or procedure that would be run by either the DB Execute or JDBC Execute nodes.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Thanks for the confirmation Jones.

Can one DB execute node (which is updating 1 million records) spawn multiple Lavastorm threads and cause deadlock with itself?
How do we identify the optimal Lavastorm configuration?

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Team,
I would greatly appreciate your quick turnaround on this. The background of this question is that there is an intermittent deadlock situation happening in a Lavastorm graph and we are trying to identify the root cause from several angles.
Using the deadlock reports generated by Oracle, we can see that the cause is two DB Execute nodes with different update statements acting on the same table. We have further analysed the data used by the update statements and they are not working on common records.

Coming to a Lavastorm angle,
1. Can one DB execute node (which is updating 1 million records) spawn multiple Lavastorm threads and cause deadlock with itself?
2. How do we identify the optimal Lavastorm configuration (eg. Thread count)?

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Originally posted by: snbkumar
					

1. Can one DB execute node (which is updating 1 million records) spawn multiple Lavastorm threads and cause deadlock with itself?

2. How do we identify the optimal Lavastorm configuration (eg. Thread count)?
			

1) No. A single DbExecute node opens a single session to the database and executes all updates thru that session.
a) I would look for the possibility that the same record is being updated twice (duplicate input records), or that within Oracle you've got some triggers defined such that some remote table is being updated twice during the single session.
b) I suppose that within Oracle's memory management, it might be dealing with 'blocks' of records as a group and throwing an error if two records in the same block are updated, but I've never seen that situation. You'd have to research this with Oracle.
c) What do you have your Commit Frequency set to?
d) Is it possible that you have two instances of the same graph operating side-by-side? That might be causing what you're seeing.

2) Unless you have separate DbExecute nodes operating in parallel, then LAE Thread Count doesn't affect the situation.