Get the Row Count

jagadam · Post by **jagadam** » Thu Jul 15, 2010 2:25 pm

Hi All,

I four jobs
1) Extracting the data from the table
2) Creating an xml file.
3) Creating an xml file with empty tags.
4) Squence which kicks off all the above jobs

My requirement is that based on the number of rows returned from job1 job 2 or job 3 should run.

for example if there are zero records returned from job 1 then job3 should run and if not then job2 should run.

I seached in the forum and found that DSjoblink() is the right function to use and also found the routine below with three arguments(JobName,StageName,LinkName)

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
JobHandle = DSAttachJob(JobName,DSJ.ERRNONE)
Ans = DSGetLinkInfo(JobHandle,StageName,LinkName,DSJ.LINKROWCOUNT)
Dummy = DSDetachJob(JobHandle)

I am calling this routine through routine activity and passing the parametes as

jobname.$JobName
"StageName" (Used in job1)
"LinkName" (Used in job1).

when i kick off the job sequence the sequence is running only the first job and not going past the routine activity .

in the job sequence i have like..

jobactivity_job1-->routine activity--->jobactivity_(job2/job3).

Iam I passing any wrong params..?

I think the routine isn't working.

Please correct me if the rouinte is wrong or if there are any better ways of doing this.

Thanks

chulett · Post by **chulett** » Thu Jul 15, 2010 2:35 pm

"jobname.$JobName"?

That should be the name of the first job, either hard-coded or you can use that syntax if "jobname" is the name of the first Job Activity stage.

How are you handling the downstream jobs, trigger expressions? If so can you post them? If you are unsure what your routine is doing and the value returned is not reflected in the Sequence job's log, then add a call or two to DSLogInfo() to explicitly put some clues out there for you. You can always wrap them in DEBUG statements or remove them later once you get this all figured out.

Oh, and what stage are you pointing at in your first job?

jagadam · Post by **jagadam** » Thu Jul 15, 2010 3:24 pm

Hi chullet,

as you guessed jobname in the string "jobname.$JobName" is the name of the first job.

I am checking the trigger expressions in the routine activity.like if return value>0 then job2 otherwise job3.

I will try to put in the DSLoginfo().

And coming to the stage i tried with the ODBC stage(which is the extract plugin) and also the Dataset(to which we are writing to).

Thanks.

chulett · Post by **chulett** » Thu Jul 15, 2010 3:37 pm

Make sure you understand that the convention is "<JobActivityStageName>.$JobName", it's still unclear to me if that is what you are doing or not.

Can you detail out all of the stages in your job, please?

jagadam · Post by **jagadam** » Thu Jul 15, 2010 4:04 pm

hi chullet,

Yes I mentioned is as use got it."<JobActivityStageName>.$JobName

Here are the stages that Iam using in the jobs.

Job 1

db2-->xfm-->ds

job2

ds-->xml(o/p)-->seq

job3

rowgenerattor-->xfm-->seq

jobsequence

jobactivity1-->routineactivity-->uservariable-->uservariable-->jobactivity2(for job2)

And one more question..The stage name and link name should be hard coded right..?

Thanks

chulett · Post by **chulett** » Thu Jul 15, 2010 4:10 pm

Yes, the stage and link names need to be hard-coded in the Routine Activity stage. And try pointing 'stage' to the transformer and 'link' to the output link that goes from there to the dataset. See if that works more better.

Those functions really come from the Server world and work very nicely there, in the PX world... not so much, I'm afraid. Especially when the job it is being pointed to ran on multiple nodes. Keep trying!

Hopefully someone else will pop in with advice or an alternate approach.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 15, 2010 7:15 pm

A more flexible approach (for example per-node) is available. Will post details later (probably on the weekend). Tres busy at the moment.

jagadam · Post by **jagadam** » Thu Jul 15, 2010 8:23 pm

Hi Chulett,

Thanks for the response.

Hey Ray will be waiting for your response.

Thanks

jagadam · Post by **jagadam** » Mon Jul 19, 2010 8:32 am

Hi Ray,

Did you get a chance to look into this.

If not, could you please look into this when you get a chance.

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jul 19, 2010 5:06 pm

Yes I was able to review this problem. Craig's point about parallel execution is a red herring; the requirement is to distinguish between zero rows processed and non-zero rows processes, irrespective of the number of nodes. Therefore the routine is fundamentally correct, provided it is passed the name of an active stage to use in the DSGetLinkInfo() function.

The job name argument is - correctly - JobActivity1.$JobName and the stage and link names are hard coded. The routine activity then needs two triggers:
(custom) RoutineActivity.$ReturnValue = 0 -- leads to Job2
(custom) RoutineActivity.$ReturnValue <> 0 -- leads to Job3

You also need to disable automatic handling of activities that fail or, if you don't want to do this, create a Failure trigger (perhaps leading to a Sequencer with no output) on the Routine activity.

An alternative approach might have been that Job1 actually determines the count and stores this in its user status area. That count could be accessed as Job1.$UserStatus in a downstream Nested Condition activity.

chulett · Post by **chulett** » Mon Jul 19, 2010 5:49 pm

Red herring, eh? I hate herring.

jagadam · Post by **jagadam** » Tue Jul 20, 2010 9:46 pm

Hi Ray,

I tried this and its working fine.But,Iam getting warnings in the job sequence
This is the warning..

"JobName..JobControl (@Routine_Activity_9): Routine DSU.GetJobRowCount did not finish OK, return code = '564'"

Here 564 is the record count.

When I didn't check the option "Log warnings after activities that finish with status other than OK"

I tried to create a Failure Trigger By setting a sequencer(Any/All) to the routine activity and setting the expression type as Failed or Otherwise etc.

But this didn't supress the warning.

Iam I missing any thing.Could please correct me where Iam doing wrong.

Thanks.

chulett · Post by **chulett** » Tue Jul 20, 2010 9:50 pm

Been discussed here quite a lot.

You need two triggers, one "OK" and one "Otherwise", to convince the Sequence that you are handling the error and that it doesn't need to do any such thing automatically. Run both to a single Sequencer set to "Any" if you really don't need to branch your path based on the result.

jagadam · Post by **jagadam** » Wed Jul 21, 2010 6:34 pm

Hi chullet,

I want the following jobs to be run based on the result(return code) of the routine.

As the routine is giving the rowcount as the result it is throwing a warning message(which I have posted) in my previous response.

Ray,
I tried to do this using the $UserStatus. It seems its working but after more testing its not. Iam curious on what does the UserStatus actually return.

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Jul 21, 2010 6:50 pm

Whatever your job puts into the user status area.