Abnormal termination : ORACI8

[anupam]

Hi All,

I am extracting data from Oracle table using ORACI8 stage and writing it to a flat file.

After extracting about 15 Million records, the job is getting aborted. The error message is "Abnormal termination of stage J4XfmNovAnu..Txfm detected".

Each time also the job aborts when the records extracted is somewhere about 15 M and the size of the file is somewhere about 1 G.I have checked the disk space. The mountpoint can have largefiles. There is no problem related to the space available.

Please suggest me to overcome this abnormal termination of Job.

[ray.wurlod]

Reset the job, and look for an event in the log called something like "from previous run...". Alternately, find the records for the job and stage in the &PH& directory (in the project directory on the server). Post your findings. Without them, there is insufficient information for an informed diagnosis.
Also, has any message appeared in the Oracle logs relating to your extraction process?

[anupam]

Required Information

From previous run
DataStage Job 1202 Phantom 17860
kgefec: fatal error Abnormal termination of UniVerse.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program JOB.1161750357.DT.1310851132.TRANS1 at address b0.0


JOB.1161750357.DT.1310851132.TRANS1
* Tokens were replaced below as follows:
* Pin%%V0S42P1.Column%%1 <= totxfm.CALLG_PARTY_NO
* Pin%%V0S42P1.Column%%2 <= totxfm.CALLD_PARTY_NO
* Pin%%V0S42P1.Column%%3 <= totxfm.BILL_DURATION
* Pin%%V0S42P1.Column%%4 <= totxfm.CALL_TYPE
* Pin%%V0S42P1.Column%%5 <= totxfm.CALL_DIRECTION
* Pin%%V0S42P1.Column%%6 <= totxfm.MIN_NO
* Pin%%V0S42P1.Column%%7 <= totxfm.START_DATE
* Pin%%V0S42P1.Column%%8 <= totxfm.START_TIME_HOUR
* Pin%%V0S42P1.Column%%9 <= totxfm.START_TIME_MIN
* Pin%%V0S42P1.Column%%10 <= totxfm.START_TIME_SEC
* GET.Pin%%V0S42P1 <= GET.totxfm
* Pin%%V0S42P1.REJECTEDCODE <= totxfm.REJECTEDCODE
* Pin%%V0S42P2.Column%%1 <= tofile.CALLG_PARTY_NO
* Pin%%V0S42P2.Column%%2 <= tofile.CALLD_PARTY_NO
* Pin%%V0S42P2.Column%%3 <= tofile.BILL_DURATION
* Pin%%V0S42P2.Column%%4 <= tofile.CALL_TYPE
* Pin%%V0S42P2.Column%%5 <= tofile.CALL_DIRECTION
* Pin%%V0S42P2.Column%%6 <= tofile.MIN_NO
* Pin%%V0S42P2.Column%%7 <= tofile.START_DATE
* Pin%%V0S42P2.Column%%8 <= tofile.START_TIME_HOUR
* Pin%%V0S42P2.Column%%9 <= tofile.START_TIME_MIN
* Pin%%V0S42P2.Column%%10 <= tofile.START_TIME_SEC
* PUT.Pin%%V0S42P2 <= PUT.tofile
* Pin%%V0S42P2.REJECTED <= tofile.REJECTED
* Pin%%V0S42P2.REJECTEDCODE <= tofile.REJECTEDCODE
*
* Subroutine for active stage J4XfmNovAnu.Txfm generated at 14:18:54 20 NOV 2003
*
SUBROUTINE DSTransformerStage(HANDLES,ERROR)

$INCLUDE DSINCLUDE DSD_RTCONFIG.H
$INCLUDE DSINCLUDE DSD_STAGE.H
$INCLUDE DSINCLUDE DSD_BCI.H

$INCLUDE DSINCLUDE JOBCONTROL.H
DEFFUN DSRLoadString(Num,Text,Args) CALLING '*DataStage*DSR_LOADSTRING'
$DEFINE Pin%%V0S42P1.Column%%1 STAGECOM.ARR(1)
$DEFINE Pin%%V0S42P1.Column%%2 STAGECOM.ARR(2)
$DEFINE Pin%%V0S42P1.Column%%3 STAGECOM.ARR(3)
$DEFINE Pin%%V0S42P1.Column%%4 STAGECOM.ARR(4)
$DEFINE Pin%%V0S42P1.Column%%5 STAGECOM.ARR(5)
$DEFINE Pin%%V0S42P1.Column%%6 STAGECOM.ARR(6)
$DEFINE Pin%%V0S42P1.Column%%7 STAGECOM.ARR(7)
$DEFINE Pin%%V0S42P1.Column%%8 STAGECOM.ARR(8)
$DEFINE Pin%%V0S42P1.Column%%9 STAGECOM.ARR(9)
$DEFINE Pin%%V0S42P1.Column%%10 STAGECOM.ARR(10)
$DEFINE GET.Pin%%V0S42P1 CALL $DSP.GetNext(1,Pin%%V0S42P1.REJECTEDCODE)
$DEFINE Pin%%V0S42P2.Column%%1 STAGECOM.ARR(1)
$DEFINE Pin%%V0S42P2.Column%%2 STAGECOM.ARR(2)
$DEFINE Pin%%V0S42P2.Column%%3 STAGECOM.ARR(3)
$DEFINE Pin%%V0S42P2.Column%%4 STAGECOM.ARR(4)
$DEFINE Pin%%V0S42P2.Column%%5 STAGECOM.ARR(5)
$DEFINE Pin%%V0S42P2.Column%%6 STAGECOM.ARR(6)
$DEFINE Pin%%V0S42P2.Column%%7 STAGECOM.ARR(7)
$DEFINE Pin%%V0S42P2.Column%%8 STAGECOM.ARR(8)
$DEFINE Pin%%V0S42P2.Column%%9 STAGECOM.ARR(9)
$DEFINE Pin%%V0S42P2.Column%%10 STAGECOM.ARR(10)
$DEFINE PUT.Pin%%V0S42P2 CALL $DS.SEQPUT(2, Pin%%V0S42P2.REJECTEDCODE)

UPDATE.COUNT = STAGECOM.RATE



LOOP
REJECTED = @TRUE
* Get next row from primary input pin totxfm
STAGECOM.PINNO = 1
GET.Pin%%V0S42P1
ERROR = Pin%%V0S42P1.REJECTEDCODE
WHILE NOT(ERROR)

STAGECOM.PINNO = 2
IF @TRUE THEN
* Column derivation code for pin tofile
Pin%%V0S42P2.REJECTED = @FALSE
PUT.Pin%%V0S42P2
IF NOT(Pin%%V0S42P2.REJECTEDCODE) THEN
REJECTED = @FALSE
END ELSE
Pin%%V0S42P2.REJECTED = @TRUE
END
END
ELSE
Pin%%V0S42P2.REJECTED = @TRUE
Pin%%V0S42P2.REJECTEDCODE = 0
END


UPDATE.COUNT -= 1
IF UPDATE.COUNT LE 0 THEN CALL DSD.Update(HANDLES);UPDATE.COUNT = STAGECOM.RAT
E
REPEAT
RETURN
END

Could not found anything in /u20/app/datastage/product/Ascential/DataStage/Projects/ric_dss_prdadhoc/&PH& directory

[mhester]

Anupam,

Are you calling any custom routines or transforms during processing?

If you do, could you verify if you are manipulating the input argument like -

Arg1 = Arg1 * 10

If you are then this might/could be causing your problem. This can cause an abnormal termination of UniVerse and produce results like you are seeing.

If you are then the above statement would be more correct like -

NewArg = Arg1 * 10

Regards,

Michael Hester

[ray.wurlod]

If you execute the UniVerse (DataStage Engine) command

Code: Select all

VLIST RT_BP1202 JOB.1161750357.DT.1310851132.TRANS1 

you will be able to determine the statement that is executing at address b0. This may assist your diagnosis.

[kduke]

Ray

Jeez you going to teach assembler next. VLIST is pretty low level stuff. I do think it will help debug their problem.

Kim.

[anupam]

Ray,

At line B0:
01E Call "$DSP.GetNext" _T0000 Pin%%VOS42PI.REJECTEDCODE

I am unable to understand what this means,

Please suggest...

[ray.wurlod]

No, you wouldn't be expected to understand. $DSP.GetNext is the subroutine that retrieves the next row from the source. For some reason this has generated a fault type 11. _T0000 is the name of a temporary variable, and REJECTEDCODE is assigned in the event of an error.
It's at this point you need to contact your support provider to get official help in diagnosing exactly what's wrong with your particular case.

[kcbland]

Here's something you may consider if you are extracting a significant number of rows from a table and writing to a sequential file.

Pick a column, one that is numeric such as a primary key. You want one that when used with a MOD statement can give you an even distribution of the data. Add two job parameters: PartitionNumber and PartitionCount. Add #PartitionNumber# to the output sequential file name.

Set PartitionNumber to default to 1, and for this example set PartitionCount to 5. Modify the select query to have a WHERE clause with

Code: Select all

MOD(WhateverColumnYouPicked, #PartitionCount#) = #PartitionNumber# - 1

Save this job as YourJob1.

Now, save the job 4 more times as YourJob2 thru YourJob5, each time setting PartitionNumber parameter to the suffix number.

Start all 5 jobs running. You should be 5X faster pulling the data out of the table, plus you end up with 5 output files. You can concatenate them together to achieve a complete file.

Once you get to DS 5.2 or higher, you can use job instantiation instead of the manual clones I described here. If you have more than 5 cpus, and the source Oracle instance has the ability to sustain more processes, increase the PartitionCount value to achieve better results, making sure you run 1 to N jobs.

I don't know quite what your problem is, but I suspect it's the size of your file. The approach I described here should get you your results much faster into smaller files, and the point is to get the job done.

[1stpoint]

I seem to remember there being a problem with ORAOCI8 leaking memory. to diagnose this you will need to run the job and while it's running interrogate the process memory (ps -aux) and see if it is exceeding your allocated memory for a process (out of process memory will cause the ORAOCI8 to Abort Abnormally).

Best of Luck