ODBC and Plugin

[nag0143]

Hi All
Whats the difference between using Plugin and ODBC stage, i tried using both the stages for same job and i don't see any change in the performance, if we have to choose between two ,if both stages meet all your requirements for data extraction or transformation. Which one should i use?

Thanks
Nag

[1stpoint]

The Plugin/OCI stages offer Native connectivity to the dbms which usually means greater control over commit points and select array sizes. Typically the OCI stages will outperform ODBC if configured correctly.

Also the OCI stages often do not require the overhead of creating and maintaining ODBC DSN's, which can be a security problem.

[s_boyapati]

Performance depends up on load size. For big loads you will see diffeence in performance for a given same resources. Due to the connectivity methods it varies. Plugin uses native/third party drivers designed for that DBMS, where as ODBC uses ODBC drivers( flexible to connect to any DBMS). In case of Plugin, need to redesign the job by changing the stage type, if source/target type changes(more maintainanace).

[vmcburney]

In version 7.1 you get the best of both worlds. The dynamic database stage works for multiple database types such as Oracle, DB2 and SQL Server and underneath the covers it has native connectivity. If you parametise it correctly you can change database types at run time.

I always use the native plugin where it is available to get the performance advantages described.

[cnguyen]

has anyone evaluated ODBC Wire Driver for Oracle against Oracle OCI driver in DataStage 7.1 (server job)? I am wondering if performance of the Wire driver is better than previous ODBC drivers.

Thanks

[cnguyen]

has anyone evaluated ODBC Wire Driver for Oracle against Oracle OCI driver in DataStage 7.1 (server job)? I am wondering if performance of the Wire driver is better than previous ODBC drivers.

Thanks

[vmcburney]

I reckon you are more likely to get a response on this at an Oracle or RDBMS forum, if you do get an answer please post it back here as it is an interesting comparison.

[ray.wurlod]

My experience has been that there's not much difference for SELECT (for most databases) between native API and ODBC, but that native API is much faster than ODBC when it comes to INSERT and UPDATE.

But, then, I even avoid the native API in this case, preferring bulk load wherever possible.

[mandyli]

Only one different between ODBC and plug-in. if u use plug-in stage it will not generate any log file but ODBC stage it will generate log file. This log file very useful for rollback process.

This is main different between ODBC and Plug-in.
[/quote]