How to solve Abnormal Termination?

[i.peppermint]

Hi All,

I found one job, it always aborted with 'Abnormal Termination'. The job design is Oracle OCI -> Transformer --> Oracle OCI. After I tried to change the target to be Sequential File, it passes with no warning. I also tried to recreate target table, but it still aborted.

I have another jobs that use the same table as a target, but every of them passes with no warning.

Has anyone got any idea what cause the 'Abnormal Termination'? Besides, how can I fix it?

Your helps are very appreciated.

Peppermint

[roy]

Hi,
What are the warnings you get ???

while your answering that also think of the following points:
1. are you using any used defined routines in the job? (also check the transformer properties for before/after routines)
2. check beofre/after sql if any exists.
3. go over the &PH& directory and search for more information regarding that abnormal termination and/Or reset the job and search for "from previous run" info entries.
4. have your DBAs check what hapens in the DB while you run the job.

IHTH,

[Sainath.Srinivasan]

What is the stage on which the 'abnormal termination' is reported?

[i.peppermint]

What are the warnings you get ???

What is the stage on which the 'abnormal termination' is reported?

This is the reported message:
Abnormal termination of stage DJobLoadVoiceBtsImeiToSubDimStg..TRNSF_IMEI detected (Transformation Stage)

1. are you using any used defined routines in the job? (also check the transformer properties for before/after routines)

Yes. This job uses routine as follows:
a) In job properties: DSJobReport at After-job subroutine for generationg log file
b) In transformer: DateTimeStampToOraOCIWithTime(DateGenericToTimeStamp(Oconv(@DATE,"D2/") : ' ' : Oconv(@TIME,"MTS"))) to convert system date and time into Oracle format
However, every other jobs uses both routines. They are all passes.

2. check beofre/after sql if any exists.

No any before and after SQL exists.

3. go over the &PH& directory and search for more information regarding that abnormal termination and/Or reset the job and search for "from previous run" info entries.

Where can I search for "from previous run"?

4. have your DBAs check what hapens in the DB while you run the job.

Can you please give me the suggestion what DBA suppose to check while this job is running?


Any suggestion would be very welcomed.

[roy]

Hi,
try to first figure out what causes the abnormal termination.
I'd start with removing the after job routine.
then the routines generating the dates and compare the generated date with the data type you use and the column's data type in the actual table.
If none of the above works try rebuilding a new target link and stage,no copy/paste!!!.

p.s.
why do you use D2 in your date conversion?
(and seeing Ray wrote:... )


IHTH,

[Sainath.Srinivasan]

One option is to remove few columns from the target load and rerun the job. I assume it is some datatype of load problem into the target.

you need to isolate to the columns that cause the error by process of elimination.

The contenders could be your date or numeric values.

[i.peppermint]

Hi Roy and Sainath,

Thanks for your suggestion.
However, I've already tried to remove routine, and column one by one and rerun job every time I remove something. Till my job is very simple. It populates data from source table to target target by use one transformer. There are only 1 column left, it is a primary key column, but it still aborted.

Have any more suggestion?

PS. Roy, I'm sorry that I mentioned your name wrong.