Developing Applications Using Only SQL

Although it is uncommon for an entire application to be developed with SQL alone, it is quite common for components of an application to be coded using only SQL. Pure SQL is a good choice for the quick development of code to satisfy simple application requirements. Examples include the following:

• Using the UPDATE statement to reset indicators in tables after batch processing

• Deleting every row from a table using a mass DELETE or deleting a predefined set of rows from a table after batch processing

• Any type of application that is composed entirely of data modification statements (that is, just a bunch of INSERT , UPDATE , and DELETE statements)

• Creating simple, unformatted table listings

• Performing simple data entry controlled by a CLIST or REXX EXEC

Additionally, now that DB2 supports code-based objects that enhance the functionality of SQL, more processing can be accomplished using SQL alone. With triggers, stored procedures, and user -defined functions, very powerful SQL-based "applications" can be developed.

NOTE

You still need to write application code when you develop stored procedures and user-defined functions. Once the code is written, it is possible to write SQL-only applications that call the stored procedures and utilize the user-defined functions.

SQL Application Guidelines

The following guidelines are helpful when developing an application using only SQL.

Use Native SQL Applications Sparingly

Although using native SQL (without embedding it into a program) in some circumstances is technically possible, avoid doing so unless the application truly can be developed without advanced formatting features or procedural logic. Achieving the level of professionalism required for most applications is difficult if you use SQL alone. For example, you cannot use SQL alone to format reports , loop through data a row at a time, or display a screen of data.

DB2 stored procedures can be coded using IBM's version of SQL/PSM, the procedural dialect of SQL. However, standalone SQL statements cannot use SQL/PSM functionality.

Enforce Integrity Using DB2 Features

If you develop a complete application or major portions of an application using only SQL, be sure to use the native features of DB2 to enforce the integrity of the application. For example, if data will be entered or modified using SQL alone, enforce user-defined integrity rules using triggers, check constraints or VALIDPROC s coded for each column and specified in the CREATE TABLE DDL.

Additionally, specify referential constraints for all relationships between tables and create unique indexes to enforce uniqueness requirements. This approach is the only way to provide integrity when a host language is not used.

NOTE

It is a wise course of action to enforce data integrity using DB2 features regardless of the type of application. DB2-based integrity is non-bypassable, and therefore, generally preferable. For standalone SQL applications, though, DB2-based integrity is a requirement because there is no other code in which you can code integrity constraints.

Simulate Domains Using Check Constraints or Tables

Mimic the use of domains when possible using domain tables or check constraints. Domain tables are two-column tables that contain all valid values (along with a description) for columns in other tables. For standalone SQL applications, be sure to use referential integrity to tie these "domain" tables to the main tables. For example, you can create a "domain" table for the SEX column of the DSN8810.EMP table consisting of the following data:

SEX	DESCRIPTION
M	MALE
F	FEMALE

The primary key of this "domain" table is SEX . You specify the SEX column in the DSN8810.EMP as a foreign key referencing the domain table, thereby enforcing that only the values M or F can be placed in the foreign key column. This way, you can reduce the number of data entry errors.

CAUTION

The advice in the previous two paragraphs is not intended to be a general purpose rule of thumb, but a guideline to be followed when SQL-only applications are being developed. In more typical DB2 application systems referential constraints on "domain"-like tables are not generally recommended. Large applications with many domain tables can create large, unruly table space sets which are difficult to manage, backup, and recover. Instead, programming constructs can be used to manipulate and use "domain"-like tables (for example, in-memory table structures).

Check constraints provide an alternative approach to enforcing domain values. Instead of creating a new table coupled with referential constraints, you can add a single check constraint to the column to enforce the data content. Consider this example:

 SEX               CHAR(1) CONSTRAINT GENDER CHECK (SEX IN ("M", "F")) 

Whether to choose domain tables or check constraints depends on the circumstances. Each is useful in different situations. Weigh the following benefits and drawbacks before choosing one method over the other:

• Check constraints are simply SQL predicates and cannot carry description columns (or any other columns), whereas domain tables can. Therefore, a domain table can be more self-documenting .

• Check constraints should outperform referential integrity because DB2 does not need to read data from multiple user tables to determine the validity of the data.

• Domain tables are easier to use when the domain is not static. Adding values to a check constraint requires DDL changes; adding values to a domain table requires a simple SQL INSERT .

• As the number of valid values increases , domain tables are easier to implement and maintain. The full text of a check constraint can contain no more than 3,800 bytes.

• For smaller domains, check constraints are preferable not only for performance reasons, but because no additional table space or index administration is required.

• When you're tying together domain tables using referential integrity, sometimes large referential sets are created. They can be difficult to administer and control.

  Large referential sets, however, may be preferable to program-enforced RI or, worse yet, allowing inaccurate data. When you're deciding whether to enforce RI for domain tables, balance performance and recoverability issues against possible data integrity violations. When large referential sets are created, consider breaking them up using check constraints for some of the simpler domains.

Follow SQL Coding Guidelines

When you're developing native SQL applications, follow the SQL coding guidelines presented in Chapter 2, "Data Manipulation Guidelines," to achieve optimal performance.