Surrogate Key
Moderators: chulett, rschirm, roy
Surrogate Key
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
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
MK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
4.
5.
Thanks and regards,
Munish
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.
How to do it in datastage.The job sequences picks up that value from where it's stored
4.
How to do it in datastage.delivers it to your main job as a job parameter.
5.
How to do it in datastage.That job parameter can be used to set the initial value in a Surrogate Key stage.
Thanks and regards,
Munish
MK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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
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
MK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: