Hi
I need of an help about this question.
I have a Job, for example, with three stages.
- An Oracle8 Stage:
To read data from an Oracle table. The output link is link1.
- A sequential file Stage:
To Read data from a sequential file. The output link is link2.
- Another Oracle8 Stage:
Insert all the data from link1 and link2.
My question is ....
DataStage split the operation described above in two task and exec them in the same breath, so it insert all rows extracted from both link simultaneosly, or it creates only one task and reads before from the Oracle8 table and then insert all rows extracted, committ and after reads from the flat file, insert and commit?
I hope to be phatom.
Thank you
Umberto
Insert rows from two links
Moderators: chulett, rschirm, roy
Umberto,
if your designer canvas has those 3 stages and just 2 links then it will do both loads to your table in parallel and using different processes. You can design and configure your job to do things differently, for instance by using a link collector stage for links 1 and 2 to make just one data stream loading to your database.
if your designer canvas has those 3 stages and just 2 links then it will do both loads to your table in parallel and using different processes. You can design and configure your job to do things differently, for instance by using a link collector stage for links 1 and 2 to make just one data stream loading to your database.
</a>No, my job is more complex. I only make an example to explain my problem and to know how DataStage works in this case. I have to know to resolve a problem about locks on Oracle table.ArndW wrote:Umberto,
if your designer canvas has those 3 stages and just 2 links then it will do both loads to your table in parallel and using different processes. You can design and configure your job to do things differently, for instance by using a link collector stage for links 1 and 2 to make just one data stream loading to your database.
Oracle only locks rows on updates, never on reading. So if you can make sure that for writing/updating DataStage only uses 1 Oracle "connection/session" you don't have a problem. But it may require some creative rewriting of the DataStage job.
Use a link collector and you should be fine (in your small example).
Ogmios
Use a link collector and you should be fine (in your small example).
Ogmios
In theory there's no difference between theory and practice. In practice there is.
I know that. I know that during a select Oracle lock in shared mode the rows extracted. But the question is. An Oracle Stage used to insert some rows from two input links includes the two insert operation in the same transaction or not? How DataStage works in this case?ogmios wrote:Oracle only locks rows on updates, never on reading. So if you can make sure that for writing/updating DataStage only uses 1 Oracle "connection/session" you don't have a problem. But it may require some creative rewriting of the DataStage job.
Use a link collector and you should be fine (in your small example).
Ogmios
It does the following steps?
1 Read from an Oracle table by OracleStage
2 Insert onto target Oracle table from step 1
3 Commit data
4 Read from a flat file by sequential Stage
5 Insert onto target Oracle table from step 4
6 Commit data
or it does the following steps?
[Thread 1]
1 Read from an Oracle table by OracleStage
2 Insert onto target Oracle table from step 1
3 Commit data
[Thread 2]
1 Read from the flat file by Sequential Stage
2 Insert onto target Oracle table the data from step 1
3 Commit data
and the two thread run in the same breath, so that Oracle has two transaction to manage, one for every thread created.
or....what?
I'm sorry for my english, I know it is orrible. Peraphs my question is not clear for this reason. If you don't understand I'll try to explain to you again.
Thank you
Umberto
Yes, same transaction as long as we're talking about a single OCI stage.Umbix62 wrote:But the question is. An Oracle Stage used to insert some rows from two input links includes the two insert operation in the same transaction or not?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
DataStage create only one transaction including both the insert from the two input links, it executes them in sequence, first reading data from a link, inserting them onto target table, committing data and then reading data from the second link, inserting them onto target table and, at the end, commiting data?chulett wrote:Yes, same transaction as long as we're talking about a single OCI stage.Umbix62 wrote:But the question is. An Oracle Stage used to insert some rows from two input links includes the two insert operation in the same transaction or not?
Is It that what do you mean?
Thank you
No, all I meant was exactly what I said - it all happens as a single unit of work. All the rest is under your control via the link ordering, array size and lastly transaction handling - the number of rows per transaction - that you setup in your job and the Oracle stage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Ok, I understand what do you mean.chulett wrote:No, all I meant was exactly what I said - it all happens as a single unit of work. All the rest is under your control via the link ordering, array size and lastly transaction handling - the number of rows per transaction - that you setup in your job and the Oracle stage.
Thank you veru much
Umberto