Tag Archives: DB2

DB2 Vs. Oracle

I was assigned to find some difference between DB2 and oracle. They are both enterprise level database management system. Tried to find some difference.



DB2, allows administrators to run maintenance tasks, such as backups, space reorganization, etc., during a predefined window, typically during non-peak hours. DB2 allows you to run only a predefined set of tasks automatically. Oracle also performs predefined set of tasks automatically. The tasks of automated optimizer statistics collections, Automatic-SQL Tuning, and re-organizing fragmented tables are set out-of-the-box to run periodically in the pre-defined maintenance window with Oracle Database 11g.
DB2 leaves the resource control to individual utilities, by running them in throttling mode, if available. DB2 utilities throttling is based on a generic performance metric sensor that causes the utilities to pause and resume, a far less  sophisticated technology than the fine-grained resource management, based on CPU %, Degree of Parallelism, Maximum Execution Time, etc., that the Oracle Resource Manager provides. Oracle’s Maintenance Windows can control these resources using the Resource

Manager plans. The Oracle Resource Manager optimizes resource utilization globally using bandwidth quotas and allows maintenance tasks to use available resources without impacting higher priority activities.

DB2 requires shared locks for readers, therefore any updates to those blocks may be blocked. Oracle implements a unique multi-version read consistency model and ensures that readers and writers never block each other by copying the original data values in database structures called ‘undo segments’.
DB2 needs to rely either on rules of thumb or instantaneous performance metrics, none of which can guarantee the same degree of effectiveness as the historical data stored in AWR. The Automatic Workload Repository (AWR), introduced in Oracle Database 10g, is a self managing repository of data that is used internally by Oracle to self-diagnose and tune. the

self-management decisions made by Oracle Database 11g are always specific to the environment that the database is operating in.

This kind of analysis is not available to DB2 DBAs. Issues that could have been caught by ASH

have the potential of surfacing later in a much more aggravating form and could be the reason behind some serious firefighting efforts.

Active Session History (ASH) samples the current state of all active sessions, i.e., those sessions that are connected to the database and at that moment are usingCPU, or waiting on a non-idle event. Active sessions are sampled every second and stored in a circular buffer in the system global area (SGA).
DB2 V9.7 lacks an equivalent self-diagnostic component to assess its own performance. All that DB2 offers for performance monitoring is the Health Monitor, exposed via alerts and reports, which notify the DBA any time a problem symptom is detected.

These alerts are accompanied by a recommendation about how to fix a particular problem.

However, there is no infrastructure which investigates all those symptoms and identifies the root

cause of problem

The unique Automatic Database Diagnostics Monitor (ADDM) technology by itself places

Oracle far ahead of any competitors, in manageability. ADDM is a self-diagnostic engine built right into the Oracle Database 11g kernel. It enables the Oracle Database 11g to automatically diagnose performance problems, find the root cause and recommend solutions based on self collected data in AWR.

IBM’s Health Monitor is somewhat similar to Oracle Database 11g’s Server Generated Alerts and the Enterprise Manager alerting framework. But Oracle Database 11g solution remains more powerful and comprehensive. Health monitoring for DB2 happens outside of the database engine by periodically polling for data and comparing values with thresholds. With Oracle Database 11g, health monitoring and alerting is done by the database itself, therefore in a much more efficient manner and only as needed. For example, Oracle performs space health checks incrementally as space is allocated and freed up in the database server. This guarantees immediate availability of space usage information. Computed on-the-fly, if space usage exceeds predefined thresholds, alerts are proactively sent through the appropriate channels. Oracle Database 11g’s built-in Server Generated alerts and Enterprise Manager’s propagation framework, along with its browser-based interface, provide the foundation to manage systems performance problems and database maintenance tasks. The various self-managing initiatives provided by Oracle Database 11g assist in enabling automation of Oracle systems – reducing manual intervention, lowering costs and providing better quality of service.
With DB2, if an operation runs into an out-of-space situation, the entire operation may have to be repeated after the space issue has been addressed. This not only results time lost, but it can also hamper normal database performance if the out-of-space situation forces the DBA to re-run the batch job during normal workload hours. Whenever an operation encounters an out-of-space situation, it is held in a “suspended” state while the administrator is notified of the problem and given a chance to fix it. The “suspended” operation automatically resumes as soon as the error condition is corrected.
DB2 Design Advisor is similar to Oracle Database 11g’s Access Advisor in a way that both recommend schema changes (for example, indexes and materialized views) required to optimize database performance. Both advisors are workload driven, and provide benefit-ranked recommendations. However, Oracle’s technology relies directly on the Oracle optimizer itself to do the analysis and takes into account a much larger set of options. Oracle Database 11g offers the SQL Tuning Advisor, which can analyze a problematic SQL statement and make specific recommendations to comprehensively tune it on the spot. Users can then implement the recommendation at a click of a button, tuning the statement. Along with

the recommendations, the SQL Tuning Advisor also provides the rationale for each recommendation and the actual performance benefit by test-executing the SQL statement with and without the changes.

DB2 also provides a tool called “Governor” which can be used to monitor and change behavior of applications that run against a database. DB2 Governor, introduced in a an earlier version of

DB2, claims to have many of the same capabilities as the Database Resource Manager including

enforcement of resource limits, assignment of relative priorities and, application of different behavioral rules (or resource management policies) during different time windows.

The Oracle Database Resource Manager enables administrators to align the distribution of system resources with enterprise goals by allowing allocation of CPU resources among database users and applications according to business priorities. Its ability to automatically limit the resources consumed by batch jobs helps in ensuring that such operations do not adversely impact online users in a mixed workload environment.
  Oracle uses a sophisticated internal algorithm to continuously monitor the distribution of memory and changes it periodically as needed, according to the demands of the workload. Also, the Memory Advisor works in conjunction with ADDM to detect any performance bottlenecks due to under-sized configurations and provide guidance on exact memory settings to overcome problems.
DB2 9.7 introduces several capabilities to simplify management of these schema objects such as online table move, transportable schema, and space saving capabilities such as index and temporary table compression. Oracle Database 11g Release 2 offers the most diverse compression schemes for various types of data, including OLTP and warehouse data, providing all the space-saving benefits of compression with no performance overhead. Oracle also has comprehensive compression support for all storage elements of the Oracle database


· Access structures, such as indexes and partitions

· Binary objects maintained in SecureFiles

· Backup data

· Data Pump data

· Network data, used for applying redo logs on a standby database

DB2 requires automatic storage to be configured for each database individually. Oracle’s ASM technology allows DBAs to automatically manage storage for multiple databases in a single ASM instance.
  SQL Performance Analyzer (SPA) enables users to assess the overall effect of change and makes it possible to remediate any negative outcome before end-users can be impacted. Being able to accurately forecast the potential impact of system changes on SQL performance enables you to tune the system beforehand in cases where some SQL statements regress, or to validate and measure the performance gain in cases where the performance of the SQL statements improves.
DB2 users cannot access the standby

database for backup they are unable to extract value out of their investment

Oracle Database 11g enables users to offload all backup activities from the primary database to a physical standby database.
DB2 users cannot check their backup to look for physical corruption of backups and to ensure recoverability. Oracle’s RMAN checks data files for physical and logical corruption and to confirm that all database files exist and are in the correct locations.
DB2 has no similar restore preview The Oracle RESTORE command supports a PREVIEW option, which identifies the

backups required to carry out a given restore operation based on the information in the

RMAN repository.

IBM’s DB2 V9.7 has no concept of a unified on-disk location for all recovery files and so it cannot

automatically manage the space used by archive logs as well as on-disk backups, etc.

Unique to Oracle Database 11g is the concept of a central location for all recovery files, called the Flashback Recovery Area. With this location defined, Oracle automatically

writes and maintains all files needed for recovery, such as Oracle backup files, archive

logs, etc.

DB2 has no comparable functionality. The only way to create a new DB2 database that is identical to an existing one is restore a backup of the source database to the new location. Oracle database templates

available using the Database Configuration Assistant (DBCA) allow for storing database definition in XML format and make these tasks extremely simple. The template definition

includes all characteristics of the database, such as initialization parameters, redo log settings, etc., and can be used to create identical databases on local or remote machines.