Routine which sums up is hanging

[aneesh5142]

Hi,

We are currently using server jobs of 7.5.2 version of Datastage.
We have a routine which counts teh number of incoming records and also sums up the value present in the one of teh input column.It was working fine before.But since last few months,the job simply hangs.
Can you please suggest us as to what can be the probability of the issue or what can be the posible approach.Please let me know if you need more info.

[priyadarshikunal]

you have not given us enough information for suggesting the answer. Is it the routine which is hanging or the job itself? If routine, what is being done in there? i think attach job and detach job have a time out so it would not hang for ever. what else is there?

[aneesh5142]

Hi,
Its the routine which is hanging.We pass a file as a parameter to this routine.
And what actually happens in Routine is ,it counts the total number of records present in the routine and the it also summates the last four digits of one column in that input file.
Since past few months the amount of data coming in in that input file has increased and then started hanging.

[priyadarshikunal]

Or may be its not hanging and just taking a lot of time to process the file? do you shell out to unix for doing the summation?

[chulett]

First off, not sure why you aren't using a job to do this rather than a routine. Regardless, I doubt any of your questions can be answered without you posting the routine code.

[aneesh5142]

Hi Craig,
Below is the routine which is hanging while trying to process huge data.PLease let us know if anything can be tuned to perform better for huge loads.

Code: Select all

   DEL = "/" ; TOT = 0

      FILENAME = argFileName
      IF FILENAME = "" OR FILENAME = @NULL THEN FILENAME = "INPUTFILE_FF"

* Check for last character being a /

      IF RIGHT(argPath,1) = "/" THEN
         FILEPATH = argPath:FILENAME
      END ELSE
         FILEPATH = argPath:"/":FILENAME
      END


* Execute the Unix command and bring the results back

*     sCommand = "grep ^20 " : FILEPATH : " | cut -c19-22"
      sCommand = "awk '/^20/' " : FILEPATH : " | cut -c19-22"
      CALL DSExecute("UNIX", sCommand, ScreenOutput, SystemReturnCode)


* Check for invalid path or file name, or empty file

      IF INDEX(ScreenOutput,"Cannot open",1) OR LEN(ScreenOutput) < 4 THEN Ans = "0":DEL:"0" ELSE


* Now whizz through the Results counting rows and summing the relevant values

         NOAS = DCOUNT(ScreenOutput,@AM)
         FOR NOA = 1 TO NOAS
            IF NUM(ScreenOutput<NOA>) THEN TOT += ScreenOutput<NOA>
         NEXT NOA

         WC = NOAS - 1                   ; * ignore the last carriage return, line feed

         Ans = WC:DEL:TOT

      END

[chulett]

OK... so you are dumping a portion of the file into the dynamic array that DSExecute returns and then looping through that. I can see that technique 'working' for small volumes but would never had put something like that together. You are probably hitting the upper limit of what that dynamic array can handle.

Let awk do the sum and just pass the result back to the routine.

[ray.wurlod]

FILENAME = @NULL is invalid. You can use either ISNULL(FILENAME) or FILENAME = @NULL.STR

Using REMOVE rather than DCOUNT and a FOR loop would be more efficient.