DataStage migration from DB2 to Oracle or Oracle to DB2-Part1

DataStage migration from DB2 to Oracle or Oracle to DB2-Part1

As time passed most the projects will move to the new environment or will upgrade from the OLD environment based on the project requirement. One the usual thing which happens is Database exchange in the project. I am explaining this from my experience where we had already working DWH with the below structure


Database-DB2(DWH Database)

ETL Tool –DataStage.

Due to new project implementation we got the new requirement as below



ETL Tool-DataStage

We have built this environment same as OLD environment only the Oracle DB is new here

The most challenging thing was to migrate the DataStage jobs as it is without doing more coding or creating new DataStage jobs from DB2 to Oracle.

Let us know the DB2 and Oracle objects in details and the difference





Table Space










Typed Table

Object Table

Temporary Table

Temporary Table



Check Constraint

Check Constraint

Column Default

Column Default

Unique Key

Unique Key

Primary Key

Primary Key

Foreign Key

Foreign Key

UDB SQL Procedure

PL/SQL Procedure

UDB SQL Function

PL/SQL Function

UDB Package

PL/SQL Package

UDB Trigger

PL/SQL Trigger

Table Alias

Public Synonym





Typed View

Object View

Identity Column

Auto Increment Column

Structured Data Type

Abstract Datatype


Binary File

Tables containing structured data type

Table containing abstract datatype

View containing structured data type

View containing abstract datatype

Stored Procedure

Stored Procedure




From the above we can find most of the objects are common But the main this is “users”. This where the problem exists. Let’s talk more on user and schema in detail.

Oracle Database Architecture

Oracle database contain 2 main components.

1. Instance: Memory structures and background processes constitute an instance

2. Database: disk resources



As we cover above, the memory structures and background processes constitute an instance. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA), and an optional area –– Software Area Code. In the other hand, the mandatory background processes are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monitor (PMON). And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc. Figure 2 will illustrate the relationship for those components on an instance.


System Global Area

SGA is the primary memory structures. When Oracle DBAs talk about memory, they usually mean the SGA. This area is broken into a few of part memory –– Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Shared Pool

Shared pool is broken into two small part memories –– Library Cache and Dictionary Cache. The library cache is used to stores information about the commonly used SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statemens among users. In the other hand, dictionary cache is used to stores information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc.

The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization parameter file.

Redo Log Buffer

Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program Global Area

Although the result of SQL statemen parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Software Area Code

Software area code is a location in memory where the Oracle application software resides.

Oracle Background Processes

Oracle background processes is the processes behind the scene that work together with the memories.


Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.


Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.


Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.


System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles.


Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.


The database refers to disk resources, and is broken into two main structures –– Logical structures and Physical structures.

Logical Structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data efficiently. The logical units are tablespace, segment, extent, and data block. Figure 3 will illustrate the relationships between those units.


A Tablespace is a grouping logical database objects. A database must have one or more tablespaces. In the Figure 3, we have three tablespaces –– SYSTEM tablespace, Tablespace 1, and Tablespace 2. Tablespace is composed by one or more datafiles.


A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are Temporary Segment and Rollback Segment.


A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes.

Physical Structures

The physical structures are structures of an Oracle database (in this case the disk files) that are not directly manipulated by users. The physical structure consists of datafiles, redo log files, and control files.


A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles.

Redo Log Files

Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes.

Control Files

Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc.


DB2 Database Architecture



Database Administrator server (DAS): which should be up and running always. It is only one user for the whole server. If it is down mans the DB2 server will be down.

Instance (db2inst1): This is the instance which will have multiple number of database inside it.we can have more than one instance for one DB2 server.

Fence user(db2fence):This is used to run the functions and procedures in the Database.

Database: The database refers to disk resources, and is broken into two main structures –– Logical structures and Physical structures



More reference:


Views: 3025


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Lohith on April 20, 2012 at 4:43am

sorry was busy in other work will update the part2 ASAP

Comment by Irene on March 15, 2012 at 10:39am

Interesting topic Lohith! I wonder how the migration's gone, the steps followed and if you have some raccomandations... in other words: when will we see the part2? :D

Comment by Lohith on November 27, 2011 at 9:36am



yes my friend without DAS the instance and database can continue but to connect from client to do admin activities in db2cc or db2cmd ..etc we need DAS i believe.

Comment by Andres Gomez Casanoav on November 25, 2011 at 1:55pm

Thanks for this excellent comparison between DB2 and Oracle, and technicals details about Oracle.

I have also a comment about the DAS definition that you gave. You say that it should be up and always running, but only for administration purposes. The instances and databases can continue working without the DAS. Even, the DAS in DB9 is deprecated.

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service