Four Stars

ONE TRANSACTION for ALL CHILD SUBJOB

Hi all,
I am ETL-ing data from source db A1, A2, A3 to target db B with many subjobs & 1 final job.
Here my description:
+ subjob_01 (ETL several tables in A1, A2 to 1 table in db B)
--> subjob_02, subjob_03 are the same.
+ final job:
subjob_01 --> subjob_02 --> subjob_03 (trigger between 2 subjob: subjobok)
I have some problems:
+ if subjob_02 raise error --> can I rollback to subjob_01 ?
+ when excuting subjob_01 is complete, do it commit into db ? (because I read in "tXXXOutput" component setting and I see the attribute: "commit every" (or batch size) --> so I think, subjob will be commited after etl to "tXXXOuput".
(see pic below)
Many thanks Smiley Happy
4 REPLIES
Seven Stars

Re: ONE TRANSACTION for ALL CHILD SUBJOB

To use transactional processing, you must use tOracleConnection to establish the connection and check "Use an existing connection" in tOracleOutput. You then use tOracleCommit and tOracleRollback as appropriate.
By subjob, I think you actually mean child jobs? The former being a blue-boxed area in a job; the latter being a stand-alone job that can be called by a tRunJob.
To share a connection between multiple jobs, put tOracleConnection in the parent job and each of the child jobs and check "Use or register a shared DB connection" giving them the same "Shared DB Connection Name".
Four Stars

Re: ONE TRANSACTION for ALL CHILD SUBJOB

@alevy: thanks .
As you share "To share a connection between multiple jobs, put tOracleConnection in the parent job and each of the child jobs and check "Use or register a shared DB connection" giving them the same "Shared DB Connection Name"."
In my child jobs (subjob) have many connections to many database. Can I create 1 tOracleConnection for them?
Example:
+ SUBJOB 01 have 2 connection A, B.
=> can I create 1 connection C for A & B ?
If I have 1 connection for all subjobs, it is easy Smiley Sad . So in every subjob , I have severals connection.
Seven Stars

Re: ONE TRANSACTION for ALL CHILD SUBJOB

If you're connecting to multiple databases and you want all the writes to be included in one "transaction" then you need a tOracleConnection with a unique "Shared DB Connection Name" for each database in each job unfortunately.
Four Stars

Re: ONE TRANSACTION for ALL CHILD SUBJOB

THANKS - I WILL TRY.