How to extract info from Oracle using DataServices 4

[Former Member]

Our environment

     Suse linux 11

     Data Services 4 SP 3

Oracle  11.2.0.3

our env

LINK_DIR <instal>/dataservices

LD_LIBRARY_PATH ${LINK_DIR}/bin:${LINK_DIR}/DataDirect/odbc/lib:<instal>/sap_bobj/enterprise_xi40/linux_x64/odbc/lib

ODBCINI <instal>/dataservices/DataDirect/odbc/odbc.ini

questions:

whats the correct driver?

how to install driver?

how to create the connection?

how to test it?

regards

[Former Member · Accepted solution]

Hi,

Do you specifically want to use ODBC for Oracle connectivity?

Normally I suggest using the Oracle native driver and for this you need:

1. Install Oracle full client 11.2.0.3 on the DS Job Server machine on your Linux
2. Configure the tnsnames.ora to communicate with your Oracle Server SID where the DS repository  and/or where your source/target database reside.
3. Use tnsping command line to test the connectivity to the SID
4. Use SQL to make sure you can access the database and query it always on Linux.
5. Then on Windows Designer install the Oracle client also and test all the connectivity to the db server as above.
6. Create a new datastore in DS>Local Object Library and select database type>oracle.
7. Import required tables

Let me know if this helps.

[Former Member · Accepted solution]

I think that I forgot an important detail,  DS installation is using DB2 9.7 database and repositories, and our data sources is Oracle.

[Former Member · Accepted solution]

Hi,

If i understand your requirement correctly, your datasource resides in a different server than the repo. In that case you need to create a datastore that connect to Oracle DB and extract source tables into it.

Arun

[Former Member · Accepted solution]

Hi,

     Is Correct, the data source is stored in a different server, our requirement is to setup ODBC driver for oracle, and catalog a database.

We search on Oracle web site but we were not able to find the correct ODBC driver.

[Former Member · Accepted solution]

Hi,

Since you are connecting through ODBC, you need to check the odbc.ini file in the installation directory for creating a system DSN connectivity. Check the below link for more details.

http://download.oracle.com/otn_hosted_doc/timesten/1121/quickstart/html/admin/dsn_setup_unix_linux.h...

Arun

[Former Member · Accepted solution]

Hi Miguel,

In my post I covered both, repository and source. But if you want only the Oracle source then the  same steps still apply. So you can ignore the repository part.

So in answer to your original questions:

Q: whats the correct driver?

A: SAP recommends using the native Oracle Driver "Oracle Net Client" this is what's listed in the PAR

Q:how to install driver?

A:Run the Oracle server setup and select clients components or just install Oracle client if you have one

Q:Setup how to create the connection?

A: See my above steps 1-7

Q:how to test it?

A:See my above steps 1-7

Now:  SAP DS also supports 3rd party ODBC generic drivers, is this what you want to use specifically? If so, then you need to tell us what licensed 3rd party level-2 compliant driver you want to setup is it DataDirect, UnixODBC,  etc...? and we'll try to help you.

You said that you have DS repository created on DB2 database on Linux, right? Have you configured the extra DS job servers/ client designers on Windows to communicate with this DB? If not then please create a new thread based  on this info and members in this community will be happy to share their knowledge with you

Thank 🙂

[Former Member]

Hi everyone,

     We implemented this connection doing the next steps

          1.- Create user and group for oracle cliente install

          2.- Set environment variables

               ORACLE_BASE,

               TNS_ADMIN,

               ORACLE_HOME

          3.- Setup oracle client

          4.- test connection using sqlplus

          5.- for the user sidadm setup the variables environment and add oracle group

          6.- install oracle full client where the DS designer is running and setup a connection to oracle                source

          7.- run a test job

thanks for all your helpful comments.