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
 
     
 

SQL Server 2000 vs DB2 v8.1

Alexander Chigrik
chigrik@mssqlcity.com


Introduction
Platform comparison
  • Hardware requirements
  • Software requirements
  • Performance comparison
  • TPC tests
  • Price comparison
    Features comparison
  • T-SQL vs DB2 SQL dialect
  • SQL Server 2000 and DB2 v8.1 limits
  • Conclusion
    Literature


    Introduction

    Often people in newsgroups ask about some comparison of DB2 and Microsoft SQL Server. In this article, I 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 the 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 the performance comparison between SQL Server 2000 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, 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 the article was wrote, 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, so 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. Other 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. You can have any discounts 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 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 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. 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 more powerful language than T-SQL.
    • More fine-tuning to the configuration can be done via start-up parameters.

    Literature

    1. SQL Server 2000 Books Online

    2. DB2 Technical Support

    3. IBM DB2 Version 8.1 and SQL Server 2000 Price Comparison

    4. DB2 Universal Database v8.1 system requirements

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

    6. Top Ten TPC-C by Price/Performance Version 5 Results


     

     
    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