For all ODBC applications which connect to CONNX DB2 module data sources, a dynamic SQL statement executes as static SQL provided that the CDD opened by the data source defines one or more static SQL packages, each with one or more static SQL statements, and that there is an equivalent static SQL statement for the dynamic SQL statement in the data source CDD. This process is completely transparent to the ODBC application, end-user, or developer. Consider, for instance the following statement:

The statement looks like a dynamic SQL statement, but is executed as static SQL if the CONNX CDD contains the appropriate static package and statement. If there is no match, the SQL statement executes as dynamic SQL.

With CONNX, dynamic SQL can be transformed and executed as static SQL, which provides for enhanced security and performance. A potential scenario for this feature is the development of an ODBC-based application using dynamic SQL statements. Once the dynamic SQL statements are finalized, they can be built into one or more static SQL packages and the results stored in a CDD. The completed ODBC application can then point to the CDD, and all dynamic SQL statements will execute as static SQL, provided that there is an appropriate match in the CONNX CDD.

The net result is that, although there is no support for static SQL in the ODBC specification, CONNX automatically transforms dynamic SQL to static SQL, without requiring changes to the ODBC application code.