ODBC stage

Bram · Post by **Bram** » Thu Jan 06, 2005 12:18 pm

Hi everyone,

I am new to Datastage. Please brief me where and why we use ODBC stage. As per my understanding, ODBC is used generally to connect to database, using the datasource. Its used only for RDBMS. whats the special in Datastage. Does it used in both editions.

Please teach me,

Thanks for your help,
Balaram

kcbland · Post by **kcbland** » Thu Jan 06, 2005 12:26 pm

There are only two ways to talk to any database: using native database connectivity or third party. ODBC is third part driver connectivity. All ETL tools have some mechanism to either use native connectivity supplied by the database manufacturer or support use of an ODBC driver.

DataStage can use both methods for most databases. The ODBC stage is a generic connection solution requiring the appropriate driver. The OCI and new DRS rely on you have the native database connectivity installed and configured.

PX uses native connectivity, Server uses both for most databases.

For more information, you should really be reading the tutorial.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 06, 2005 2:56 pm

To add to what Ken said, ODBC (and, later, OLEDB) are standards devised by Microsoft. These are the only ways for getting at Microsoft-hosted data (for example Excel, Access, SQL Server).

nandu · Post by **nandu** » Tue Jan 25, 2005 9:31 am

Hi ,
Can anybody help me :

do i need DSN to import table definations if am using OCI for ORacle Stage ?

Or without DSN i can import the metadata and insert the data from sequential file to oracle tables

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jan 25, 2005 9:34 am

For Oracle, you can use the OCI plugin that connects directly to Oracle without an ODBC. To do this, you need to have your tnsnames.ora setup for that Oracle SID in the DataStage Server machine. In other words, you must be able to run

sqlplus user/pass@oracle_sid

where user and pass are oracle connection params and oracle_sid is the Oracle SID - which you intend to connect to.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 25, 2005 3:57 pm

Just an observation in passing.

I teach never to use that form for sqlplus connection string. The reason is that your password is exposed; anyone looking at the processes (using a ps -ef command on UNIX) or anyone looking over your shoulder at the screen will be able to see your Oracle password.

Better is to use

Code: Select all

sqlplus user@oracle_sid

and have sqlplus prompt you for the password, in which case it's not echoed and does not appear on your command line.

The same is true in shell scripts. Load up the password in an environment variable and use that in the script.

Code: Select all

sqlplus ${ORACLE_USER}@${ORACLE_SID} << -EOI
${ORACLE_PASSWORD}
# other SQL statements go here
-EOI

T42 · Post by **T42** » Wed Jan 26, 2005 12:27 am

One interesting aspect of Oracle Enterprise stage on 6.x. Well, you should pay very close attention to the files being created on your scratch space.

Upgrade to 7.x, and this surprise will be eliminated. A very good reason to have the latest version, no?

I'll leave it to everyone to discover this interesting situation.

DSXchange

ODBC stage

ODBC stage

Do i need a DSN if am using oracle OCI stage