Page 1 of 1
Getting Max Row From DB2 To Be Used DownStream
Posted: Wed May 23, 2007 12:35 pm
by 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
Posted: Wed May 23, 2007 1:04 pm
by narasimha
UserStatus is a good candidate in such situations.
Posted: Wed May 23, 2007 1:07 pm
by 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.
Posted: Wed May 23, 2007 1:14 pm
by narasimha
My bad, did not check the "Job Type".
Not sure why this feature was excluded from the Parallel jobs.
Getting Max Row From DB2 To Be Used DownStream
Posted: Wed May 23, 2007 1:42 pm
by dtatem
DSguru2B wrote: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
Posted: Wed May 23, 2007 1:45 pm
by chulett
DSguru2B wrote: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.
Posted: Wed May 23, 2007 1:54 pm
by DSguru2B
So, a px job cannot be used. But as you said, a server job is a perfectly viable solution.
Posted: Wed May 23, 2007 2:40 pm
by 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.
Getting Max Row From DB2 To Be Used DownStream
Posted: Wed May 23, 2007 2:51 pm
by dtatem
ray.wurlod wrote: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
Posted: Wed May 23, 2007 3:23 pm
by ray.wurlod
You are joining on the constant value 'X'.
Posted: Thu May 24, 2007 7:15 am
by DSguru2B
ray.wurlod wrote: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.
Posted: Thu May 24, 2007 2:36 pm
by 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.
Posted: Thu May 24, 2007 5:34 pm
by 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.
Posted: Sun May 27, 2007 10:29 pm
by 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
Posted: Sun May 27, 2007 11:06 pm
by 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.