< Day Day Up > |
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:
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 GuidelinesThe following guidelines are helpful when developing an application using only SQL. Use Native SQL Applications SparinglyAlthough 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 FeaturesIf 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 TablesMimic 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:
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:
Follow SQL Coding GuidelinesWhen you're developing native SQL applications, follow the SQL coding guidelines presented in Chapter 2, "Data Manipulation Guidelines," to achieve optimal performance. |
< Day Day Up > |