Configuring Oracle Database in DataStage

  • Share this blog:

Oracle Database

Select database Category

  • There are 4 new stages in the database
  • That is in 8 version
  • I way Enterprise
  • Classic Federation
  • Netezza Enterprise
  • ODBC

 To read the data from Oracle

We have 4 options in Reading Method

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

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 Metadata 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 plugin 7ORACLE 9 7 EMP
  • IN columns

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

7Now view Data

Do you want to master DataStage? Then enrol in "DataStage Training" This course will help you to master DataStage

To Select Required fields 

Read Method = user-defined SQL

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


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

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

Read Method = Auto-generated SQL

Oracle enterprise  −  Dataset


First head method = Table load and then 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

Read Method = SQL builder generated SQL

Now go to file


Oracle Enterprise  —   Dataset


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)

Select Table 

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

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

Select columns 

7Required columns will be displayed

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 :

The data connection is only in the 8 versions.

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

The first way of creating a Data connection 

Suppose that we have 3 different oracle enterprises, and in 1 oracle enterprise We create a 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 = stark 7Save Data connection as 7Table  Definition 7Star 7Save 7 ok

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, a remote server)

The 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 7database 7 oracle enterprise 7open 7Server = oracle 7 user-id = Scott  password = Tiger 7ok07Ok7 Save in Table Definition 7 ok
  • Now, we have created a 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      —       Dataset

Properties 7Read method = Table

Table = EMP

Connection Data source = chaladi

Click on connection user = Scott password = tiger

Columns à 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”]

To overcome the limitations of the ODBC enterprise we have an ODBC connector.

ODBC Enterprise

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

ODBC Connector

  • 0.1 version
  • List the DSN
  • Can test the connection (either good or not )
  • Reads parallel, writes parallel
  • Good performance
  • Schema Reconstitution (we can view data, even if a 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 

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 overcome this, we have a substitute that is ODBC connector

Good performance

Oracle enterprise supports reject link at target but not at the source

ODBC connector 

  • Version Independent à depends on the operating system To connect the Oracle database.
  • Poor performance
  • ODBC connector supports reject links both at source and target.

In oracle enterprise à while loading àIn import à, we are using plugin Metadata

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 Workbook
  • Filename = Workbook à 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 Workbook 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 an in-depth understanding of DataStage click on

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.