Oracle migration
This section provides an overview of the migration process required from Oracle to DB2 Express-C
. At this time, we only describe migrations from Oracle 9i; however, migrations from other versions of Oracle should be similar. Once you have migrated to DB2 Express-C, if your needs grow in the future, it is easy to upgrade to other DB2 Editions with no changes required.
Architecture Comparison
Though Oracle's and DB2's architectures are different, some of the concepts are the same. In order to take advantage of your Oracle skills to learn DB2, we use Table 1 to map some of these concepts.
Table 1 - Summary of Oracle vs. DB2 UDB concepts
| Oracle | DB2 UDB | Comment |
|---|---|---|
| Instance | Instance | A DB2 UDB instance can contain several databases |
| Database | Database | |
| initSID.ora OR SPFILE | DBM CFG and DB CFG | DB2 UDB uses two levels of configuration:- Database Manager Configuration (DBM CFG) (at the instance level)- Database Configuration (DB CFG) (at the database level).Like in Oracle, many of these configuration parameters can be dynamically changed. |
| Table spaces | Table spaces | DB2 UDB supports SMS and DMS Table spaces. DMS Table spaces are similar to Oracle's. |
| Data Blocks | Pages | DB2 UDB supports these page sizes: 4k, 8k, 16k, 32k. A row must fit any of these page sizes. It cannot span to other pages like in Oracle's. |
| Extents | Extents | |
| Data Files | DMS Table space containers | Containers for DMS table spaces can be raw devices or files. |
| Redo Log Files | Transaction Log Files | |
| Data Buffers | Buffer pools. | DB2 UDB does not have a predefined set of bufferpools, but you can create as many as desired. A bufferpool of a given page size must be exist prior to creating a table space with the given page size. |
| SGA | Database Manager and Database Shared memory | |
| Data Dictionary | Catalog | |
| Library cache | Package cache | |
| Large Pool | Utility Heap | |
| Data Dictionary Cache | Catalog cache | |
| SYSTEM table space | SYSCATSPACE table space |
Migration considerations
There are many migration considerations that must be taken into account when migrating from Oracle to DB2. In this page we cannot cover all such considerations, but we list here some of them so you get an idea of the work involved. Note that there are migration tools, as you'll see in the next section, that can substantially reduce the conversion work.
1. The amount of stored procedures, functions and triggers using proprietary Oracle PL/SQL syntax can add to the complexity of a migration. For example, Oracle's "connect by prior" statements can be challenging, however, they can be rewritten with DB2's common table expressions (WITH clause)
2. In Oracle the function dbms_output.put_line() is used frequently for debugging purposes. In DB2 you can implement a function like that yourself, or use the Development Center GUI tool to debug your SQL stored procedures.
3. Oracle performs implicit data type casting in some cases, while DB2 is strongly typed.
Please see the resources section for more migration consideration details.
Tools for migration
The IBM Migration toolkit(MTK) can save you time and effort when migrating from Oracle to DB2. The MTK can extract your Oracle database structure and convert it into DB2's structure. Stored procedures, triggers and functions can also be converted with this tool. The MTK can also be used to extract your data and load it into DB2.
Resources
This page provided a brief comparison between Oracle and DB2, and described some migration considerations. For more information, please refer to these sources:
