DESCSTAT, zparm v. bind parameter, interested parties

Linda Hagedorn

DESCSTAT, zparm v. bind parameter, interested parties
I'm pursuing DESCSTAT as a bind parameter, enabling supporting DESCSTAT=NO and DESCSTAT=YES on the same data sharing group or subsystem.

I'm looking for the original five people who submitted DCR/MR (Design Change Request/Marketing Requirement), customers who added their names as interested parties, and anyone else who has a vested interest in having this change implemented. Please email me. I am going to the lab in three weeks to discuss.

I have applications on the same data sharing group requiring both. These came to light only when DESCSTAT was turned to YES and applications went down; the applications depended on NO. Unfortunately, all future development requires YES. To further complicate the matter, third party products require the 15 DB2 'Metadata' packages (DSNASPCC.*) at DESCSTAT=YES.

Currently to keep everything running, a spare member is brought up with DESCSTAT=YES, the 15 DSNASPCC.* packages are bound there, and new packages/stored procedures (automatically routed to a DESCSTAT=NO member by default), are manually rebound on the spare member. The inherent risk here is rebind. As there are over 400k packages, autorebind is a risk, but due to the volume, has to be on.

The ideal solution is DESCSTAT as a bind parameter and the implementation, to my understanding, is not complicated.

This solution has an additional benefit, that of not increasing SPT01. ZPARM DESCSTAT=YES will increase the size of all packages and stored procedures. If it's a BIND parameter, the increase in SPT01 can be controlled. As a significant portion of OLTP is still COBOL/CICS, there's no reason to set DESCSTAT=YES for these objects, increase the package size, and all associated overhead handling it. While compression helped with SPT01, the size increase caused by DESCSTAT can be diminished, almost eliminated, if DESCSTAT is a BIND parm.

Let me know. Thanks, Linda

___________________________________________________________________

IDUG DB2 Tech Conference * Prague, Cz * 14-18 Nov 2011 * http://IDUG.ORG/EMEA
___________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information

Murali Tummala

RE: DESCSTAT, zparm v. bind parameter, interested parties
(in response to Linda Hagedorn)

Hi,

Reading thru this post and I am curious to find out if a solution has been reached.

We refer the columns returned by the cursor by the ordinal number and not by name. With a recent upgrade, we had to set the DESCSTAT to YES. This caused all the Stored Procedure calls to fail. As a temporary solution, we reverted it back to NO.

We are looking to mitigate the problem, Can the DESCSTAT be dynamically set to NO during bind of a stored procedure? Greatly appreciate any response.

 

Thanks,

Murali.

Linda Hagedorn

RE: DESCSTAT, zparm v. bind parameter, interested parties
(in response to Murali Tummala)

Hi Murali,

In V10, the only option is to swap zparms, either to bind the DB2 modules DESCSTAT=YES, or controlled rebinds for the stored procedures with zparms set to DESCSTAT=NO.  Consider how unexpected autobind could affect a running system.  There is lower risk to temporarily setting DESCSTST=YES and binding the DB2 modules as you can expect them to change infrequently, only when maintenance is applied.

The solution is in V11 when DESCSTAT was introduced as a bind parameter.  Here is the link to the syntax.   https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/comref/src/tpc/db2z_bindoptdescstat.html 

Best regards, Linda 

Murali Tummala

RE: DESCSTAT, zparm v. bind parameter, interested parties
(in response to Linda Hagedorn)

This information is helpful. Thanks much Linda.