Oracle commit interval
Moderators: chulett, rschirm, roy
Oracle commit interval
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.
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.
Last edited by dinthat on Wed Mar 26, 2008 2:29 am, edited 1 time in total.
Thanks And Regards,
dinthat
dinthat
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Ray,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".
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.
Thanks And Regards,
dinthat
dinthat
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is no option to change the type to string. I tried that also....chulett wrote:You must have the parameter setup as an Integer with that error... perhaps just change it to a String? ...
Kindly guide me how to set the data type for APT_ORAUPSERT_COMMIT_TIME_INTERVAL as string from my job?
Thanks And Regards,
dinthat
dinthat
-
- Participant
- Posts: 45
- Joined: Thu Aug 31, 2006 3:13 am
- Location: Bangalore
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
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
-
- Participant
- Posts: 45
- Joined: Thu Aug 31, 2006 3:13 am
- Location: Bangalore