Difficulty with Nested Conditions using row count

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
cpando1974
Participant
Posts: 7
Joined: Wed Oct 11, 2017 2:55 am
Location: Nottingham

Difficulty with Nested Conditions using row count

Post by cpando1974 »

Hello

I am pretty new to DataStage and having a problem with Nested Conditions and row counts.

Basically, I have an error table that records discrepancies and need them notifying to me. I have a Nested Condition that determines if there are no records in the table then send a Successful notification else send that there are errors.

Presently I have a parallel job performing a sql query row count and pushing the result to a file, then have a parameter pointing to the file. The Nested Condition is then deciding from the parameter result what to do.

I believe parameters have to be stated at the beginning of the run and cannot change during? So the Nested Condition always picks up the result that the parameter file had to start with and not after the parallel job has been performed. I have tried to use a UserVariables Activity after the parallel job but no luck getting this to work either.

I hope I have made sense here?

I have searched the net for help with very little success, can any help or point me towards some examples or tutorials?

Many thanks
tanumoy2017
Participant
Posts: 6
Joined: Tue Oct 10, 2017 12:32 pm

Post by tanumoy2017 »

We had a similar requirement in our project when at the end of our job processing if there were any error records which came due to a reject we needed to sent a error notication or else success.

Steps :
1. After the job load is done and errors are put in the reject table, another job reads the table and output the count into a seq file and also made null to 0 for no errors (errorcount.txt)
2. In the sequential job with execute command (CheckCount) we read the file to get the count - head -1 errorcount.txt
3. Then from the previous step we pulled 2 links one to Success Mail (Field(CheckCount.$CommandOutput,@FM,1)=0) and another to Error Mail (Field(CheckCount.$CommandOutput,@FM,1)>0) and wrote the expression in the triggers
4. Last step we put 2 mail notification task for Success & Error
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I for one would like to see some details around your "no luck getting this to work" statement, specifics about what you've been trying and what not working means here...
-craig

"You can never have too many knives" -- Logan Nine Fingers
cpando1974
Participant
Posts: 7
Joined: Wed Oct 11, 2017 2:55 am
Location: Nottingham

Post by cpando1974 »

Thanks tanumoy2017. Getting a problem now where nothing is being read from the file. The file has the entry of 2 but being ignored by the conditions for diverting to the emails. Looking in the log the Output from command ====> is empty. Any ideas?

Thanks
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I use a generic routine, invoked through a Routine activity, to determine whether there are any rows in a file or Data Set. The routine also has a "first line is column headings" flag.

Code: Select all

FUNCTION FileContainsData(aDirPath, aFileName, aFirstLineIsColumnHeadings)

$UNDEFINE DEBUGGING

***   Arguments (input)
*     Pathname of directory in which file exists.
*     Name of file.
*     FirstLineIsColumnHeadings flag.

***   Return values: 
*      1 (true) if file contains data
*      0 (false) if file does not contain data.
*     -1 if the file cannot be opened.
*     -2 if the Data Set does not exist.

      Ans = 0
      vFilePath = aDirPath : "/" : aFileName

***   If file name has ".ds" suffix assume it is a Data Set and use dsrecords to get row count.
*     In this case, the first line is column headings flag is ignored.
      If Downcase(aFileName[3]) = ".ds"
      Then

         * Pick up record count from Data Set.
         Shell = "UNIX"
         Command = "`cat /.dshome`/../PXEngine/bin/dsrecords -n " : vFilePath
         Output = ""
         ExitStatus = 0
         Call DSExecute(Shell, Command, Output, ExitStatus)
$IFDEF DEBUGGING
         Call DSLogInfo("Command = " : Quote(Command) : @FM : "Output = " : Quote(Output<1>) : @FM : "Exit status = " : ExitStatus, "Debugging")
$ENDIF
         Ans = (If ExitStatus = 0 Then Not(Not(Trim(Output<1>))) Else -2)

      End
      Else

***      If file name does not have ".ds" suffix, assume it is not a Data Set and use UNIX wc -l command to get row count.

         * Pick up line count from file.
         Shell = "UNIX"
         Command = "wc -l " : vFilePath : " | tr -d \n"
         Output = ""
         ExitStatus = 0
         Call DSExecute(Shell, Command, Output, ExitStatus)
$IFDEF DEBUGGING
         Call DSLogInfo("Command = " : Quote(Command) : @FM : "Output = " : Quote(Output<1>) : @FM : "Exit status = " : ExitStatus, "Debugging")
$ENDIF

         * Make sure wc command executed succesfully.
         If Index(Output, "cannot open", 1) = 0
         Then

            * Determine result from line count.
            LineCount = Field(Trim(Output), " ", 1, 1)

            * Ignore first line if column headings.
            LineCount -= Not(Not(aFirstLineIsColumnHeadings))

            * Return 1 if line count is non zero, 0 if line count is zero.
            Ans = Not(Not(LineCount))

         End
         Else

            * Problem with wc command.
            Call DSLogWarn("Unable to process file " : Quote(vFilePath) : ".", "FileContainsData")
            Ans = -1

         End

      End

RETURN(Ans)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
cpando1974
Participant
Posts: 7
Joined: Wed Oct 11, 2017 2:55 am
Location: Nottingham

Post by cpando1974 »

ray.wurlod wrote:I use a generic routine, invoked through a Routine activity, to determine whether there are any rows in a file or Data Set. The routine also has a "first line is column headings" flag.

Code: Select all

FUNCT ...[/quote]

Thanks Ray, unfortunately I cannot see all of your reply due to the Premium notice. 

According to the logs the file is empty before it gets to the next routine. I have tested with a file where I have manually entered the number 2 and nothing else. The Output from Command in the log is still showing empty. I then pointed the ExecCommand to a location that does not exist as a test and got the error message as expected 'is not recognized as an internal or external command, operable program or batch file' to make sure that it was trying to read the file.

I am confused why nothing is coming back, I think the problem is with the ExecuteCommand but not sure what I am doing wrong?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You need to explain the commands you are using to generate the file, and to read the file.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
cpando1974
Participant
Posts: 7
Joined: Wed Oct 11, 2017 2:55 am
Location: Nottingham

Post by cpando1974 »

A Parallel Job writing to a SeqFile holds the count of the rows from the error capturing table.

Then an Execute Command calls the file by the location C:\PATH\FILENAME. This is where I am unsure, unclear whether a command is needed before file location? Searching on the web has drawn a blank so far and I have requested a Premium Membership from my Company as I cannot yet read a lot of the useful posts on here.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So the Execute Command stage only has the filename in it? You are correct in that you need a command to 'read' it and on UNIX with a file that only contains a single record with single value, you can use cat for that. It will essentially send the file contents to standard output which then $CommandOutput will pick up.

Now, if the file actually needs to be "read" because it has column headers or multiple records, field delimiters or quotes then that's a different kettle of fish.
-craig

"You can never have too many knives" -- Logan Nine Fingers
cpando1974
Participant
Posts: 7
Joined: Wed Oct 11, 2017 2:55 am
Location: Nottingham

Post by cpando1974 »

Bingo! Thank you Craig. Thanks everyone for your help.
Post Reply