The maximum number of statements has been reached for the current connection. SQLSTATE: HY000, SQLCODE:-1500

I'm working on a desktop application and using OLEDB providers to connect to DB2 database. While trying to execute update statement for 100 times with different values, I'm getting exception "The maximum number of statements has been reached for the current connection. SQLSTATE: HY000, SQLCODE:-1500."

I'm able to execute update statement for 15 times successfully. How to increase the limit of statements for one connection? I have added 'MARS Connection=True;' in connection string. But it didn't work :(

    OleDbConnection conn = new OleDbConnection("ConnectionString");
        conn.Open();
        DbTransaction  trans = conn.BeginTransaction();
        ConnectionInfo connInfo = new ConnectionInfo(trans);

    DBCommandWrapper cmdUpdate = cmdFactory.CreateDBCommandWrapper(connInfo.Connection, "UpdateAccount", connInfo.Transaction);
        cmdUpdate.SetParameterValue("@CustomerNumber", intToCustomerNumber);
        cmdUpdate.SetParameterValue("@BankNumber", account.BankNumber);
        cmdUpdate.SetParameterValue("@AccountNumber", Convert.ToDouble(account.AccountNumber.Trim()));
        RowsAffected = cmdUpdate.CommandObject.ExecuteNonQuery();

Update query is

 <command name="UpdateAccount" commandType="Text">
  <commandText>
    UPDATE #.ACCOUNT1
    SET CustNum = ?
    WHERE BankNum = ?
    AND AcctNum = ?
  </commandText>
  <parameters>
    <parameter name="@CustomerNumber" dbType="Decimal" scale="0" precision="0" size="0" direction="Input" value="" isNullable="false" sourceColumn="" sourceVersion="Default" />
    <parameter name="@BankNumber" dbType="Decimal" scale="0" precision="0" size="0" direction="Input" value="" isNullable="false" sourceColumn="" sourceVersion="Default" />
    <parameter name="@AccountNumber" dbType="Double" scale="0" precision="0" size="0" direction="Input" value="" isNullable="false" sourceColumn="" sourceVersion="Default" />
  </parameters>
</command>

Solution

As stated in Troubleshooting the OLE DB Provider for DB2, SQLCODE:-1500 indicates DB2OLEDB_MAX_SECTIONS, which explains this message:

The maximum number of statements (128) has been reached for the current connection.

By default, OLE DB provider for DB2 uses 128 package sections which allows client to execute 128 concurrent prepared statements for each initiated connection. If outstanding concurrent statements exceeds number of default predefined sections, it not able to execute new statement.

The section limit seems to be hardcoded from OLE DB provider settings for most issues, but you can try to find a REG_DWORD key named NumberOfPackages inside registry (see this reference). If the key exists, modify it with power of 2 numbers above 128 (e.g. 256, 512 and so on) and recreate packages afterwards, otherwise you need to do workaround with lesser amount of prepared statements in current connection to avoid that exception.

Update 1

SQLSTATE: 07002, SQLCODE: -804 indicates this problem based on DB2 documentation:

An error was found in the application program input parameters for the SQL statement.

Reason: Prepare of EXECUTE IMMEDIATE.

In other words, the message tells that the call parameter list or SQLDA is invalid, which related to EXECUTE IMMEDIATE command (usually used for dynamic SQL string).

DB2 update procedure with dynamic query string should be looks like this example:

CREATE OR REPLACE PROCEDURE UpdateDDATransferAccount(IN [parameter_name] [data_type](size) ...)
-- query settings

BEGIN
DECLARE statement = VARCHAR(2000) -- size of query string
SET statement = 'UPDATE [schema].' || '[table_name]' || ' SET [field_name] = ' || '[parameter_name]' || ' ... ' WHERE ' || '[field_name]' || ' = ' || '[parameter_name_or_value]'; 
EXECUTE IMMEDIATE statement;
END;