Differences between DB2 for i CLI and embedded SQL

DB2® for i CLI and embedded SQL differ in many ways.

An application that uses an embedded SQL interface requires a precompiler to convert the SQL statements into code. Code is compiled, bound to the database, and processed. In contrast, a DB2 for i CLI application does not require precompilation or binding, but instead uses a standard set of functions to run SQL statements and related services at run time.

This difference is important because, traditionally, precompilers have been specific to a database product, which effectively ties your applications to that product. DB2 for i CLI enables you to write portable applications that are independent of any particular database product. This independence means that a DB2 for i CLI application does not need to be recompiled or rebound to access-different database products. An application selects the appropriate database products at run time.

DB2 for i CLI and embedded SQL also differ in the following ways:

DB2 for i CLI does not require the explicit declaration of cursors. DB2 for i CLI generates them 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 necessary in DB2 for i CLI. Instead, the processing of a SELECT automatically causes a cursor to be opened.
Unlike embedded SQL, DB2 for i CLI allows the use of parameter markers on the equivalent of the EXECUTE IMMEDIATE statement (the SQLExecDirect() function).
A COMMIT or ROLLBACK in DB2 for i CLI is issued through the SQLTransact() or SQLEndTran() function call rather than by passing it as an SQL statement.
For some statements, a corresponding connection attribute is provided as a different means of accomplishing the same function as running the statement would. For example, CLI provides a connection attribute that can be used to free locators allocated in the CLI application. This connection attribute is more convenient to use that the statement because it allows for an array of locators to be passed on the SQLSetConnectAttr() API call.
DB2 for i CLI manages statement-related information on behalf of the application, and provides a statement handle to refer to it as an abstract object. This handle avoids 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 for i CLI uses the SQLSTATE values defined by the X/Open SQL CAE specification. Although the format and many of the values are consistent with values that are used by the IBM® relational database products, there are differences.
CLI uses the SQLSTATE values defined by the X/Open SQL CAE specification. Although the format and many of the values are consistent with values that are used by the IBM relational database products, there are differences.

Despite these differences, there is an important common concept between embedded SQL and DB2 for i CLI:

DB2 for i CLI can process any SQL statement that can be prepared dynamically in embedded SQL. This is guaranteed because DB2 for i CLI does not actually process the SQL statement itself, but passes it to the Database Management System (DBMS) for dynamic processing.

Start of change Table 1 lists each SQL statement, and whether it can be processed using DB2 for i CLI. End of change

Table 1. SQL statements
SQL statement	Dyn ¹	CLI ³
ALLOCATE CURSOR
ALLOCATE DESCRIPTOR
ASSOCIATE LOCATORS
ALTER PROCEDURE	X
ALTER SEQUENCE	X
ALTER TABLE	X	X
BEGIN DECLARE SECTION ²
CALL	X	X
CLOSE		`SQLFreeStmt()`
COMMENT ON	X	X
COMMIT	X	`SQLTransact()`, `SQLEndTran()`
CONNECT (Type 1)		`SQLConnect()`
CONNECT (Type 2)		`SQLConnect()`
CREATE ALIAS	X
CREATE FUNCTION	X
CREATE INDEX	X	X
CREATE PROCEDURE	X
CREATE SCHEMA	X
CREATE SEQUENCE	X
CREATE TABLE	X	X
CREATE TRIGGER	X
CREATE TYPE	X
CREATE VARIABLE	X	X
CREATE VIEW	X	X
DEALLOCATE DESCRIPTOR
DECLARE CURSOR ^b		`SQLAllocStmt()`
DECLARE GLOBAL TEMPORARY TABLE	X
DELETE	X	X
DESCRIBE		`SQLDescribeCol()`, `SQLColAttribute()`
DESCRIBE CURSOR
DESCRIBE PROCEDURE
DISCONNECT		`SQLDisconnect()`
DROP	X	X
END DECLARE SECTION ^b
EXECUTE		`SQLExecute()`
EXECUTE IMMEDIATE		`SQLExecDirect()`
FETCH		`SQLFetch()`
FREE LOCATOR	X	`SQLSetConnectAttr()`
GET DESCRIPTOR
GET DIAGNOSTICS
GRANT	X	X
HOLD LOCATOR	X
INCLUDE ^b
INSERT	X	X
LABEL	X
LOCK TABLE	X	X
MERGE	X	X
OPEN		`SQLExecute()`, `SQLExecDirect()`
PREPARE		`SQLPrepare()`
REFRESH TABLE	X
RELEASE		`SQLDisconnect()`
RELEASE SAVEPOINT	X
RENAME	X
REVOKE	X	X
ROLLBACK	X	`SQLTransact()`, `SQLEndTran()`
SAVEPOINT	X
SELECT	X	X
SET CONNECTION
SET CURRENT DEBUG MODE	X
SET CURRENT DEGREE	X
SET CURRENT IMPLICIT XMLPARSE OPTION	X	`SQLSetConnectAttr()`
SET DESCRIPTOR
SET ENCRYPTION PASSWORD	X
SET PATH	X
SET SCHEMA	X
SET SESSION AUTHORIZATION	X
SET RESULT SETS
SET TRANSACTION	X
SIGNAL
UPDATE	X	X
VALUES INTO	X
WHENEVER ²
Notes: ¹ Dyn stands for dynamic. All statements in this list can be coded as static SQL, but only those marked with X can be coded as dynamic SQL. ² This is a non-executable statement. ³ An X indicates that this statement can be processed using either `SQLExecDirect()` or `SQLPrepare()` and `SQLExecute()`. If there is an equivalent DB2 for i CLI function, the function name is listed.

Each DBMS might have additional statements that can be dynamically prepared, in which case DB2 for i CLI passes them to the DBMS. There is one exception, COMMIT and ROLLBACK can be dynamically prepared by some DBMSs but are not passed. Instead, the SQLTransact() or SQLEndTran() should be used to specify either COMMIT or ROLLBACK.