MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

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


     

     
    Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

    (c) 1997, 2010 Bits on the Wire, Inc