Execute a routine into a command stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
Execute a routine into a command stage
Hi All,
I have to set a value selected from the database into the user status. I retrieve the value (a single value) from an odbc stage, via a query on sql server 2k5.
I need to make this value available to all the sequencer this job belong to, so I thought to put it into the user status via the function DSSetUserStatus function.
What I did was to link my odbc stage to a Command Stage specifiing, but here I have two problems:
1) I set the command type to TCL, but with the following commands I get the following errors:
- Call DSSetUserStatus("test")
error:
Illegal verb "CALL".
- DSSetUserStatus("test")
error:
Command failed to execute - Verb "DSSETUSERSTATUS("DSLINK10.LOG_CD")" is not in your VOC.
- CustomUserStatus("test") #CustomUserStatus is a transform routine that call DSSetUserStatus
error:
Command failed to execute - Verb "CUSTOMUSERSTATUS("DSLINK10.LOG_CD")" is not in your VOC. .
- SUS("test") #SUS is a transform function that call DSSetUserStatus
error:
Command failed to execute - Verb "SUS("DSLINK10.LOG_CD")" is not in your VOC. .
So, is there a way to call a custom routine (or directly the DSSetUserStatus function) from the Command Stage?
2) How can I pass the input column value of the Command Stage to my command?
Thanks a lot for any help,
Ale
I have to set a value selected from the database into the user status. I retrieve the value (a single value) from an odbc stage, via a query on sql server 2k5.
I need to make this value available to all the sequencer this job belong to, so I thought to put it into the user status via the function DSSetUserStatus function.
What I did was to link my odbc stage to a Command Stage specifiing, but here I have two problems:
1) I set the command type to TCL, but with the following commands I get the following errors:
- Call DSSetUserStatus("test")
error:
Illegal verb "CALL".
- DSSetUserStatus("test")
error:
Command failed to execute - Verb "DSSETUSERSTATUS("DSLINK10.LOG_CD")" is not in your VOC.
- CustomUserStatus("test") #CustomUserStatus is a transform routine that call DSSetUserStatus
error:
Command failed to execute - Verb "CUSTOMUSERSTATUS("DSLINK10.LOG_CD")" is not in your VOC. .
- SUS("test") #SUS is a transform function that call DSSetUserStatus
error:
Command failed to execute - Verb "SUS("DSLINK10.LOG_CD")" is not in your VOC. .
So, is there a way to call a custom routine (or directly the DSSetUserStatus function) from the Command Stage?
2) How can I pass the input column value of the Command Stage to my command?
Thanks a lot for any help,
Ale
...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
DSSetUserStatus is actually a subroutine. To use it from a Transformer stage you need to embed it in a routine of type "transform function". Search the forum to find an example.
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.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Re: Execute a routine into a command stage
...all the sequencer... or ... all the sequences....Alethesnake wrote:....
I have to set a value selected from the database into the user status. I retrieve the value (a single value) from an odbc stage, via a query on sql server 2k5.
I need to make this value available to all the sequencer this job belong to
......
What I have understood is that the value selected from database needs to be made available to remaining sequences..that is you want to pass the value "fetched" to all other sequences, right ?
Also what is mean by "this job"... which job is being referred here ?
Share to Learn, and Learn to Share.
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
@ray
I read in other posts that it's not possible to call DSSetUserStatus directly into a ExecTCL.
I Tried in fact to embedding it firstly into a routine of type "transform function" and secondly into a transform function but in both cases I get the above reported errors.
I read also that I must define the custom function before using it into an after/before stage but I haven't understood where I must define it..
note: I'm not using a transformer stage, I'm trying to call my custom-set-us-function inside a "Command Stage" placed as last stage of my job.
@asitagrawal
The value I put in the user status will be useful to several job activities of the same sequence (I fill the user status in the first job activity of the sequence)
Thanks
I read in other posts that it's not possible to call DSSetUserStatus directly into a ExecTCL.
I Tried in fact to embedding it firstly into a routine of type "transform function" and secondly into a transform function but in both cases I get the above reported errors.
I read also that I must define the custom function before using it into an after/before stage but I haven't understood where I must define it..
note: I'm not using a transformer stage, I'm trying to call my custom-set-us-function inside a "Command Stage" placed as last stage of my job.
@asitagrawal
The value I put in the user status will be useful to several job activities of the same sequence (I fill the user status in the first job activity of the sequence)
Thanks
...
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
I have one approach for this:
1. Job1:
Store the value fetched from SQL server 2k5, into a hashed file, keyed by single key and some constant value, say "Dummy".
2. Routine Stage - R1:
R1 will use UtilityHashLookup routine, to fetch the value back from the Hashedfile (here provide key value as "Dummy" )
3. Create a custom before/after subroutine, which accepts a parameter and calls DSSetUserStatus function with the parameter value, I mean something like this:
4. Invoke this in the Before subroutine of the job, for which the user status needs to be set...
So, the job design that comes out is:
This is the approach I have used....
May be a better approach exists.. please suggest..
HTH
1. Job1:
Store the value fetched from SQL server 2k5, into a hashed file, keyed by single key and some constant value, say "Dummy".
2. Routine Stage - R1:
R1 will use UtilityHashLookup routine, to fetch the value back from the Hashedfile (here provide key value as "Dummy" )
3. Create a custom before/after subroutine, which accepts a parameter and calls DSSetUserStatus function with the parameter value, I mean something like this:
Code: Select all
Call DSSetUserStatus(InputArg)
ErrorCode = 0 ;* set this to non-zero to stop the stage/job
So, the job design that comes out is:
Code: Select all
Job1----->R1---->JobForUserStatus
May be a better approach exists.. please suggest..
HTH
Share to Learn, and Learn to Share.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
Hi asitagrawal,
Thanks for your suggestion but my aim is to avoid using a support file.
Actually I make in this way:
In a derivation of the transformer I pass the result of my query through a transform function that set the user status, than I save the value into the seq file.
This job is the first of a sequence, and I pass to the other job activities of the sequence my saved value by the way of <FirstJobActivityName>.$UserStatus.
Everything runs well, but being the seq file in the first job (the one where I set the User Status) superfluous (or better 'never used' in the flow) I'd like "to refine" my work by replacing it with a Command Stage where I set the User Status, i.e.:
But I've some problems (above reported) in calling the (transform function type) routine that set the User Status in the Command-stage command field.
Thanks for your suggestion but my aim is to avoid using a support file.
Actually I make in this way:
Code: Select all
odbc-stage -----> transformer ----> sequential file
This job is the first of a sequence, and I pass to the other job activities of the sequence my saved value by the way of <FirstJobActivityName>.$UserStatus.
Everything runs well, but being the seq file in the first job (the one where I set the User Status) superfluous (or better 'never used' in the flow) I'd like "to refine" my work by replacing it with a Command Stage where I set the User Status, i.e.:
Code: Select all
odbc-stage -----> command-stage
...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Now you're getting well into the realm of "outside DataStage" - the Command stage executes a command, not a subroutine. You would need to create a command in code (and therefore be relying on a "support file", albeit one containing the command source code and another containing the compiled command). DataStage itself does not have the facility for creating "main" programs, though it can be done - but you would be introducing a maintenance nightmare. Are you sure you want to go down this path? It would be much easier to include a Transformer stage and invoke setting the user status in that.
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.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Ray has pointed out correctly that the command stage executes a Command not a DataStage routine...
Also, I wonder if we can have a design, where there is an Input Stage, a transformer , BUT no output !! (This, I am saying, becuase the seq file in your current design is being considered as Superfluous).
You are looking for a refinement or is there any other reason behind the removal of that seq file ?
Also, I wonder if we can have a design, where there is an Input Stage, a transformer , BUT no output !! (This, I am saying, becuase the seq file in your current design is being considered as Superfluous).
You are looking for a refinement or is there any other reason behind the removal of that seq file ?
Share to Learn, and Learn to Share.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
You're right, a stage where you can i.e. execute a stand-alone sql command without the necessity to place at least a link would be very useful.asitagrawal wrote:Ray has pointed out correctly that the command stage executes a Command not a DataStage routine...
Also, I wonder if we can have a design, where there is an Input Stage, a transformer , BUT no output !! (This, I am saying, becuase the seq file in your current design is being considered as Superfluous).
You are looking for a refinement or is there any other reason behind the removal of that seq file ?
Coming back to my question it's just a refinement because I don't really need that seq file. Actually I delete it at the end of my laoding process together with the other "working" seq files.
Sorry but.. I haven't understood your suggestion..ray.wurlod wrote:Rather than removing the file, why not use the target Sequential File stage to append to the "black hole" (/dev/null in UNIX, .\NUL in Windows)? ...
...
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
/dev/null is a special device on UNIX into which you can dump any data you like - it will never be seen again (which explains why I called it a "black hole"). The equivalent on Windows is a file called .\NUL
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: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
Thanks Ray,
I didn't know the concept of balck hole in OS', .\NUL works perfectly.
Just related to this argument, I tried also to trigger off an ODBC stage from a seq file pointing to .\NUL, because sometimes I faced the necessity to execute a sql dml "stand alone" (an update or a delete with no input parameters). My simple job is:
Seq File (.\NUL) ---> ODBC
but the file figure obviously empty and the user defined sql statement nested into the odbc stage is not executed.
Is there an "elegant" and practical way to do this? (actually I use a before-created sequential file containing a single row with a dummy value)
I didn't know the concept of balck hole in OS', .\NUL works perfectly.
Just related to this argument, I tried also to trigger off an ODBC stage from a seq file pointing to .\NUL, because sometimes I faced the necessity to execute a sql dml "stand alone" (an update or a delete with no input parameters). My simple job is:
Seq File (.\NUL) ---> ODBC
but the file figure obviously empty and the user defined sql statement nested into the odbc stage is not executed.
Is there an "elegant" and practical way to do this? (actually I use a before-created sequential file containing a single row with a dummy value)
...