Getting Max Row From DB2 To Be Used DownStream

[dtatem]

I want to get the max(row_id)+1 from a db2 table then use this value as a parameter to pass into my surrogate stage.. what is the best way to implement this?

I can create the DB2 job first to get the result...but then I want to use that result downstream, how would I pass it to be used downstream?

Thanks

dtatem

[narasimha]

UserStatus is a good candidate in such situations.

[DSguru2B]

USER STATUS is not present in parallel jobs.
For a parallel job. You can load the value into a flat file and then use execute command stage to cat the value and pass it as a parameter value to your next job, via job sequence.

[narasimha]

My bad, did not check the "Job Type".
Not sure why this feature was excluded from the Parallel jobs.

[dtatem]

USER STATUS is not present in parallel jobs.
For a parallel job. You can load the value into a flat file and then use execute command stage to cat the value and pass it as a parameter value to your next job, via job sequence.

Thanks, seems kinda complex can you provide some additional information? Kinda new at this


dtatem

[chulett]

USER STATUS is not present in parallel jobs.

So? Use a Server job to get the max value. Have it pass the value via Sequence job and USERSTATUS to the PX job.

[DSguru2B]

So, a px job cannot be used. But as you said, a server job is a perfectly viable solution.

[ray.wurlod]

Rubbish! A parallel job can certainly be used. Put a DB2 stage feeding a reference link to a Lookup stage, and execute the query in the DB2 stage.

Code: Select all

SELECT 'X', MAX(surrkey) + 1 FROM tablename

The trick is to make sure that the reference input to the Lookup stage uses Entire partitioning, so that the same initial value is available on all partitions.

Upstream use a Column Generator stage to generate the constant value 'X'. Use this as the lookup key.

Downstream, in a Transformer stage, you increment as required; you will need an expression based upon the partition number and partition count to guarantee uniqueness. Search the forum or Vincent's blogs; the expression has been given a number of times before.

[dtatem]

Rubbish! A parallel job can certainly be used. Put a DB2 stage feeding a reference link to a Lookup stage, and execute the query in the DB2 stage.

Code: Select all

SELECT 'X', MAX(surrkey) + 1 FROM  ...[/quote]

with the lookup stage, what am I joining on? I am not sure on this...if you can mock up a dsx and email it to me that would be great


dtatem

[ray.wurlod]

You are joining on the constant value 'X'.

[DSguru2B]

Rubbish! A parallel job can certainly be used.

....and who said it cant be. The job type argument was based on specifically using USER STATUS. Any other method, including the one you advised, has no relation to the "USER STATUS with Parallel Job" discussion.

[ray.wurlod]

Nothing to stop you using the user status area in a parallel job. Though you will need to create a parallel routine that invokes DSSetUserStatus() - the one from the C-callable API.

[chulett]

Or - I assume - use a BASIC Transformer in the PX job, with all the caveats that brings to the table. It's not that Parallel jobs "don't have a user status area" - they do. It's just that it is not quite as simple to get to in a Parallel job as it is in Server.

[dtatem]

Ray:

Getting back to your reply on "Rubbish! A parallel job can certainly be used. Put a DB2 stage feeding a reference link to a Lookup stage, and execute the query in the DB2 stage. Code: SELECT 'X', MAX(surrkey) + 1 F ..."

If I understand what you are referring to, I use a sequential File going into a lookup stage with the DB2 statement you mentioned. In the sequential File I Have one column 'X' joining to the DB2 stage which also have a colum 'X'

Is this is what you mean?

thanks,
dtatem

[ray.wurlod]

Not quite.

What I mean is a new column, generated in a Column Generator stage (that is, not in the file itself). The column name can be anything you like; the value in it must be 'X' for every row.