Count Validatation and updation of Job even if it fails
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
Count Validatation and updation of Job even if it fails
Hi All,
This is my requriement
I need to compare file source count with target table count and write the both counts in to an audit table with status Success(If count are equal) or Fail on the other case and I also need to fail the job if count mismatch is there.
The issue I face is,If we try to achieve above with a lookup stage and fail the job if count mismatches, the counts wont be written in target table as the job is aborting.
Kindly suggest a way out
This is my requriement
I need to compare file source count with target table count and write the both counts in to an audit table with status Success(If count are equal) or Fail on the other case and I also need to fail the job if count mismatch is there.
The issue I face is,If we try to achieve above with a lookup stage and fail the job if count mismatches, the counts wont be written in target table as the job is aborting.
Kindly suggest a way out
Thanks
Nibu Mathew Babu
Nibu Mathew Babu
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
Well, here's what I was thinking.
First step would be to record the results in your audit table, so make sure when that happens that your transaction size is set to 1 for the record is not only inserted but also committed so it doesn't rollback when you fail the job. I'm thinking it could be as simple as two output links from a transformer, first one to the audit table insert and then a second one (enforced by link ordering) that is a "reject" link set to "Abort after rows=1". You shouldn't need a constraint on the audit link as it should always trigger the insert but your reject constrain should only trigger when your counts don't match.
First step would be to record the results in your audit table, so make sure when that happens that your transaction size is set to 1 for the record is not only inserted but also committed so it doesn't rollback when you fail the job. I'm thinking it could be as simple as two output links from a transformer, first one to the audit table insert and then a second one (enforced by link ordering) that is a "reject" link set to "Abort after rows=1". You shouldn't need a constraint on the audit link as it should always trigger the insert but your reject constrain should only trigger when your counts don't match.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
Thanks Craig.
As you suggested I have two links from transformer
Link1: Constraint source count=target count then to target table with update audit table with target count and status in table is success
Target DB stage Read committed,Record Count and Array size=1
Link 2
Constraint source count <> target count then to target table with update audit table with target count and abort after 1 row and status in table is failed
Target DB stage Read committed,Record Count and Array size=1
Please guide whether the above idea is what you had meant
As you suggested I have two links from transformer
Link1: Constraint source count=target count then to target table with update audit table with target count and status in table is success
Target DB stage Read committed,Record Count and Array size=1
Link 2
Constraint source count <> target count then to target table with update audit table with target count and abort after 1 row and status in table is failed
Target DB stage Read committed,Record Count and Array size=1
Please guide whether the above idea is what you had meant
Thanks
Nibu Mathew Babu
Nibu Mathew Babu
Not quite.
The suggestion was to use the first link to update the audit table regardless of success or failure. Meaning always do it, record the result and commit the transaction, match or mismatch.
Then all the second link does is abort the job when the counts don't match. It doesn't even need to have a 'real' target at the end, perhaps use a flat file writing to /dev/null or something of that nature.
The suggestion was to use the first link to update the audit table regardless of success or failure. Meaning always do it, record the result and commit the transaction, match or mismatch.
Then all the second link does is abort the job when the counts don't match. It doesn't even need to have a 'real' target at the end, perhaps use a flat file writing to /dev/null or something of that nature.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai