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
- 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 columns
Load
Table Definitions
Import
Plug in Metadata definition
Select the Driver that is Oracle 9
ok
Data 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
- 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,……)
- 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 :
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 Stage
Data connection
Browse
Save data connection
data connection Name = star
Ok
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, 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
ok0
Ok
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
- 5 *2 version
- Cannot list the DSV
- Cannot list the connection
- Reads Sequentially writes parallel.
- Very poor performance
- If 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 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 over come 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 operating system To connect the Oracle database.
- Poor performance
- 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 DEPT
Rename the sheet1, sheet2
save then with .xls extension
Creating DSN for excel Driver :-
- Start à control panel
administrative Tools
Data source
system DSN
AddMicrosoft 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
import
ODBC Table Definition
Select the DSN : Chaladil user name = admin password = phil
Ok
- 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