Calling one Job from another Job....

nvkravi · Post by **nvkravi** » Thu Jul 31, 2003 3:26 am

Hey All,

I doing a Job J1 in that I am passing one Source Column value to Target Column. So, When the Source Column Pass "E" or "-1" to Target column, I want to go to Another job and Insert Log an error to this particular column. For example,

If (a < b) Then
-- Statements
End Else
-- Here I have to call another Job.

Is this possible in Transformer Stage? Are Can make any routines for this? Pls give your suggestions or let me know how to do this in DataStage?
Right now Im using DataStage 5.2 in Win 2000. Thanx in advance.

Ravi

chulett · Post by **chulett** » Thu Jul 31, 2003 6:24 am

Unless I'm missing something here, there's no need for another job to accomplish what you need. Simply establish another target in the job for your error log and then use a constraint in your transform to only populate it when your error condition occurs.

-craig

ray.wurlod · Post by **ray.wurlod** » Thu Jul 31, 2003 4:56 pm

There's not really enough detail in your description of what you want to do. When you say "log error" for example, do you mean log an entry into the job log, or write a row into a file?
If the latter, as Craig suggests, all you need is a Transformer stage with two outputs. One output has a constraint (a < b) while the other output has a constraint (a >= b). The second of these outputs writes to a text file (for example), and you can derive the text for its columns on the output columns grid.
Why do you claim that you need another job? Surely another stream of processing in the same job would suffice?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

nvkravi · Post by **nvkravi** » Thu Jul 31, 2003 10:32 pm

Hey Ray, Craig

Thanx ur respond. Yes you are right I want log an Error for column. That means here Im taking one Lookup also. For Example,

Master_table.column1 = Lookup.column1 then retrieve Lookup.column2 and pass to Target table.

So here Lookup.column2 is not available then display "-1" to that particular target column and log an Error to "ErrTable" (In Oracle) table. So The ErrTable Columns are Linkname,StageName,JobName,TableName,ErrorID.
So, Suppose I have 1000 records passing to Target in that Target Column displayed 10 records for "-1" Then I have to log an error for 10records.

Hope this will clear to understand. So that I am planning to do this other job and Calling that job into other Job transformation.

So, If anybody have easy method to do, pls suggest me. Thanx in Advance.

Ravi

nvkravi · Post by **nvkravi** » Thu Jul 31, 2003 10:33 pm

Hey Ray, Craig

Thanx ur respond. Yes you are right I want log an Error for column. That means here Im taking one Lookup also. For Example,

Master_table.column1 = Lookup.column1 then retrieve Lookup.column2 and pass to Target table.

So here Lookup.column2 is not available then display "-1" to that particular target column and log an Error to "ErrTable" (In Oracle) table. So The ErrTable Columns are Linkname,StageName,JobName,TableName,ErrorID.
So, Suppose I have 1000 records passing to Target in that Target Column displayed 10 records for "-1" Then I have to log an error for 10records.

Hope this will clear to understand. So that I am planning to do this other job and Calling that job into other Job transformation.

So, If anybody have easy method to do, pls suggest me. Thanx in Advance.

Ravi

nvkravi · Post by **nvkravi** » Thu Jul 31, 2003 10:45 pm

Hey,

Here is my exact process..

If the Error Condition occurs then Insert default value ("-1") to target table and Log an error in Err table. I have already given my ErrTable column names in the above Message.

So, Pls give your Suggestions how to do in DataStage? Right now Im using DataStage 5.2 on Win 2000.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 01, 2003 12:50 am

The Transformer stage performs the lookup. There is a reference key expression "MasterTable.Column1" for the reference key "Lookup.Column1". The reference input link returns Column1 and Column2 from the lookup table. (Here I've used MasterTable and Lookup as the names of the primary and reference input links respectively.)

The Transformer stage has two output links, let's call them OutLink1 and OutLink2.

OutLink1 is the "success" output link, taken when the lookup is successful. It has a constraint expression that identifies that the lookup succeeded, for example Not(Lookup.NOTFOUND) where NOTFOUND is a link variable, or Not(IsNull(Lookup.Column1)). Propagate Lookup.Column2 onto this output, which connects to TargetTable. Alternately, if you need the error rows propagated to TargetTable as well (which your description suggests), have no constraint expression at all on this link, and derive Target.Column2 with a conditional expression such as (If Lookup.NOTFOUND Then -1 Else Lookup.Column2).

OutLink2 is the "failure" output link, taken when the lookup is not successful. It has a constraint expression that identifies that the lookup failed, for example Lookup.NOTFOUND or IsNull(Lookup.Column1). Propagate anything you like along this link, which probably connects to ErrTable, so that you don't need any -1 on OutLink2. Output derivations can be:
LinkName DSLinkName (macro)
StageName DSStageName (macro)
JobName DSJobName (macro)
TableName "MasterTable" (or job parameter)
ErrorID -1 or DSLinkLastError (macro)

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518