Job Aborts after 50 errors; how to change?

whenry6000 · Post by **whenry6000** » Tue Jun 24, 2008 11:35 am

I am using the Oracle Enterprise stage to load data using the Load/Append option (instead of upsert). If trying to insert invalid records (duplicate keys), it appears that it will try to load 50 rows before it aborts. Is there any way to change this number??
Is this value set in Datastage or is it in the Oracle database?? I have not been able to find it anywhere.

lstsaur · Post by **lstsaur** » Tue Jun 24, 2008 11:51 am

On the Limits tab, check the "No limit" box.

josh.guffey · Post by **josh.guffey** » Tue Jun 24, 2008 11:56 am

Inside of DataStage Director, go to Tools and select Options. Once the Options dialog box pops up select the Limits tab and change warnings to No limit or select Abort job after: value to something > 50.

Hope this helps.

whenry6000 · Post by **whenry6000** » Tue Jun 24, 2008 12:11 pm

josh.guffey wrote:Inside of DataStage Director, go to Tools and select Options. Once the Options dialog box pops up select the Limits tab and change warnings to No limit or select Abort job after: value to something > 50.

Hope this helps.

Thanks for the info, but this seems to be different. This is not addressing the warnings in the Director log.

If you go to the Scratch directory (for our installation /opt/IBM/InformationServer/Server/Scratch directory), it contains log files for jobs using the SQL Loader technology. It has the following entry:

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Jun 18 20:34:59 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: ora.8214.348555.0.ctl
Data File: ora.8214.348555.fifo.0
File processing option string: "FIX 2807"
Bad File: ora.8214.348555.0.log.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Further down in the file, it has a message as follows:

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table DW_FNNCL_TRNSCTN:
1 Row successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

However, I don't know where this gets set.
Thanks!

kcbland · Post by **kcbland** » Tue Jun 24, 2008 12:11 pm

Warning limits are set when a job is ran by whatever facility you are using. If it's Director, it's part of the dialog box where you press the Run button. If it's job control, it's part of the command line invocation. If you fail to specify via the job control, it then defaults to what is set as the default for the job, else the default for the project.

whenry6000 · Post by **whenry6000** » Tue Jun 24, 2008 12:38 pm

kcbland wrote:Warning limits are set when a job is ran by whatever facility you are using. If it's Director, it's part of the dialog box where you press the Run button. If it's job control, it's part of the command line invocation. If you fail to specify via the job control, it then defaults to what is set as the default for the job, else the default for the project.

I understand what you are saying, but this is not a warning limit I appear to be running up against. I have set that in Director (and I am running from Director). This appears to be related to the errors from SQL Loader. The logs I included were from the SQL Loader log in the Information Server's Scratch directory.

Thanks!

tobypanzer · Post by **tobypanzer** » Tue Jun 24, 2008 1:58 pm

whenry6000 wrote: I understand what you are saying, but this is not a warning limit I appear to be running up against. This appears to be related to the errors from SQL Loader. The logs I included were from the SQL Loader log in the Information Server's Scratch directory.
Thanks!

Looks like you answered your own question!

It definitely looks like a problem with SQL loader, which is an oracle utility.

I googled "sql loader error limit"

http://www.lazydba.com/oracle/0__88670.html

kcbland · Post by **kcbland** » Tue Jun 24, 2008 2:48 pm

Sorry, I was answering while you were adding more info. Did you check your $APT_ORACLE_LOAD_OPTIONS to see what your settings are? You'll want to set this according to your needs. It affects your Control file options and you can set custom values there.

whenry6000 · Post by **whenry6000** » Wed Jun 25, 2008 5:36 am

kcbland wrote:Sorry, I was answering while you were adding more info. Did you check your $APT_ORACLE_LOAD_OPTIONS to see what your settings are? You'll want to set this according to your needs. It affects your Control file options and you can set custom values there.

Yes, it is $APT_ORACLE_LOAD_OPTIONS. I found it yesterday and then saw your answer this morning!

Thanks!

whenry6000 · Post by **whenry6000** » Wed Jun 25, 2008 5:37 am

tobypanzer wrote:
whenry6000 wrote: I understand what you are saying, but this is not a warning limit I appear to be running up against. This appears to be related to the errors from SQL Loader. The logs I included were from the SQL Loader log in the Information Server's Scratch directory.
Thanks!
Looks like you answered your own question! It definitely looks like a problem with SQL loader, which is an oracle utility.

I googled "sql loader error limit"

http://www.lazydba.com/oracle/0__88670.html

Although it is SQL Loader, Datastage controls it through the $APT_ORACLE_LOAD_OPTIONS variable. That is the information I was looking for.