DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 22
Location: Chennai
Points: 211

Post Posted: Thu Jan 04, 2018 9:06 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

_________________
Thanks
Nibu Mathew Babu
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42617
Location: Denver, CO
Points: 219422

Post Posted: Fri Jan 05, 2018 7:35 am Reply with quote    Back to top    

Can you not perform the check, write the results to the audit table and only then abort the job if required?

And there was no reason to post this twice, I removed the duplicate post.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1244

Points: 8164

Post Posted: Fri Jan 05, 2018 8:09 am Reply with quote    Back to top    

External source stage with a filter: wc -l /path/filename.txt output to column source_count
Connector stage SQL statement: select count(*) from table output to column table_count

now just use a transformer on that stuff and redirect output to success or failure if condition branch.
Rate this response:  
Not yet rated
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 22
Location: Chennai
Points: 211

Post Posted: Mon Jan 08, 2018 1:35 pm Reply with quote    Back to top    

Thank you for the response.But I need to write the table count to audit table and if count mismatches i need to fail the job also that is the req

_________________
Thanks
Nibu Mathew Babu
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42617
Location: Denver, CO
Points: 219422

Post Posted: Mon Jan 08, 2018 5:00 pm Reply with quote    Back to top    

Okay then, how about chiming in on my response?

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 22
Location: Chennai
Points: 211

Post Posted: Tue Jan 09, 2018 12:09 pm Reply with quote    Back to top    

Thank you for the response. I am fine with first part you suggested.

"and only then abort the job if required"

Could you please specify how to implement above step?

_________________
Thanks
Nibu Mathew Babu
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42617
Location: Denver, CO
Points: 219422

Post Posted: Tue Jan 09, 2018 12:59 pm Reply with quote    Back to top    

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 ...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 22
Location: Chennai
Points: 211

Post Posted: Tue Jan 09, 2018 7:53 pm Reply with quote    Back to top    

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

_________________
Thanks
Nibu Mathew Babu
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42617
Location: Denver, CO
Points: 219422

Post Posted: Tue Jan 09, 2018 11:30 pm Reply with quote    Back to top    

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, ma ...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 22
Location: Chennai
Points: 211

Post Posted: Thu Jan 11, 2018 12:37 pm Reply with quote    Back to top    

Thanks, I will try and let you know

_________________
Thanks
Nibu Mathew Babu
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours