Business Intelligence

Every application is based on a set of business rules - whether the rules are formally defined using a 4GL or tacitly assumed based on a programmer's knowledge of the real business model the application depicts. Business rules represent the set of policies and requirements needed when you perform a business function. They're usually defined at the level of a business transaction, such as taking a new order. Business rules are in place to ensure that all your business requirements are met on each transaction. For example, if you implement a policy that charges customers an additional $10 handling fee for overnight delivery unless the order's value exceeds $100, you've created a new business rule. You want to make certain this rule is enforced regardless of the source of the order, whether it's a new order or a change to an existing order, and no matter who receives the request for overnight shipping from the customer.

The rules might be as simple as defining the validations and computations needed before updating a database object, or they can define how the database update affects other tables in your database. For example, the requirements for inserting a new order might be:

• verifying that the order doesn't exceed the maximum order size
• validating that the customer's balance doesn't exceed his credit limit
• calculating the order total
• ensuring that more items are ordered when the inventory level falls below a given threshold

In the past, business rules were typically defined and coded on an application-by-application basis. However, new tools implemented in DB2 UDB for iSeries let you define certain types of business rules at the database level. Many of you have already implemented similar business rules in your applications, and you may wonder what the benefits are to using DB2 UDB for iSeries to enforce your policies and processes. The main benefits are improved data integrity, code reuse, and modularity.

By declaring your business rules in the database, you're assured that those requirements are enforced across all transactions and, more importantly, all interfaces. In contrast, business rules implemented in an application are enforced only when that application is used to change your database. Relying on application-enforced business rules opens up serious data integrity issues when data corrections are made using tools like SQL and data file utility (STRDFU). The proliferation of new modern interfaces (e.g., web browsers) on iSeries and iSeries servers make data integrity across all interfaces an even higher priority.

Consistency across different transactions is also important. Checking the maximum order size at the database level guarantees that new order transactions and change order transactions follow the same rule. The application programmer is no longer responsible for ensuring that the maximum order size enforcement is consistent across all programs.

Database enforcement of business rules leads to better code reusability. Because no programmer writes code from scratch, code for the change order transaction was probably copied from the new order transaction and tweaked to request an update transaction instead of an insert transaction. By moving policies such as the maximum order size into the database, less common code is copied from application to application -- the code is reused at the database level rather than rewritten for different transactions. Centralization of your business rules in the database should result in less code that your developers have to write (or copy and modify), maintain, and test.

Centrally located business rules offer distinct advantages when a rule such as the maximum order size changes or a new application is deployed. Changing the maximum order size is as simple as changing the database rule from OrderQuantity <= 100 to OrderQuantity <= 250. The updated rule is automatically enforced for all transactions, and no change to the application is required. When rules are stored in the database, modifying a rule is a relatively short and simple procedure. When rules are stored in multiple applications, changing even a simple rule can be a complex procedure.

The final benefit of database-based business processing is improved modularity and partitioning of functions in your application. Modularizing all your core business rules in the database naturally leads to application partitioning. Application partitioning provides an avenue for modernizing your application.

Some of the simpler business policies can be enforced at the file and field level. For instance, you may require that an order's customer number reference a valid entry in the customer table and that the order not exceed the maximum order quantity. DB2 UDB for iSeries supports several constraint types for making these rules part of your database definition: check constraints, referential constraints, primary key constraints, and unique constraints.

DB2 UDB for iSeries check constraints ensure that a field is only assigned legal values. A check constraint is a natural way to ensure that order quantity doesn't exceed the maximum (e.g., OrderQty>0 and OrderQty<100). Check constraints let you compare a field's value to a constant value, another field value in the same file, or the result of an SQL built-in function such as ABSVAL or CONCAT. (Figure 2 lists all SQL built-in functions.)

Referential integrity constraints (also known as RI constraints or referential constraints) implement business rules at the file level. An RI constraint would be used in DB2 UDB for iSeries to make sure that each new order references a customer that exists in the customer table. A referential constraint is defined for the order table to keep the child-parent (detail-master) data relationship in synch between the order and customer files. With a referential integrity constraint in place between the two files, DB2 UDB for iSeries guarantees that each order always refers to a valid customer.

Because the customer number will be used to uniquely identify each customer in the customer table, a primary key constraint would be defined in DB2 UDB for iSeries to ensure each new customer number value is a unique value. If the customer table is already defined as uniquely keyed (i.e., DDS keyword UNIQUE), a primary key constraint doesn't have to be defined explicitly. If you have a unique keyed physical file and add a referential constraint that uses the same unique key fields for the parent key, then the system automatically generates an extra ADDPFCST (Add Physical File Constraint) command to add a primary key constraint to the parent table. If you look at constraints for the table using iSeries Navigator or the WRKPFCST (Work with Physical File Constraint) command, the primary key constraint will be shown.

Because SQL allows only one primary key per file, unique constraints must be used for any other fields that require unique values in each record. For example, if for confidentiality reasons, your company generates an employee serial number instead of using the Social Security number, there will be two unique keys to the employee table. The employee serial number would be the primary key uniquely identifying each person in the employee table, and a unique constraint would be defined for the Social Security number column ensuring that two employee records don't accidentally have the same Social Security number. You can think of primary key and unique constraints as another way of creating uniquely-keyed logical files.

Constraints of all types can be defined in DB2 UDB for iSeries with iSeries Navigator, SQL, or native (CL command) interface. As you start defining constraints for your database, you might also want to start using SQL to create your database objects because the SQL interface for constraints offers one advantage over the native interface: the SQL CREATE TABLE statement lets you define any constraints for that file object. So in a single SQL request, you can create both your database table and database constraints.

The advent of Operations Navigator's DB2 UDB for iSeries components makes it easier to move your business rules into DB2 UDB for iSeries. Using iSeries Navigator, you can graphically define your database objects, constraints, and triggers - a modern graphical interface for your application's modernization. If you are unfamiliar with iSeries Navigator, download tutorials are available on the web at:
ibm.com/partnerworld/wps/training/i5os/courses

Constraints provide great vehicles for defining simple file and field level rules, but more complex business rules, such as checking inventory levels before accepting an order, may require program code that reads and processes data spanning multiple database tables. More complex processes and policies can be carried out in DB2 UDB for iSeries with triggers and stored procedures. You can understand these objects most easily by considering them to be programs. Coding details for trigger and procedures can be found in the "Stored Procedures & Triggers on DB2 UDB for iSeries" Redbook (SG24-5409) (hotlink "Redbook" to ibm.com/redbooks).

A trigger is an SQL routine or OS/400 program object associated with a database table and set in motion (or triggered) by a change in data. DB2 UDB for iSeries triggers can be fired with the insert, update, or delete of a row. When you define a trigger, you define which of these database events will cause DB2 UDB for iSeries to automatically call the trigger logic. A DB2 UDB for iSeries trigger could easily be used to read data in other files to determine whether the order should be accepted or not. The trigger could also kick off a batch job to order additional inventory whenever the trigger logic determines that inventory levels have dropped low enough to require back order processing. Because triggers are just programs, they can perform non-database actions as well. For example, each time you add a new customer to the customer table, a trigger could send the customer information to a data queue. Then, another program can take the data off the queue and fax a welcome letter to the new customer.

DB2 UDB for iSeries triggers give you the flexibility to call programs before or after the event. "Before" triggers can perform complex data validation and even data correction. With before triggers, you can also change the row data before DB2 UDB for iSeries copies it into the database. For example, if you wanted to centralize a complex tax calculation in the database, you could have a before trigger program that fills in the tax field with the correct value.

Stored procedures, like triggers, are programs, but the programmer controls when the program is called -- the database doesn't invoke stored procedures automatically. (Stored procedures is just really an SQL term for a called module.) Stored procedures are a good way to modularize, or partition, a related set of database operations, possibly even an entire transaction. For example, the database changes necessary when transferring inventory from one warehouse location to another could be implemented as a stored procedure. Well-defined modular stored procedures can be shared across different business transactions to improve code reusability. Stored procedures also provide performance savings in a multiple tier environment by letting you perform multiple database operations on a single server request from the client. You can write DB2 UDB for iSeries stored procedures entirely in SQL using procedural extensions (known as PSM), or you write them using high-level languages such as Java, C, RPG, and Cobol. In DB2 UDB for iSeries stored procedures written in SQL are created with the SQL CREATE PROCEDURE statement. You can invoke a stored procedure using the SQL CALL statement. Development tools based on middleware such as an ODBC or JDBC driver have their own interfaces for invoking an SQL CALL statement.

DB2 UDB for iSeries also supports user-defined scalar functions and user-defined table functions. If you find that the SQL functions aren't rich and flexible enough for implementing your business rules and processes, you can create your own user-defined function for DB2 UDB for iSeries to use. Like Stored Proceudre, any iSeries programming language can be used to create user-defined functions. For more details on user-defined functions you can refer to the iSeries SQL Reference (hotlink "SQL Reference" to: www.iseries.ibm.com/db2/books.htm) or the "DB2 UDB for iSeries Object-Relational" Redbook (SG24-5409).

Business rules encourage a new approach and methodology for both application implementation and design. One standard approach to application design work with end users requires breaking down new requests into business transactions, analyzing each transaction to determine the policies and requirements, and defining rules that enforce each requirement. You can also start working with end users to develop an inventory of existing business transactions and their associated business rules. Modularizing your current application code will also make it easier to view the detailed business rules your company uses. After this analysis is completed, the next step is to use the DB2 UDB for iSeries constructs to implement the business rules in your database.

Using DB2 UDB for iSeries based business rules requires a different approach from the application programmer. Instead of coding business rules into each program, you write code that checks for feedback from the database about business rule violations. DB2 UDB for iSeries sends a violation message when it finds the first constraint in error rather than forwarding all violations at once. This too requires some changes in the way you design programs because most end users are accustomed to seeing all their problems identified at once.

Here are some things to keep in mind when designing the layout and sequencing of your application's user interface. First, constraints aren't enforced in the order of fields on the screen, they are enforced in the reverse order that they were defined in the database file. The easiest way to determine this is to reverse the order of the constraint information returned by the DSPFD (Display Physical File Description) command or iSeries Naviagor interface. Second, referential constraints are validated before check constraints, which means that the application will receive any referential constraint violation messages first. (The "Advanced Functions & Administration on DB2 UDB for iSeries" Redbook (SG24-4249-03) (hotlink "Redbook" to: ibm.com/redbooks) devotes a section to this topic.)

The ordering of constraint enforcement should be a key influence to determining the layout and sequencing of your end-user interfaces. You can control the order messages are returned by controlling the order in which constraints are defined. Redesigning end-user screens or educating end users about new database interaction behavior should also be part of your rollout plan for having DB2 UDB for iSeries enforce your business rules and processes.

To ensure that you get some code reusability benefits from constraints, you'll want to modularize the code that handles the database feedback and violations. A service program will let you create the error handling logic once and share it with all your application programs.

The modularity and reusability of business rules should make DB2 UDB for iSeries-based business rules a key part of any application modernization project or new application development. Yes, things will be different from your current application environment, but over the long haul, a more intelligent database puts you in a much better position for incorporating new technologies into your application portfolio. At the very least, the improved data integrity across all interfaces should motivate you to use DB2 UDB for iSeries-based business rules as a safety net for those cases where your application based rules are bypassed.

DB2 UDB for iSeries constraints, triggers, and stored procedures are relevant to your business. Using these features will help you to modernize your application development and better leverage the resources and services provided by DB2 UDB for iSeries -- instead of duplicating the work.

This paper, written by Kent Milligan, originally appeared in The iSeries Strategist, October 1998, Volume 1, Issue 8. Kent is a member of the Database technology team in IBM eServer Solutions iSeries Partners in Development business intelligence team. He spent the first eight years of his IBM career as a member of the DB2 UDB development team in Rochester working on numerous enhancements including constraints and triggers. You can reach him at kmill@us.ibm.com.

• Contact us

• PartnerWorld website feedback