Error in control file when using Oracle Load

mkiru23 · Post by **mkiru23** » Wed Apr 15, 2009 4:11 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Apr 15, 2009 4:19 pm

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

mkiru23 · Post by **mkiru23** » Wed Apr 15, 2009 6:23 pm

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 16, 2009 4:10 am

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.

BugFree · Post by **BugFree** » Thu Apr 16, 2009 5:08 am

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.

BugFree · Post by **BugFree** » Thu Apr 16, 2009 5:12 am

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

mkiru23 · Post by **mkiru23** » Thu Apr 16, 2009 8:07 am

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

chulett · Post by **chulett** » Thu Apr 16, 2009 8:10 am

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.

mkiru23 · Post by **mkiru23** » Fri May 15, 2009 9:32 am

Works fine with patch provided by IBM.

nagarjuna · Post by **nagarjuna** » Mon Jul 13, 2009 1:38 pm

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