Record load getting committed even after job abort

udayanguha · Post by **udayanguha** » Mon Feb 16, 2015 8:24 pm

Hi,
I am loading some data into an Oracle table using Oracle connector. I want the load to roll back completely from the Oracle table if the job aborts. For this I have set the record count property under Isolation level to '0'. But still I can see records in the target table though my job has aborted due to some reason (primary key violation).
Other properties set by me:
Isolation level: Read commited
Array size: 2000
Drop unmatched fields: Yes
Fail on row error: Yes

ray.wurlod · Post by **ray.wurlod** » Mon Feb 16, 2015 8:29 pm

Welcome aboard.

This behaviour is governed primarily by the transaction record count specified in your Oracle connector. Each time that many rows is sent to Oracle, DataStage also sends a COMMIT.

You can change the behaviour to "all or nothing" by setting the transaction record count to 0 (all rows are one transaction), but make sure you are in your DBA's good books, as it will be necessary to handle very large transactions (talk to them about rollback or redo segments).

udayanguha · Post by **udayanguha** » Mon Feb 16, 2015 8:41 pm

Hi Ray,
Thanks!!
I have already set the property record count = 0 under transaction but still some data is getting loaded after job abort. Is there some other property as well?

ray.wurlod · Post by **ray.wurlod** » Tue Feb 17, 2015 5:13 am

Array Size and Record Count are related. With either property selected click on Help.

chulett · Post by **chulett** » Tue Feb 17, 2015 8:31 am

Sure, related but it will change the Array Size to be an even multiple of the Record Count if it needs to but not the other way 'round. And that doesn't apply if the Record Count is set to zero. And as noted in the docs, if record count is a zero then "the connector processes all records before it commits the transaction".

What about the two APT ORA variables that control commits? Are they set in the environment? I don't have their exact names off the top of my head but one commits based on time and another based on count. Wondering if they are causing what you are seeing.

Mike · Post by **Mike** » Tue Feb 17, 2015 10:45 am

Does your selected method/write mode support transactions?

Many bulk loaders don't.

My Oracle usage is too rusty to remember SQL*Loader options.

Mike

chulett · Post by **chulett** » Tue Feb 17, 2015 10:52 am

I don't think we are but if we're talking about an Oracle bulk load then there are two kinds, DIRECT and CONVENTIONAL. The former is the true bulk load where you have DIRECT=TRUE in the control file and there are no commits done as everything is written directly to the table and bypasses the redo mechanism. Conventional (DIRECT=FALSE) is the default and does plain old regular inserts and commits so not really all that... bulky.

udayanguha · Post by **udayanguha** » Tue Feb 17, 2015 2:05 pm

Hi,
Its not a bulk load which I am trying to do. A normal load through oracle connector.
Record count property has been set to 0

Mike · Post by **Mike** » Tue Feb 17, 2015 2:26 pm

Ok. Thanks for clarifying. With a write method of insert, you'll be using transactions and everything Ray and Craig have suggested would apply.

Mike

chulett · Post by **chulett** » Tue Feb 17, 2015 3:06 pm

These are the two I mentioned:

$APT_ORAUPSERT_COMMIT_TIME_INTERVAL
$APT_ORAUPSERT_COMMIT_ROW_INTERVAL

With those not set or not applicable and Record Count = 0 seemingly not working, I would involve support.

udayanguha · Post by **udayanguha** » Wed Feb 25, 2015 2:36 pm

Hi,
I check both the environment variables i.e.

$APT_ORAUPSERT_COMMIT_ROW_INTERVAL and
$APT_ORAUPSERT_COMMIT_TIME_INTERVAL

They are set to default value on project level in the administrator (5000 and 2 respectively).
I want to override this property in my job so I used these variables in my job. I tried setting their value to 0, -1, $UNSET etc but still the records are getting committed. Any idea how can I actually unset these values so that project level values are not assigned to these variables.

chulett · Post by **chulett** » Wed Feb 25, 2015 3:14 pm

Pretty sure that's a question that will need to go back to support.

udayanguha · Post by **udayanguha** » Wed Feb 25, 2015 3:51 pm

Hi Chulett,
Thanks for the suggestion. Just wanted to understand if there's some support E-mail ID where in I need to drop a mail or do I need to contact my company's Datastage administrator?

SURA · Post by **SURA** » Wed Feb 25, 2015 4:09 pm

You need to reach the IBM support (Need to raise a PMR).

chulett · Post by **chulett** » Wed Feb 25, 2015 4:18 pm

Technically it would go back to your 'Official Support Provider' which may or may not be IBM... it can depend on whom you got the software from. That vendor may be your official first line of defense for support. Hopefully someone there knows.