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
ODBC stage
Moderators: chulett, rschirm, roy
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Do i need a DSN if am using oracle OCI stage
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 useand 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.
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
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.