Focus on Database Management

Business Intelligence
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

If I were to ask you how the System/36 and native AS/400 differ, you’d probably mention that the AS/400 lets you define files externally to programs. You might also mention that DB2/400 logical files are more powerful than S/36 alternate indexes. You’d be saying that the S/36 has a file system but that the AS/400 has a database management system (DBMS).

Here is a short list of features you can expect to find in any decent relational DBMS but not in a file system:

• Null—A null value is not the same as a blank or zero value. A cost of zero means that something is free. A cost of null means the cost is unknown or there is no cost.

• Joins—If you need data from related files, join them. To put the billing address on a sales order, join to the customer master file.

• Date and time data types—When the DBMS understands that a certain field contains a date or time, it can do arithmetic. You can tell the system to find all the customers who are at least 30 days overdue in making their payments.

• Journaling and commitment control—Journaling logs changes to the database as they occur, so you can back out changes to the database and you can recover lost work by applying journaled changes to a restored backup.

• Referential integrity—Referential integrity has to do with the way files relate to one another. You can tell the system that the lines of a purchase order belong to the header of the purchase order. When someone deletes an order header, the DBMS can remove the line items automatically.

• Triggers—A trigger program runs when a record is added, deleted, or changed. When you entrust a task to a trigger program, you don’t have to depend on programmers to remember to do that task when they write programs that use that database file.

• User-defined functions—A function converts one or more values into another value. The square root function converts 16 into four. You can make DB2/400 convert an item number and customer class code into a price.

• Stored procedures—Stored procedures are programs that are called from SQL. You like the way RPG programs can call each other, don’t you? Why not let ODBC programs that run on PCs do the same thing?

This list boils down to one thing: Get the business logic out of the program and into the database. If you don’t use these features—and many AS/400 shops don’t—you don’t have a database, but a glorified file system.

Part of my job is to keep up with what’s going on in the information-processing industry and to help you make some sense of what’s good and what’s rotten. Closely related is my duty to encourage you to change with the times whenever I believe you’ll be better off for doing so.

To that end, I’ve collected a few articles dealing with database topics. I didn’t have enough room to run articles on all the topics I thought were important, but that’s OK. I’ve got more database articles planned for upcoming issues.