Wierd problem in transformer

[maheshsada]

Hi

I am having a control job, which inturn calls a child job. In the child job there is an oracle stage, transformer stage and sequential file stage.

The oracle stage selects around 16316653 rows, and then writes to the sequential file through transformer. But after writing 15564747 rows, the job got aborted. The error message is

"Abnormal termination of stage INSInsightExtractInvoicesChild.026.OneToOne detected",

where INSInsightExtractInvoicesChild - Job Name
026 - Invocation Id
OneToOne - Transformer Stage Name

I have checked the storage available in the directory where the file is generated and i have around 2Gb of space left

The file as of now generated is over 2GB size

Can you please help in knowing the exact problem

Magesh S

[ArndW]

Do a "reset" from the director and look at the entry with the heading "from previous run" to get more detailed error information; it is not possible to diagnose an exact cause from the information you have provided.

When you say that your sequential output file is over 2Gb does that mean quite a bit larger or pretty much exactly 2Gb? The file system 2Gb limitation is by far and away the most common cause of this type of mid-job abort. Are you certain that your ulimit allows creation of files greater than 2Gb?

[maheshsada]

Hi

I have reset the job, and this is what it has shown

From previous run
DataStage Job 161 Phantom 20532
Abnormal termination of UniVerse.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program JOB.1227847453.DT.1376034616.TRANS1 at address e2.kgefec: fatal error 0

We have checked with Unix administrator and have confirmed that they have set large file size

Magesh S

[maheshsada]

Hi
this is the ulimit -a

Executed command: "ulimit -a"
*** Output from command was: ***
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 1024
memory(kbytes) unlimited

Magesh S

Hi

I have reset the job, and this is what it has shown

From previous run
DataStage Job 161 Phantom 20532
Abnormal termination of UniVerse.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program JOB.1227847453.DT.1376034616.TRANS1 at address e2.kgefec: fatal error 0

We have checked with Unix administrator and have confirmed that they have set large file size

Magesh S

[ArndW]

Magesh,

put a constraint "1=2" into your transform and retest. This will evaluate to false for each record and ensure that nothing gets written to the sequential file. Alternatively write to a sequential file called "/dev/null". Does the error still happen? If yes, take a look at your transform derivations - do you do anything in there such as call your own routines or is everyting a straight drag-through of column values?

[maheshsada]

Hi Andrw

Its all a straight drag through of column values, but one or two columns are assigned Null values using @NUll, and in one column the data format is derived through a routine. the logic in routine is

*DD/MM/YYYY HH24:MI

hour = Arg1[12,2]

If hour < 12
Then Ans = oconv(Iconv(Arg1[1,10], "D/E") - 1,"D/E")
Else Ans = Arg1[1,10]

Ex: 20/08/2005 10:30 - 19/08/2005
20/08/2005 12:30 - 20/08/2005

Magesh S

Magesh,

put a constraint "1=2" into your transform and retest. This will evaluate to false for each record and ensure that nothing gets written to the sequential file. Alternatively write to a sequential file called "/dev/null". Does the error still happen? If yes, take a look at your transform derivations - do you do anything in there such as call your own routines or is everyting a straight drag-through of column values?

[ArndW]

Did the job have the same error using a 1=2 constraint or writing to /dev/null? If the error went away, then the source of the problem has to do with the sequential file. If the error remains, then take out your routine call and see if the error goes away.

[maheshsada]

Hi

Iam running the job by putting a restriction on the number of rows selected (rownum < 1000001 and the job has completed

Magesh S

Did the job have the same error using a 1=2 constraint or writing to /dev/null? If the error went away, then the source of the problem has to do with the sequential file. If the error remains, then take out your routine call and see if the error goes away.

[ArndW]

Magesh,

what kind of a test is that? The original error didn't show up until row 16,316,653 according to your post.

[maheshsada]

Hi

This is to check whether there is any problem as such in the job. If this gets completed, then we are planning to split the file generation like gettting the max invoice and dividing by a constant value and then giving that in where clause so that simultaneously we can generate multiple files and in control job concatenating all the files

This statergy has worked before, any better idea is welcomed

Magesh S

Magesh,

what kind of a test is that? The original error didn't show up until row 16,316,653 according to your post.

[ArndW]

Magesh,

you are trying to fix a problem without knowing what your problem is by applying a fix to some other issue. With the suggestions you got in this thread you will be able to identify the cause and you can then choose how to solve it. At the moment it might be a memory leak issue with your routine, or a problem writing large sequential files, or any one of a host of problems. Localizing the issue is just a matter of doing some simple tests (perhaps with long runtimes, but no human effort) on copies of your job.

Look at this another way - if you make changes to your job without identifying your problem what guarantee do you have that it won't happen again down the road at a much more painful juncture?

[maheshsada]

Hi

Since it involves production and thats the reason for me to go for urgent fix, anyhow i have now convinced the user and going for testing

First thing iam trying it out was instead of restricting to 1M, iam restricting at 15M

Second thing is, removing the transformer and just connecting oracle stage to seq stage and without having any rownum restriction, so that it can fetch the whole 16M

Will keep posted the results

Magesh S

Magesh,

you are trying to fix a problem without knowing what your problem is by applying a fix to some other issue. With the suggestions you got in this thread you will be able to identify the cause and you can then choose how to solve it. At the moment it might be a memory leak issue with your routine, or a problem writing large sequential files, or any one of a host of problems. Localizing the issue is just a matter of doing some simple tests (perhaps with long runtimes, but no human effort) on copies of your job.

Look at this another way - if you make changes to your job without identifying your problem what guarantee do you have that it won't happen again down the road at a much more painful juncture?