commit transaction

karthi_gana · Post by **karthi_gana** » Tue Feb 21, 2012 3:12 am

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?

manoj_23sakthi · Post by **manoj_23sakthi** » Tue Feb 21, 2012 3:55 am

could u please give the correct information about the stage?

ray.wurlod · Post by **ray.wurlod** » Tue Feb 21, 2012 3:15 pm

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".

chulett · Post by **chulett** » Tue Feb 21, 2012 7:10 pm

Well, someone let us know what stage we're talking about here.

karthi_gana · Post by **karthi_gana** » Thu Feb 23, 2012 9:29 am

ODBC Stage - in Parallel

ray.wurlod · Post by **ray.wurlod** » Thu Feb 23, 2012 3:05 pm

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.

qt_ky · Post by **qt_ky** » Thu Feb 23, 2012 5:38 pm

Which ODBC stage type?

karthi_gana · Post by **karthi_gana** » Sat Feb 25, 2012 9:35 am

can you tell me which stage property control this?

qt_ky · Post by **qt_ky** » Sat Feb 25, 2012 12:08 pm

karthi_gana wrote:can you tell me which stage property control this?

Yes. You go first...

qt_ky wrote:Which ODBC stage type?

karthi_gana · Post by **karthi_gana** » Sun Feb 26, 2012 3:01 am

which ODBC type?

ODBC Enterprise ! ?

or

Parallel Job - ODBC Enterprise to connect SQL Server

qt_ky · Post by **qt_ky** » Sun Feb 26, 2012 10:04 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.

karthi_gana · Post by **karthi_gana** » Mon Feb 27, 2012 12:20 am

Note: ODBC Connector is the preferred stage type for ODBC.

can you tell me why?

karthi_gana · Post by **karthi_gana** » Mon Feb 27, 2012 12:36 am

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.

karthi_gana · Post by **karthi_gana** » Mon Feb 27, 2012 12:59 am

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' ?

chulett · Post by **chulett** » Mon Feb 27, 2012 8:25 am

karthi_gana wrote:
Note: ODBC Connector is the preferred stage type for ODBC.
can you tell me why?

Connectors are the way of the future and are thus preferred across the board. All other stages are deprecated, which the documentation clearly states.

DSXchange

commit transaction

commit transaction

Re: commit transaction