January 28, 2008

DB2 9 System Parameter Changes for Application Development

Last week I discussed a quick way to create a list of system parameter changes by comparing the source macros found in SDSNMACS library. Some of these parameters impact your application developers while others impact your database programmers and how DB2 manages memory and resources.

With so many parameters to review, I figure we should break things up. So I'll start by listing the macros and parameters that impact developers. Next week we'll run down the lengthy list of macros and parameters that impact the DB2 subsystem and cover more of the systems-related CPU and memory management capabilities.

What Developers Should Look For
The parameter settings in the system zparms provide a powerful and flexible way to customize DB2 for your needs. Sometimes IBM takes new DB2 features and extends the capabilities to the previous release by issuing either an Authorized Problem Analysis Report (APAR) or individual customer fixes that are activated and deactivated by way of a zparm. In my list of zparm changes, I noted the APAR number.

As a DB2 developer you should review the DSNHDECP and DSN6FAC macros. DSNHDECP controls the defaults used by your application -- all the parameters listed for DSNHDECP are included in the DB2 installation guide. These parameters include things like data-encoding CCSID, decimal point representation of "." and "," and much more.

If your application is distributed, you'll want to know what parameter settings are in DSN6FAC, which is used by DDF the distributed data facility.

The DSNHDECM macro contains the initialization parameters for application programming defaults stored in DSNHDECP.

New Parameter

Specify the system default for decimal floating point rounding mode.

The default ROUND_HALF_EVEN will round to nearest number. If the discarded digits are greater than 0.5, the value of 1 appears in the next left position, because DB2 increments the result coefficient by 1. If the discarded digits are less than 0.5, DB2 ignores them. If the discarded digits are 0.5 and the rightmost digit is even, DB2 doesn't alter the result coefficient. If the discarded digits are 0.5 and the rightmost digit is odd, DB2 increments the result coefficient by 1 to make it an even digit.

See the installation guide for a full description of all the parameter options.

The DSN6FAC macro contains the initialization parameters used by the distributed data facility (DDF). These are PRGSTRIN and SQLINTRP.

PRGSTRIN is a new parameter made available in IBM DB2 Version 9 with APAR PK46079. Valid settings are ENABLE (the default) or DISABLE.

DB2 V9 for z/OS introduced support for progressive streaming when DB2 on z/OS is acting as a server. Progressive streaming provides optimizations in retrieving LOB or XML data in a distributed environment. It can also improve resource utilization on the DB2 for z/OS server in terms of freeing storage associated with LOB or XML data in a cursor-based scope (as opposed to a transaction-based scope). Certain applications may require modification to work with progressive streaming semantics. Progressive streaming can be configured on the client side (e.g., IBM DB2 Driver for JDBC and SQLJ or DB2 CLI driver), but to minimize client configuration, some customers may want a server-side mechanism for indicating to the driver the desired default for progressive streaming if it differs from the driver default.

SQLINTRP is a new parameter made available in DB2 V9 with APAR PK59385 (status open). It's available in IBM DB2 Version 8 with APAR PK41661. The valid settings are ENABLE (the default) or DISABLE.

DB2 z/OS V8 introduced DDF support that allows remote client systems to interrupt application SQL based on client system timeout values. Prior to DB2 z/OS V8, the only interruption mechanism available to a remote client system was to terminate the connection to the DB2 z/OS server. But this causes the entire DB2 z/OS thread to terminate, and thus abort all changes after the prior commit/rollback, making this mechanism sometimes too drastic for remote client systems to exploit.

As a result, SQL interrupt processing was introduced in DB2 z/OS V8 to provide a mechanism to interrupt only the currently executing SQL, resulting in a negative SQLCODE. Some customers may have many remote client systems and many applications where the logic doesn't tolerate the new error symptoms that occur as a result of SQL interrupt support. This wasn't a concern with DB2 z/OS V7 server subsystems, because DB2 z/OS V7 didn't support the SQL Interrupt mechanics. But when migrating DB2 z/OS V7 subsystems to V8, customers may experience application failures as a result of the missing application error-handling logic. This in turn may prevent them from migrating to V8 until all of their applications on all of their client systems can be evaluated and modified to tolerate the new execution-time symptoms. For this reason, assistance is needed for z/OS customers migrating from DB2 V7 to V8.

An Incomplete List
Because system parameters change as maintenance is put on DB2, it's likely that my list of system parameter changes between DB2 for z/OS V8 and V9 is incomplete. But my goal in compiling this list is to give you a basic understanding of what's changed. Hopefully seeing this changes will help you plan your company's upgrade to DB2 V9. You may even find that some of these changes help you build a business case to upgrade sooner rather than later.

As usual, I really appreciate your feedback. If you find this type of information helpful, please send me a note or post a comment. If there's a topic you'd like me to cover, then post that idea also.