Oracle 9i Database vs DB2 v8.1

http://www.mssqlcity.com/Articles/Compare/oracle_vs_db2.htm

Oracle 9i Database vs DB2 v8.1

Alexander Chigrik

chigrik@mssqlcity.com

 

 

Introduction Platform comparison Hardware requirements Software requirements Performance comparison TPC tests Price comparison Features comparison PL/SQL vs DB2 SQL dialect Oracle 9i and DB2 v8.1 limits Conclusion Literature

 

 

Introduction

Often people in newsgroups ask about some comparison of Oracle and DB2. In this article, I compare Oracle 9i Database with DB2 Universal Database version 8.1 regarding price, performance, platforms supported, SQL dialects and products limits..

Platform comparison

Both Oracle 9i Database and DB2 Universal Database version 8.1 support all known platforms, including Windows-based platforms, AIX-Based Systems, HP-UX systems, Linux Intel, Sun Solaris and so on..

Hardware requirements

To install Oracle 9i under the Windows-based platforms, you should have the following hardware::

Hardware Requirements Processor Pentium 166 MHz or higher Memory RAM: 128 MB (256 MB recommended)
Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB Hard disk space 140 MB on the System Drive
plus 4.5 GB for the Oracle Home Drive (FAT)
or 2.8 GB for the Oracle Home Drive (NTFS)
To install Oracle 9i Database under the UNIX Systems, such as AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris, you should have the following hardware:

Hardware Requirements Memory A minimum of 512 MB RAM Swap Space A minimum of 2 x RAM or 400 MB, whichever is greater Hard disk space 4.5 GB
To install DB2 Universal Database v8.1 under the Windows-based platforms, you should have the following hardware:

Hardware Requirements Processor Pentium or Pentium compatible CPU Memory RAM: 256 MB minimum,
additional memory may be required. Hard disk space Typical installation: 350 Mb minimum
Compact installation: 100 Mb minimum
Custom installation: 100 Mb minimum.

Additional disk space may be required
on FAT drives with large cluster size.
To install DB2 Universal Database v8.1 under the UNIX Systems, such as AIX-based systems, HP-UX systems, Linux and Sun Solaris, you should have the following hardware:

Hardware Requirements Processor For AIX:
       IBM RISC/6000 or eServer pSeries.

For HP-UX:
       HP 9000 series 700 or 800 system.

For Linux:
       Intel 32-bit,
       Intel 64-bit,
       S/390 9672 generation or higher,
       Multiprise 3000,
       eServer z-Series.

For Solaris:
       Solaris UltraSPARC-based computer. Memory RAM: 256 MB minimum,
additional memory may be required. Hard disk space Typical installation: 450 to 550 Mb minimum
Compact installation: 350 to 400 Mb minimum
Custom installation: 350 to 700 Mb minimum.

Software requirements

Oracle 9i Database comes in three editions: Enterprise, Standard and Personal and requires the following software:

Platform Operating System Version Required Patches Windows-based Windows NT 4.0 Service Pack 5 Windows-based Windows 2000 Service Pack 1 Windows-based Windows XP Not Necessary AIX-Based AIX 4.3.3 Maintenance Level 09 and IY24568,
IY25282, IY27614, IY30151 AIX-Based AIX 5.1 AIX 5L release 5.1 ML01+ (IY22854),
IY26778, IY28766, IY28949, IY29965, IY30150 Compaq Tru64 UNIX Tru64 5.1 5.1 patchkit 4 Compaq Tru64 UNIX Tru64 5.1A 5.1A patchkit 1 HP-UX HP-UX version 11.0 (64-bit) Sept. 2001 Quality Pack, PHCO_23792,
PHCO_24148, PHKL_24268, PHKL_24729,
PHKL_ 25475, PHKL_25525, PHNE_24715,
PHSS_23670, PHSS_24301, PHSS_24303,
PHSS_24627, PHSS_22868 Linux SuSE Linux Enterprise Server 7
(or SLES-7) with kernel 2.4.7,
and glibc 2.2.2 Not Necessary Sun Solaris Solaris 32-Bit 2.6 (5.6), 7 (5.7)
or 8 (5.8) Not Necessary Sun Solaris Solaris 64-Bit 8 (5.8) Update 5
DB2 Universal Database v8.1 comes in six editions:

DB2 Enterprise Server Edition (ESE) DB2 Workgroup Server Edition (WSE) DB2 Workgroup Server Unlimited Edition (WSUE) DB2 Personal Edition (PE) DB2 Universal Developer's Edition (UDE) DB2 Personal Developer's Edition (PDE)

and requires the following software:

Platform Operating System Version Required Patches Windows-based Windows NT 4.0 Service Pack 6a or higher Windows-based Windows 2000 Service Pack 2 is required for
Windows Terminal Server Windows-based Windows XP Not Necessary AIX-Based AIX 4.3.3 (32-bit) Maintenance Level 9 or later, and
APARs IY22308, Y32690, and IY33024 AIX-Based AIX 5L (32-bit) Maintenance Level 2 or later AIX-Based AIX 5.1.0 (32-bit) Maintenance Level 2 or later, and
APARs IY31254, IY32217, IY32905,
IY33023, and IY29345 AIX-Based AIX 5.1.0 (64-bit) Maintenance Level 2 or later, and
APARs IY31254, IY32217, IY32905,
Y33023, and IY32466 HP-UX HP-UX 11i December 2001 GOLDBASE11i,
December 2001 GOLDAPPS11i bundles Linux For Intel 32-bit:
kernel level 2.4.9 or later glibc 2.2.4 or later RPM 3 or later

For Intel 64-bit and z-Series:
Red Hat Linux 7.2 SuSE Linux SLES-7 Not Necessary Sun Solaris Solaris 7 (32-bit) patch 106327-10 Sun Solaris Solaris 7 (64-bit) patch 106300-11 Sun Solaris Solaris 8 (32-bit) patch 108434-03 and 108528-12 Sun Solaris Solaris 8 (64-bit) patch 108435-03 and 108528-12 Sun Solaris Solaris 9 Not Necessary

Performance comparison

It is very difficult to make the performance comparison between Oracle 9i Database and DB2 Universal Database v8.1. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database's provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms.

TPC tests

The Transaction Processing Performance Council (TPC.Org) is independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy.

The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.

The TPC organization made the specification for many tests. There are TPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-B and TPC-D. The most popular test is the TPC-C test (OLTP test).

At the moment the article was wrote, Oracle held the top TPC-C by performance results. See Top Ten TPC-C by Performance Version 5 Results

Note. Because most organizations really do not run very large databases, so the key points on which Oracle 9i Database won the TPC-C benchmarks do not really matter to the vast majority of companies.

Price comparison

Compare pricing for Oracle9i Standard Edition and IBM DB2 v8.1 Workgroup Edition:

Number of CPUs Oracle9i Standard Edition IBM DB2 v8.1 Workgroup Edition 1 $15,000 $7,500 2 $30,000 $15,000 4 $60,000 $30,000 8 $120,000 $60,000 16 $240,000 $120,000 32 $480,000 $240,000
Compare pricing for Oracle9i Enterprise Edition and IBM DB2 v8.1 Enterprise Edition:

Number of CPUs Oracle9i Enterprise Edition IBM DB2 v8.1 Enterprise Edition 1 $40,000 $25,000 2 $80,000 $50,000 4 $160,000 $100,000 8 $320,000 $200,000 16 $640,000 $400,000 32 $1,280,000 $800,000
Note. This is not a full price comparison between Oracle 9i Database and DB2 Universal Database v8.1. It is only a brief comparison. You can have any discounts and the prices can be increased or decreased in the future. See Oracle and IBM to get more information about the price of their products.

Features comparison

Both Oracle 9i Database and IBM DB2 Universal Database v8.1 support the ANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediate level. In the Features comparison section of this article I want to make the brief comparison of the PL/SQL with DB2 SQL dialect and show some Oracle 9i Database and DB2 Universal Database v8.1 limits.

PL/SQL vs DB2 SQL dialect

The dialect of SQL supported by Oracle 9i Database is called PL/SQL. The dialect of SQL supported by IBM DB2 v8.1 is called DB2 SQL dialect. This is the brief comparison of PL/SQL and DB2 SQL dialect:

Feature PL/SQL DB2 SQL dialect Indexes B-Tree indexes,
Bitmap indexes,
Partitioned indexes,
Function-based indexes,
Domain indexes B-Tree indexes,
Bitmap indexes,
Partitioned indexes,
block indexes,
dimension block indexes Tables Relational tables,
Object tables,
Temporary tables Relational tables,
Object tables,
Temporary tables Triggers BEFORE triggers,
AFTER triggers,
INSTEAD OF triggers BEFORE triggers,
AFTER triggers,
INSTEAD OF triggers Procedures PL/SQL statements,
Java methods,
third-generation language
(3GL) routines DB2 SQL dialect statements,
Java methods,
third-generation language
(3GL) routines Multiple Triggers Supported Supported Arrays Supported Supported

Oracle 9i and DB2 v8.1 limits

Here you can find some Oracle 9i Database and DB2 version 8.1 limits:

Feature Oracle 9i Database IBM DB2 v8.1 database name length 8 8 column name length 30 128 index name length 30 128 table name length 30 128 view name length 30 128 stored procedure name length 30 128 most columns per table 1000 1012 most columns in an index key 32 16 max number of columns in GROUP BY 255 1012 max number of columns in ORDER BY 255 1012 longest index key 3155 1024 max varchar() size 4000 32672 max char() size 2000 254 max table row length 255000 32677 longest SQL statement 16777216 65535 recursive subqueries 64 28 constant string size in SELECT 4000 32672

Conclusion

It is not true that Oracle 9i Database is better than DB2 Universal Database v8.1 or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database's provider.

Literature

1. Oracle documentation

2. DB2 Technical Support

3. DB2 Universal Database v8.1 system requirements

4. Top Ten TPC-C by Performance Version 5 Results

5. SQL Server 2000 vs Oracle 9i

6. SQL Server 2000 vs DB2 v8.1