Capabilities and limitations of dynamic commands

This section describes dynamic command capabilities that the DB2 access service supports, as well as the limitations that apply.

• You can only prepare use procedure, transfer, and most non-select SQL statements, such as insert, update, and delete. For a current list, see the IBM DB2 SQL Reference manual.

• The DB2 access service does not support the from clause in an update statement.

• The DB2 access service supports a maximum of 50 simultaneously-prepared dynamic statements per client connection.

• When the client application releases all resources associated with the prepared statement, it must also free its connection at this point so that CT-Library synchronizes with the actual state of both DB2 and the DB2 access service.

• The DB2 access service can support both short and long transactions in either allocate on request or allocate on connect mode.

• The first time an application issues a prepare (or declares a cursor), the DB2 access service forces TransactionMode for that connection to long. The Allocate property remains unchanged.

• The DB2 access service supports the execute immediate capability of a dynamic command, but the statement must not return data. (This is an Open Server restriction, not a DB2 access service limitation.) The DB2 access service allows a use procedure statement (as long as the stored procedure does not return data) but not a transfer statement.

• Parameter markers for dynamic commands are supported as follows:

  • In sybase mode, parameter markers within the text can use Sybase conventions, such as @paramname. When necessary, the DB2 access service transforms parameters to the native syntax.

  • In passthrough mode, parameter marker syntax is database-specific, so you must use question marks (?) as parameter markers.

• The DB2 access service also supports long character and binary parameters. Client applications must describe such properties as datatype CS_LONGCHAR or CS_LONGBINARY.

  Some restrictions:

  • DB2 limits the SQL statement for any request (such as a prepare) to a total of 32,765 bytes. The maximum size of both data and null indicators in a row is 32,767 bytes.

  • Open Server Tabular Data Stream™ (TDS) allows a maximum TextSize property of 32,000 bytes.

For general information about dynamic SQL and dynamic commands, see the Sybase Open Client Embedded SQL Programmers Guide and the IBM DB2 SQL Reference manual.