DSXchange

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

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.

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
......

...all the sequencer... or ... all the sequences....

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 ?

@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 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

Also, Once a value , fetched from the SQL server 2k5, has already been stored in a hashed file... you can use UtilityHashLookup to retrieve that value again and again....

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.

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.

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 ?

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)?

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 ?

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.

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.

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)? ...

Sorry but.. I haven't understood your suggestion..

Possibly..what Ray is suggesting is ..to dump a NULL into the seq file...anyhow you mentioned that the file is being deleted at the end ...so whats the worry ?

asitagrawal wrote:Possibly..what Ray is suggesting is ..to dump a NULL into the seq file...anyhow you mentioned that the file is being deleted at the end ...so whats the worry ?

No worries, only curiosity

/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

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)