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, you must make the most fundamental design decision: 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 enforce some business rules in your 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 this list mentions some capabilities more than once, such as triggers. This reflects the flexibility of these capabilities to address more than one design criteria.
Your 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 you can change data or data logic 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 need to process validation errors on data entry in a specific order. In general, you should do these types of data validation 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 using either stored procedures, UDFs, or a combination of both.
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 you may choose how to integrate the SQL into your application. You can choose from the following interfaces and their supported languages:
Embedded SQL has the advantage that it can consist of either static or dynamic SQL or a mixture of both types. If the content and format of your SQL statements will be frozen when your application is in use, you should consider using embedded static SQL in your application. With static SQL, the person who executes the application temporarily inherit the privileges of the user that bound the application to the database. Unless you bind the application with the DYNAMICRULES BIND option, dynamic SQL uses the privileges of the person who executes the application. In general, you should use embedded dynamic SQL where the executable statements are determined at run time. This creates a more secure application program that can handle a greater variety of input.
| Note: | Embedded SQL for Java (SQLJ) applications can only embed static SQL statements. However, you can use JDBC to make dynamic SQL calls in SQLJ applications. |
You must precompile embedded SQL applications to convert the SQL statements into host language commands before using your programming language compiler. In addition, you must bind the SQL in the application to the database for the application to run.
For additional information on using embedded SQL, refer to Writing Static SQL Programs.
REXX applications use APIs which enable them to use most of the features provided by database manager APIs and SQL. Unlike applications written in a compiled language, REXX applications are not precompiled. Instead, a dynamic SQL handler processes all SQL statements. By combining REXX with these callable APIs, you have access to most of the database manager capabilities. Although REXX does not directly support some APIs using embedded SQL, they can be accessed using the DB2 Command Line Processor from within the REXX application.
As REXX is an interpretive language, you may find it is easier to develop and debug your application prototypes in REXX as compared to compiled host languages. Note that while DB2 applications coded in REXX do not provide the performance of DB2 applications that use compiled languages, they do provide the ability to create DB2 applications without precompiling, compiling, linking, or using additional software.
For details of coding and building DB2 applications using REXX, see Programming in REXX.
The DB2 Call Level Interface (DB2 CLI) is IBM's callable SQL interface to the DB2 family of database servers. It is a C and C++ application programming interface for relational database access, and it uses function calls to pass dynamic SQL statements as function arguments. A callable SQL interface is an application program interface (API) for database access, which uses function calls to invoke dynamic SQL statements. It is an alternative to embedded dynamic SQL, but unlike embedded SQL, it does not require precompiling or binding.
DB2 CLI is based on the Microsoft(TM) Open Database Connectivity (ODBC) specification, and the X/Open(R) specifications. IBM chose these specifications to follow industry standards, and to provide a shorter learning curve for DB2 application programmers who are familiar with either of these database interfaces.
For more information on the ODBC support in DB2, see the CLI Guide and Reference.
DB2's Java support includes JDBC, a vendor-neutral dynamic SQL interface that provides data access to your application through standardized Java methods. JDBC is similar to DB2 CLI in that you do not have to precompile or bind a JDBC program. As a vendor-neutral standard, JDBC applications offer increased portability.
An application written using JDBC uses only dynamic SQL. The JDBC interface imposes additional processing overhead.
For additional information on JDBC, refer to JDBC Programming.
You can write database applications that conform to the ActiveX Data Object (ADO) in Microsoft Visual Basic(TM) or Visual C++(TM). ADO applications use the OLE DB Bridge. You can write database applications that conform to the Remote Data Object (RDO) specifications in Visual Basic. You can also define OLE DB table functions that return data from OLE DB providers. For more information on OLE DB table functions, see OLE DB Table Functions.
This book does not attempt to provide a tutorial on writing applications that conform to the ADO and RDO specifications. For full samples of DB2 applications that use the ADO and RDO specifications, refer to the following directories:
DB2 supports the Perl Database Interface (DBI) specification for data access through the DBD::DB2 driver. For more information on creating appliations with the Perl DBI that access DB2 databases, see Programming in Perl. The DB2 Universal Database Perl DBI Web site contains the latest DBD::DB2 driver and information on the support available for your platform.
Query products including IBM Query Management Facility (QMF) and Lotus Notes 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. This book does not provide further information on query products.
One traditional area of application logic is validating and protecting data integrity by controlling the values allowed in the database. Applications have logic that specifically checks data values as they are entered for validity. (For example, checking that the department number is a valid number and that it refers to an existing department.) There are several different ways of providing these same capabilities in DB2, but from within the database.
The database stores every data element in a column of a table, and defines each column with a data type. This data type places 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, the database does not compare an integer 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.
Unique constraints prevent occurrences of duplicate values in one or more columns within a table. Unique and primary keys are the supported unique constraints. For example, you can define a unique constraint on the DEPTNO column in the DEPARTMENT table to ensure that the same department number is not given to two departments.
Use unique constraints if you need to enforce a uniqueness rule for all applications that use the data in a table. For additional information on unique constraints, refer to the SQL Reference.
You can use a table check constraint to define restrictions, beyond those of the data type, on the values that are allowed for a column in the table. Table check constraints take the form of range checks or checks against other values in the same row of the same table.
If the rule applies for all applications that use the data, use a table check constraint to enforce your restriction on the data allowed in the table. Table check constraints make the restriction generally applicable and easier to maintain.
For additional information on table check constraints, refer to the SQL Reference.
Use referential integrity (RI) constraints if you must maintain value-based relationships for all applications that use the data. For example, you can use an RI constraint to ensure that the value of a DEPTNO column in an EMPLOYEE table matches a value in the DEPARTMENT table. This constraint prevents inserts, updates or deletes that would otherwise result in missing DEPARTMENT information. By centralizing your rules in the database, RI constraints make the rules 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 cannot define the desired rules as table check constraints, or the rules 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 retrieval of data to the set that is valid for your application. Additionally, if you can update the view, 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.
When you write your application logic in a programming language, you also declare variables to provide some of the same restrictions on data that are described above. In addition, you can choose to write code to enforce rules in the application instead of the database. Place the logic in the application server when:
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 you add a new department, then you need to create a new account code. 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. Use the CREATE TABLE or ALTER TABLE statements to define the behavior of operations that affect the related primary key, such as DELETE and UPDATE.
RI constraints enforce your rules on the data across one or more tables. If the rules apply for all applications that use the data, then RI constraints centralize the rules in the database. This makes the rules generally applicable and easier to maintain.
For additional information on referential integrity, refer to the SQL Reference.
You can use triggers before or after an update to support logic that can also be performed in an application. If the rules or operations supported by the triggers apply for all applications that use the data, then triggers centralize the rules or operations in the database, making it generally applicable and easier to maintain.
For additional information on triggers, see Using Triggers in an Active DBMS and refer to the SQL Reference.
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.
Triggers that run after an update, insert or delete can be used in several ways:
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 you cannot generally apply the rules to the database. 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 application logic can handle the rules or operations more efficiently.
A final aspect of application design for which DB2 offers additional capability is running some of your application logic at the database server. Usually you will choose this design to improve performance, but you may also run application logic at the server to support common functions.
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 Stored Procedures.
You can write a user-defined function (UDF) for use in performing operations within an SQL statement to return:
A UDF cannot contain SQL statements. UDFs are useful for tasks like 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).
For additional information on writing user-defined functions, refer to Writing User-Defined Functions (UDFs) and Methods.
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 Using Triggers in an Active DBMS.