I Love a Smart Database
Not too long ago, when IBM added SQL support to the AS/400, one of the complaints was that the stored procedure programs needed to be HLL programs (RPG, COBOL, etc.) and did not support SQL scripting at all. Since most AS/400 shops did not use SQL anyway it was not a big problem except for those trying to port applications to the AS/400 platform from something else. At the time, the practicality of doing that was pretty slim anyway.
With version four though, a lot has been added to SQL support. We’ve discussed in this column before the changes that have been added to the database that DDS may never see. SQL is where it’s going to be. This is actually a good thing. It forces the database to be more conventional and standard. No other platform has anything like DDS, even if it does make sense.
It used to be that when we built our databases and programs, there was little to worry about concerning how the data got into our systems. It had to come from our programs. Oh sure, we tried to keep logic in one place (some of us anyway) and we tried to have an efficient design, but we owned the data and how it got in. Then EDI started growing, and we had to have a batch way to get data in. No big deal, we still owned the pipe into the system.
Then some schmo added ODBC support and all of a sudden we had spreadsheets, little database applications such as Access, and even Visual Basic. Our nice clean world of controlled data access and entry got confused and we started to lose control. Most of us really hate to lose control, especially when we’re the ones that get called when something doesn’t balance, a transaction is missing, the integration is broken, or other data somehow doesn't look the way it did yesterday.
Then there’s the mountain of questions we get about where to get this data or that for their Crystal Reports query, or how to hook up their spreadsheet. Sometimes you just want to scream! If that wasn’t enough to have us all open hot dog stands, the world decides that we need 24 hour availability because some nut wants to look at orders, or add an order at 3 o'clock in the morning—the wonderful World Wide Web of wacky folks who can't follow directions, work reasonable hours, and want access to our databases!
Enter the Internet. Now we hire outside consultants to build sites that need our data. Sales and marketing say they need the data current, up to date, and now! This means that offline storage is not an option. Can't backup? Your problem!
Oh well, it’s good for us, so why complain? It gives us something new to learn—we were all getting a little stale anyway. But how do you let folks at the data, and more importantly enter data that has to end up in your database without trashing it? Of course you have to control it, but now you have to start duplicating entry, edit and control logic in multiple places—because it can come from multiple places.
If, however, we move some of that logic deeper into the database and out of traditional programs, then we can build into the database rules, controls, edits and walls that will assure its integrity no matter where the data comes from. How do you do that? Not by coding the logic in your Web sites. You do it by coding it into the database itself. There are a number of ways to accomplish this. Unfortunately, it requires that we think about the design of the database a little. Of course I’m assuming that you have a real database design to start with and not something that you’ve grown over the years that has no integrity or structure of design.
Some of the ways to accomplish this include using a combination of the following: triggers, stored procedures, constraints, and referential integrity. All of these we now have in DB2 UDB on the AS/400.
As I help companies with migrating to these new concepts, I often get asked if it’s an all or nothing process. "Do we have to start all over to gain the benefits?" If that were the case, it would be impossible for companies to upgrade their systems without wholesale replacements. Now don't get me wrong, sometimes wholesale replacement is what’s needed, especially if the current design is really bad. You do have to start with a reasonable database scheme—design to take advantage of these new tools and concepts.
If the design is adequate, then start to build the controls into the database through these triggers and stored procedures. Referential integrity is the easiest of all. Operations Navigator in Client Access Express will do it for you. Constraints are also not too difficult. Start easy and as new requirements from the Web or outside data influence impact you, use the tools to help you. You may even find that it helps clean up some of your own data issues. After all, referential integrity is not just for the Web stuff, it counts for your programs too.
Over the next few columns we’ll take a look at how to make the database work for you. New rules require new technologies to keep up with. Don't fight it, employ it. It can be used for competitive advantage, or you can let the guy across the street beat you to it.
John Bussert is president of Swift Technologies (Marengo, Ill.). jbussert@stecnet.com