DB2 provides you with a variety of application development capabilities that you can use to supplement or extend the traditional capabilities of an application. As an application designer, the design decision that you must make is the most fundamental one: which DB2 capabilities should I use in the design of my application? In order to make appropriate choices, you need to consider both the database design and target environments for your application. For example, you can choose to include some of the enforcement of business rules in the database design instead of including the logic in your application.
The capabilities you use and the extent to which you use them can vary greatly. This section is an overview of the capabilities available that can significantly affect your design and provides some reasons for why you might choose one over another. For more information and detail on any of the capabilities described, a reference to more detail is provided.
The capabilities that you need to consider include:
You will notice that the above list mentions some capabilities such as triggers, more than once. This reflects the flexibility of these capabilities to address more than one design criteria.
The first, and most fundamental decision is whether or not to move the logic to enforce application related rules about the data into the database.
The key advantage in transferring logic focussed on the data from the application into the database is that your application becomes more independent of the data. The logic surrounding your data is centralized in one place, the database. This means that maintenance of data or data logic can be done once and affect all applications immediately.
This latter advantage is very powerful, but you must also consider that any data logic put into the database affects all users of the data equally. You must consider whether the rules and constraints that you wish to impose on the data apply to all users of the data or just the users of your application.
Your application requirements may also affect whether to enforce rules at the database or the application. For example, you may be required to process validation errors on data entry in a specific order. In general, these types of data validation need to be done in the application code.
You should also consider the computing environment where the application is used. You need to consider the difference between performing logic on the client machines against running the logic on the usually more powerful database server machines.
In some cases, the correct answer is to include the enforcement in both the application (perhaps due to application specific requirements) and in the database (perhaps due to other interactive uses outside the application).
In a relational database, you must use SQL to access the desired data, but the method with which you integrate the SQL into your application is your choice. These choices are:
Embedded SQL has the advantage that it can consist of either static or dynamic SQL or a mixture of both types. If your SQL statements will be frozen in terms of content and format when your application is in use, you should consider using embedded static SQL in your application. With static SQL, the executor of the application can temporarily inherit the privileges of the user that bound the application to the database. Embedded dynamic SQL can be used where the statements that need to be executed are determined while the application is running. This creates a more generalized application program that can handle a greater variety of input.
An application that includes embedded SQL requires program preparation prior to using your programming language compiler. In addition, the SQL that makes up the application must be bound to the database in order for the application to run.
For additional information on using embedded SQL, refer to Chapter 2. "Writing Embedded Static Programs".
CLI provides data access to your application through the use of function calls to CLI functions. Any SQL statements that need to be issued are passed to the database using a CLI function call. The advantages of CLI include the elimination of the need for precompiling and binding the program, as well as the increased portability of your application through the use of the Open Database Connectivity (ODBC) interface which is supported by CLI.
An application written using CLI only uses dynamic SQL. There is some additional overhead in processing imposed by the CLI interface itself.
For additional information on CLI and ODBC, refer to "Programming With the DB2 Call Level Interface (CLI)" and "The DB2 Call Level Interface (CLI)".
REXX supports a dynamic SQL interface that does not require program preparation or the need for the application to be bound to the database. Using this interpretive language may help speed development of applications.
Various query products are available that support query development and reporting. The products vary in how SQL statements are developed and the degree of logic that can be introduced. Depending on your needs, this approach may meet your requirements to access data. Further information on query products is not provided in this book.
One traditional area of application logic is the validation and protection of data integrity with respect to the values allowed in the database. Applications have logic that specifically checks data values as they are entered, to ensure that the data is valid. (For example, check that the department number is a valid number and that it is for an existing department.) There are several different ways of providing these same capabilities on DB2, but from within the database.
Every data element in the database is stored in a column of a table and the column is defined to have a data type. This data type will place certain limits on the types of values for the column. For example, an integer must be a number within a fixed range. The use of the column in SQL statements must conform to certain behaviors; for instance, an integer cannot be compared to a character string. DB2 includes a set of built-in data types with defined characteristics and behaviors. DB2 also supports defining your own data types, called user-defined distinct types, that are based on the built-in types but do not automatically support all the behaviors of the built-in type. You can also use data types, like binary large object (BLOB), to store data that may consist of a set of related values, such as a data structure.
For additional information on data types, refer to the SQL Reference.
A table check constraint is used to define restrictions, beyond those of the data type, on the values that are allowed for a column in the table. This can be in the form of range checks or checks against other values in the same row of the same table. Table check constraints are used to enforce your rules on the data allowed in the table. If the rules apply for all applications that use the data, then using the table check constraints centralizes the rule in the database, making it generally applicable and easier to maintain.
For additional information on table check constraints, refer to CREATE TABLE in the SQL Reference.
Referential integrity (RI) constraints, as considered from the perspective of data value control, allow you to control the uniqueness of the values of a key (one or more columns) and existence of a row with a specified foreign key when the primary key does not exist. As with table check constraints, RI constraints are used to enforce your rules on the data, except RI constraints may span one or more tables. If the rules apply for all applications that use the data, then using the RI constraints centralizes the rules in the database, making it generally applicable and easier to maintain. See "Data Relationship Control" for further uses of RI constraints.
For additional information on referential integrity, refer to the SQL Reference.
If your application has rules that cannot be defined as table check constraints or that do not apply to all uses of the data, there is another alternative to placing the rules in the application logic. You can consider creating a view of the table with the conditions on the data as part of the WHERE clause and the WITH CHECK OPTION clause specified. This view definition restricts the data that can be retrieved to the set that is valid for your application. Additionally, if the view can be updated, the WITH CHECK OPTION clause restricts updates, inserts, and deletes to the rows applicable to your application.
For additional information on the WITH CHECK OPTION, refer to the SQL Reference.
The programming language in which you write your application logic also provides some of the same restrictions on data that are described above, through declaring variables. In addition, you can choose to write code to enforce rules in the application instead of the database. Do this in cases where the rules are not generally applicable, but not in the case of views noted in "Views With Check Option". You may also choose to place the logic in the application when you do not have control over the definitions of the data in the database, or when you believe the rule can be more effectively handled in the application logic. For example, processing errors on input data in the order that they are entered may be required, but cannot be guaranteed from the order of operations within the database.
Another major area of focus in application logic is in the area of managing the relationships between different logical entities in your system. (For example, if a new department is added, then a new account code needs to be created.) DB2 provides two methods of managing the relationships between different objects in your database: referential integrity constraints and triggers.
Referential integrity (RI) constraints, considered from the perspective of data relationship control, allow you to control the relationships between data in more than one table. This includes rules for restricting deletes, cascading deletes, or setting null values on deletes of rows from a parent table. You can also restrict updates when the parent table does not contain a corresponding key value. RI constraints are used to enforce your rules on the data across one or more tables. If the rules apply for all applications that use the data, then using the RI constraints centralizes the rules in the database. This makes it generally applicable and easier to maintain.
For additional information on referential integrity, refer to the SQL Reference.
You can use triggers in a number of different ways to support logic that can also be performed in an application. Using triggers that run before an update or insert, values that are being updated or inserted can be modified before the database is actually modified. These can be used to transform input from the application (user view of the data) to an internal database format where desired. These before triggers can also be used to cause other non-database operations to be activated through user-defined functions.
Using triggers that run after an update, insert or delete can be used in several ways:
If the rules or operations supported by the triggers apply for all applications that use the data, then using triggers centralizes the rules or operations in the database, making it generally applicable and easier to maintain.
For additional information on triggers, refer to Chapter 8. "Using the Active DBMS Capabilities", or the SQL Reference.
You may decide to write code to enforce rules or perform related operations in the application instead of the database. You must do this for cases where the rules are not generally applicable. You may also choose to place the logic in the application when you do not have control over the definitions of the data in the database or you believe the rules or operations can be more efficiently handled in the application logic.
A final aspect of application design for which DB2 offers additional capability is having some of your application logic running at the database server. This is usually considered for performance reasons but can also be done for common function support.
A stored procedure is a routine for your application that is called from client application logic but runs on the database server. The most common reason to use a stored procedure is for database intensive processing that produces only small amounts of result data. This can save a large amount of communications across the network during the execution of the stored procedure. You may also consider using a stored procedure for a set of operations that are common to multiple applications. In this way, all the applications use the same logic to perform the operation.
For additional information on Stored Procedures, refer to Chapter 5. "Writing Stored Procedures".
A user-defined function (UDF) can be written for use in performing operations within an SQL statement that returns a single scalar value. A UDF cannot contain any SQL statements. UDFs are useful for such things as transforming data values, performing calculations on one or more data values, or extracting parts of a value (such as extracting parts of a large object). They are also quite flexible, as they can be written in a high-level language (C, C++, or Java).
For additional information on writing user-defined functions, refer to Chapter 7. "Writing User-Defined Functions (UDFs)".
In "Triggers", it is noted that triggers can be used to invoke user-defined functions. This is useful when you always want a certain non-SQL operation performed when specific statements occur, or data values are changed. Examples include such operations as issuing an electronic mail message under specific circumstances or writing alert type information to a file.
For additional information on triggers, refer to Chapter 8. "Using the Active DBMS Capabilities".