DB2 from an Oracle Perspective, by Enzo Cialini and George Baklarz

By Enzo Cialini posted Aug 25, 2015 07:06 AM


How can there be a DB2 perspective for the Oracle DBA? There is almost nothing more sacred than a DBA's loyalty to their database of choice, so why would you consider one DBMS over another (and trust us, the debates get heated over this)?

The reality is that we are dealing with major changes in the types of data being captured, the quantity of data being managed, and the way that the data is being analyzed. Sometimes our traditional DBMS (which we know can do everything!) may not be the best choice for some applications.

So sometimes you need consider a different database that may be outside your comfort zone. Luckily for an Oracle DBA, you don't really need to learn too much about DB2 because a lot of the concepts are similar. They aren't identical products, but you'll soon find that working with DB2 isn't as hard as you first might have thought.


A Lot of Stuff is Similar

Before diving into DB2 concepts and objects, let’s review what you don't have to worry about any more. The DB2 LUW product (Linux, Unix, Windows) has been around for a while and contains a significant amount of features that are equivalent (or similar to) those in Oracle. Some features have been added within the last five years to help customers move applications over to DB2.

Any move to a new technology is potentially disruptive - from migration of the data, re-write of the application, and training/skills of the users. In order to minimize some of these costs, DB2 now includes support for a number of features that are found within an Oracle database: 

  • Oracle data types including NUMBER and VARCHAR2
  • Oracle administration VIEWs
  • Unique Oracle SQL like OUTER JOIN (+) and hierarchical joins
  • Currently committed support (readers don't block writers, writers don't block readers)
  • PL/SQL support (Yes, really!)
  • PL/SQL Packages
  • Built-in package library
  • Oracle JDBC extensions
  • Oracle Call Interface (OCI)
  • SQL*Plus Scripts

In fact, some DB2 behaviors were changed to avoid errors when migrating apps from Oracle. For instance, weak typing ("1" = 1) has always worked in an Oracle environment, but used to fail in DB2 because of strong typing.

Even what would be considered a simple table creation in Oracle would sometimes fail in DB2:


If you had a 4K-page size in DB2, this table creation would fail because the potential size of a row would be 6000 bytes – larger than the page size. This may not be the best design for a table, but it happens all the time. If you don't actually use that space, why should DB2 give you an error? This would have failed in the past, but now DB2 recognizes that the table could exceed the size of a physical page on disk and handles it by overflowing some of the data to another location.

 The end result is that well over 99% of Oracle procedures, functions, and code will migrate with very little effort.  So less work for the DBA and the developer to get things moved over to DB2. A side benefit is that you can continue to use Oracle coding techniques and logic in a DB2 environment, so considerably less effort will be required to get comfortable with DB2.


What You Need to Know About DB2 Objects

From the logic side (applications, stored procedures, triggers, functions) and table design (data types, integrity), most things move to DB2 without any change. When it comes to environmental setup (Table space, Buffer pools, etc.) things are different between the two technologies.

The objects that you need to be aware of, and how they relate between Oracle and DB2, are listed below.

  • Instances
  • Databases
  • Storage Groups
  • Table Spaces
  • Buffer Pools
  • Schemas
  • Access Control
  • Workload Management


Instances and Databases

Instances and Databases are two key fundamental concepts in both Oracle and DB2.   However, the implementation of both is different not only between Oracle and DB2, as expected, but also within Oracle, when looking at multitenant vs. non-multitenant configurations.  This could lead to confusion during migration from Oracle to DB2 and hence is a great starting point for us.

Let’s start by looking at Oracle architecture related to Instance and Database, prior to Oracle 12c multitenant capabilities.


Figure 1 shows an architecture diagram of Oracle Database, prior to the 12cR1 release, and depicts the relationship of Instance and Database.  Each Oracle instance contains only 1 Oracle database. If multiple databases are required on a single server host, this requires another complete Instance and Database. So essentially, an Oracle Instance and Database are one in the same as they are a 1:1 relationship.  The “instance” contains the memory structures and background processes used to access data from the database.  The “database” contains the physical files (Data, Control, Redo logs, etc.) used to store the information (data).   Database clients/applications connect to the database via the Oracle SID (System Identifier).


Looking at a similar architecture diagram of DB2, Figure 2, each DB2 Instance can contain 1 or more Databases (i.e., a one-to-many relationship of Instance to Database).   With DB2, if more databases were required on a single server host, you can create additional databases within the same instance.  However, you also have the option to deploy an additional DB2 Instance and Database, if desired.  You can have multiple instances on a given host and each instance can manage multiple databases.  The DB2 “instance” contains the directory information and Engine Dispatchable Units (EDUs) common to all databases created within the instance.  The “database” contains processes, memory, configuration, objects, and physical files (Data, Logs, etc.) used to store the information (data).

Let’s have a quick look at the multitenant perspective of both Oracle and DB2.


Figure 3 is a high-level architectural view of Oracle Database multitenant architecture. The elements to the left of the dashed line are shown in red and represent the Common Database (CDB). These are shared amongst ALL databases in the Oracle instance. The elements to the right of the dashed line (i.e., those displayed in purple) represent the Private Database (PDB) and are private or specific to each database and not shared. The maximum number of PDBs that can be associated with a CDB are 252.

Let’s take a closer look at some of the Oracle CDB elements.  

The initialization parameters element refers to the 210 of 357 key Oracle parameters like buffer cache size, KEEP and RECYCLE pools, etc. that must be identical for ALL PDBs. Hence, each parameter setting must be based on the largest PDB requirements even if other PDBs do not require the same or are offline. 

The shared System Global Area (SGA) requires that database memory and CPUs be configured at a minimum to handle the largest PDB within a CDB. Since the CDB-PDB architecture is advertised as being able plug PDBs in and out of different CDBs, the SGA sizing between different CDBs needs to be considered prior to an unplug and move operation. 

The Redo Log Set and Flashback elements are responsible for all logging activities of the PDBs. As this is shared among PDBs, it means that all PDBs are using the same logging device and the logs contain data from different PDBs.  Hence, the logging rate/requirements of one PDB can impact other PDBs.  The common logging could cause security concerns, as the data is not truly separated by PDBs.

Similar to the logging issue, a common UNDO table space may also present a security concern. In addition, the UNDO table space must be sized for the most active PDB in terms of write activity.

Common users are defined as a CDB element. This can be a benefit as there is one location in which to define all users. However, it can also be a security exposure, especially privileged users like SYSDBA and SYSOPER, for having multitenant applications with different access requirements and separate security. It is possible to prevent a SYSTEM user from executing against any PDB within a CDB by implementing Oracle Database Vault at the PDB-level.

Oracle’s multitenant solution does allow for multiple databases but awareness and care of the usage and security requirements needs to be taken into consideration when determining which PDBs to deploy within a CDB.

DB2 for LUW has had the concept of multitenancy from the beginning. Figure 4 is a high-level architecture view of DB2 multitenant databases.  You’ll notice that the architecture is the same as previously shown in Figure 2.


The elements to the left of the dashed line are common to the databases.  The Instance and Database Manager (DBM) parameters (i.e. those in blue) are common to the databases created within the Instance. The User elements (those in yellow) represent the authentication of user IDs and are common to all databases in all instances – authorization and privileges are handled within each database separately.  This provides simplified user management within a host, i.e. Operating System (OS) level, and optionally across the enterprise, i.e. LDAP level.  Those elements to the right of the dashed line (i.e. those in green) represent the core database related elements and are specific and private to each database. The maximum number of databases within a DB2 Instance are 256.

Taking a closer look at some of these DB2 database elements we have the Database memory (DB memory) which consists of the Buffer Pool, Database Heap, Sort Heap, etc.  This memory is defined and allocated uniquely to each database, allowing for ALL the memory to be dedicated to the particular database and also allows the DBA to control memory allocations as needed by the database.

Logging activity (transaction logs and archive logs) is separate for each database. There is no need to worry about logging rates, impact or security risks from other databases.

Although user password authentication is common, the actual user authorization and privileges are uniquely defined in each database.

The DB2 for LUW multitenant architecture provides true multitenant separation of application databases, by having different logs, parameters, database memory, and database authorizations and privileges for each database underneath a DB2 database instance. 

DB2 objects within a database

Now that we’ve covered the basics of Instance and Database, let’s have a look at the relationship of the DB2 objects within the database, as depicted in Figure 5. 


We’ll briefly touch on these to give a high-level understanding: 

  • Storage
    • physical storage devices
    • can have a variety of devices, such as HDD, SATA, SSD, etc
    • can be internal, storage area network (SAN), direct attached, fiber channel attached, network attached, etc
  • Storage Groups
    • set of storage paths where data can be stored
    • can have multiple table spaces associated with a storage group
    • define new storage group within the database via CREATE STOGROUP
CREATE STOGROUP sg_ten2 ON '/path1', '/path2’
  • Table spaces
    • logical object used to organize data, tables, indexes, etc. in a database
    • each table space can only be assigned to one Storage Group
    • initially assign table space to storage group via CREATE TABLESPACE
    • ability to reassign/move table space online between storage groups
ALTER TABLESPACE tbsp_ten1 USING sg_ten1a
    • authority control via GRANT USE OF TABLESPACE
  • Tables & Indexes
    • physical object to hold the actual data in the database
    • assigned to one or more Table Spaces
    • can be assigned to a specific Buffer Pool (via choice of tablespace)
  • Schemas
    • Used to classify or group objects in a database
      • Table, Index, View, Alias, Function, etc
    • Defined using CREATE SCHEMA command
      • CREATE SCHEMA george
    • CURRENT SCHEMA special register is used to identify the schema name for the current session
    • Use SET SCHEMA statement to change schema during current session
    • Authority control via GRANT CREATEIN ON SCHEMA
      • GRANT CREATEIN ON SCHEMA george TO enzo
  • Buffer Pools
    • working memory or cache for table and index data
    • Ability to isolate data into different buffer pools
    • Can have multiple buffer pools and of different sizes
    • Can have multiple table spaces associated with a buffer pool


Commands and Terms

Now that we have some fundamental understanding of Instance and Database, let’s review of some commands and terms used frequently.

Working with a database you have a familiarity of how to not only start and stop a database but also what terms like data cache, autoextend, and alert log are.  Here we’ll touch on some most common Oracle commands and terms used and map them to the equivalent DB2 command and term.

From an instance and database administration perspective there are only a few commands to understand, as shown in Figure 6, that are used to start and stop of the instance and database.


As a means of helping with the migration from Oracle to DB2, Table 1 provides some common Oracle terms to DB2.


The creation of a database is another area of difference that we’ll explore. Although both Oracle and DB2 have a ‘CREATE DATABASE’ command, the actual steps required are different.

The creation of database in Oracle is as follows:

  1. Set environment variables
  2. Create password file
  3. Create pfile
  6. Create data dictionary views
  7. Create PL/SQL packages
  8. Add the SID to tsnames.ora

It’s much simpler to create a database in DB2. The same create database in DB2 is as follows:

  1. Set registry variables

DB2 uses external authentication and as such no password file is necessary.  During the database creation, DB2 automatically creates the system catalog, and when the compatibility vector is enabled, it will also create the Oracle-compatible data dictionary views and PL/SQL packages.  DB2 uses ‘autoconfigure’ feature to set the variables.  Hence, no files (like Oracle pfile) are needed.  Also, DB2 automatically has Self Tuning Manager enabled by default.

With well over 99% of Oracle procedures, functions, and code migrating to DB2 with very little effort and continuing to use Oracle coding techniques and logic in a DB2 environment, you’ll find that working with DB2 isn’t as hard as you first might have thought.

About the Authors

Enzo Cialini is currently a Senior Technical Staff Member in the Worldwide Core Database Technical Sales organization for Analytics and formerly the Chief Quality Assurance Architect for DB2 & PureData in the IBM Toronto Laboratory. He has 20+ years experience in database technology from software development, testing, support and production deployments with DB2 LUW. Enzo is also a published book author and various papers on DB2.

George Baklarz, B. Math, M. Sc, Ph.D. Eng., has spent 30 years in IBM working on various aspects of database technology. From 1987 to 1991 he worked on SQL/DS as part of the product planning department, system test team, performance team, and application development group. In 1991, Baklarz was part of the team that helped move the OS/2 ES database to Toronto to become part of the DB2 family of products. Since this time he has worked on vendor enablement, competitive analysis, product marketing, product planning, and technical sales support. He currently manages the Worldwide Core Database Technical Sales department.