The maximum number of statements (128) has been reached for the cu

[cmurialdo]

Hello,
I have been testing ado.net data provider of the HIS 2004 and I have the
following exception when the prepared statements count reaches 128:

Exception: Microsoft.HostIntegration.MsDb2Client.MsDb2Exception
Message: The maximum number of statements (128) has been reached for the
current connection. SQLSTATE: HY000, SQLCODE: -1500
Source: Microsoft.HostIntegration.MsDb2Client at
Microsoft.HostIntegration.MsDb2Client.DRDAAR.ExecDirect(IntPtr
requesterHandle, IntPtr statementHandle, String statement, Byte[]
parameterData, Int32[] parameterOutSize) at
Microsoft.HostIntegration.MsDb2Client.MsDb2Command.ExecuteNonQuery()

Is it a data provider limitation? How can I configure this maximum?


Thanks,
Regards.

[Aaron Grady [MSFT]]

cmurialdo-

I believe this is a hard coded limit in our provider that we only allow 128
open SQL statements against a single connection. I do not believe there is a
way to configure this.

I guess my first question would be: Is there a legitmate reason why you
would need more than 128 open statements against a single connection?
If you can explain what you need to accomplish maybe we can provide some
helpful guidance.
--
Aaron Grady
MCSE + Internet, MCDBA, MCSD, MCAD.NET
SNA Server/Host Integration Server
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Post by cmurialdo
Hello,
I have been testing ado.net data provider of the HIS 2004 and I have the
Exception: Microsoft.HostIntegration.MsDb2Client.MsDb2Exception
Message: The maximum number of statements (128) has been reached for the
current connection. SQLSTATE: HY000, SQLCODE: -1500
Source: Microsoft.HostIntegration.MsDb2Client at
Microsoft.HostIntegration.MsDb2Client.DRDAAR.ExecDirect(IntPtr
requesterHandle, IntPtr statementHandle, String statement, Byte[]
parameterData, Int32[] parameterOutSize) at
Microsoft.HostIntegration.MsDb2Client.MsDb2Command.ExecuteNonQuery()
Is it a data provider limitation? How can I configure this maximum?
Thanks,
Regards.

[cmurialdo]

Aaron,
The application we are using to test the HIS data provider is a batch
process of a banking system which has a big number of metadata. We have
tested it with jdbc access to the iseries server and the optimal performance
is reached when the maximum number of statements is about 500 against the
connection.
We want to test the same batch process with HIS data provider but with 128
statements would be sensible to get performance degradation.

Do you know if there are plans to remove this limitation?. Why is this limit
hard coded inside the data provider? I mean, Why can't it be configured and
controlled by the program which uses the data provider?

Thanks,
Regards.

[Aaron Grady [MSFT]]

I will talk with the developer and see what I can find out.

However, you may find that you get better performance with our providers by
turning on connection pooling and having less outstanding statements per
connection. You may want to try testing that and I'll post back any
additional information I can find within the next day or two.

HTH,
--
Aaron Grady
MCSE + Internet, MCDBA, MCSD, MCAD.NET
SNA Server/Host Integration Server
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Post by cmurialdo
Aaron,
The application we are using to test the HIS data provider is a batch
process of a banking system which has a big number of metadata. We have
tested it with jdbc access to the iseries server and the optimal performance
is reached when the maximum number of statements is about 500 against the
connection.
We want to test the same batch process with HIS data provider but with 128
statements would be sensible to get performance degradation.
Do you know if there are plans to remove this limitation?. Why is this limit
hard coded inside the data provider? I mean, Why can't it be configured and
controlled by the program which uses the data provider?
Thanks,
Regards.

[cmurialdo]

Aaron,
The whole banking system is using the connection pool except the batch
process.
Changing the batch process to use several connections implies redesigning
process logic because of the transactional operations. It wouldn’t be a
simple change.

Thanks,
Regards,
Claudia Murialdo.

[Aaron Grady [MSFT]]

I double checked with the developers and the current limit of 128 is
hardcoded and not configurable. It would be possible to ask that this be
changed, but to do so you need to open a case with Microsoft support and get
them to put in what we call a Design Change Request (DCR).

Without a change you will need to work around the limit of 128.

NOTE: This limit only applies to prepared statements, not statements which
contain no parameters, or to stored procedures.

HTH,
--
Aaron Grady
MCSE + Internet, MCDBA, MCSD, MCAD.NET
SNA Server/Host Integration Server
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Post by cmurialdo
Aaron,
The whole banking system is using the connection pool except the batch
process.
Changing the batch process to use several connections implies redesigning
process logic because of the transactional operations. It wouldn't be a
simple change.
Thanks,
Regards,
Claudia Murialdo.

[cmurialdo]

Thank Aaron.
I opened the case with the Design Change Request at Microsoft support.
Regards.
Claudia.