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 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.
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:
|