DSXchange

Hi

The job aborts while using Oracle Load, the design is DB2 Enterprise-> Xfm -> Ora.
This job is successful while using Upsert but slow with 450 rows/sec, only fails when using Load method in target Oracle, I have 135 columns with 6 million rows the same job is successful for ~116 columns when using load.

Error message : I opened the control file I don't see the all the columns

CDR_AVG_UNITS_USAGE_DAYS" POSITION (786:792) decimal(12,4) NULLIF (786:792) = BLANKS,
"CDR_AVG_CALLS_USAGE_DAYS" POSITION (793:799) decimal(12,4) NULLIF (793:799) = BLANKS,
"CDR_AVG_IN_CALLS_USAGE_DAYS" POSITION (800:806) decimal(12,4) NULLIF (800:806) = BLANKS,
"CDR_AVG_OUT_CALLS_USAGE_DAYS" POSITION (807:813) decimal(12,4) NULLIF (807:813) = BLANKS,
"ESN" POSITION (814:835) varcha


Please let me if there any limitation on number of columns. I did search the forum I didn't any post for this error.

Thanks
Kumar

How (with what tool) did you open the control file? Maybe the limit is in that tool.

Thanks Ray,
I did cat on that file in unix (AIX). The DataStage job aborts too.

Remember seeing a similar post with issue in control file size thus resulting in truncation of control file.

That was when there were 400+ fields in the table.

hi,

I believe the job failure is not becuase of number of fields.
I can assume that the load option is rejecting some records and hence the error.
Can you check whether the ora BAD files are created for that job run?

I got the simillar problem using ora load option. The bulk load option will convert the space in the non nullable fields to NULL and rejects the records (we can see this with the created BAD files). But it is not the case in the Upsert method.

Just try: Set the APT_ORACLE_PRESERVE_BLANKS = true and try to run the job. what is result?

Thanks for your response,

These is no bad file created and I tried with preserve blanks, still it aborted. I just tried with 10 rows as input.

DataStage log:
Oracle_Enterprise_4,0: SQL*Loader-350: Syntax error at line 121.
Oracle_Enterprise_4,0: Expecting ")", found end of file.

Control file starting and ending are:

/projects81/Scratch >more ora.446792.756596.0.ctl
OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)
LOAD DATA length semantics byte INFILE 'ora.446792.756596.fifo.0' "FIX 868"
APPEND INTO TABLE aggs_tst
(
"ID" POSITION (1:8) INTEGER (8) NULLIF (1:8) = BLANKS,
....
....
"USAGE_DAYS" POSITION (814:820) decimal(12


Thanks
Kumar

Sainath.Srinivasan wrote:Remember seeing a similar post with issue in control file size thus resulting in truncation of control file.

That was when there were 400+ fields in the table.

I remember seeing something similar as well. In other words, possibly a bug that your official support provider could have a patch for.

Works fine with patch provided by IBM.

i am also facing same problem..Could you please provide patch details ??