DSXchange

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

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.

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.

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.

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

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