Page 1 of 1
Routine which sums up is hanging
Posted: Wed Nov 06, 2013 5:05 am
by 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.
Posted: Wed Nov 06, 2013 5:36 am
by 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?
Re: Routine which sums up is hanging
Posted: Wed Nov 06, 2013 5:47 am
by 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.
Posted: Wed Nov 06, 2013 6:33 am
by 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?
Posted: Wed Nov 06, 2013 7:53 am
by 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.
Posted: Thu Nov 07, 2013 4:22 am
by 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
Posted: Thu Nov 07, 2013 7:55 am
by 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.
Posted: Thu Nov 07, 2013 7:55 am
by 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.