We welcomed the year 2000 with one thing that people tend to forget about – a name for our precious single-level storage beauty of a database: DB2. The database is available on all IBM platforms, and can be installed on many non-IBM platforms as well. The difference between the i5 and other platforms, is that the System i has full functionality of DB2 built-in to the operating system. The operating system fully supports the database and all of its functionality.
You might think that this is just a pitch associated with the recent IBM viral video(and for the record, I believe dragons are orange… with wings). Despite these rumors of collusion, I don't think that enough people push the fact that DB2 is one of the most robust databases on the planet.
DB2 supports
- Referential integrity: The ability to let the database maintain the rules of the relationships between physical files. e.g., a customer in the order header file must have a customer in the customer master file. Or every order detail file member must have a corresponding order header record.
- Triggers: The ability to react to certain database adds/updates/deletions that happen to a file. e.g., for every update to the customer master, it performs business logic attached directly to the database no matter which program updates that function. You can run an update procedure on corresponding Web files whenever an update to the production files occurs.
- Journaling: a significantly underused function of DB2 that allows users to perform all functions on a file (update/add/delete) with the ability to roll those changes back or commit them back to the physical file. The journal can also show what happened at what time to a file, adding logging capability as well. AS/400 users know that if while performing a nightly update you encounter an error you can roll back the changes made without having to restore the entire database.
- Extensive error handling: SQL return codes let the user know what is wrong, and why. Positive return codes represent successful execution of SQL with warnings, while negative return codes represent unsuccessful execution with errors.
The i5 system was developed in the 1970s, and was one of the first database engines on the planet. Several things set the system apart from the competition. First, is that it was designed around the principle of "single level storage" where the system thinks of every scrap of storage -- be it memory or disk -- as a two dimensional plane that it manages. Because the system manages these entities simultaneously, the only reason to use one or another is for performance purposes. Therefore, you can load a file into memory and speed up its processing on its own. It gets better: with the introduction of smart caching, the i5 OS actually predicts the file access and loads portions into memory for you!
Click on image for a larger version
DB2 is the successor to the famous DBMS management system from the monster mainframe days of the past. Another thing that came of this research was the development of a structured English query language that became SQL. Until 1999 when DB2 was named on the AS/400 was not fully compliant with data standards. It is now fully compliant (SQL-99 standards).
Larry Ellison, President of Oracle, has even commented that DB2 is the one competitive database that he respects and admires/ So anyone dissing my favorite system isn't going to be dismissed without a fight!
ABOUT THE AUTHOR: Andrew Borts is webmaster at United Auto Insurance Group in North Miami, Fla. He is a frequent speaker at COMMON and is past president of The Southern National Users Group, an iSeries-AS/400 user group based in Deerfield Beach, Fla.
