Configuring Oracle Database in DataStage

Oracle Database

Select database Category

• There are 4 new stages in 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 Read Method

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

1. Oracle -Data set

Oracle properties Source  Read Method = Table  (Select)

Table = EMP  (Specify)

Connection

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 columnsLoadTable DefinitionsImportPlug in Metadata definition Select the Driver that is Oracle 9  okData source Name = oracle/User id = Scot/ Password = TigeràNextà coroner  list = Scott    Table  Next Select  EMP import Table Definition  plug – in ORACLE 9  EMP
• IN columns

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

Now 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,……)

2. Read Method = Auto-generated SQL

Oracle enterprise  −  Dataset

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

Properties  Read Method  = Auto generated SQL      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

3. Read Method = SQL builder generated SQL

Now go to file

Now

Oracle Enterprise  —   Dataset

              ↓

Properties  Read Method = SQL builder generated SQL.

SQL Query = <<SQL builder Query>>

Password = Tiger

User = Scott     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.

2. Select columns 

Required columns will be displayed

3. 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 a data connection, then it automatically other 2 records enterprise Should the username, password.

(i)   Oracle enterprise          Data set

       ↓                ↓

Properties         User = Scott

Password = Tiger

Remote Server =  Oracle   StageData connection Browse  Save data connection  data connection Name = starOk Save Data connection as Table  Definition Star Save  ok

1. Oracle enterprise  dataset

Properties  stage  data connection = load data connection  Table Definition  star  open ok   (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 New  other (click on data connection)  ok (click on data connection name = shilpa)  click ok parameters  browse stage types  parallel data base  oracle enterprise open Server = oracle  user id = Scott  password = Tiger ok0Ok Save in Table Definition  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  Repository  Table Definition select Data connection Right click  delete

ODBC Enterprise 

ODBC enterprise      —       Dataset

Properties Read method = Table

Table = EMP

Connection Data source = chaladi

click on connection user = Scott password = tiger

columns à load à view data

Creation of Data source 

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

[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  it lists the DSN Names Select the Data Source  (that is chaladi) ok username = Scott  password = tiger Click on test  display the connection is good SQL Select Statement  = select * from EMP click 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 a substitute that is ODBC connector

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

ODBC connector 

1. Version Independent à depends on operating system To connect the Oracle database.
2. Poor performance
3. ODBC connector supports reject link 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  programs  micro soft excel create excel sheet EMP DEPTRename the sheet1, sheet2  save then with .xls extension

Creating DSN for excel Driver :-

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

ODBC enterprise   —    Dataset

Properties  Read Method = Table

Data source = chaladil

User = admin                         system user id and password

Password = phil

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

Now, in properties

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

For in-depth understanding of DataStage click on

• Introduction to DataStage
• Architecture of Data Stage
• Oracle Enterprise in Data Stage
• SCD(Slow changing Dimension) in Data Stage
• DataStage Tutorials