Store Logic Not Just Data In Your DB

• 05/22/2000

After a while, you look in the mirror and say, “Huh? What happened?” When did the things we used to worry about—data integrity, performance, and day-to-day operations—stop being our core concerns? Well, even with the all this new technology, the basics still require our time, attention and design effort. In fact, they become more important as more external resources and influences are involved.

In the past we’ve discussed how triggers are used in a database to enforce data integrity so that we know things update the way we designed them to update. At the same time triggers became available to us, stored procedures became available. Unfortunately, most people, unless they’re well versed with other sophisticated database management systems, don’t even know about them.

Stored procedures are powerful techniques that allow you to build code on the AS/400 and execute it just like an SQL query. ODBC and JDBC (the Java database support API) allow calls to the database that can invoke stored procedures. These can accept parameters, process any data you desire and can be driven by functions outside the AS/400.

Stored procedures are programs that you write in any AS/400 language except Rexx. They cannot be written using the S/36 subsystem. They can, however, perform any function you need; of course the idea is that you perform functions related to the data you’re updating. The neat part about them is that all your applications can call these procedures. Web applications, green-screen programs, APIs that you might hook up with EDI or other applicationsall can update things in the database the same way.

When stored procedures first became available, they could only be executed as HLL programs. While those of us on the 400 couldn’t see any problem with that, companies trying to port applications to the 400 from other database systemswhere stored procedures were more SQL-basedhad a very difficult time. So IBM opened up the 400's stored procedure programs with SQL scripts. This is great, but with one wrinkleit requires the C compiler to create the procedures. Why, you ask? Because, the create stored procedure process converts the SQL script to C function calls in a C program, compiles it, and that becomes the stored procedure object.

At first this may sound confusing, but the reality is, “Who cares?” All HLL programs get compiled into MI programs anyway, so as long as we can write our routines and understand them, what difference does it make what language they’re implemented in. I guess the only thing that I’d like to see if this is a requirement, is that they include C as a base part of the operating system. Or at the least include it as part of the SQL development function that includes Query Management, which is purchased separately.The nice part about using SQL script is that it’s more conventional, more portable, and for those who’ve been learning it, easier to understand than C. But how should you use it? Let's say you have a pretty sophisticated pricing routine. Your Web guys just don't understand why they can't use an ODBC call and get the price from a file. You’ve tried to explain to them that there are rules based on customer, class, promotions, contracts, etc., that all affect the price, not to mention the date an order is taken, special SPIFs and the like. The reality is, they don't care. When a customer signs on a Web site, they need to get a price.

Stored procedures would be a way to implement that, where the Web guys can use a simple ODBC call to get the answer they need. The required information is then passed to the ODBC call, and a price is returned using your AS/400 logic. The beauty of this is that you could use the same exact code to handle all requests for information or for processes that take place because of a database event.

That’s how you get data management integrity. Through design and consistency in implementation across technologies, you make sure that the database contains the data you intended. Those coming from independent database systems such as Oracle or Informix understand this design and coding technique. Those of us on IBM platforms have been spoiled by the power of the systems we’ve been dealing with for years. Now with all these outside expectations, we need ways to handle events that did not originate from within our nice clean RPG (or whatever) environment.

The key to using stored procedures on the 400 is understanding what they are, how they work and the peculiarities of the way the 400 works with C, SQL and stored procedures. As an example, a character field defined in ILE C (the procedure target language) adds one character to the field definition. Why? Who cares? That's the way it works, deal with it! But it might be worth reading the manual “DB2 UDB for AS/400 SQL Programming,” to learn some of these peculiarities.

Stored procedures are a powerful way to open up a new world of functionality. All it takes is a little understanding, some experimentation, some errors, and a lot of design time. Hmmm…where is that coming fromwho has time for design? And we thought software was engineered!

John Bussert is president of Swift Technologies (Marengo, Ill.), a firm specializing in AS/400 and Windows NT software. jbussert@stecnet.com

Related Editorial: