KevinHarrison 1000005N52 Visits (1388)
GZJ 1100006WMT Visits (1347)
Migrating to DB2 10? Running .NET applications that call stored procedures? If so, then this item should be of interest to you.
Full information is available in APAR PM54662, but the following (edited) extract indicates a release incompatibility:
"DB2 10 for z/OS implemented enhancements to the way in which stored procedure parameters are returned to the calling application at the remote requester.
- Before Version 10, DB2 returned stored procedure parameters according to the type of the corresponding arguments in the stored procedure CALL.
- Beginning in Version 10, DB2 returns stored procedure parameters according to the SQL type of the parameter as defined in the stored procedure declaration. This change improves performance for the call by eliminating server conversions of the parameter data; implements the same behavior as used by DB2 for returning other outputs, such as
cursor or singleton select data; and is the same behavior as is implemented on other servers in the DB2 Family.
However, .NET has strong-typing semantics and this change in DB2 server behavior may cause some .NET applications to fail with the CLI error message CLI0102E, if they do not specify argument types that are compatible with the declared type of the corresponding parameter in the stored procedure. The required corrective action is to change the .NET application so that it satisfies .NET semantics for strong-typing and, thus, conforms to good .NET programming practices."
The solution? Convince your .NET application developers that they need to change their application programs, as DB2 10 closes a loop-hole that previously allowed bad .NET programming practices and the specification of incompatible data types. Good luck ...
agburke 060001QPDN Visits (1268)
Often there is confusion regarding WLM address spaces and stored procedure performance.
As long as the stored procedures are meeting their WLM goal z/OS is reluctant to spawn a new address space even if all of the TCBs in that address space are currently being used. WLM will look at the available resources, i.e. TCBs and address spaces every 2 seconds, but only react every 10 seconds. In the accounting report for a stored procedure you can look in the STORED PROC row under SUSPEND TIME to see how much time was spent waiting for a TCB.
To impact this wait time you might tighten up the WLM goals for the stored procedure to pressure WLM into action, or implement the following PTF to keep a certain number around all the time.