DSXchange

Posted: **Mon Dec 17, 2007 2:05 pm**

Hi,
I am having a job designed. I would like to find how many number of rows were transferred to the target file. I need to write a after job subroutine and design a job and load the number of rows transferred to the target file in a seperate file.

Thanks
Kavuri

Posted: **Mon Dec 17, 2007 2:16 pm**

The approach you suggest in your subject is the correct one.

You can write the after job subroutine yourself or hire someone else to write it.

Posted: **Tue Dec 18, 2007 12:50 am**

Code: Select all

Equate RoutineName To "RecordsPerNode"
Ans = ""
RecordCount = 0
If JobName = "" Or StageName = "" Or LinkName = "" Or InterfaceFilePath = "" Or ProcessLogFileName = ""
then
     Ans = "One of the input arguments is empty."
     Goto ErrorExit
End
JobHandle = DSAttachJob (JobName, DSJ.ERRNONE)
RowCount = DSGetLinkInfo(JobHandle,StageName,LinkName,DSJ.INSTROWCOUNT)
ErrCode = DSDetachJob(JobHandle)
CountNodes = count(RowCount : ',',',')
For i = 1 to CountNodes
         RecordCount = RecordCount + field(RowCount,',',i)
Next

OpenSeq InterfaceFilePath : "/" : ProcessLogFileName To TempFile
On Error
   Ans = "Not able to open the specified file"
   Goto ErrorExit
End
Then
   writeseq RecordCount to TempFile On Error Ans = "Failed at writeseq" Then Ans="Written"
End
Closeseq TempFile
ErrorExit:
      Return(Ans)

In the above code InterfaceFilePath, ProcessLogFileName, stageName, LinkName, JobName will be the input arguments. DSGetLinkInfo() with the option of DSJ.INSTROWCOUNT (for parallel job) will give the individual row count for each node. The values will be coma separated. Hence i am using the count() function to get the number of nodes and then loop it to find the total record count for all the nodes.

May not be the best code but it is working. Will have to wait for the gurus opinions, suggestions, corrections etc on this code. Try it and let us know.

Posted: **Tue Dec 18, 2007 4:56 am**

An after-job subroutine has only a single input argument, containing anything from the Input Value field. You need to parse out the names from the InputArg argument.

You can use DSJ.LINKROWCOUNT to get the total rows across all partitions - you don't need to add the separate partitions as in the code example given.

Posted: **Tue Dec 18, 2007 7:06 am**

Code for before\after subroutine:

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

      If count(InputArg,',') <> 4
      Then
         k = 2
         goto ErrorExit
      End
      Else
         StageName = field(InputArg,",",1)
         LinkName = field(InputArg,",",2)
         InterfaceFilePath = field(InputArg,",",3)
         ProcessLogFileName = field(InputArg,",",4)
         x=""
         Ans = ""
         RecordCount = 0
         CountNodes = 0
      End
! Check to see if any of the input arguments is blank
      If StageName = "" Or LinkName = "" Or InterfaceFilePath = "" Or ProcessLogFileName = ""
      then
         Ans = "One of the input arguments is empty."
         k = 3
         Goto ErrorExit
      End

!Attach to the Job

      JobHandle = DSJ.ME

      RecordCount = DSGetLinkInfo(JobHandle,StageName,LinkName,DSJ.LINKROWCOUNT)

      x = "Total number of records = " : RecordCount

      OpenSeq InterfaceFilePath : "/" : ProcessLogFileName To TempFile
      On Error
         Ans = "Not able to open the specified file"
         k = 4
         Goto ErrorExit
      End
      Then
         writeseq x to TempFile
         On Error
            k = 5
            Goto ErrorExit
         End
            Then Ans = "Written"
      End
         Else Closeseq TempFile

      ErrorCode = 0

ErrorExit:
      ErrorCode = k

This is the code for the before/after job subroutine. The input must be Stage Name, Link name, path, and file name. These values must be coma separated without a space in between and should end with a coma. Like
Stage Name,Link name,FilePath,FileName,

FilePath and FileName are the path and name of the file to where the output must be written.

Error codes and their description:
2,3 : Blank input argument.
4 : File Path or file name not present. Note - If the file is not present in the folder then the the code will return 4.
5 : Not able to write into the file. Check file permissions.

However i am getting one warning.
"After-job routine returned error: Error variable unassigned on return from AFTER routine DSU.LinkRowCount".

Any ideas how to remove this warning?

Gurus please let me know areas of improvements, modifications.
Thanks.

Posted: **Tue Dec 18, 2007 8:30 am**

Maveric wrote:Any ideas how to remove this warning?

Without really looking at anything else, I assume it is because you are falling through your 'error exit' all the time, and when all is well 'k' is not set. Try changing the 'ErrorCode = 0' near the end to 'k = 0' and see what happens.

Posted: **Tue Dec 18, 2007 9:32 am**

Put a RETURN statement before ErrorExit:

Posted: **Tue Dec 18, 2007 7:20 pm**

Hi,
My job is a parallel Job. And here I am writing a after Job Subroutine. Do I need actually a parallel routine? or server routine is sufficient. I need to find RowCount for each link seperately, But not sum of all rowscount. Please tell me how can I do this?

I had the code from your previous postings but I am unknow where to put exactly the code? And How to link? where to collect the value of number of rows?. How to put that value in the target file. Which is a seperate file apart from the data file.

Thanks
Bhargava

Posted: **Tue Dec 18, 2007 7:48 pm**

All before/after job subroutines, irrespective of job type, are server routines. To get individual link row counts use DSJ.INSTROWCOUNT as the fourth argument of DSGetLinkInof().

Posted: **Tue Dec 18, 2007 8:30 pm**

For Testing, I had written the code like this

#INCLUDE DSINCLUDE JOBCONTROL.H
Ans=DSGetLinkInfo(DSJ.ME,"Transformer_21","InputArg",DSJ.LINKROWCOUNT)
Return (Ans)

I had created the afterJob subroutine as a server routine. Then I am having the parallel job like following which is also for test

Source file-----> Transformer ----->TargetFile1
------> Target File2
------->Target File3

Now I am trying is to pass the linknames for the targetfile1, Target file2 etc.

From where I need to call this routine?

I appreciate your help.

Thanks
Bhargava

Posted: **Wed Dec 19, 2007 12:59 am**

You can only pass them through InputArg, separated by some delimiter character (space will do). You then parse InputArg in your routine and call DSGetLinkInfo() as many times as there are links named in InputArg.

Posted: **Wed Dec 19, 2007 12:31 pm**

Hi,
I tried to write rowcount for a single link by passing parameters. I am having the after job routine code like follows:

Code: Select all

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
If count(InputArg,',') <> 3
Then 
k = 2 
goto ErrorExit 
End 
Else 
LinkName = field(InputArg,",",1) 

!InterfaceFilePath = field(InputArg,",",2) 
InterfaceFilePath="/dsd/InStore/rejects/testparam"

!ProcessLogFileName = field(InputArg,",",3) 
ProcessLogFileName="testfile.txt" 

x="" 
Ans = "" 
RecordCount = 0 
CountNodes = 0 
End 
! Check to see if any of the input arguments is blank 
If StageName = "" Or LinkName = "" Or InterfaceFilePath = "" Or ProcessLogFileName = "" 
then 
Ans = "One of the input arguments is empty." 
k = 3 
Goto ErrorExit 
End 

!Attach to the Job 

RecordCount = DSGetLinkInfo(DSJ.ME,StageName,LinkName,DSJ.LINKROWCOUNT) 

x = "Total number of records = " : RecordCount 

OpenSeq InterfaceFilePath : "/" : ProcessLogFileName To TempFile
On Error 
Ans = "Not able to open the specified file" 
k = 4 
Goto ErrorExit 
End 
Then 
writeseq x to TempFile
On Error 
k = 5 
Goto ErrorExit 
End 
Then Ans = "Written" 
End 
Else Closeseq TempFile

ErrorCode = 0 

ErrorExit: 
ErrorCode = k

But actually the file was not created, even if I create it is not writing anything to the file. Job is running fine

Can you please help me to find the solution for this.

Thanks
Kavuri

Posted: **Wed Dec 19, 2007 4:19 pm**

OpenSeq takes its Else clause (and returns 0 through Status() function) if the file is opened but does not yet exist. You have not coded for this possibility. Search the forum for a function called OpenSequentialFile() which covers all the possibliities.

Posted: **Thu Dec 20, 2007 3:49 pm**

As Maveric said the previous code is giving error. So I tried by writing shell script to get the information.

Code: Select all

#!/bin/ksh

. /opt/IBM/Ascential/DataStage/DSEngine/dsenv
ProjectName=InStore
JobName=test
StageName=tran

ORGCODE=$1
FILEPATH=$2

link=link
filename=filename
i=1
for i in 1 2 3 4
do
        link=link$i
        filename=filename$i
        i=$1+1
        #echo $link

        echo $ORGCODE >> /dsd/InStore/rejects/testparam/testfile.txt
        echo $filename >> /dsd/InStore/rejects/testparam/testfile.txt
        dsjob -linkinfo $ProjectName $JobName $StageName $link | grep "Link Row Count"|cut -d ":" -f2 >> /dsd/InStore/rejects/testparam/testfile.txt
done

and I called this shell script by using ExecSh and passed the parameters. Now I am getting the result as I expected. In the above example I had almost hard coded to get the linkInfo etc. Now I am trying to getJobInfo and then GetStageInfo and GetLinkInfo in the same shell script and write the values to the target text file.

Thanks for all of your co-operation.

Kavuri

DSXchange

After Job subroutine, DSGetLinkInfo()

After Job subroutine, DSGetLinkInfo()

Do we need parallel routine

No uotput