DB2 ODBC and CLI

ODBC, CLI, and Db2 ODBC

CLI is the ISO/X-Open call interface. ODBC is the Windows-era API built on CLI (same function names, extra driver manager). On z/OS you link the Db2 ODBC library and include sqlcli1.h (names vary by SMP/E FMIDs). You do not precompile. SQL is still dynamic on the server unless you use a separate static SQL path.

Initialization uses an ODBC INI file (common, subsystem, and data-source sections) for keywords such as AUTOCOMMIT, CONNECTTYPE, CURRENTSQLID, and TABLETYPE. Application attributes can override many of those keywords.

SQLAllocHandle

1
2
3
4
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
/* connect here */
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

A statement handle is tied to one connection. You cannot allocate STMT until the connection is open (SQLSTATE 08003). Default how many statements you may use depends on isolation; exceeding it returns HY014 on SQLPrepare or SQLExecDirect. Free with SQLFreeHandle in reverse order; SQLFreeStmt can unbind, close the cursor, or drop parameters without destroying the handle.

SQLConnect and SQLDisconnect

SQLConnect takes a data-source name, user ID, and password. SQLDriverConnect takes a connection string (DSN=...;UID=...;PWD=... or DATABASE=location). The HWND argument is unused on z/OS (pass NULL). SQL_DRIVER_NOPROMPT is the usual completion flag because there is no GUI.

Remote data sources go through DRDA to DDF—the same PORT/SECPORT and TCPALVER rules as JDBC. Local ODBC uses the subsystem attachment. SQLDisconnect ends the connection but leaves the DBC handle allocated so you can connect again.

SQLPrepare, SQLExecute, SQLExecDirect

• SQLPrepare — send statement text (dynamic prepare). Then bind parameters and SQLExecute one or more times.
• SQLExecute — run the prepared statement with current bound parameter values.
• SQLExecDirect — prepare and run in one call. Parameter markers are allowed if already bound. No open cursor may exist on that handle. For a query, ODBC generates a cursor name and opens it.

1
2
3
4
5
6
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                 SQL_CHAR, 3, 0, dept, 4, &deptLen);
SQLPrepare(hstmt,
  (SQLCHAR *)"SELECT EMPNO, LASTNAME FROM HR.EMPLOYEE WHERE WORKDEPT = ?",
  SQL_NTS);
SQLExecute(hstmt);

SQLBindParameter, SQLBindCol, SQLFetch, SQLGetData

SQLBindParameter ties a ? to an application variable: parameter number (1-based), input/output, C type, SQL type, precision, scale, buffer, and length/indicator. Use SQL_NULL_DATA in the indicator for NULL.

SQLBindCol ties a result column to a buffer. SQLFetch (or SQLFetchScroll) copies the next row into those buffers. Unbound columns are read with SQLGetData after fetch—useful for LOBs you do not want in a fixed buffer. SQL_NTS means a C nul-terminated string length on input APIs.

Positioned UPDATE/DELETE need a cursor name (SQLSetCursorName) and a second statement handle on the same connection, with the cursor positioned on a row.

Catalog and describe functions

These calls produce a result set on the statement handle; SQLBindCol + SQLFetch as usual. Schema and table arguments accept search patterns. INI keywords DBNAME, SCHEMALIST, and TABLETYPE can restrict what SQLTables returns; a non-null argument on the call overrides TABLETYPE. Underscore in a pattern is a single-character wild card unless you set the literal-underscore keyword.

ODBC transactions

SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0) then SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT) or SQL_ROLLBACK. Isolation: SQL_ATTR_TXN_ISOLATION with SQL_TXN_READ_UNCOMMITTED (UR), READ_COMMITTED (CS), REPEATABLE_READ (RS), SERIALIZABLE (RR). Global transactions follow the same “do not commit on the connection” rule as JDBC when a transaction manager owns the UOW.

ODBC diagnostics

On any SQL_ERROR or SQL_SUCCESS_WITH_INFO, call SQLGetDiagRec:

1
2
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate,
              &native, message, sizeof(message), &msgLen);

sqlstate is the five-character SQLSTATE (HY009 invalid use, 08S01 communications failure, 22001 truncation, 23505 unique constraint, and so on). native is the Db2 SQLCODE. RecNumber 1 is the first record; increment until SQL_NO_DATA. SQLGetDiagField reads individual fields (row number in a batch, cursor name). Always check the return code of every CLI call; ignoring SQL_SUCCESS_WITH_INFO hides truncation.

A minimal lifecycle

1. SQLAllocHandle ENV, set ODBC version if required.
2. SQLAllocHandle DBC, SQLConnect or SQLDriverConnect.
3. SQLAllocHandle STMT, bind, prepare/execute or exec direct.
4. SQLFetch until SQL_NO_DATA, SQLCloseCursor / SQLFreeStmt.
5. SQLEndTran if autocommit is off.
6. SQLDisconnect, SQLFreeHandle STMT, DBC, ENV.

Explain It Like I'm Five

ODBC is a walkie-talkie instead of a pre-printed letter (embedded SQL). First you pick up the radio (environment), then you open a channel to one friend (connection), then you ask one question at a time (statement). Binding is taping the answer into a labeled box on your desk so each FETCH drops the next toy in the same box. Catalog functions are asking “what toys are in the cupboard?” Diagnostics are the error code the radio squawks when nobody is listening.

Exercises

1. Sketch SQLAllocHandle calls for env, dbc, and stmt, then the matching SQLFreeHandle order.
2. Write SQLBindParameter for a CHAR(3) department code and SQLPrepare a SELECT that uses one ?.
3. When would you SQLGetData instead of SQLBindCol?
4. Call SQLTables for schema HR and describe how you fetch TABLE_NAME.
5. On SQL_ERROR, loop SQLGetDiagRec and print SQLSTATE plus native SQLCODE.