Grant on Data

Home | Products & Services | News | Community | Downloads | About | Contact | Privacy & Legal



In Focus

Ask Tom

Jonathan Lewis

Pete Finnigan

SQLBlogs.com

Expert's Guide to DB2

Database Soup

Xaprb (Baron Schwartz)

The Database Rosetta Stone

Over the years there have been numerous attempts to compare one relational database management system with another, from just about every conceivable angle. Features, limits, SQL compliance, power, performance, etc. Many of these seem to disappear just as quickly as they appear, taking their information with them

This comparison was put on ice for the last five years, but we've slowly been reconstructing it so as to re-release it for your enjoyment :-) It still isn't complete, however it includes a wide range of areas that we hope you'll find useful, and we're always happy to expand the Rosetta Stone to cover any area you think has been missed.

An important note on the content below

Every effort is made to present accurate, non-perjorative information in the Database Rosetta Stone. We're not interested in putting the boot into vendors, nor do we seek to deliberately or accidentally mis-represent or denegrate a product or its vendor. If you find any innaccuracies or omissions, please rest assured that this is not part of some colossal conspiracy against a given RDBMS (if it helps - wear your tin foil hat while reading :-) ). As always, we welcome any feedback, so feel free to comment on these directly by email.

Coming Soon!

We're currently updating the information in the Database Rosetta Stone to include Oracle 11g and SQL Server 2008. Look for that in the coming weeks, or send your thoughts to us.

Interpreting the Rosetta Stone

In general, The Database Rosetta Stone is mostly self-explanitory. Where features, qualities, etc, are noted with yes/no answers, the feature statement is phrased such that an answer of yes is positive or better than an answer of no. Features or functions with descriptive or numeric answers are largely self-explanatory

Where ever you see a question mark (?), this indicates we are currently unaware of the capability of the relevant database for the feature or function in question - we're always happy to hear from the database community regarding any information on these topics. "Partial" indicates that the feature is supported in an incomplete, or relatively limited way. See the notes for more explanation of each

An entry of "N/A" indicates that this feature is not applicable - the notes again have the details.

The sections on datatypes, commands, system catalogue tables, etc, provide descriptive details, also using ? where we are yet to determine the necessary details.

And again, you may disagree with the information presented here. Feel free to email your comments, criticisms, and examples.

Acknowledgements

As mentioned above, this certainly isn't the first such comparison, and doubtless won't be the last. We've been inspired by many discussions on usenet groups, mailing lists such as Oracle-l and DB2-L, vendor whitepapers, etc.


?>
Part 1: Features            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Operating Systems »»            
AIX Yes Yes Yes Yes No Yes
Free/Net/Open BSD No Yes No Yes No No
HP-UX, PA-RISC Yes Yes Yes Yes No Yes
HP-UX, Itanium Yes Yes Yes Yes No No
Linux x86, 32 bit Yes Yes Yes Yes No Yes
Linux x86, 64 bit Yes Yes Yes Yes No No
Linux PPC/POWER Yes Yes Yes Yes No No
Linux Mainframe Yes Yes Yes Yes No No
Linux Itanium ? Yes Yes Yes No No
OpenVMS (alpha) No Yes Yes Yes No No
OS X (Apple) Yes Yes Yes Yes No No
Solaris SPARC Yes Yes Yes Yes No Yes
Solaris x86 No Yes Yes Yes No No
Tru64 No Yes Yes Yes No No
Windows x86, 32 bit Yes Yes Yes Yes Yes Yes
Windows x86, 64 bit Yes Yes Yes Yes Yes No
Windows Itanium Yes Yes Yes Yes Yes No
z/OS N/A ? Yes ? No No
Application Architecture »»            
Multiprocess architecture (non-Windows OS) Yes No Yes ? N/A Yes
Multithreaded architecture (Windows OS) Yes Yes Yes ? Yes Yes
SMP Support Yes Yes Yes Yes Yes Yes
MPP Support Yes Yes Yes ? No No
Client threads separately controllable (killable in all circumstance) Yes No Yes ? No ?
Multiple Instances may be resident Yes Yes Yes Yes Yes Yes
Single Instance supports multiple databases Yes Partial (maps to schema) No Yes Yes Yes
Multiple Instances access single database (shared disk) No No Yes No No Yes
Multiple Instances access federated database (shared nothing) Yes Yes Yes Yes Yes No
Memory Architecture »»            
Block/Page memory buffer dynamically resizes Yes Yes Yes ? Yes ?
Objects may be pinned/kept in memory Yes No Yes ? Yes Yes
NUMA Support Yes Yes Yes ? No No
Disk and I/O Architecture »»            
Asynchronous I/O Support ? Partial (InnoDB only) Yes ? Yes Yes
Kernel-mode I/O Support ? No Yes ? ? ?
Direct I/O (OS Cache bypass) Support ? Partial (MyISAM index pages only; InnoDB) Yes ? ? ?
Raw I/O Support Yes Yes Yes ? Yes Yes
Configurable Block/Page Size Yes No Yes ? No Yes
Different Block/Page Size in same DB Yes No Yes ? No Yes
Configurable Extent/Allocation Size Yes No Yes ? No No
Availability Architecture »»            
Primary and Secondary nodes use independent (not shared) disk Yes Yes Yes ? No No
Primary and Secondary nodes allow different OSes Yes Yes No ? No No
Secondary (failover) node may be online for normal use Yes Yes Yes ? Yes Yes
Authentication Architecture »»            
Database managed usernames and passwords No Yes Yes Yes Yes Yes
Database roles Yes No Yes Yes Yes Yes
Operating System managed usernames and passwords Yes Partial (text name/host matching, no challenge/response) Yes Yes Yes Yes
Kerberos authentication Yes No Yes Yes Partial Yes
LDAP managed authentication Yes No Yes Partial (by PAM) Partial Yes
NIS/NIS+ authentication Yes No Yes Partial (by PAM) No No
OSF DCE Secure Core managed authentication Yes No Yes ? No Yes
Locking model »»            
Row-level locks Yes Partial (InnoDB only) Yes ? Yes Yes
Unlimited row-level locks (not memory constrained) No No Yes No No No
Avoids lock escalation to page or table level No ? Yes ? No No
Where lock escalation is normally unavoidable, provides configuration option to disable escalation Yes ? N/A ? Yes No
Row level versioning/read consistency No Partial (InnoDB only) Yes Yes Partial No
Isolation level support: Serialisable Yes Partial (InnoDB only) ? Yes Yes ?
Isolation level support: Repeatable Read (RR) Yes Partial (InnoDB only) Yes Yes Yes Yes
Isolation level support: Read Stability (RS) Yes Partial (InnoDB and BDB only) Yes Yes Yes Yes
Isolation level support: Cursor Stability (CS) Yes ? Yes Yes Yes Yes
Isolation level support: Uncommited Read (UR) Yes Partial (InnoDB only) Partial Yes Yes Partial
Query Optimiser »»            
Cost Based Query Optimiser Yes Yes Yes Yes Yes Yes
Statistics Gathering: Full scan ability Yes Yes Yes Yes Yes Yes
Statistics Gathering: Sample ability Yes No Yes Yes Yes Yes
Statistics Gathering: Histograms on indexed columns Yes No Yes Yes Yes Yes
Statistics Gathering: Histograms on all columns ? No Yes Yes ? ?
Statistics Gathering: Estimates for missing statistics ? No Yes No Yes ?
Statistics Gathering: Automated gathering Yes Partial (during bulk operations only) Yes No Yes ?
Statistics Gathering: CPU Costing ? No Yes Yes No No
Statistics Gathering: Network Costing ? No Yes ? No No
Statistics Gathering: Disk / IO Costing Yes No Yes Yes No No
Query Management »»            
Shared SQL Statement Cache Yes No Yes ? Yes Yes
Shared Execution Plans Yes No Yes ? Yes Yes
Query/Resource Governor Yes No Yes ? No ?
View current queries in system cache Yes Yes Yes ? Yes Yes
View historic queries in system cache No No Yes ? No ?
Log Model »»            
Circular logging Yes No Yes No Yes Yes
Continuous Circular logging Yes No Yes No No No
Non-Circular/streamed logging Yes Yes Yes Yes No No
Arbitrary task execution on log switch Yes No No No No No
Multiple log writers (processes or threads) No No Yes No No No
Log mirroring/multiplexing Yes No Yes ? Yes Yes
Automatic log file rollover Yes Yes Yes Yes No No
Parallelism »»            
Parallel Sorts ? No ? ? ? ?
Intraquery Parallelism Yes No Yes ? Yes Yes
Parallel Index (re)build ? No ? ? ? ?
Parallel (table) Reorganisation ? No ? ? ? ?
Object Support - Tables »»            
Autoincrementing columns Yes Yes No Yes Yes Yes
Object Support - Indexes »»            
B-tree indexes Yes Yes Yes Yes Yes Yes
Bitmap indexes No No Yes Yes No No
Domain indexes No No Yes ? No No
Reverse-key indexes No No Yes ? No No
Function-based indexes No No Yes Yes No Yes
Clustered indexes/Index-organised physical storage Partial (by reorg only) Partial (InnoDB only) Yes Partial (by cluster only) Yes Yes
Index usage monitoring No Partial Yes ? ? ?
Object Support - Stored Procedures »»            
Built-in Stored Procedure language Yes Yes Yes Yes Yes Yes
Java Stored Procedure Support Yes No Yes ? No ?
Object Support - Triggers »»            
Before trigger Yes ? Yes ? No ?
After trigger Yes ? Yes ? Yes Yes
Instead of trigger Yes ? Yes ? Yes Yes
System Event triggers No No Yes ? No No
Row-level triggers Yes ? Yes ? No No
Multiple triggers per action ? No Yes ? No No
Object Support - Other »»            
Non-blocking sequence/serial generator Yes No Yes Yes No No
Synonyms Yes No Yes No Yes No
Join Support »»            
Nested Loop Join Yes Yes Yes Yes Yes Yes
Hash Join Yes No Yes Yes Yes No
Merge Join Yes Yes Yes Yes Yes Yes
Semijoin Yes No Yes Yes Yes No
 
 
Part 2: Data Types            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Numbers »»            
TINYINT datatype ? ? Partial ? Yes Yes
SMALLINT datatype ? ? Partial Yes Yes Yes
INT datatype Yes Yes Yes Yes Yes Yes
INTEGER (ANSI) datatype Yes Yes Yes Yes Yes Yes
BIGINT datatype Yes Yes Partial Yes Yes Yes
Maximum (signed) integer size 64-bit 64-bit 128-bit ? 64-bit 64-bit
Float (ANSI) datatype ? ? Yes ? ? Yes
Number datatype Yes ? Yes ? ? ?
Number precision range ? ? 1 to 38 ? ? ?
Number scale range ? ? -84 to 127 ? ? ?
Fixed and Varying Characters »»            
Supports character semantics Partial (for some string functions) No Yes No No No
Char maximum length, single-byte 32672 ? 2000 ? ? 16384
Char maximum length, Unicode or UTF8 32672 bytes ? 2000 characters ? ? ?
Varchar maximum length, single-byte 32672 65535 4000 ? 8000 16384
Varchar maximum length, Unicode or UTF8 32672 bytes 21,844 (UTF8) 4000 characters ? 4000 characters ?
Dates and Times »»            
Earliest Date (Gregorian/Julian) 01-JAN-0001 01-JAN-1000 01-JAN-4712 BCE ? 01-JAN-1753 01-JAN-1753
Latest Date (Gregorian/Julian) 31-DEC-9999 31-DEC-9999 31-DEC-9999 ? 31-DEC-9999 31-DEC-9999
Timestamp [without timezone] Yes Partial (has datatype of that name, but represents Unix epoch) ? Yes ? ?
Timestamp with timezone No No ? Yes ? ?
Interval No No ? Yes ? ?
Date Yes Yes ? Yes ? ?
Time [without timezone] Yes Yes ? Yes ? ?
Time with timezone No No ? Yes ? ?
Binary / Large Objects »»            
Binary large object datatype name BLOB BLOB (and TINYBLOB, MEDIUMBLOB and LONGBLOB) BLOB ? image image
Maximum binary object size 2 GB 4 GB (LONGBLOB) 4 GB ? 2 GB 2 GB
CLOB Datatype Yes (text, as well as tinytext, mediumtext and longtext) Yes ? No (text)
Maximum CLOB size 2 GB 4 GB (LONGTEXT) 4 GB ? N/A 2 GB
NCLOB Datatype (DBCLOB) No (see text and related types) Yes ? No (unitext)
Maximum NCLOB size 1 GB 4 GB (longtext) 4 GB ? N/A 2 GB
External Object (Pointer) Datatype Yes No Yes ? ? ?
Maximum External Object size ? N/A 4 GB ? ? ?
 
 
Part 3: Built-in Functions and Procedures            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Arithmetic Functions »»            
Divisor Div Div Div ? Div ?
Modulus Mod Mod (and %) Mod Mod (and %) % %
Mathematical Functions »»            
Absolute Value ABS and ABSVAL ABS ABS ABS ABS ?
Ceiling Ceil (and Ceiling) Ceil (and Ceiling) Ceil Ceil (and Ceiling) Ceiling ?
Exponentiation Exp Exp Exp Exp (and ^) Exp ?
Floor Floor Floor Floor Floor Floor ?
Hexadecimal value Hex ? ? ? ? ?
Logarithm, natural Ln and Log Ln Ln Ln Log ?
Logarithm, base 10 Log10 Log(10) (and Log10) Log(10) Log(10) Log10 ?
Logarithm, base n No Log(n) Log(n) Log(n) No ?
Power Power Power (and Pow) Power Power Power ?
Random Number Rand Rand dbms_random Random Rand ?
Round Round Round Round Round Round ?
Square Root Sqrt Sqrt Sqrt Sqrt Sqrt ?
Test for sign Sign Sign Sign Sign Sign ?
Statistical Functions »»            
Mean/Average AvgAvg Avg ?
Mode ? ? ? ?
Median ? ? ? ?
Standard Deviation Stddev Stddev (and Std) Stddev Stddev Stdev ?
Variance Var, Variance Variance Variance Variance Var ?
Covariance Covar and Covariance ? ? ? ? ?
Trigonometric Functions »»            
Cosine COS COS COS COS COS COS
Sine SIN SIN SIN SIN SIN SIN
Tangent TAN TAN TAN TAN TAN TAN
Cotangent COT COT ? COT COT ?
Hyperbolic Cosine COSH No COSH No No ?
Hyperbolic Sine SINH No SINH No No ?
Hyperbolic Tangent TANH No TANH No No ?
Arc Cosine ACOS ACOS ACOS ACOS ACOS ACOS
Arc Sine ASIN ASIN ASIN ASIN ASIN ASIN
Arc Tangent (x) ATAN ATAN ATAN ATAN ATAN ATAN
Arc Tangent (x,y) ATAN2 ATAN2 (and ATAN) ATAN2 ATAN2 ATN ?
Hyperbolic Arc Tangent ATANH ? ? ? ? ?
Degrees from Radians Degrees ? ? ? ? ?
Radians from Degrees Radians ? ? ? ? ?
String Functions »»            
String Length length() ? length() ? datalength(), len() ?
String Concatenation ||, concat() concat() ||, concat() ? + ?
Trim leading spaces ltrim() ? ltrim() ? ltrim() ?
Trim trailing spaces rtrim() ? rtrim() ? rtrim() ?
Pad leading spaces Partial (requires space() and replace()) ? lpad() ? No ?
Pad trailing spaces Partial (requires space() and replace()) ? rpad() ? space() ?
Convert to uppercase upper(), ucase() ? upper() ? upper() ?
Convert to lowercase lcase(), lower() ? lower() ? lower() ?
Convert to Sentence case ? ? ? ? ? ?
Convert to Initial caps ? ? initcap() ? ? ?
Cast to character data char(), to_char() ? to_char() ? cast(), convert() ?
Return ASCII value character ascii() ? ascii() ? ascii() ?
Return character for ASCII value chr() ? chr() ? char() ?
First instance of character in string ? ? instr() ? charindex() ?
First instance of string in string ? ? instr() ? patindex() ?
Substring substr() ? substr() ? substring() substring()
Replace character replace() ? replace() ? stuff() stuff()
Repeat characters repeat() ? rpad() ? replicate() ?
Soundex soundex() ? soundex() ? soundex() ?
Test for Null ? ? nvl(), nvl2() ? isnull() ?
Date Functions »»            
Current Date and Time current timestamp now() sysdate ? getdate() getdate()
Day in Date Day ? ? ? ? ?
Name of Day Dayname ? ? ? ? ?
Day of Week Dayofweek ? ? ? ? ?
Day of Year Dayofyear ? ? ? ? ?
Hour in Date/Time Hour ? ? ? ? ?
Julian Day Julian_day ? ? ? ? ?
Microsecond in Date/Time Microsecond ? ? ? ? ?
Seconds after Midnight Midnight_seconds ? ? ? ? ?
Minute in Date/Time Minute ? ? ? ? ?
Month in Date Month ? ? ? ? ?
Name of Month Monthname ? ? ? ? ?
Seconds in Date/Time Second ? ? ? ? ?
Week in Date Week ? ? ? ? ?
Year in Date Year ? ? ? ? ?
More to follow
 
 
Part 4: Commands            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Configuration Parameters »»            
Show Current Parameters GET DB[M] CFG show variables show parameters ? sp_configure sp_configure
Examine Object Structures »»            
Show Table Details describe table tablename show table status tablename select * from user_tables where table_name = 'tablename' ? sp_help tablename sp_help tablename
Show Table DDL db2look show create table tablename ? ? ? ?
Query input, output and redirection »»            
Read and execute command from file ? ? (within sql*plus) @filename, or start filename ? (within osql or sqlcmd) :r filename (within isql) :r filename
Spool output to a file ? (within mysql) tee filename (within sql*plus) spool filename ? N/A N/A
End spooling ? (within mysql) notee (within sql*plus) spool off ? N/A N/A
More to follow
 
 
Part 5: System Catalogue / Tables            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Information Schema »»            
SQL92 Standard Information Schema Views No Yes No ? Yes ?
More to follow
 
 
Part 6: XML Support            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Coming Soon »»            
 
 
Part 7: Interfaces, Bindings and Programmability            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Interfaces »»            
ODBC (windows) Yes Yes Yes Yes Yes Yes
ODBC (Unix/Linux) Yes ? Yes ? No Yes
OLE DB Yes ? Yes Yes Yes Yes
.Net Data Provider Yes Yes Yes Yes Yes Yes
JDBC Type 2 Yes Yes Yes Yes No Yes
JDBC Type 4 Yes Yes Yes Yes Yes ?
Perl DBI Yes Yes Yes Yes No Yes
PHP Yes Yes Yes Yes ? ?
Python Yes Yes Yes Yes No ?
Ruby Yes Yes Yes Yes ? ?
 
 
Part 8: Advanced Features (Partitioning, Warehousing, etc)            
Feature DB2 UDB MySQL Oracle PostgreSQL SQL Server Sybase
  9.5 5.0 10g R2 8.3 2005 15
Job Scheduling »»            
Provides integrated job scheduler Yes No Yes Yes Yes Yes
Time based scheduling Yes N/A Yes Yes Yes No
Event based scheduling Yes N/A Yes Yes Yes Yes
Threshold/Alert based scheduling Yes N/A Yes Yes Yes No
More to follow



Copyright 2000-2008, Grant Allen. All rights reserved