Review: Summary of our Getting Started tests of the "Free" databases
Feature: The only major misstep were the Migration Utlities and Oracle imports
One would expect our 3 "free" databases to do reasonably well in the getting Started exercises - but even this grizzled veteran of database usage was impressed with how well the 3 databases did in our tests. I was expecting some frantic searches in the support forums but that was mostly avoided. Here is a summary of the results.
First, the three "free" databases chosen, IBM DB2 Express C, MySQL 5, and OracleXE were chosen on the following basis. First, I wanted a cross platform database because too many applications and systems have to perform on several servers - and I and most CIOs don't want to have to support Windows-only or some variants of it. Second, the databases had to suppport SQL with reasonably close compliance to the SQL2003 standard. Effectively this meant:
- full simple query support for all clauses - order by, group by, etc
- query support for subqueries, left, right, and full joins
- support for views, updatable views, and associated syntax
- support for constraints, triggers, and stored procedures
- expected but not tested clustering, parallel, and partitioned designs
- expected and to be tested support for replication, conflict resolution, and long transactions
- expected and to be tested design, development and administration tools from the vendors
Finally, these "free" database tools are expected not only to be able to fit in a workgroup or departmental setting; but also act as a good citizens not only within their own toolsets but integrating across to an enterprises database and development environs. This means that databases and apps developed with the "free" should transparently move to enterprise settings with no or minimal conversion. The only area where the tested tools fell off the wagon was in advanced feature support. So that raises questions about what is in our "free" databases
The Nature of the "Free" Databases
All along I have been using quotes around "free". That is for five important reasons:
1)the databases are free to download design, develop with and use with limits;
2)but they are not all Open Source (only MySQL is);
3)they run in several versions of Linux and Windows (and many other OS in the case of MySQL);
4)and there are limits on exactly how they can be deployed in the case of IBM and Oracle;
5)and there are limits to the support you will get for each of the databases.
The two critical limits here are support and deployment limits. All of the vendors have online support through user forums/bulletin boards with varying degrees of staff support answering questions. If a client wants vendor support it is avilable at varying rates:
IBM DB2 User support forum and a Web Support Center. Rates for IBM support starts at $170US.
MySQL 5 User support forum and DevCenter. Rates for MySQL support starts at $595US.
Oracle XE: User discussion forum and a Community Board. Oracle support requires a license.
IBM DB2 Express C supports Win XP/Home, WinXP/Pro and Vista plus 32 and 64bit Linux.
Its deployment limits are:
• Maximum processors: 2
• Maximum addressable memory: 4GB
• Database Partitioning and Win Cluster Support - Not available
• Connection Concentrator, Informix data Source and Replication Data Capture - Not available
• DB2 Geodetic and Spatial Extender - Not available
• Query Patroller, DB2 Web tools, GSKit, APPC and Netbios- Not available
Upgrade to DB2 Express, WebGroup, Or Enterprise Server present no conversion problems.
MySQL 5.0 there is no limit as to what features you get or what you can deploy. However, just as in the other databases you must choose carefully among the alternative offerings. Also support for any MySQL databases is two pronged - free user forums and online support or fee-based MySQL or other 3rd party support.
Oracle XE does not support multiple database instances and its 64bit, advanced clustering and partitioning features require an upgrade to an Enterprise edition of Oracle. Deployment limits include:
• Allows up to 4GB of user data (in addition to Oracle system data)in the databse files
• Single instance only of Oracle Database XE on any server
• May be installed on a multiple CPU server, but XE only executes on one processor in any server
• May be installed on a server with any amount of memory, but will only be able to use 1GB RAM
For a detailed comparison of Oracle versions with Oracle XE go here. For an XE FAQ click here.
So as you can see from the information, above these are "free" databases until you have problems in design, development/testing, or operations when you or your clients want support. At this point you actually have available huge and largely free Web based resources coming either directly from the vendors or from third party sites like theOpenSourcery.com (also see our SQL References). But if you need an answer yesterday, then you will have to pay the piper. Hence, with this and deployment limits in the case of IBM and Oracle, our quotes around "free" are necessary. However, as many developers know who continue to pay the price for Visual Studio/SQL Server or DataMirror Pointbase or Sybase ASE13 - "free" has some real solid benefits - particularly low cost trial and entry for their staffs. Likewise educational institutions would be fools to pass up these free databases given the Getting Started results posted below.
As noted at the outset getting started with all 3 of the databases turned out to be a lot easier than I expected. The installs went smoothly whilethe getting started guides got this user up to speed with few snags. And most of this work was done in less than two hours - which is not too shabby for what are essentially enterprise caliber databases. and the importing of data using our 2 standard databases, the Lahman Baseball database and the WordWeb Dictionary of words database turned out to be a little more challanging while the data migration toolkits were like Hertz car commercial - not exactly ready for prime-time.
Finally we tested an array of utility goodies for quering, displaying and monitoring the databases and again were pleasantly pleased with what was made available. However, given the amount of "newbie chatter" encountered on the community forums this reviewer would recommend to each of the vendors a simple getting loaded guide on their download page which in 2-3 paragraphs explained clearly what the users should download and in what order. Sort of like this:
Barebones getting started if you just want to see the database working: download database core.
Complete documentation for design, querying, and development: download full documentation set.
Additional free, useful tools and utilities: download database tools.
We have provided our equivalent of these recommended links in the table below. And in fact the summary table provides a good overview of what was encountered during this review exercise.
Glancing through the table above one can see that all of the databases are relatively easy to download and install. All of them have very good Getting Started Documentation. It always pays to to do the extra downloads. For IBM get the Info Center documentation and Database Developers workbench; for MySQL get the GUI Tools plus the JDBC and ODBC connectors. For Oracle get the full set of documentation plus SQL Developer and JDeveloper. All of these tools are free - and the download links are provided in the table above.
Working with the Getting Started tutorials I had no problems with any of the databases using the tutorial databases plus some quickly imported test databases. If you want to come up to speed with basic DBA or development work, all three databases get you started well. However, there is a caution. First, I have not tested the databases for complex joins, subqueries, referential integrity, views handling, triggers, constraints, stored procedures - performance tuning. This will be the subject of our second round of tests - DBA Functionality of the databases. However, all of the databases provide good free tools for such work.
Second concern, for cross database imports, only DB2 and MySQL provided migration toolkits - and each presented different problems. The part of DB2's Migration Toolkit for MySQL is alpha and it shows. The IBM MySQL migrator simply could not convert a MySQL database to DB2 - though it did convert the Oracle tutorial database with no problems. MysSQL's Migration tool did convert data and tables, but not triggers, constraints,stored procedures nor all grants and privileges
correctly on Access and Oracle test conversions. So as always- cross database is a problem which will crop up again when we consider federated or multi-database queries and joins.
Third concern is data imports. Embedded commas, single quotes, and double quotes in large VARCHAR and CLOB fields threw the text data import facilities of both MySQL and Oracle off. DB2's Load wizards handled these problems straight forwardly. Oracle XE presented the more serious difficulties - with the default LOAD command providing terse error messages, the error logs hard to find, and the documentation for the underlying SQLLDR routine hard to find and then difficult to interpret. Also, Oracle XE does not handle tables with multiple fields as primary keys well - it has a maximum of 2 fields; where as our test data had 6 and 7 field composite keys. The Lahman Baseball database has several 3 field composite primary keys.
But despite these issues the bottom line is that developers can design, develop, and deploy for free real workgroup if not departmental databases with hundreds of thousands of records in the tables and achieve good response times for 10-20 concurrent users in most Linux or Windows setups (and many more OS platforms in the case of MySQL). They will be using the same tools that full Enterprise developers would be using. They will be able to do the latest Java, C++, AJAX and SOAP/Web Services based programming with these tools that Enterprise developers do - and all for the cost of maybe a day of time to download, install, and get started. In short, "Free" starts to look pretty darn good to this developer.
Developers Weblog Graphics Reviews and Tips
Top of Page Home Tutorials