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
elinzeyjr
Participant



Joined: 11 May 2017
Posts: 9

Points: 162

Post Posted: Wed May 17, 2017 2:53 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
I have a request that requires us to pull the max value of a field from a table which will be incremented and appended to the end of records which are sourced from the mainframe downstream in the sequence. Is it possible to set a user variable to the max value and use it later down the line when processing the EBCIDC file? Or do we need to pull the max value, write it out to a file, then use it as a lookup file downstream?

Any feedback is appreciated. -elinzeyjr
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42151
Location: Denver, CO
Points: 216399

Post Posted: Tue May 23, 2017 2:16 pm Reply with quote    Back to top    

Did you get a resolution to this? Either way should be possible but I would suggest a separate job/task do the "get max" part and then pass it to the job as a Job Parameter. And I assume this methodology would only be valid if this process is loading the table in isolation, meaning no other process is loading to the target at the same time.

_________________
-craig

Can't keep my eyes from the circling skies
Tongue tied and twisted just an earth bound misfit, I
Rate this response:  
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 614
Location: Malvern, PA
Points: 5825

Post Posted: Tue May 23, 2017 2:57 pm Reply with quote    Back to top    

I strongly suggest writing to a file to be used in the later job, rather than an in-stream parameter. Quite simply, a job abend means you must re-run the query to get the max value, and if you're in a "live" environment you run the risk of getting a different result.

Rather than add clauses to your SQL to avoid that, writing to a file means you have it for recovery from an abend.

_________________
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: http://www.dsxchange.com/viewtopic.php?t=143596
Rate this response:  
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42151
Location: Denver, CO
Points: 216399

Post Posted: Tue May 23, 2017 5:42 pm Reply with quote    Back to top    

I'm assuming you're not strictly addressing the "max value" aspect of this for the target but are advocating the landing of the source data before starting the load so you have a static source in the event of an abend. If so, I agree 100% with that... for whatever that is worth. Wink

_________________
-craig

Can't keep my eyes from the circling skies
Tongue tied and twisted just an earth bound misfit, I
Rate this response:  
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 597
Location: BRENTWOOD, TN
Points: 6741

Post Posted: Fri Jun 02, 2017 4:05 pm Reply with quote    Back to top    

You can write a DS Basic routine using BCI and the ODBC api to retrieve a database value from an ODBC compliant database - but you must strictly control the output returned by such a routine which calls embedded SQL. And if you want to pass arguments to the SQl within the routine - you must properly escape it . You must also unlock the license associated with the Data Direct drivers
Rate this response:  
elinzeyjr
Participant



Joined: 11 May 2017
Posts: 9

Points: 162

Post Posted: Thu Jun 22, 2017 6:46 am Reply with quote    Back to top    

Thank You all for your responses. This was a great help.
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