DSXchange

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?

could u please give the correct information about the stage?

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

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

ODBC Stage - in Parallel

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.

Which ODBC stage type?

can you tell me which stage property control this?

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

Yes. You go first...

qt_ky wrote:Which ODBC stage type?

which ODBC type?

ODBC Enterprise ! ?

or

Parallel Job - ODBC Enterprise to connect SQL Server

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.

can you tell me why?

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.

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

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.