DSXchange

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

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.

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.

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.

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

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

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?

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

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?

Yes, same transaction as long as we're talking about a single OCI stage.

chulett wrote:

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?

Yes, same transaction as long as we're talking about a single OCI stage.

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?

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.

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.

Ok, I understand what do you mean.

Thank you veru much

Umberto