DSXchange

I have a job in a sequencer where I need to read the number of rows
in a table and based on that populate an user activity variable in the
sequencer. I am using the job to read the number of rows and put it in a
sequential file. I am then using an Execute Command stage to read the
value from the sequential file and populate the user activity variable.
Is there a more efficient way?

You can read the "number of rows in a table" and directly write it to the DSSetUserStatus in the job.
(You need to write a simple routine)
Check this post

You can use this UserStatus later in your Job Sequence.

This way you can skip the Execute Command stage.
Lets wait on more efficient way, implemented by others

I am using PX jobs so using DSSetUserStatus is not an option. What if I
write a custom server routine? I am thinking of something like this:

FUNCTION UpdateRowCount(Arg1)
Call DSSetUserStatus(Arg1)
Ans = Arg1
RETURN(Ans)

Now, which job's user status would this set? In my sequencer, I have,

JobActivity1 ----> JobActivity2

JobActivity1 calls a job which gets the number of rows using an Oracle
stage and puts it in a sequential file. JobActivity2 needs this value. I am
thinking may be JobActivity1 should be a server job. But how do I get the value so that JobActivity2 can use it?

splayer wrote:I am using PX jobs so using DSSetUserStatus is not an option.

Are you and the OP (abc123) the same?

In JobActivity 2, when double click the expression window open, right click on it you will find 'Activity Variables'. Choose that, you will see 3 options. One of them will be $UserStatus. Use that.

I wrote a small routine called GetRowCount. How do I get the output of JobActivity1 so that I can set the UserStatus for this job activity? I would assume that there would some posts similar to this.

Post your routine. You can call DSSetUserStatus() function within that job to set the user status. And then retrieve it in a sequence job as i explained in my previous post.

Not in a Parallel job.

This is the routine:

FUNCTION UpdateUserStatus(intRowCnt)
Call DSSetUserStatus(intRowCnt)
Ans = intRowCnt
RETURN(Ans)

I can create a server job if needed as I am just doing a SELECT COUNT. However, I don't understand where in the job I need to call it. I guess I need a tranformer.

Yes. Inside the transformer. Call your routine inside the transformer.

The method you're using (via a file) is adequately efficient.

Even a job's user status area involves writing to and from disk, so there's no big gain there. The main additional cost is the additional file opens and closes (a total of four operations, and thus negligible).

Ok. So here is the interesting part. My server job has:

Oracle stage----> Transformer ----> SeqFile

However, the SeqFile stage is completely unnecessary but I am forced to use it as I don't have the output side without it. In the derivation of the output column, I execute the routine which sets the userstatus of the job. Is there a way around it without adding an extra seq file stage?

You need to add a Sequential File Stage or something else as an Output.
But you have the option to write the output to /dev/null with the Sequential File Stage

In the file name space use this /dev/null

Incidentally, none of this is possible in a sequencer.

It is, however, possible in a job sequence.

A sequencer is a component in a job sequence that makes an "any or all" decision based on whether its inputs have fired.

Alternatively BASIC Transformer can also be used in PX job for the same reason. Or the rowcount can be written into a Sequential file, and Execute command activity can be used to read and pass it as a variable.