Considerations for choosing between SQL and DB2 ODBC

Before you determine which interface to use, consider key factors like performance, encapsulation, and security.

DB2 ODBC is ideally suited for query-based applications that require portability. Use the following information to help you decide which interface meets your needs.

ODBC is a dynamic SQL interface

Only embedded SQL applications can use static SQL. Both static and dynamic SQL have advantages. Consider these factors:

Performance

Dynamic SQL is prepared at run time. Static SQL is prepared at bind time. The preparation step for dynamic SQL requires more processing and might incur additional network traffic.

However, static SQL does not always perform better than dynamic SQL. Dynamic SQL can make use of changes to the data source, such as new indexes, and can use current catalog statistics to choose the optimal access plan.

Encapsulation and security

In static SQL, authorization to objects is associated with a package and validated at package bind time. Database administrators can grant execute authority on a particular package to a set of users rather than grant explicit access to each database object.

In dynamic SQL, authorization is validated at run time on a per statement basis; therefore, users must be granted explicit access to each database object.

ODBC applications can call a stored procedures that use static SQL

An application programmer can create a stored procedure that contains static SQL. The stored procedure is called from within a DB2 ODBC application and executed on the server. After the stored procedure is created, any DB2 ODBC or ODBC application can call it.

An ODBC application can mix static and dynamic SQL:

You can write a mixed application that uses both DB2 ODBC and embedded SQL. In this scenario, DB2 ODBC provides the base application, and you write key modules using static SQL for performance or security. Choose this option only if stored procedures do not meet your applications requirements.

DB2 ODBC does not support embedded SQL statements in a multiple context environment.