Set user variable from SQL Query result set to be used later

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
elinzeyjr
Participant
Posts: 9
Joined: Thu May 11, 2017 9:18 am

Set user variable from SQL Query result set to be used later

Post by elinzeyjr »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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
elinzeyjr
Participant
Posts: 9
Joined: Thu May 11, 2017 9:18 am

Post by elinzeyjr »

Thank You all for your responses. This was a great help.
Post Reply