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)

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

Now

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