Reusing Statements

One of the key performance features of Application Engine is the ability to reuse SQL statements by dedicating a persistent cursor to that statement.

Unless you select the ReUse property for a SQL action, %BIND fields are substituted with literal values in the SQL statement. The database has to recompile the statement every time it runs.

However, selecting ReUse converts any %BIND fields into real bind variables (:1, :2, and so on), which enables Application Engine to compile the statement once, dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can improve performance significantly.

In addition, some databases have SQL statement caching. Every time they receive SQL, they compare it against their cache of previously carried out statements to see if they have seen it before. If so, they can reuse the old query plan, but only if the SQL text matches exactly. This circumstance is unlikely with literals instead of bind variables.

When using the ReUse property, note that:

  • The ReUse property is valid only for SQL actions.

  • Use the ReUse property only if you do not use bind variables for column names.

  • Use the ReUse property only if you have no %BIND variables in the Select list.

  • If the SQL is dynamic, as in you are using %BIND to resolve to a value other than a standard bind value, and the contents of the bind change each time the statement is executed, then you cannot enable the ReUse property.

    In this situation, the SQL is different (at least from the database perspective) each time and, therefore, cannot be reused.

  • If you use the NOQUOTES modifier inside %BIND, a STATIC is implied.

    For dynamic SQL substitution, the %BIND has a Char field and NOQUOTES to insert SQL rather than a literal value. If you enable the ReUse property, the value of the Char field is substituted inline instead of using a bind marker (as in :1, :2, and so on). The next time that the action is carried out, the SQL that it runs is the same as the previous one, even if the value of a static bind has changed.

  • To prepare a reused statement from the beginning, because one of the static binds has changed and the SQL has to reflect that change, use %ClearCursor.

  • When making calls to an external section, program, or library, the reusable cursors are retained upon exiting the program. However, if the calling program attempts to call another external section thereafter, the reusable cursors are discarded.

If you are running DB2 on OS/390 or AS/400, use the ReUse property only when you are not using %BINDS as operands of the same operator, as shown in the following example: 

UPDATE PS_PO_WRK1
SET TAX = %BIND(STATE) + %BIND(FED)

This example causes error -417. You can modify the SQL so that you can use the ReUse property successfully. Suppose your program contains the following SQL: 

UPDATE PS_PO_WRK1
   SET TAX = 0
   WHERE %BIND(TAX_EXEMPT) = %BIND(TAX_STATUS)

If you modify it to resemble the following SQL, the ReUse property works:

UPDATE PS_PO_WRK1
   SET TAX = 0
   WHERE %BIND(TAX_EXEMPT, STATIC) = %BIND(TAX_STATUS)