I am trying to generate the surrogate key for a type one dimension. and it is an ongoing load stratergy. I am using usrrogate key generator stage. But I need to set the start value of the surrogate key so that it should start from the max(exixintg values) and start from there. They way I am trying to do is get the max(existing surrogate key) througth a lookup and stroing that into a job parameter and then using that job parameter as a start values in surrogate key.
Anyone know how to do that ?
Rao
Setting Job parameter at runtime from lookup stage
Moderators: chulett, rschirm, roy
Setting Job parameter at runtime from lookup stage
Faheem Jabbar
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use a separate job (maybe even a server job) to do the SELECT MAX(...) query, capturing the result into a file. Use an Execute Command activity in your job sequence to read that file, perhaps using a cat command. Use the $CommandOutput activity variable from that Execute Command activity to supply the parameter value in your main job.
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.
Hi,
Use Rays advice, create a multiple instance job(infact his advice of using a server job for this is very good since in this case a server job will outperform a parallel job since u r fetching a single record only). In this server job set the userstatus of the job as the max value and in the next job set the start value parameter as the user status of the generic job which calculates the max val. Do this whole thing in a job sequence. And, in the generic job set parameters for table name and surrogate key column so that u can use it for the start value retrieval for every job where u need it.
:D
Use Rays advice, create a multiple instance job(infact his advice of using a server job for this is very good since in this case a server job will outperform a parallel job since u r fetching a single record only). In this server job set the userstatus of the job as the max value and in the next job set the start value parameter as the user status of the generic job which calculates the max val. Do this whole thing in a job sequence. And, in the generic job set parameters for table name and surrogate key column so that u can use it for the start value retrieval for every job where u need it.
:D
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
As Ray Mentioned....similar to that i have and idea
1.store the maximum value in a sequential file
2. write a basic tranform function which reads the max value from the sequential file and returns maxvalue+1
3. the return value of this routine can be assigned to a variable by calling the routine thru "User variable activity".
per my conclusion, the only backdrop i would see is the amt of complexity involved.
the other way would be working with oracle sequences.
1.store the maximum value in a sequential file
2. write a basic tranform function which reads the max value from the sequential file and returns maxvalue+1
3. the return value of this routine can be assigned to a variable by calling the routine thru "User variable activity".
per my conclusion, the only backdrop i would see is the amt of complexity involved.
the other way would be working with oracle sequences.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: