Controlling job flow by counting rows in hashed files - ???

[jdmiceli]

Hello to all the DS Gurus!

I have an interesting situation and I would like opinionions from those who may have dealt with this type of situation. Hopefully, this isn't just me having a mental speed bump and overcomplicating things.
(That would be embarassing)

Here is the background:
I have 16 companies that run the same coded jobs. All filenames, regardless of whether they are sequential files or hashed files, are dynamically created based on parameters passed in through Ken Bland's job control architecture. Given the 194 tables at 11 jobs per table that have to be processed for each company, this equates to a large number of jobs and files being processed each night. I have about a 5 hour window to process all systems to completion. I am rewriting a process that is currently being done by SQL Server 2000 DTS packages. I need to rebuild the process as it will be be moving to another RDBMS at some point in the future and I don't want to do this twice as changes are required now.

Here is the need:
I am looking for a way to stop the process for tables that don't need to run. I figure the best way to do this is to count the number of rows in the surrogate key hashed files (Step 3 of 11). If there is more than one row in the Inserts or Updates hashed files, then the jobs need to run. If not, then the rest of the processing for that table and that company should stop.

The names of the files are dynamic and pathed, so using the SETFILE command to make a VOC entry just to get a count and then having to delete any entries as soon as processing is done seems inefficient in my mind.

I believe the entries have to be out of the VOC before my cleanup process runs. I am in the process of building an app in Perl to go and move all files built for a particular batch into a tarball, zip it and store in an archive directory until it has been backed up. Then it is deleted. (My version of housekeeping).

I am open to any other creative/mundane suggestions/ideas and would appreciate any input our community could offer.

[chulett]

If you really don't need to know the actual count, but just the fact that rows were written to particular hashed files, investigate the DSGetLinkInfo function with the DSJ.LINKROWCOUNT InfoType.

A generic routine can leverage that and you can make your decision based on the row count being 0 or > 0.

[jdmiceli]

Thanks, Craig! I will check into that as soon as I can.

[jdmiceli]

Thanks, Craig! I will check into that as soon as I can.

[jdmiceli]

I am marking this as resolved and thought I would post the solution for others to use if desired. I ended up writing a little routine to do as Craig suggested. The code is as follows:

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

* JobName = Arg1
* StageName = Arg2
* LinkName = Arg3

handleJob = DSAttachJob(JobName, DSJ.ERRFATAL)

If NOT(handleJob) Then
   Ans = -99
   End 
Else
   Ans = DSGetLinkInfo (handleJob, StageName, LinkName, DSJ.LINKROWCOUNT)
   End 

Return(Ans)

DetachResult = DSDetachJob(handleJob)

The routine is written as a Transform Function. Here is the rest of the documentation I wrote in the description area:

This routine allows a developer to pass in a job name, stage name and link name to get determine whether or not there was a count greater than zero for a particular link.
This routine was designed to be used from a Sequence Job to evaluate jobs it is running. Normally, this cannot be done.

Remember: the count comes from the transformer link and not the target link.

Three arguments are passed in the routine.
1. The job name
2. The stage name
3. The link name

Example:

Call BTSGetLinkCnt("STEP3_Parse_SKeys_ADM_USER","xfm_Ins_SKey_ADM_USER","ins_lnk")

Return codes are:
0 = positive row count greater than zero was found
1 = zero row count was found
-1 = error condition of some sort was found

Hope this helps!

[chulett]

Return codes are:
0 = positive row count greater than zero was found
1 = zero row count was found
-1 = error condition of some sort was found

I don't see where this is happening in your code. Without intervention, you get the actual count returned, not simply a zero or a one.

[chucksmith]

I have a nice routine that does the same, for anyone who is interested. I usually use it in job sequences, but it has been used as a derivation.

You will find it on the DataStage Tools page of www.anotheritco.com .

[jdmiceli]

Hi Gents!

Craig is correct! I forgot to update the documentation after I corrected the code during my development of it (my baaaad! )

The actual return codes should be:

any number greater than zero = positive row count was found
0 = zero row count was found
-99 = error condition of some sort was found

The routine is built to return the number of rows to the calling stage.

Sorry for the confusion and thanks for catching it. I never want to be responsible for mis-information.

[sjutba]

I have a nice routine that does the same, for anyone who is interested. I usually use it in job sequences, but it has been used as a derivation.

You will find it on the DataStage Tools page of www.anotheritco.com .

----
I invoked this function in a routine activity stage in my control job but it always displays the message that the job did not finish ok because its return code is equal to the row count. How do we handle this so that it will not flag it as an error.

[chulett]

Please start a new thread of your own rather than hijack this Resolved one. When you do, post the full error message you are getting.