Difference between 'Oracle OCI' and 'Oracle Connector'

joycerecacho · Post by **joycerecacho** » Wed Oct 31, 2012 7:16 am

Hi everybody.

Does anybody know the difference between the objects 'Oracle OCI' and 'Oracle Connector'?

Do they work different? When should I use one or the other?
The thing is, I need to load a large amount of data.
PS. I am working with a Server job.

Thank you so much.

Best Regards,

saiwelcomes · Post by **saiwelcomes** » Wed Oct 31, 2012 7:45 am

With Oracle OCI, you can:
Generate your SQL statement.
Use a file name to contain your SQL statement.
Clear a table before loading by using a TRUNCATE statement. (Clear table)
Select how often to commit rows to the database. (Transaction size)
Input multiple rows of data in one call to the database. (Array size)
Read multiple rows of data in one call from the database. (Array size)
Specify transaction isolation levels for concurrency control and transaction performance tuning. (Transaction Isolation)
Specify criteria that data must meet before being selected. (WHERE clause)
Specify criteria to sort, summarize, and aggregate data. (Other clauses)
Specify the behavior of parameter marks in SQL statements.
Oracle OCI is dependent on the libclntsh shared library, which is created during the installation of the Oracle client software. You must include the location containing this shared library in the shared library search path for InfoSphere DataStage jobs to run successfully by using this stage.

Refer the below link about oracle connector:
http://publib.boulder.ibm.com/infocente ... tages.html

saiwelcomes · Post by **saiwelcomes** » Wed Oct 31, 2012 8:11 am

Better use Oracle Connector stage to handle large volumes of data. It is 20% faster than OCI stage.

chulett · Post by **chulett** » Wed Oct 31, 2012 8:45 am

Connectors are the way of the future and all of the 'legacy' stages have been deprecated.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 31, 2012 10:04 am

saiwelcomes wrote:Better use Oracle Connector stage to handle large volumes of data. It is 20% faster than OCI stage.

42% of all statistics are made up.
Where did you get this figure?
Are there any specified conditions under which the 20% was obtained?
I could create jobs in which the OCI is faster than the Oracle Connector; I could create jobs in which the Oracle Connector outperforms the OCI stage by a factor of 10x or more.
It all depends...

joycerecacho · Post by **joycerecacho** » Wed Oct 31, 2012 11:31 am

I also want to understand these statistics (20%).

Thanks guys!

BI-RMA · Post by **BI-RMA** » Thu Nov 01, 2012 4:30 am

ray.wurlod wrote: 42% of all statistics are made up

... and 42% is a very noticeable number, indeed!

But one good thing about Connector-Stages is, that they share the same user-interfaces in Server and Parallel Jobs (except that you can't switch the server variant to parallel mode). They are even able to use the same Data-Connection-objects (which was not possible for the different versions of DB2 API Stage for example).

There is a good chance that Connector stages are, in fact, faster than OCI-Stages, because the connectors are optimized to connect to the latest versions of the databases. For example there are options in the Oracle Connector stage to connect to the version 10 or 11 variants of Oracle - which does not seem to change much in the mechanics of the stage, though. If IBM announces that DataStage 9.1 has much better Oracle-throughput than 8.7 this assumes you are using the Oracle Connector in both versions. And it assumes You believe this statistic is part of the 58% Ray did not refer to...

The Oracle OCI-Stage was developed for Oracle 9i and no further development is carried on by IBM regarding this stage - except necessary bugfixing (hopefully).

joycerecacho · Post by **joycerecacho** » Thu Nov 01, 2012 6:05 am

Guys,
Thank you very much for your explanations.

Best regards,