Summary: Two important mid-range SQL databases have been recently ported to OS/2. In this review we compare the two together and also see how well they stack up to one of the mainstream industrial-strength databases: IBM's DB2
Databases are usually divided into two categories, the simple personal and small-business database programs such as Lotus Approach and DBExpert, and the full-blown, industrial grade, relational databases that include Oracle and IBM's DB2. But in-between these two are a number of mid-range databases that have recently become available on OS/2. This review puts two of these new alternatives for OS/2 -- miniSQL (also called mSQL) and MySQL -- through their paces to identify their strengths and weaknesses. I also compare these packages to what I have found to be the premier full blown relational database; IBM's DB2.
Controlled almost entirely with SQL (Standard Query Language), they provide a compromise between the restrictions of the first and the expense of the second, attributes that make them very attractive as the back-ends of medium sized web sites or custom small-business applications.
Download and Installation
Both mSQL and MySQL are light weight relational databases which work on OS/2 and also have versions for Unix and Win32. mSQL is free to use non-commercially, but requires a license fee of $250 (with volume discounts) after a 14 day trial period for commercial applications. MySQL is free to use (even commercially) on OS/2, but requires a license fee if it is distributed commercially (such as would be the case if you were selling an application based on it). The Win32 version of MySQL is only available for use with payment of the licensing fee. If you do distribute MySQL commercially, the basic license fee for MySQL starts at $200 with volume discounts.
Both databases also require the EMX runtime libraries (version 0.9c with Fix Level 4, which is pretty much the standard) already be installed on your system. MySQL also requires FixPak 29 for Warp 3 or FixPack 4 for Warp 4. mSQL requires at least FixPak 6 for Warp 4 and the equivalent FixPak for Warp 3, though this was not clearly documented and caused me some problems in testing it.
I reviewed mSQL 188.8.131.52 Build 5 from the mSQL download page. It is a 250K compressed zip file. After unzipping there is an installation script that creates the final directory, copies the files, and creates the OS/2 icons. It takes up about 640K installed, but does not include documentation. There was a bug in the mSQL import utility, mSQLimport, that was not resolved in time for the performance review. The next build of mSQL should include a corrected mSQLimport, however.
For MySQL we reviewed version 3.21.33. The normal HTML references to download MySQL were not working when I looked for it, so had to find my own Hobbes reference. It is a 1.3 megabyte compressed zip file which includes documentation. Like many applications developed on and ported from Unix it decompresses into the final directory structure. It also includes long names, so it's easiest to just unzip it from the root of an HPFS drive.
The documentation for MySQL directs you to install and run MySQL using Unix shell scripts (sh) which require at least passing Unix familiarity since the scripts are not adapted to OS/2. It is also helpful to have the GNU shell, sh, installed on your system before you try. A really nice addition to MySQL would be a Rexx installation utility that creates icons and so on as in mSQL. However, these MySQL shell scripts can be skipped by just invoking the server with the command "mysqld --skip-grant --log" (it is important to note that there is no space between the skip and grant options) from the '\mysql2\bin' directory and then type "mysqladmin create sample". After that you can submit SQL commands to the MySQL client by running "MySQL sample".
The first minor problems I encountered with MySQL was a requirement that the environment variable of HOME be set before the console is invoked (otherwise the console client fails with a segment violation). Also, the environment variable of EMXOPT needs to be set to either "-h256" or "-c -n -h1024" or requests to access more than four or so tables will fail with 'file not found' errors.
Documentation and Debugging
MySQL includes documentation in .inf format, viewed with the OS/2 VIEW program (my favorite way to get documentation). mSQL does not come with any documentation, but there is an excellent article in EDM/2 which explains how to set up mSQL and use it with a web server. You can find other documentation at the mSQL ports home page and there is a reference manual on the Hughes web site. But if you are using mSQL heavily you will probably want to buy the book that Hughes advertises.
Both MySQL and mSQL are supported via E-Mail and I can say with confidence that there are prompt replies to most problems. In comparison, the documentation for DB2 is more accurate and more complete, but there is no e-mail forum to which you can submit simple questions. With DB2 if you get stuck with an error message such as 'transaction log full' (as I did with the 100,000 record test script), it will take a person new to DB2 a fair amount of effort to research the solution to the problem.
But then the error codes in DB2 are better documented than Mini or MySQL and often point you to a suggested solution anyway. For example, when I'm typing in SQL I sometimes forget to specify the "from table" clause. The DB2 error message will often clearly identify the error:
SQL0104N An unexpected token "where" was found following "select * ". Expected tokens may include: "<from_clause>". SQLSTATE=42601 db2 =>In mSQL you would get:
ERROR : parse error at line 1 near "* where" mSQL >While in MySQL you get:
ERROR 1064: parse error near 'where grp = 5' at line 1 mysql>
It is in the area of functionality that MySQL becomes the clear winner when put against mSQL.
mSQL provides a very narrow selection of data types; only 'char' (characters), 'text' (blocks of text up to 32K in size), 'int' (integers) and 'real' (floating point numbers). There is also limited support for a non-standard timestamp variable. In contrast, MySQL supports virtually all SQL data types, including the standard date and time types (which can be very useful when used with the functions to adjust and compare dates and times).
However, there is a good reason why mSQL does not support any data types beyond the most basic. Other than storing and retrieving the data there is very little that you can actually do with the data in mSQL. You can not write an update which uses the current value of a column as a starting point ("set count = count + 1" is the classic example). It is up to you to retrieve the value of the column into your program and then store the new value. Further you can not do computations in the select values. For example if you want Profit (a computed value), you have to select Revenue and Costs and then compute the desired result (Revenue - Cost) in your program. mSQL also does not support the classic summary functions like count, sum, and avg so that all summary report generation must be done by your program.
But MySQL provides virtually all functions you would expect in a relational database with the only exception I found being the ability to do subselects in the 'where' clause of an update. This can be a powerful tool for updating records in a table relative to other records in the same or a different table. The classic example of this is identifying customers who made repeat purchases (a good thing) or who had repeat problems (not a good thing).
Neither mSQL or MySQL supports the usual commit and roll back logic. But if you are just using either to record information (simple inserts) or display information (simple selects) that is not a problem. For transaction processing where a record needs to be updated based on its current values, each database has non-standard techniques to allow the programmer(s) to manually work out the locking and/or roll back logic. This is almost certainly not the right approach if you are doing a lot of transaction processing. In that case it would probably be cheaper in the long run to buy a full blown relational database with built in commit and rollback support.
For light transaction processing, MySQL has the simple and powerful ability to lock and unlock selected sets of tables. The downside of this approach is that it can present severe throughput and reliability problems (if the application with the locks fails, for example). mSQL offers a special timestamp for each record in a table which allows you to write high throughput and reliable transaction processing code, but if the transactions you need to support are very complex, this approach will quickly get so complex as to be untenable. If this was the preferred solution for transaction processing, you could use the standard timestamp supported by MySQL to implement the same logic (with just a little more effort).
Both mSQL and MySQL each come with a useful CGI extension for web servers which allows the integration of HTML and SQL logic to collect information or display results from your database. Without this ability you would normally require separate files and some minimal integration logic to produce seamless integration of the database into your web pages.
|Copyright © 1999 - Falcon Networking||ISSN 1203-5696||February 1, 1999|