Rules for a Db2 ODBC stored procedure

Db2 ODBC stored procedures are like other Db2 ODBC applications. However, several differences exist.

Although stored procedures that are written in embedded SQL provide more advantages than stored procedures that are written in ODBC, you might want components of Db2 ODBC applications to run on servers. You can write stored procedures in Db2 ODBC to minimize the required changes to the code and logic of those components.

You write ODBC stored procedures as ordinary ODBC applications, with the following exceptions:
  • You must turn off AUTOCOMMIT. Set the SQL_ATTR_AUTOCOMMIT attribute to SQL_AUTOCOMMIT_OFF with SQLSetConnectAttr(). You can also specify AUTOCOMMIT=0 in the Db2 ODBC initialization file to disable AUTOCOMMIT.
  • You must make a null database connection with SQLConnect(). A stored procedure runs under the same connection and transaction as the client application. A null SQLConnect() call associates a connection handle in the stored procedure with the underlying connection of the client application. To make a null SQLConnect() call, set the szDSN, szUID, and szAuthStr argument pointers to NULL, and set their respective length arguments to 0.
  • If your stored procedure contains any LOB data types or distinct types in its parameter list, specify MVSATTACHTYPE=RRSAF in the Db2 ODBC initialization file. Db2 for z/OS® requires that stored procedures containing any LOBs or distinct types must run in a WLM-established stored procedure address space.

When you define a Db2 ODBC stored procedure to Db2, specify the COMMIT ON RETURN NO clause in the CREATE PROCEDURE SQL statement. For stored procedures that are written in Db2 ODBC, the COMMIT ON RETURN clause has no effect on Db2 ODBC rules. However, COMMIT ON RETURN NO overrides the manual-commit mode that is set in the client application.