Page 1 of 1
Response to Private message on Update to sdkseq
Posted: Thu Aug 02, 2007 10:01 am
by gateleys
This is a Private message I received , which is as shown below. It is a query based on one of my recent posts on updating sdksequnces file.
paddu wrote:
This question is specific to you , so i am sending a private mail.Hope you help me.
Did you create this routine as a tranformer type routine?
How did you pass the input values to the Arg1 and Arg2 ? have you created afile with all the sequencenames and passed it?
i have the same requirement to do done.
Please let me know ?
Appreciate your help
Thanks
Paddu
My earlier post -
paddu wrote:
Hi, I was writing this test routine to update the SDKSequences next values. I have a bunch of sequences whose next values need to be updated. Of course, I could issue the command to do so via the Admin client, but that would take a lot of time. So, I wrote this simple routine -
Code:
NextValue = "'":Arg1:"'"
SequenceName = "'":Arg2:"'"
TclCommand = "UPDATE SDKSequences USING DICT VOC SET F1 = NextValue WHERE @ID = SequenceName;"
CALL DSExecute("TCL",TclCommand,ScreenOutput,ReturnCode)
It accepts 2 arguments Arg1 and Arg2 for NextValue and SequenceName, respectively and updates the SDKSequences file. However, I get the following error ..... seems like it doesn't like the fact that the way the Args are passed. I tried all the combinations (wit/without quotes) ... it doesn't perform the update.
Code:
Message to be logged is...
> Cmd log is: DataStage/SQL: syntax error. Unexpected symbol. Token was "NextValue".
> Scanned command was UPDATE SDKSequences USING DICT VOC SET F1 = NextValue
>
Of course, it does update the sequence if I hard code the values of the arguments (within single quotes).
How do I get to do that with the arguments?
Re: Response to Private message on Update to sdkseq
Posted: Thu Aug 02, 2007 10:11 am
by gateleys
gateleys wrote:This is a Private message I received , which is as shown below. It is a query based on one of my recent posts on updating sdksequnces file.
paddu wrote:
This question is specific to you , so i am sending a private mail.Hope you help me.
Did you create this routine as a tranformer type routine?
How did you pass the input values to the Arg1 and Arg2 ? have you created afile with all the sequencenames and passed it?
i have the same requirement to do done.
Please let me know ?
Appreciate your help
Thanks
Paddu
Based on Craig's suggestions, I changed my query. The routine in its raw form is as follows -
Code: Select all
FUNCTION UpdateSDKSeq (Arg1, Arg2)
EQUATE RoutineName To 'UpdateSDKSeq'
NextValue = "'":Arg1:"'"
SequenceName = "'":Arg2:"'"
TclCommand = "UPDATE SDKSequences USING DICT VOC SET F1 = ":NextValue:" WHERE @ID = ":SequenceName: ";"
CALL DSExecute("TCL",TclCommand,ScreenOutput,ReturnCode)
*You may embed code to handle exceptions here
Ans = 0
RETURN(Ans)
The code itself looks self-explanatory. But to answer your questions -
Did you create this routine as a tranformer type routine?
As you can see, it is a function
How did you pass the input values to the Arg1 and Arg2 ? have you created afile with all the sequencenames and passed it?
Since the SDKSequences can be accessed via a hashed file, use it (or a UV stage), with the filename SDKSequences and columns SeqName (varchar[20]) and NextValue (decimal [15]). So, your job design should be - HashedFile (pointing to SDKseq) ----> Transformer (call the above function with the 2 input cols as args)---->DummyOutput_Sequential_file.
Let me know if you have any questions.
Posted: Thu Aug 02, 2007 10:29 am
by paddu
Thanks a lot .
Thanks
Paddu
Posted: Thu Aug 02, 2007 2:52 pm
by ray.wurlod
This could also be built as a server job, containing just a Transformer stage and a Hashed File stage, that generates exactly one row containing the sequence name and new value.