Preparation and execution of SQL statements

After you allocate a statement handle, you can specify and execute SQL statements.

You can execute SQL statements through the following steps:
  • Prepare then execute:
    1. Call SQLPrepare() with an SQL statement as an argument.
    2. Call SQLBindParameter() if the SQL statement contains parameter markers.
    3. Call SQLExecute().
  • Execute direct:
    1. Call SQLBindParameter() if the SQL statement contains parameter markers.
    2. Call SQLExecDirect() with an SQL statement as an argument.
The first method, prepare then execute, splits the preparation of the statement from the execution. Use this method when either of the following conditions is true:
  • You execute a statement repeatedly (usually with different parameter values). This method allows you to prepare the same statement only once. Subsequent executions of that statement make use of the access plan the prepare generated.
  • You require information about the columns in the result set, before it executes the statement.
The second method combines the prepare step and the execute step into one. Use this method when both of the following conditions are true:
  • You execute the statement only once. This method allows you to call one function instead of two to execute an SQL statement.
  • You do not require information about the columns in the result set before you actually execute the statement.
Db2 for z/OS® and Db2 for Linux®, UNIX, and Windows provide dynamic statement caching at the database server. In Db2 ODBC terms, dynamic statement caching means that for a given statement handle, once the database prepares a statement, it does not need to prepare it again (even after commits or rollbacks), as long as you do not free the statement handle. Applications that repeatedly execute the same SQL statement across multiple transactions, can save a significant amount of processing time and network traffic by:
  1. Associating each such statement with its own statement handle, and
  2. Preparing these statements once at the beginning of the application, then
  3. Executing the statements as many times as is needed throughout the application.