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?
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 -
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.