Static SQL (DB2)

Topics:

In early releases of the adapter, FOCUS access to the RDBMS was entirely through dynamic SQL requests. While dynamic SQL is ideal for applications like ad hoc reporting, in which you do not know in advance what SQL statements you will execute at run time, it is less desirable for applications that do not require such flexibility.

In contrast, static SQL is ideal when you know beforehand all the SQL statements a procedure may execute. You register the procedure itself, including the SQL statements, with the RDBMS through a process known as binding. The resulting database object, called a DB2 application plan, is stored in the database and retrieved whenever you run the procedure. Introduction to Adapters for Relational Data Sources, introduces bind concepts.

Static SQL provides an additional degree of security not available with dynamic SQL. Authorization is granted to the static plan, not the underlying database objects, restricting the SQL statements a user can execute with these objects.

Another important aspect of the static SQL process is that the RDBMS optimizes each SQL statement in the program and chooses an access path for it. A data access path consists of low-level data access requests that the RDBMS formulates and stores in internal format. When you execute the procedure, the RDBMS retrieves these requests and executes them immediately. The net effect is that SQL statements in the static request are not reinterpreted at run time. The access path for each statement is pre-selected and reused each time you run the procedure.


Information Builders