Rules That Can't be Broken
We've all heard lately about some of the neat stuff in databases such as Oracle, Informix and even the new versions of DB2 (see
Data Data Everywhere from last issue). What does it all really mean, and why should you care? Your programs are reading the files they need, your logical files keep the performance level where it needs to be. What else do you need?
There are a number of tools available in the newer versions (since OS/400 V3R2 anyway) that can make the database a much more active member in your application. Instead of just holding the data (which is a good thing for it to do), it can also help monitor itself and make sure that certain things happen or don't happen depending on activities that take place in the database.
Even though the best way to take advantage of these capabilities is to design them in when setting up the database, with a little imagination you can still add them to your existing applications. As an example, let's say that your marketing department came up with a new rule for pricing. Instead of having prices using any amount of cents (pennies), such as $10.56, the new rule says all prices over 50 cents must round to that magic 99 cents we've all see in the stores. Now you have a lot of programs that maintain prices, there are discounts using percentages that impact prices and as you start to make your impact list you decide that your marketing people are nuts!
After losing this battle, you have to figure out a way to do this without changing every pricing and invoicing routine you have. In comes the database tools--create a trigger program for your price file and your order detail file (the one with prices) and anytime there's an added or changed record, call a trigger (a before trigger) to check the price. If the calculated price is over 50 cents, make it 99. Then update the record the way it should be. You may be able to implement this type of change with two programs (one for the pricing master, the other for the order detail).
This is one of the extremely powerful features of today's databases. Data management calls a trigger program automatically--your programs don't even know they exist. They can enforce business rules (like the one above) or do any other processing that should be done based on some activity (trigger) in the database. The beauty in this is that your application may not have to change and there's no way for other programs such as DFU or DBU to update the record without the trigger being evoked.
We did a change like this not too long ago and the DP manager wanted to change the price to something other than the rules set up. No matter what he tried--DFU, a quick-fix program, whatever--the price kept getting changed. He finally called and learned why--we had a trigger on the file enforcing HIS rules. He said "Oops" and hung up.
You can attach the trigger (
ADDPFTGR) to fire before the record is updated or after the record is updated (or deleted). This gives you the ability to fire off some other update or process anytime you do update a record, or if it's updated with a certain value.
Obviously you have to be careful or you can get yourself into a loop calling a program to update another file that fires a trigger to update the file you just left. It's quite powerful though, with a little thought. You can even have multiple triggers active at one time, although you want to be careful because, as with any program called, there is a performance impact.
One little note--make sure your program does not end (do not turn on LR). The trigger needs to be written to be resident all the time that the process is active or you'll take a huge performance hit.
DB2 on the AS/400 can now have triggers, stored procedures (programs attached to the database that can be called from SQL procedures) and constraints (such as not allowing you to delete a customer with AR activity). The beauty of all this is that it makes your database a more active partner in the application. After all, isn't that what most of your code is for--validating data, ensuring it's correct and then making sure the right things happen because of it. If you could build it from scratch, wouldn't it be nice to imbed these rules in the database itself?
The new capabilities in DB2/400 provide us with tremendous facilities to ensure data accuracy as well as constraints that guarantee the data is correct based on your business rules. And now those business rules are more than just RPG (or Cobol) programs running when the user calls them. Now the database calls them to check itself and to make sure that the rules you set up stay enforced. Of course you want to make sure the rules are correct, these program have no sense of humor.