Configuring Oracle Database in DataStage

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

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

Now 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  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

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)

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 

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

Password = Tiger

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

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

The 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 database  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”]

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  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 

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  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 Workbook 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 an 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