Database interaction

The WHERE clause can, however, refer to message fields, correlation names declared by containing SELECTs, and to any other declared variables or constants within scope. If the whole expression cannot be evaluated by the database, the broker looks for top-level AND operators and examines each subexpression separately. It then attempts to give the database those subexpressions that it can evaluate, leaving the broker to evaluate the rest. This information is important for or two reasons:

1. Trivial changes to WHERE clause expressions can have large effects on performance. You can determine how much of the expression was given to the database by examining a user trace.
2. Some database functions exhibit subtle differences of behavior from the behavior of the broker.

The following statement can be used only when Price is 100 and Company is IBM. Therefore, when Price or Company change, another statement is needed, with another PREPARE statement:

PASSTHRU(’UPDATE SHAREPRICES AS SP SET Price = 100 WHERE SP.COMPANY = ‘IBM’’);

Recoding the sample allows Price and Company to change, but still uses same statement as follows:

PASSTHRU(’UPDATE SHAREPRICES AS SP SET Price = ? WHERE SP.COMPANY = ?’, rMessage.Price, rMessage.Company);

To see the level of dynamic statement cache activity with DB2®, use the following commands:

db2 connect to <database name>
db2 get snapshot for database on <database name>

To see the contents of the dynamic statement cache, use the following commands:

db2 connect to <database name>
db2 get snapshot for dynamic SQL on <database name>

Avoid nested IF statements. The following example uses nested IF statements, and the structure is not recommended:

IF mylogic = myvalue1 THEN
    -- Do something
ELSE
    IF mylogic = myvalue1 THEN
        -- Do something else
    ELSE
        IF mylogic = myvalue1 THEN
            -- Do something else
        ELSE
            -- Do something else
        END IF;
    END IF;
END IF;

Use ELSEIF or better still, use CASE with WHEN clauses to provide a quicker drop-out from the conditional logic. The following example uses ELSEIF conditional logic:

IF mylogic = myvalue1 THEN
    -- Do something
ELSEIF mylogic = myvalue1 THEN
    -- Do something else
ELSEIF mylogic = myvalue1 THEN
    -- Do something else
ELSE
    -- Do something else
END IF;

The following example uses a CASE statement:

CASE mylogic
WHEN myvalue1 THEN
    -- Do something
WHEN myvalue1 THEN
    -- Do something else
WHEN myvalue1 THEN
    -- Do something else
ELSE
    -- Do something else
END CASE;