DB2 z/OS - Statement Concentrator

Bill Gallagher

DB2 z/OS - Statement Concentrator
Good afternoon, List.

I'm around 2 months into a potentially long-term assignment with the State of CT. We have an approximately 20 year old "traditional" client server application with a Windows GUI front-end (built in PowerBuilder) with DB2 z/OS v11 on the back-end.

I only recently got access to a DB2 monitor (Omegamon) and started poking around to see what I could find for the application, in particular with the dynamic SQL that the application was generating and sending to the mainframe for execution. It looks like the app is building the dynamic SQL with literals rather than being coded to use parameter markers. It also looks like the dynamic cache is being flooded with unique SQL statements throughout the day, and is getting virtually no reusability of statements in the cache as they are being flushed out within 1 to 2 minutes (as best as I can tell).

Now, I was not brought in to address any performance issues, and I have not been told that there are performance issues with this particular application (it has many users spread out across a number of regional offices across the state). But I suspect that they may just be accepting current performance "as is" because it's what they've always had and it's what they're used to. I also suspect that this application (and the DB2 subsystem it runs in) would greatly benefit if their use of dynamic SQL could be fixed. I need to follow up with the DB2 systems programmer to see if he feels that Db2 subsystem performance is a "pain point" from his point of view. That's probably my task for tomorrow.

To the point: I'm considering recommending the possible use of enabling statement concentration on the client end of the application. In particular, I'm looking at adding the following to the "db2cli.ini" file for the data source that points to the production DB2 subsystem:

STMTCONCENTRATOR=WITHLITERALS

So in short, my question to the List is: is there anything I should be concerned about or aware of in doing this? Obviously, it would need to be tested thoroughly in our test system to make sure that it doesn't result in any unexpected adverse behavior. And then there's the logistical problem of updating the "db2cli.ini" file on hundreds of client workstations. But what I'm really looking for are any "gotcha's" that I should be worried about before even discussing this with the powers that be here.

Any comments, suggestions, feedback would be appreciated.

Thanks.

Bill Gallagher
DB2 Database Administrator
State of Connecticut

Peter Conlin

RE: DB2 z/OS - Statement Concentrator
(in response to Bill Gallagher)

Hi Bill,

You may want to look at the DSNACLI plan/packages to ensure UR is coded where appropriate (for dynamic SQL, probably almost everywhere.)  Coding FOR READ ONLY or multiple CLI plans/packages is another approach.

OPTIMIZE for 1 row may also be critical if appropriate & may eliminate multiple table scans, particularly in a multi-table select.

I was brought into a situation where this provided immediate relief to a pair of major (station booking & domestic violence complaints) that worked well on test data, but failed miserably when turned live.  It took over an hour to get the consultant developers to finally provide the code they were using & a couple of hours with the very helpful NYPD's DB2 sysprog to test & fix it.  (Yes, dynamic SQL, but that's another issue.)

Good Luck,

Peter 

Gary Bronziet

RE: DB2 z/OS - Statement Concentrator
(in response to Peter Conlin)

Statement Concentration is a useful feature implemented by IBM to make more effective use of the statement cache.  Without statement concentration, Db2 does not recognise SQL statements which are essentially the same (as SQL already in the cache), leading to unnecessary cache thrashing and missed opportunities for PREPARE avoidance.

There is another challenge in tuning Db2 systems comprising SQL with literal values. Most performance monitors will not recognise these essentially the same SQL, and report them all individually. If these SQL are low-cost, then they may fly under the radar.

SQL consolidation, in this context, is the ability to recognises these “essentially the same” SQL and aggregate/consolidate/report the metrics for such statements as if they were the same. Such a process will help the user identify the  seemingly low-cost statements which, when aggregated together, are actually the real performance pain points.

We use the metaphor of “elephants versus mosquitoes” when performing system tuning. The SQL “elephants” are easily identifiable by most performance monitors, but typically it is the SQL “mosquito storms” that do the most damage and where there is the most tuning opportunity.

Without SQL consolidation it is impossible to identify the mosquito storms, so make sure your monitor also supports some form of SQL consolidation!

Gary Bronziet

Syncsort/Cogito

Bill Gallagher

DB2 z/OS - Statement Concentrator
(in response to Peter Conlin)
Hi Peter,

Thanks for the response, but I think there’s a disconnect here between my post and your response.

I’m talking about a client/server OLTP application. I’m not sure why you’re recommending we use UR as our isolation level, that would seem to be a very bad idea with respect to transaction concurrency. I’m also failing to understand how your comments would even relate to enabling statement concentration for our dynamic cache.

What am I missing?

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Peter Conlin [mailto:[login to unmask email]
Sent: Tuesday, November 21, 2017 4:35 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS - Statement Concentrator


Hi Bill,

You may want to look at the DSNACLI plan/packages to ensure UR is coded where appropriate (for dynamic SQL, probably almost everywhere.) Coding FOR READ ONLY or multiple CLI plans/packages is another approach.

OPTIMIZE for 1 row may also be critical if appropriate & may eliminate multiple table scans, particularly in a multi-table select.

I was brought into a situation where this provided immediate relief to a pair of major (station booking & domestic violence complaints) that worked well on test data, but failed miserably when turned live. It took over an hour to get the consultant developers to finally provide the code they were using & a couple of hours with the very helpful NYPD's DB2 sysprog to test & fix it. (Yes, dynamic SQL, but that's another issue.)

Good Luck,

Peter

-----End Original Message-----

Michael Hannan

RE: DB2 z/OS - Statement Concentrator
(in response to Bill Gallagher)

Bill,

Concentrate SQLs With Literals or CSWL is not suitable for all workloads. Some SQL queries need the runtime optimization to get a suitable access path. A good portion of dynamic workloads are suited though. Very long running search queries are not well suited. Very short running keyed lookups are well suited.

Have a look at a sample of SQLs that ran, and see if most would execute fine with a standard generic access paths, as though parameter markers were used.

A good indicator of the need for CSWL is when the Prepare costs outweigh the costs of executing the Queries. Expensive queries should optimized for the literals at run time. Very cheap queries (with very good access paths) probably can live with a single generic access path.

I like to start with SMF fields in the Acctng data that shows your dynamic Stmt. Cache Hit ratios, and the total counts for Prepares and for other SQL statements.  

You obviously need to come up with an estimated savings, for hat you are doing. This means you need to know what percentage of the costs are in Prepare. Some monitors can give this type of information.

The other thing you can do is try it for a small number of cases, getting SMF performance measurement data before and after. Measure is always better than estimate. If you have not got a good dynamic SQL monitor, nor got a good collection of the Acctng SMF data, then it gets more difficult.

Db2 12 for zOS has the ability to try CSWL on certain Packages.
 
In Reply to Bill Gallagher:

Good afternoon, List.

I'm around 2 months into a potentially long-term assignment with the State of CT. We have an approximately 20 year old "traditional" client server application with a Windows GUI front-end (built in PowerBuilder) with DB2 z/OS v11 on the back-end.

I only recently got access to a DB2 monitor (Omegamon) and started poking around to see what I could find for the application, in particular with the dynamic SQL that the application was generating and sending to the mainframe for execution. It looks like the app is building the dynamic SQL with literals rather than being coded to use parameter markers. It also looks like the dynamic cache is being flooded with unique SQL statements throughout the day, and is getting virtually no reusability of statements in the cache as they are being flushed out within 1 to 2 minutes (as best as I can tell).

Now, I was not brought in to address any performance issues, and I have not been told that there are performance issues with this particular application (it has many users spread out across a number of regional offices across the state). But I suspect that they may just be accepting current performance "as is" because it's what they've always had and it's what they're used to. I also suspect that this application (and the DB2 subsystem it runs in) would greatly benefit if their use of dynamic SQL could be fixed. I need to follow up with the DB2 systems programmer to see if he feels that Db2 subsystem performance is a "pain point" from his point of view. That's probably my task for tomorrow.

To the point: I'm considering recommending the possible use of enabling statement concentration on the client end of the application. In particular, I'm looking at adding the following to the "db2cli.ini" file for the data source that points to the production DB2 subsystem:

STMTCONCENTRATOR=WITHLITERALS

So in short, my question to the List is: is there anything I should be concerned about or aware of in doing this? Obviously, it would need to be tested thoroughly in our test system to make sure that it doesn't result in any unexpected adverse behavior. And then there's the logistical problem of updating the "db2cli.ini" file on hundreds of client workstations. But what I'm really looking for are any "gotcha's" that I should be worried about before even discussing this with the powers that be here.

Any comments, suggestions, feedback would be appreciated.

Thanks.

Bill Gallagher
DB2 Database Administrator
State of Connecticut



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd