• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Configuring Oracle Database in DataStage

Oracle Database

Select database Category

  • There are 4 new stages in data base
  • That is in 8 version
  • I way Enterprise
  • Classic federation
  • Netezza Enterprise
  • ODBC

 To read the data from Oracle

  • We have 4 options in Read Method

Read Method =   Table /user defined  SQL / Auto generated / SQL builder generated SQL


  1. Oracle –7Data set


Oracle 7properties 7Source  Read Method  = Table  (Select)

Table = EMP  (Specify)


DB options  = {  user = <<user>>, password = <<password>>}

DB options Mode = Auto  generate

Password = Tiger

User = Scott

  • Click on connection
  • Click on Remote Server
  • Remote Server = Oracle
  • Click on columns7Load7Table Definitions7Import7Plug in Meta data definition 7Select the Driver that is oracle 9 7 ok7Data source Name = oracle/User id = Scot/ Password = TigeràNextà coroner  list = Scott    7Table 7 Next 7Select  EMP 7import 7Table Definition 7 plug – in 7ORACLE 9 7 EMP
  • IN columns

[7 [change the SQL type of Hire date from Data to time stamp ]

7Now view Data


To Select Required fields 

  1. Read Method = user – defined SQL

SQL   Query = Select e no, e name, Sal from emp where dept no = 10


(click on columns on right side, to view all the column names of EMP tables

Thai is e no, e name Sal, job, Hire data,…… )


  1. Read Method = Auto generated SQL


Oracle enterprise  −  Data set

↓                         First head method = Table load and than go for  Auto generated SQL

Properties  7Read Method  = Auto generated SQL  7    Read Method  = user defined  SQL

SQL Query = Select emp no, e name, job from emp where dept no = 10;

  • Now go to columns:-
  • Delete the remaining fields other than e no, e name, job
  • Ok
  • Now view data
  1. Read Method = SQL builder generated SQL

7Now go to file


Oracle enterprise  —   Data set


Properties 7 Read Method = SQL builder generated SQL

SQL Query = <<SQL builder Query>>

Password = Tiger

User = Scott    7 click on build SQL

Remote Server =  Oracle  à Build new Query  (oracle 9 syntax)


  1. Select Table 

Table Definition – plug in – ORACLE 9- Scott –Select EMP , DEPT

  • Here double click on the required columns from both the table.


  1. Select columns 

7Required columns will be displayed


  1. construct filter expression (WHERE Clause) 


Predicates                                    expression editor

    ↓                          ↓

Comparison in link null              emp. Dept no  =  10  add


  • Click on Add
  • Click on SQL
  • Ok
  • Now the query will be displayed in SQL query
  • View data

Data Connection

  • In version 7.5 * 2 , stage property values cannot be reused.
  • In version 0.1 , stage property values cannot be reused.

Note :-

Data connection is only in 8 version

It is a technique for saving and reusing stage property values.


First way of creating Data connection 

Suppose  that  we have 3 different oracle enterprise , and in 1 oracle enterprise We create an data connection , then it automatically other 2 records enterprise Should the username, password

(i)   Oracle enterprise          Data set

       ↓                ↓

Properties  7       User = Scott

Password = Tiger

Remote Server =  Oracle  7 Stage7Data connection 7Browse 7 Save data connection 7 data connection Name = star7Ok 7Save Data connection as 7Table  Definition 7Star 7Save 7 ok


  1. Oracle enterprise  dataset


Properties 7 stage 7 data connection = load data connection 7 Table Definition 7 star 7 open 7ok   (it that is oracle enterprise automatically gets user id, pwd, remote    server)

Second way of creating Data connection :-  (BY default to all oracle enterprise)

  • Go to file 7New 7 other (click on data connection) 7 ok (click on data connection name = shilpa) 7 click ok parameters 7 browse 7stage types  7parallel 7data base 7 oracle enterprise 7open 7Server = oracle 7 user id = Scott  password = Tiger 7ok07Ok7 Save in Table Definition 7 ok


  • Now, we have created an Data connection with the name “Shilpa”
  • Now, oracle enterprise à properties à stage à Data connection à click on shilpa à ok
  • Now again click on properties à now, user id, password, remote server

will be automatically displayed.

Deleting Data connection 

View  7Repository 7 Table Definition 7select Data connection 7Right click 7 delete


ODBC Enterprise 

ODBC enterprise      —       Data set


Properties 7Read method = Table

Table = EMP

7Connection Data source = chaladi

7click on connection user = Scott password = tiger

7columns à load à view data


Creation of Data source 

Start 7control panel 7 Administrate Tools 7 Data source (ODBC) 7System DSN 7 Add Microsoft ODBC for oracle 7 finish 7    Data source = chaladi   user = Scott  Server = oracle 7ok

[Data source  Name is created with the Name “chaladi”]

At TekSlate, we offer resources that help you in learning various IT courses.
We avail both written material and demo video tutorials.
To gain in-depth knowledge and be on par with  practical experience, 
then explore DataStage Training.

To overcome the limitations of ODBC enterprise we have ODBC connector

ODBC Enterprise


  1. 5 *2 version
  2. Cannot list the DSV
  3. Cannot list the connection
  4. Reads Sequentially writes parallel.
  5. Very poor performance
  6. If data type mismatch is there , we can’t view data.

ODBC Connector

  1. 0.1 version
  2. List the DSN
  3. Can test the connection (either good or not )
  4. Reads parallel , writes parallel
  5. Good performance
  6. Schema Reconstitution (we can view data , even if mismatch is there)

Automatically handles the conflict between Source data type and Data stage datatypes.

ODBC connection 

ODBC Connector —– Data set

Double click  on ODBC connector 7 it lists the DSN Names 7Select the Data Source  (that is chaladi) 7ok 7username = Scott 7 password = tiger 7Click on test 7 display the connection is good 7SQL Select Statement  = select * from EMP 7click on view Data

Note :

Oracle enterprise 

  1. Version dependent

It works only with the versions that are associated with Dataset(that is it can access Oracle 9 i but not oracle10g and 11g )To over come this, we have an substitute that is ODBC connector

  1. Good performance
  2. Oracle enterprise supports reject link at target but not at the source

ODBC connector 

  1. Version Independent à depend on operating system To connect the oracle data base.
  2. Poor performance
  3. ODBC connector supports reject link both at source and target.

In oracle enterprise à while loading àIn import à we are using plug in Meta data

Then we need to change Data Datatype to Time Stamp.

But while import à use or Orchestrate

It automatically takes à time stamp Best Results.

To read Excel data with ODBC 

  • Xls filename is known as Work book
  • File name = Work book à user


Sheet, sheet 2, …

While reading Data with ODBC

Sheet 1 becomes Table 1

Sheet 1 becomes Table 2


  • Starts 7 programs 7 micro soft excel 7create excel sheet EMP DEPT7Rename the sheet1, sheet2 7 save then with .xls extension

Creating DSN for excel Driver :-

  • Start à control panel 7 administrative Tools 7 Data source 7system DSN7AddMicrosoft excel Driver (* .xls) à Data source Name = chaladil 7Select Work book 7 D :/shilpa à Double click dept .xls 7 emp .xls Select Emp.xls 7ok 7 Select chaladil

ODBC enterprise   —    Dataset

Properties 7 Read Method = Table

Data source = chaladil

User = admin                         system user id and password

Password = phil

  • Columns 7 load 7 Table Definition 7 import7ODBC Table Definition 7 Select the DSN : Chaladil user name = admin password = phil7Ok
  • Select filter include System Tables 7Ok
  • Select EMP 7Import
  • Table Definitions
  • ODBC
  • Chaladil
  • EMP
  • Ok
  • Ok

Now, in properties

Table = “EMP$”  (always table name should be in double Quotes followed with $ symbol)

For indepth understanding of DataStage click on

Review Date
Reviewed Item
Configuring Oracle Database in DataStage
Author Rating

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Configuring Oracle Database in DataStage"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.