Differences between DB2 ODBC and embedded SQL

Even though key differences exist between DB2® ODBC and embedded SQL, DB2 ODBC can execute any SQL statements that can be prepared dynamically in embedded SQL.

An application that uses an embedded SQL interface requires a precompiler to convert the SQL statements into code, which is then compiled, bound to the data source, and executed. In contrast, a DB2 ODBC application does not have to be precompiled or bound, but instead uses a standard set of functions to execute SQL statements and related services at run time.

This difference is important because, traditionally, precompilers have been specific to each database product, which effectively ties your applications to that product. DB2 ODBC enables you to write portable applications that are independent of any particular database product. Because you do not precompile ODBC applications, the DB2 ODBC driver imposes a fixed set of precompiler options on statements that you execute through ODBC. These options are intended for general ODBC applications.

This independence means DB2 ODBC applications do not have to be recompiled or rebound to access different DB2 or DRDA data sources, but rather just connect to the appropriate data source at run time.

DB2 ODBC and embedded SQL also differ in the following ways:
  • DB2 ODBC does not require the explicit declaration of cursors. They are generated by DB2 ODBC as needed. The application can then use the generated cursor in the normal cursor fetch model for multiple-row SELECT statements and positioned UPDATE and DELETE statements.
  • The OPEN statement is not used in DB2 ODBC. Instead, the execution of a SELECT automatically causes a cursor to be opened.
  • Unlike embedded SQL, DB2 ODBC allows the use of parameter markers on the equivalent of the EXECUTE IMMEDIATE statement (the SQLExecDirect() function).
  • A COMMIT or ROLLBACK in DB2 ODBC is issued using the SQLEndTran() function call rather than by passing it as an SQL statement.
  • DB2 ODBC manages statement related information on behalf of the application, and provides a statement handle to refer to it as an abstract object. This handle eliminates the need for the application to use product specific data structures.
  • Similar to the statement handle, the environment handle and connection handle provide a means to refer to all global variables and connection specific information.
  • DB2 ODBC uses the SQLSTATE values defined by the X/Open SQL CAE specification. Although the format and most of the values are consistent with values used by the IBM® relational database products, differences do exist (some ODBC SQLSTATEs and X/Open defined SQLSTATEs also differ).

Despite these differences, embedded SQL and DB2 ODBC share the following concept in common: DB2 ODBC can execute any SQL statement that can be prepared dynamically in embedded SQL.

Table 1 lists each DB2 for z/OS® SQL statement and indicates whether you can execute that statement with DB2 ODBC.

Each database management system might have additional statements that can be dynamically prepared, in which case DB2 ODBC passes them to the database management system.

Exception: COMMIT and ROLLBACK can be dynamically prepared by some database management systems but are not passed. The SQLEndTran() function should be used instead to specify either COMMIT or ROLLBACK.

Table 1. ODBC support for SQL statements
SQL statement Dynamic1 DB2 ODBC2
ALTER TABLE Yes Yes
ALTER DATABASE Yes Yes
ALTER INDEX Yes Yes
ALTER STOGROUP Yes Yes
ALTER TABLESPACE Yes Yes
BEGIN DECLARE SECTION3 No No
CALL No Yes4
CLOSE No SQLFreeHandle()
COMMENT ON Yes Yes
COMMIT Yes SQLEndTran()
CONNECT (type 1) No SQLConnect(), SQLDriverConnect()
CONNECT (type 2) No SQLConnect(), SQLDriverConnect()
CREATE { ALIAS, DATABASE, INDEX, STOGROUP, SYNONYM, TABLE, TABLESPACE, VIEW, DISTINCT TYPE } Yes Yes
DECLARE CURSOR3 No SQLAllocHandle()
DECLARE STATEMENT No No
DECLARE TABLE No No
DECLARE VARIABLE No No
DELETE Yes Yes
DESCRIBE No SQLDescribeCol(), SQLColAttribute()
DROP Yes Yes
END DECLARE SECTION3 No No
EXECUTE No SQLExecute()
EXECUTE IMMEDIATE No SQLExecDirect()
EXPLAIN Yes Yes
FETCH No SQLFetch(), SQLExtendedFetch()
FREE LOCATOR4 No Yes
GET DIAGNOSTICS No No
GRANT Yes Yes
HOLD LOCATOR4 No Yes
INCLUDE3 No No
INSERT Yes Yes
LABEL ON Yes Yes
LOCK TABLE Yes Yes
MERGE5 Yes Yes
OPEN No SQLExecute(), SQLExecDirect()
PREPARE No SQLPrepare()
RELEASE No No
RENAME Yes Yes
REVOKE Yes Yes
ROLLBACK Yes SQLEndTran()
select-statement Yes Yes
SELECT INTO No No
SET CONNECTION No SQLSetConnection()
SET host_variable No No
SET CURRENT APPLICATION ENCODING SCHEME No No
SET CURRENT DEGREE Yes Yes
SET CURRENT PACKAGESET No No
SET CURRENT PATH Yes Yes
SET CURRENT SCHEMA Yes Yes
SET CURRENT SQLID Yes Yes
UPDATE Yes Yes
WHENEVER3 No No
Note:
  1. All statements in this list can be coded as static SQL, but only those marked Yes can be coded as dynamic SQL.
  2. An X indicates that this statement can be executed using either SQLExecDirect(), or SQLPrepare() and SQLExecute(). Equivalent DB2 ODBC functions are listed.
  3. This statement is not executable.
  4. Although this statement is not dynamic, DB2 ODBC allows the statement to be specified when calling either SQLExecDirect() or SQLPrepare() and SQLExecute().
  5. Start of changeThe FOR n ROWS clause cannot be specified in a MERGE statement in a DB2 ODBC program. To specify the number of rows to be merged, use SQLSetStmtAttr() with the SQL_ATTR_PARAMSET_SIZE statement attribute.End of change