Best Practices for Easier Code Maintenance

The following suggestions will help ensure that your code is easy to understand and follow.

Code Readability

The following tips will help you maintain optimal code readability:

• Use indentation! For example, when creating the body of a FOR loop, indent all nested lines by three spaces.

• Use meaningful labels in the body of the SQL procedure.

• Avoid declaring variables with the same name in different compound statements of a same program. Though supported, this can often lead to confusion.

• Avoid using the GOTO statement, because it can lead to spaghetti code which is both difficult to read and maintain. If you have to use the GOTO statement, try to skip to the end of the SQL procedure or the loop.

• Use SQL procedure nesting to break up a complex task into smaller and more manageable components.

Code Size

If a trigger or function body becomes too large, try converting it to a SQL procedure. Triggers can call SQL procedures in DB2, as long as certain rules are not violated. Functions in LUW and iSeries can also call SQL procedures.

If an SQL procedure is very small, consider converting it to a function.

Grouping SQL Procedures by Schema

Use schemas to group application objects by functional component. For example, if you have a group of triggers, functions, and SQL procedures that are responsible for day-to-day maintenance of a payroll invoice system, you may want to group them under the schema PAYROLL. You would then have objects like PAYROLL.ADD_EMPLOYEE and PAYROLL.REMOVE_EMPLOYEE.

Naming Conventions

Always use naming conventions that suggest what the trigger, function, or SQL procedure is responsible for. For example, if creating a trigger, its name should suggest its type (DELETE, UPDATE, or INSERT) and its firing sequence (before or after). For example, a trigger by the name of tab1_bupt could represent a trigger that performs a before update of tab1.

On LUW and iSeries, when creating a SQL procedure or function, always specify a specific name in the CREATE procedure or function header. This makes SQL procedure management much easier, especially when it comes to dropping the SQL procedure. On iSeries, the specific name has the added benefit of specifying the name of the C program that is created by the system (as long as it is a valid system name).

Avoid using the same name for both a variable and a table column. Such naming is allowed but typically causes confusion. If the variable and column names are the same, DB2 will resolve the name to the table column.

On iSeries, further consideration needs to be given when naming triggers, functions, and SQL procedures. For object names that are greater than 10 characters, a system name is generated. When any SQL statement accesses an object with a long name, it is converted internally to the system name. The conversion process may have a small performance impact. This is only an issue when using *SYS naming and when the SQL object names are unqualified.

Return Values Versus Out Parameters

Only use the RETURN statement to return integers as status indicators; anything else convolutes its use. Typically, a status of 0 means successful execution, while a negative value means an error and a positive value means a warning. Out parameters should be used for everything other than status indicators.

Exception Handling

To make code more portable, use SQLSTATE for exception handling instead of SQLCODE. In addition to SQLSTATE being standard across the DB2 family of products, a large proportion of the SQLSTATEs are also standard across the different database vendors.

To make the code more readable, declare condition names for specific SQLSTATEs and then declare handlers for the named condition. Figure 13.1 demonstrates this.

Figure 13.1. Declaring condition names.

 DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE '23503'; DECLARE EXIT HANDLER FOR FOREIGN_KEY_VIOLATION ... ; 

Some SQLSTATE ranges are reserved for customized SQLSTATEs for your application. This prevents the unintentional use of an SQLSTATE value that might be defined by DB2. The customizable SQLSTATEs begin with the characters 7 through 9 or I through Zfor example:

 SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'custom error' 

Commit and Rollback

Be explicit with transaction control, because DB2 SQL procedures neither COMMIT nor ROLLBACK for you by default at the end of execution. When running SQL procedures through the DB2 Development Center, it may appear as though SQL procedures COMMIT on completion. In reality, it is the Development Center issuing the commit. (See the RUN settings in Appendix D, "Using the DB2 Development Center.") On zSeries, you can use the CREATE PROCEDURE clause 'COMMIT ON RETURN' to tell the database manager to commit the transaction when returning from the SQL procedure call.