Surrogate Key

[Munish]

Hi Everybody,
I am facing a very common problem of capturing maximum value from table and using it as inital value for Surrogate Key Generator.

We are using Parallel Edition.

I have searched the forum but most of them is for Server addition.

Could you put some light on how to take care of this in PARALLEL Edition.

Thanks,
Munish

[ray.wurlod]

It's almost exactly the same for parallel jobs. A prior job (which may as well be a server job, since it's only going to process one row) captures the next available key and stores it somewhere (a file, its user status area, whatever). The job sequences picks up that value from where it's stored and delivers it to your main job as a job parameter. That job parameter can be used to set the initial value in a Surrogate Key stage.

[kumar_s]

What happens when we use a upstream database stage to find the max value, and pass it to surrogate key generator stage??? Sorry I dont have access to PX now.

[ray.wurlod]

You can't pass it to a property of the Surrogate Key stage.

[kumar_s]

Thanks for clarifying.

[Munish]

Hi Ray,

I tried the
1. I created Job1 which gets the max value from table.
2. Stored it in sequential file.
>>>>>>> Not sure, how user status area works. This concept is new to me. Could you please provide some more explaination on this.



however, I need a bit more explaination on
3.

The job sequences picks up that value from where it's stored

How to do it in datastage.


4.

delivers it to your main job as a job parameter.

How to do it in datastage.

5.

That job parameter can be used to set the initial value in a Surrogate Key stage.

How to do it in datastage.

Thanks and regards,
Munish

[ray.wurlod]

All of these questions can be answered by searching the forum.

You don't seem to want to use the user status area; if you do please search for DSSetUserStatus().

3. If you've stored it in a file, read it from that file (maybe using a cat command in an Execute Command activity and trimming off the line terminators).

4. Use the $CommandOutput result from the Execute Command activity.

5. Standard parameter reference; click on Insert Parameter Value button.

[kumar_s]

You need to have a seperate job with a BASIC transformer or a BASIC routine which will have simple code
Call DSSetUserStatus(Arg1)
Ans = 0.
The argument can be used as input parameter to the forth comming jobs. If you do a search on Userstatus, you can find lot many post.
But calling the job or the routine in the JobSequence in the upstream stage, the next JobActivity can get the value as userStatus of the previous stage.

[Munish]

Thanks Champs,
However I could not make much progress.

To start with

I am able to figure our how to pass on values from first job to another.
But,
coming back to first job.
Initially I was storing the max value in sequential file.
Now, if I want to store in userstatus.
What type of job should I use.
Should it be a command sequencer or
when I try to use Oracle Enterprise stage without writting to sequential file, the properties are changed.

How should I proceed.

Thanks,
Munish

[naren]

go through this

viewtopic.php?t=99399

[Munish]

Hi Naren,
I went thru this post.
In my case, I need to run the sql script to get the value.
What might be the best thing to use to get that from DB and pass on to Userstatus.

Thanks and regards,
Munish

[ray.wurlod]

Not possible from a script. Only jobs have a user status area.

[kumar_s]

Create a routine, and use a job to find the max and in transformer, call the routine to set the user status as the max value that you found out.