commit transaction
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
commit transaction
All,
when we use "Server job", i know there is an option to mention "Rows Per Transaction" to commit the transaction.
But i don't see that option in "Parallel job". is it because parallel job use the BULK COPY method?
when we use "Server job", i know there is an option to mention "Rows Per Transaction" to commit the transaction.
But i don't see that option in "Parallel job". is it because parallel job use the BULK COPY method?
Karthik
-
- Participant
- Posts: 47
- Joined: Tue Feb 23, 2010 12:16 am
- Location: CHENNAI
Re: commit transaction
could u please give the correct information about the stage?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I doubt that U could give the correct information, because U works in Singapore for a different company than does karthi_gana.
The second person personal pronoun in English is spelled "you".
The second person personal pronoun in English is spelled "you".
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.
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Bulk loading is non-transactional, so that COMMIT is not relevant.
But the ODBC Enterprise stage also allows you to use Upsert method. Then transactions are relevant, and the number of rows per transaction can be controlled either by an environment variable or by a stage property that becomes enabled when Upsert method is selected.
But the ODBC Enterprise stage also allows you to use Upsert method. Then transactions are relevant, and the number of rows per transaction can be controlled either by an environment variable or by a stage property that becomes enabled when Upsert method is selected.
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.
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
Bulk load is not a default setting for ODBC stages.
Note: ODBC Connector is the preferred stage type for ODBC.
For the ODBC Enterprise stage, the default Write Method = Write and default Write Mode = Append. To override the default commit interval:
Go to Options, select Insert Array Size. The default is 2000. Set it to 1.
Go to Options again, select Row Commit Interval. Set it to some number.
Initially you won't find the Row Commit Interval setting. You'll only find it after you set Insert Array Size = 1.
The default Row Commit Interval is equal to the Array Size, whatever you have set it to.
Note: ODBC Connector is the preferred stage type for ODBC.
For the ODBC Enterprise stage, the default Write Method = Write and default Write Mode = Append. To override the default commit interval:
Go to Options, select Insert Array Size. The default is 2000. Set it to 1.
Go to Options again, select Row Commit Interval. Set it to some number.
Initially you won't find the Row Commit Interval setting. You'll only find it after you set Insert Array Size = 1.
The default Row Commit Interval is equal to the Array Size, whatever you have set it to.
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
I have also seen APT_RDBMS_COMMIT_ROWS environment variable in the below link.
http://etl-tools.info/infosphere-datast ... iables.htm
I don't see this variable in my project. !!! ???
I can see $APT_ORAUPSERT_COMMIT_ROW_INTERVAL in my project. The dafault value is 5000.
We are using SQL 2008. So i am sure i can't use this variable.
http://etl-tools.info/infosphere-datast ... iables.htm
I don't see this variable in my project. !!! ???
I can see $APT_ORAUPSERT_COMMIT_ROW_INTERVAL in my project. The dafault value is 5000.
We are using SQL 2008. So i am sure i can't use this variable.
Karthik
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
I have tried to learn about ODBC stage and its option (like Insert Array Size, Fetch Array Size, Row Commit Interval etc.,). But i don't see this stage in any manual. If anybody knows where i can found more info on this, please let me know.
Meanwhile can you explain what is the difference between 'Insert Array Size' and 'Row Commit Interval' ?
Meanwhile can you explain what is the difference between 'Insert Array Size' and 'Row Commit Interval' ?
Karthik
Connectors are the way of the future and are thus preferred across the board. All other stages are deprecated, which the documentation clearly states.karthi_gana wrote:can you tell me why?Note: ODBC Connector is the preferred stage type for ODBC.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers