Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 10, 2003

A Comparison of Oracle 9i with DB2 v8.1 - Page 2

By Alexander Chigrik

Performance comparison

It is very difficult to make a performance comparison betweenOracle 9i Database and DB2 Universal Database v8.1. The performanceof your databases depend rather upon the experience of the databasedevelopers and database administrator than from the database'sprovider. You can use both of these RDBMS to build stable andefficient systems. It is also possible to define the typicaltransactions, which are used in inventory control systems, airlinereservation systems and banking systems. After defining thesetypical transactions, it is possible to run them under the differentdatabase management systems working on different hardware andsoftware platforms.

TPC tests

The Transaction Processing Performance Council (TPC.Org) is an independentorganization that specifies the typical transactions (transactions usedin inventory control systems, airline reservation systems and bankingsystems) and some general rules these transactions should satisfy.The TPC produces benchmarks that measure transaction processing anddatabase performance in terms of how many transactions a given systemand database can perform per unit of time, e.g., transactions persecond or transactions per minute.The TPC organization made the specification for many tests. There areTPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-Band 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 performanceresults. See Top Ten TPC-C by Performance Version 5 ResultsNote. Since most organizations really do not run very large databases, 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 Editionand 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 Editionand 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 may qualify for discounts and the prices may 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 theANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediatelevel. In the Features comparison section of this article I want tomake a brief comparison of the PL/SQL with DB2 SQL dialect and showsome 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.A brief comparison of PL/SQL and DB2 SQL dialect follows:

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

Some of the limitations of Oracle 9i Database and DB2 version 8.1:

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.1or vice versa. Both products can be used to build stable and efficient systemsand the stability and effectiveness of your applications and databases dependrather upon the experience of the database developers and database administratorthan 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

» See All Articles by Columnist Alexander Chigrik



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date