Oracle commit interval

dinthat · Post by **dinthat** » Wed Mar 26, 2008 1:48 am

Hi All,

Good Morning.

I 've an issue while using the two environment variables APT_ORAUPSERT_COMMIT_TIME_INTERVAL and APT_ORAUPSERT_COMMIT_ROW_INTERVAL.
In my job I am upserting (update first and insert) data into an oracle stage. My requirement is, I 've to commit records after each insert/update. I am using the two variables APT_ORAUPSERT_COMMIT_TIME_INTERVAL and APT_ORAUPSERT_COMMIT_ROW_INTERVAL with value 1 in my job

. But the strange problem is whenever the job is executing, in Oracle the commit is occurring after each 500 records.

ArndW · Post by **ArndW** » Wed Mar 26, 2008 2:19 am

I had a similar issue under 7.5.2 and AIX but in my case neither of the two setting seemed to make a difference and the job was doing a commit at the end, as if the frequency was set to 0. We had opened up a support call and after we had changed the job around (for other reasons) it suddenly started working and we ended up not knowing the cause. And, as is often the case, we had other work to do so did not pursue the issue. It might be worth opening up a call with your support provider.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 26, 2008 3:11 am

If the time interval is asserted the row interval is ignored. Bring both environment variables into your job as job parameters, and give the special default value of $UNSET to the time interval parameter. In this way the row interval parameter will be able to "do its thing".

dinthat · Post by **dinthat** » Wed Mar 26, 2008 3:57 am

ray.wurlod wrote:If the time interval is asserted the row interval is ignored. Bring both environment variables into your job as job parameters, and give the special default value of $UNSET to the time interval parameter. In this way the row interval parameter will be able to "do its thing".

Hi Ray,

Thans for the reply.

I tried the same, but when I am trying to run my job, I am getting a Dialog box saying that this parameter should have a value in the range of -2147483648 to 2147483647.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 26, 2008 4:02 am

$UNSET tells DataStage to pretend that the environment variable was never set at all, which is entirely legal. Check your spelling.

dinthat · Post by **dinthat** » Wed Mar 26, 2008 5:21 am

Hi Ray,

Spelling is currect...
And I tried to set the value by double clicking on the default value grid and I selected the $UNSET from default value dialog. But result is same...

chulett · Post by **chulett** » Wed Mar 26, 2008 7:35 am

You must have the parameter setup as an Integer with that error... perhaps just change it to a String?

dinthat · Post by **dinthat** » Thu Mar 27, 2008 4:22 am

chulett wrote:You must have the parameter setup as an Integer with that error... perhaps just change it to a String? ...

There is no option to change the type to string. I tried that also....

Kindly guide me how to set the data type for APT_ORAUPSERT_COMMIT_TIME_INTERVAL as string from my job?

dinthat · Post by **dinthat** » Sat Apr 05, 2008 11:17 am

Hi All,

I tried all the above methods to execute commit records in oracle after each row.. But none of the methods are working in my case..

Anybody in DSXchange can help me?

Or datastage cant do that? or nobody in this world dont have such a requirement yet?

Sudhindra_ps · Post by **Sudhindra_ps** » Mon Apr 07, 2008 4:45 pm

hi dinthat,

We had a similar issue a while ago. We raised an ecase with IBM but couldn't get proper solution from them in time. And moreover we didn't had enough time to wait for the solution from their end we just went with implementing PL/SQL SP to perform the task. The problem here is, Datastage performs commit at the end of job completion. You could raise an ecase with IBM and try your luck. They can provide you with patch for resolution or they might ask you to alter your job design to handle this scenario.

Thanks & regards
Sudhindra P S

dinthat · Post by **dinthat** » Sat Apr 12, 2008 6:03 am

Hi Sudhindra,

Thanks for your reply.

Can you please explain how you solved this issue using PL/SQL SP. I think it will be helpful for everyone who is facing the same issue.

Sudhindra_ps · Post by **Sudhindra_ps** » Fri Apr 25, 2008 4:34 pm

hi Dinthat,

Open up an cursor in your PL/SQL procedure to loop through each record and commit based on your insert / update DML statement execution status.

Thanks & regards
Sudhindra P S