KeyMgtGetNextValue

[sheema]

I have job where in i to generate a surrogate Key,so i have used KeymgtGetNextValue('seq') in my transformer.But now i would like to extract the max value of the surrogate key from the table and increment the Surrogate key from that value.Which is the best possible way to do this.

Thanks
sheema

[chulett]

In a nutshell - Ditch the routine. Create a job to select the value and store it somewhere - could be a file, USERSTATUS, whatever. Then retrieve it and pass as a parameter to your job or retrieve from inside your job. Increment from there.

The USERSTATUS method has been discussed here ad nauseum, a search will turn up the gory details.

[sheema]

I have created a job,where i am taking the maximum(key) into a sequential file.Now i want to use this max value in the another job,where i have a odbc stage as source and oracle stage as target (where i need to generate surrogate key).How do i retreive the above value in this job.

Thanks

sheema

[narasimha]

Use Job sequence to achieve it.
You can write a script to read the contents of the file, pass the output of the command as a parameter to the next job.
Again, the solution provided by Craig is the the easiest to implement, give that a shot.

[swades]

Hi
Try this one which Explain A to Z about how to read that Max value from flat file and pass that value as parameter to next Job in Sequence.

http://dsxchange.com/viewtopic.php?t=10 ... 17d6665f09[/url]

[DSguru2B]

Swades, append www before dsxchange in your website address. This way it will take us directly to the page and not to the login page.

[sheema]

DSguru2B,Is there any way that i can do it without using a sequence.

[chulett]

Sure - as long as you don't need it in a Job Parameter, for use in a database query for example.

Create a stage variable. Use a routine to capture the value and call it in the Initial Value column of the variable. That routine could simply 'cat' or 'type' the file and capture the output.

It is what is discussed (amongst other things) in the link that swades posted where they learned the same thing. To avoid the 'Login prompt' problem, right-click to copy the shortcut then paste it in the address bar. Add the www to the front before clicking 'Go'. This for IE, adjust as needed for other browsers.

[DSguru2B]

Use the following routine in the initial value of a stage variable.

Code: Select all

      Function GetMaxDbValue(Arg1)
      ShellType="UNIX"
      Command="cat ":Arg1
      Call DSExecute(ShellType, Command, Output, SystemReturnCode)
      Ans=Output<1>

Where Arg1 is the fully qualified path of your text file that has only a single value, which is the max key. Now your stage variable will have the max key. You can increment it by one for every incoming record.

[sheema]

Thanks, I will try this function.

[sheema]

That's working perfect.

In the first Stage variable MaxValue i assigned the MaxValue using the function,and I am using the following Stage Variable to increment

If @INROWNUM=1 then MaxValue+1 else MaxValue+@INROWNUM.

Thanks a lot for all the help.

[DSguru2B]

To make it even more easier you can right click on your stage variable, choose "Stage Variable Properties". You will see your stage variable name and in the second colum where it says Initial value. Call that function. This way the initial value of the stage variable will be set to whatever is in your file. Now you can just specify the derivation MaxValue+@INROWNUM.

[sheema]

oh,yes.I did forget about that.Thanks,now i have changed it.

But when i am assigning my function to the stagevariable it is coloured red.I have checked the Function name ,it is spelled right.I do not get any compilation errors or run time errors.The job is running fine,but the stage variable is coloured red.

[DSguru2B]

Did this happen after assigning the function to the initial value? What version of DataStage are you on?

[sheema]

yes,it happened when i assigned the function to the first StageVariable.
And when i am assigning this first stage variable to the Initial Value of Second stage variable ,it is saying Variable StageVar1 not defined.

I am on Version 7.5.2.