Alexander Chigrik compares SQL Server 2000
with DB2 Universal Database version 8.1 regarding price, performance,
platforms supported, SQL dialects, and products limits.
Introduction
Often people in newsgroups ask about a comparison of DB2 and
Microsoft SQL Server. In this article I will compare SQL Server 2000
with DB2 Universal Database version 8.1 regarding price, performance,
platforms supported, SQL dialects, and products limits.
Platform comparison
SQL Server 2000 only works on Windows-based platforms, including Windows 9x,
Windows NT, Windows 2000 and Windows CE.
In comparison with SQL Server 2000, DB2 Universal Database version 8.1
supports all known platforms, including Windows-based platforms, AIX-based
systems, HP-UX systems, Linux Intel, Sun Solaris and so on.
Hardware requirements
To install SQL Server 2000, you should have Intel or compatible
platforms and the following hardware:
Hardware
|
Requirements
|
Processor
|
Pentium 166 MHz or higher
|
Memory
|
32 MB RAM (minimum for Desktop Engine),
64 MB RAM (minimum for all other editions),
128 MB RAM or more recommended
|
Hard disk space
|
270 MB (full installation),
250 MB (typical),
95 MB (minimum),
Desktop Engine: 44 MB
Analysis Services: 50 MB minimum and 130 MB typical
English Query: 80 MB
|
DB2 Universal Database v8.1 supports Windows-based platforms,
AIX-based systems, HP-UX systems, Linux Intel, Sun Solaris and so on.
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
SQL Server 2000 comes in six editions: Enterprise, Standard, Personal,
Developer, Desktop Engine, and SQL Server CE (a compatible version for
Windows CE) and requires the following software:
Operating System
|
Enterprise Edition
|
Standard Edition
|
Personal Edition
|
Developer Edition
|
Desktop Engine
|
SQL Server CE
|
Windows CE
|
No
|
No
|
No
|
No
|
No
|
Yes
|
Windows 9x
|
No
|
No
|
Yes
|
No
|
Yes
|
No
|
Windows NT 4.0 Workstation with Service Pack 5
|
No
|
No
|
Yes
|
Yes
|
Yes
|
No
|
Windows NT 4.0 Server with Service Pack 5
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
No
|
Windows NT 4.0 Server Enterprise Edition with Service Pack 5
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
No
|
Windows 2000 Professional
|
No
|
No
|
Yes
|
Yes
|
Yes
|
No
|
Windows 2000 Server
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
No
|
Windows 2000 Advanced Server
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
No
|
Windows 2000 DataCenter
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
No
|
Windows XP Professional
|
No
|
No
|
Yes
|
Yes
|
Yes
|
No
|
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 a performance comparison between
SQL Server 2000 and DB2 Universal Database v8.1. The performance of your
databases depend rather on the experience of the database
developers and database administrator, than on the database's
provider. You can use both of these RDBMS to build stable and
efficient systems. It is possible to define the
typical transactions, such as those used in inventory control systems,
airline reservation systems and banking systems. After defining
these typical transactions, it is possible to run them under
different database management systems working on the different
hardware and software platforms.
TPC tests
The Transaction Processing Performance Council (TPC.Org) is an 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 this article was written, SQL Server 2000 held the top
TPC-C by performance results with Distributed Partitioned Views-based
cluster systems.
See Top Ten TPC-C by Performance Version 5 Results
At the moment this article was written, SQL Server 2000 held the top
TPC-C by price/performance results.
See Top Ten TPC-C by Price/Performance Version 5 Results
Note. Because most organizations really do not run very large databases,
the key points on which SQL Server 2000 won the TPC-C benchmarks
do not really matter to the vast majority of companies.
Price comparison
One of the main Microsoft SQL Server 2000 advantage in comparison with
DB2 Universal Database v8.1 is that SQL Server is cheaper. Another SQL Server
advantage is that Microsoft includes the Online analytical processing
(OLAP) and Data Mining as standard features in SQL Server 2000
Enterprise Edition. So, you can save up to four times with SQL Server
2000 Enterprise Edition if you use OLAP and Data Mining.
The price comparisons below were based on the
IBM DB2 Version 8.1 and SQL Server 2000 Price Comparison
article from Microsoft.
Compare pricing for SQL Server 2000 Standard Edition and IBM DB2 v8.1
Workgroup Edition:
Number of CPUs
|
IBM DB2 v8.1 Workgroup Edition
|
SQL Server 2000 Standard Edition
|
1
|
$7,500
|
$4,999
|
2
|
$15,000
|
$9,998
|
4
|
$30,000
|
$19,996
|
8
|
$60,000
|
$39,992
|
16
|
$120,000
|
$79,984
|
32
|
$240,000
|
$159,968
|
Compare pricing for SQL Server 2000 Enterprise Edition (which include
OLAP and Data Mining) and IBM DB2 v8.1 Enterprise Edition with OLAP
and Data Mining:
Number of CPUs
|
IBM DB2 v8.1 Enterprise Edition
|
IBM DB2 v8.1 Enterprise Edition With OLAP and Data Mining
|
SQL Server 2000 Enterprise Edition
|
1
|
$25,000
|
$113,000
|
$19,999
|
2
|
$50,000
|
$126,000
|
$39,998
|
4
|
$100,000
|
$252,000
|
$79,996
|
8
|
$200,000
|
$504,000
|
$159,992
|
16
|
$400,000
|
$1,008,000
|
$319,984
|
32
|
$800,000
|
$2,016,000
|
$639,968
|
Note. This is not a full price comparison between SQL Server 2000 and
DB2 Universal Database v8.1. It is only a brief comparison. Discounts
may apply and the prices can be increased or decreased in the
future. See Microsoft and IBM to get more information about the price
of their products.
Features comparison
Both SQL Server 2000 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 Transact-SQL with DB2 SQL dialect
and show some SQL Server 2000 and DB2 Universal Database v8.1 limits.
T-SQL vs DB2 SQL dialect
The dialect of SQL supported by Microsoft SQL Server 2000 is called
Transact-SQL (T-SQL). The dialect of SQL supported by IBM DB2 v8.1
is called DB2 SQL dialect. DB2 SQL dialect is a more powerful language
than T-SQL. This is the brief comparison of DB2 SQL dialect and T-SQL:
Feature
|
DB2 SQL dialect
|
T-SQL
|
Tables
|
Relational tables,
Object tables,
Temporary tables
|
Relational tables,
Temporary tables
|
Triggers
|
BEFORE triggers,
AFTER triggers,
INSTEAD OF triggers
|
AFTER triggers,
INSTEAD OF triggers
|
Procedures
|
DB2 SQL dialect statements,
Java methods,
third-generation language
(3GL) routines
|
T-SQL statements
|
User-defined functions
|
External Scalar functions,
External table functions,
OLE DB External table functions,
Sourced or Template functions,
SQL Scalar, Table or Row functions
|
Scalar functions,
Inline table-valued functions,
Multistatement table-valued functions
|
Arrays
|
Supported
|
Not Supported
|
SQL Server 2000 and DB2 v8.1 limits
Here you can find some SQL Server 2000 and DB2 version 8.1 limits:
Feature
|
SQL Server 2000
|
IBM DB2 v8.1
|
column name length
|
128
|
128
|
index name length
|
128
|
128
|
table name length
|
128
|
128
|
view name length
|
128
|
128
|
cursor name length
|
128
|
18
|
constraint name length
|
128
|
18
|
password length
|
128
|
32
|
SQL variable length
|
128
|
64
|
user name length
|
128
|
30
|
index length
|
900
|
1024
|
max char() size
|
8000
|
254
|
max varchar() size
|
8000
|
32672
|
max columns per table
|
1024
|
1012
|
max table row length
|
8036
|
32677
|
max columns per index
|
16
|
16
|
max indexes per table
|
250
|
32767
|
longest SQL statement
|
16777216
|
65535
|
columns per SELECT statement
|
4096
|
1012
|
columns per INSERT statement
|
1024
|
1012
|
nested stored procedure levels
|
32
|
16
|
Conclusion
It is not true that SQL Server 2000 is better than DB2 Universal Database v8.1
or vice versa. Both products can be used to build stable and efficient systems
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. But SQL Server 2000 has some advantages
in comparison with DB2 Universal Database v8.1 and vice versa.
The SQL Server 2000 advantages:
- SQL Server 2000 is cheaper to buy than DB2 v8.1 Database.
- SQL Server 2000 holds the top TPC-C performance and price/performance results.
- SQL Server 2000 is generally accepted as easier to install, use and manage.
The DB2 Universal Database v8.1 advantages:
- DB2 Universal Database v8.1 supports all known platforms, not only the
Windows-based platforms.
- DB2 SQL dialect is a more powerful language than T-SQL.
- More fine-tuning can be done to the configuration via start-up parameters.
Literature
- SQL Server 2000 Books Online
- DB2 Technical Support
- IBM DB2 Version 8.1 and SQL Server 2000 Price Comparison
- DB2 Universal Database v8.1 system requirements
- Top Ten TPC-C by Performance Version 5 Results
- Top Ten TPC-C by Price/Performance Version 5 Results
»
See All Articles by Columnist Alexander Chigrik