Best Practices for Performance

The following recommendations provide alternate ways to perform common tasks to achieve better performance. These suggestions cover both SQL and SQL PL.

Exceptions

On LUW and iSeries, use nested compound statements to localize exception handling and cursors. If the cursors and handlers for specific exceptions are scoped to a compound statement, processing for these exceptions and cursors does not occur outside the compound statements.

Repetitive SQL

Repeated statements should use parameter markers instead of literals to avoid the cost of recompilation.

Table Truncation

On LUW, mass DELETEs can be accomplished with the command ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY EMPTY TABLE. Because these operations are not logged, they will avoid using log space.

Reducing Locking

Avoid using an isolation level higher than what you need. DB2 supports No Commit (NC, iSeries only), Uncommitted Read (UR), Cursor Stability (CS), Read Stability (RS), and Repeatable Read (RR), where RR requires the most locks (it locks every row it reads) and UR or NC requires no locks and can read uncommitted data. The more locks held, the less concurrency exists between applications that are trying to acquire locks on the same objects. By default, on LUW and zSeries, SQL procedures use CS; on iSeries, the commit level at the time of the create is used.

On LUW

Two new procedures have the ability to set and unset PREP options: SET_ROUTINE_OPTS() and GET_ROUTINE_OPTS(). These are the recommended ways of changing options rather than using DB2_SQLROUTINE_PREPOPTS, because you can control the options on a per-procedure level rather than instance-wide. Additionally, you do not have to stop and start the instance for the change to take effect.

For example, to set the isolation level to Uncommitted Read:

 CALL SET_ROUTINE_OPTS('ISOLATION UR') 

You can also specify the isolation level to use at a statement level using the option WITH <isolation level> at the end of your statement.

On iSeries

The SET OPTION clause's COMMIT option of the CREATE PROCEDURE statement can be used to specify the isolation level. See the section on iSeries precompile options later in this chapter for more details.

On zSeries

In DB2 for zSeries, use the BIND option ISOLATION to specify the isolation level. The isolation levels supported are the same as DB2 LUW. In addition, DB2 for zSeries uses the ACQUIRE and RELEASE bind parameters to determine when to acquire the locks and when to release them, respectively. ACQUIRE(USE) and RELEASE(COMMIT) will provide for most concurrency.

Note

Use the parameter EVALUATE UNCOMMITTED found in the panel DSNTIP8 to reduce locking. This parameter is equivalent to the registry variable DB2_EVALUNCOMMITTED in DB2 LUW.

In addition, DB2 for zSeries has mechanisms to avoid locking on rows and pages with committed data; this is known as lock avoidance. Review the DB2 for zSeries considerations section in Chapter 5, "Understanding and Using Cursors and Result Sets," for details about lock avoidance.

Dynamic SQL for DDL

Dynamic SQL is often used for DDL statements to get around dependency problems on LUW. To illustrate usages of dynamic DDL statements, implement the following SQL procedure (shown in Figure 13.2) that uses a static SQL to create a table called audit, if the table does not already exist.

Figure 13.2. DDL in SQL procedures using static SQL. This sample can lead to errors on LUW.

 CREATE PROCEDURE flush_audit()     LANGUAGE SQL     SPECIFIC flush_audit           -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>          -- applies to zSeries fa: BEGIN     DECLARE tabcount INT DEFAULT 1;     DECLARE table_not_found CONDITION FOR SQLSTATE '42704';     DECLARE CONTINUE HANDLER FOR table_not_found         SET tabcount=0;     DROP TABLE AUDIT;  -- COMMIT;                        -- (1) applies to zSeries     CREATE TABLE AUDIT (AUD_ID INT, OP_TIME TIMESTAMP, OPERATION VARCHAR(500));     RETURN tabcount; END fa 

At first glance, the code in Figure 13.2 appears fine because it will build and run without errors. Upon trying to rebuild after running the SQL procedure on LUW, however, you will get the following error:

 [IBM][CLI Driver][DB2/NT] SQL0601N The name of the object to be created is iden- tical to the existing name "DB2ADMIN.AUDIT" of type "TABLE". LINE NUMBER=12. SQLSTATE=42710 

The error occurs because on LUW, DB2 checks for object dependency when the SQL procedure is created. In the case of the static CREATE TABLE statement, DB2 found that the AUDIT table already existed and therefore the SQL procedure creation fails. The simple workaround is to drop the existing table and then attempt to rebuild the SQL procedure again. However, this may be a hassle or not feasible if you need to preserve the information in the existing table. The recommendation for DDL, then, is to use dynamic SQL for any DDL Statements to defer dependency checking until execution time.

On iSeries and zSeries, this would not be a problem. On iSeries, the dependency checking is deferred until runtime (if it cannot resolve an object at build time), and hence the form shown in Figure 13.3 can be built without any errors.

Figure 13.3. Executing DDL using dynamic SQL.

 CREATE PROCEDURE flush_audit2 ()     LANGUAGE SQL     SPECIFIC flush_audit2                               -- applies to LUW and iSeries   --WLM ENVIRONMENT <env>                               -- applies to zSeries fa2: BEGIN     DECLARE vide VARCHAR (100);     DECLARE tabcount INT DEFAULT 0;     SELECT count (*) INTO tabcount         FROM SYSCAT.TABLES WHERE TABNAME='AUDIT' AND TABSCHEMA=USER;      -- applies to iSeries      -- FROM SYSTABLES WHERE TABLE_NAME='AUDIT' AND TABLE_SCHEMA=USER;      -- applies to zSeries      -- FROM SYSIBM.SYSTABLES WHERE NAME='AUDIT' AND CREATOR=USER;      IF tabcount=1 THEN          SET vide='DROP TABLE ' || USER || '.AUDIT';          EXECUTE IMMEDIATE vide;       -- COMMIT;                                  -- (1) applies to zSeries      END IF;      SET vide='CREATE TABLE ' || USER || '.AUDIT ' ||              '(AUD_ID INTEGER, OP_TIME TIMESTAMP, ' ||              'OPERATION VARCHAR (500))';      EXECUTE IMMEDIATE vide;      RETURN tabcount; END fa2 

On zSeries, dependency checking is also deferred until runtime when the BIND parameter VALIDATE is set to a value of RUN which is the default. Notice that on Line (1), an explicit COMMIT is coded. If this had been omitted and the table audit existed, DB2 on zSeries would have raised an SQLCODE -679 (the object cannot be created because a drop is pending on the object).

Figure 13.3 illustrates how the previous example can be rewritten using dynamic SQL to avoid object dependency errors on LUW.

Now with Figure 13.3, the same duplicate object error will not be encountered because the CREATE TABLE statement is dynamic, and therefore dependency checking for the tables is deferred until runtime. On Line (1), a COMMIT is needed on zSeries for the same reasons that were explained earlier for Figure 13.2.

Using a Single Insert to Replace Multiple Insert Statements

On LUW and iSeries, you can use a single INSERT statement to insert multiple rows at the same time. This pays best performance dividends when used inside some kind of repetitious code, such as a loop. Simply group each row's values in a set of parenthesis and separate the rows by a comma, as shown in the following example:

 INSERT INTO employee (empono, firstnme, lastname, midinit, edlevel)  VALUES (empnum1, empfname1, emplname1, empinit1, empedulvl1),         (empnum2, empfname2, emplname1, empinit1, empedulvl1),         (empnum3, empfname3, emplname1, empinit1, empedulvl1) 

Value Assignment

On LUW, SET statements allow for variables to be assigned in parallel (the database manager will take care of this). Using a SELECT ... INTO or VALUES ... INTO statement will actually cause the values to be assigned in a serial fashion, which is not as efficient as SET.

 VALUES value1, value2, value3 INTO v_1, v_2, v_3; 

should be rewritten as

 SET v_1, v_2, v_3 = value1, value2, value3; 

Deterministic Versus Not Deterministic

Like UDFs, SQL procedures can be defined as DETERMINISTIC or NOT DETERMINISTIC. A DETERMINISTIC SQL procedure helps avoid the cost of repeated execution by giving the database manager the option of caching the result from the first execution.

An SQL procedure should be declared as DETERMINISTIC if for the same input parameter values and same database state (for example, tables referenced by the SQL procedure have not changed), the same result set and/or output parameters values will be returned. That is, if you call ProcA with a single input parameter of "100" and a result of "50" is returned in an output parameter, then if you call the same procedure again with the same input parameter of "100", a DETERMINISTIC procedure would return "50" as long as no database tables have been modified between procedure calls.

Case Statement

Procedural code is generally more expensive to execute than SQL. Sometimes you can use a CASE expression instead of ELSE or IF statements. This leads to more compact and more efficient code. For example, the following procedural logic shown in Figure 13.4 could be replaced with a single CASE expression.

Figure 13.4. IF statement logic.

 IF (NumEmployees < MaxEmployees) THEN    INSERT INTO smb VALUES (CompanyID, ContactID, NumEmployees); ELSE    INSERT INTO smb VALUES (CompanyID, ContactID, MaxEmployees); END IF; 

This could be better written as the code that appears in Figure 13.5.

Figure 13.5. CASE statement logic.

 INSERT INTO smb VALUES (CompanyID,                        ContactID, CASE    WHEN (NumEmployees < MaxEmployees)       THEN NumEmployees    ELSE MaxEmployees END); 

Functions

To improve performance, always declare functions as DETERMINISTIC, if possible. This will specify that the function always returns the same results for a given argument. The database manager may be able to avoid execution of the entire function if this is performed.

On LUW, whenever you are simply reading information from the database and returning some kind of value, consider using a function instead because they are more efficient than SQL procedures (the function is executed as part of the SQL, instead of as a separate package). However, if you are performing any data changes, you must use an SQL procedure.