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

Bill Gallagher

DB2 z/OS Statement Concentrator
(in response to Bill Gallagher)
Hi,

We are looking to play with the statement concentration feature for dynamic SQL coming from a distributed client.

We've updated the db2cli.ini file on our distributed client to include the following statement for the database alias (i.e. DB2 z/OS subsystem) that we are testing in:

STMTCONCENTRATOR=WITHLITERALS

Can't seem to get it to work. All the SQL we are executing against the subsystem are coming through with literals, and are not being concentrated with literals replaced by &.

I was able to do this at my previous shop. Adding the above line to the db2cli.ini file was all that was necessary. So I'm stumped as to why it's not working here.

Any thoughts on what we may be missing?

Thanks!

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Michael Hannan

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

Bill,

I was at a site where we set it in the JDBC Config File. Some early doco on how to do it was either wrong or got changed. That site was one where where we requested that it be a BIND parameter too. Putting it in Prepare is too hard.

IBM has a paper "DB2 for z/OS: Dynamic Statement Cache"

See if that helps. I quote from it:

"6.1.1 Statement concentration

If dynamic SQL statements are used with literals, and the literals change from one statement execution to another, the cache is not used very efficiently. Each statement is different, which means that each statement needs a full prepare and a separate entry in the DSC. If the access path is always the same, it makes sense to store the statement only once and reuse it. A common best practice is to use parameter markers instead of literals for best DSC efficiency, but sometimes applications run as “black box”. They are bought from third parties or vendors and cannot be changed easily. Using statement concentration can mitigate that problem.

The appropriate parameter settings are:  In SQL PREPARE statement, the ATTRIBUTES setting of CONCENTRATE STATEMENTS WITH LITERALS. Consider that ATTRIBUTES options must be in a host variable. 

In JDBC on connection level, setDBStatementConcentrator(2) 

In ODBC initialization file, LITERALREPLACEMENT=1 

Beginning with DB2 12, CONCENTRATESTMT can be specified as a BIND option" 

 There are fields in your DB2 Acctng 101 Thread level (some call it Plan level - I don't) records that allow you see how effective is your CSWL, "Concentrate SQLs With Literals". i.e. how well are you getting Dynam Stmt Cache access path reuse by the SQL when literals are replaced by markers, for the 2nd Cache match attempt.

We had application IBM  Dynam SQL application "Content Manager" I think it was called (ICM), where Prepares were costing far more than the SQLs themselves (after Index tuning). So CSWL helped a lot. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 16, 2019 - 03:25 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 16, 2019 - 03:41 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 16, 2019 - 03:42 PM (Europe/Berlin)

Bill Gallagher

[External] DB2 z/OS Statement Concentrator
(in response to Michael Hannan)
Michael,

Thanks, yes I have seen the IBM whitepaper that you reference below.

I have used this successfully at a previous shop. I was able to enable it there by adding the appropriate line to the db2cli.ini file. We were at DB2 11 CM there.

We’re trying to do the same thing here under DB2 11 NFM. We’ve also added the appropriate line to the db2dsdriver.cfg file. But concentration is just not happening. We can’t for the life of us figure out why not, since everything appears to be configured properly on the client end.

I believe my colleague is opening a ticket with IBM to get some help.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration
Travelers
1 Tower Square | PB02, Desk #203
Hartford, CT 06183
W: 860.500.4763

[cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]>
Sent: Thursday, May 16, 2019 9:23 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - RE: DB2 z/OS Statement Concentrator


Bill,

I was at a site where we set it in the JDBC Config File. Some early doco on how to do it was either wrong or got changed.

IBM has a paper "DB2 for z/OS: Dynamic Statement Cache"

See if that helps. I quote from it:

"6.1.1 Statement concentration

If dynamic SQL statements are used with literals, and the literals change from one statement execution to another, the cache is not used very efficiently. Each statement is different, which means that each statement needs a full prepare and a separate entry in the DSC. If the access path is always the same, it makes sense to store the statement only once and reuse it. A common best practice is to use parameter markers instead of literals for best DSC efficiency, but sometimes applications run as “black box”. They are bought from third parties or vendors and cannot be changed easily. Using statement concentration can mitigate that problem.

The appropriate parameter settings are: • In SQL PREPARE statement, the ATTRIBUTES setting of CONCENTRATE STATEMENTS WITH LITERALS. Consider that ATTRIBUTES options must be in a host variable. •

In JDBC on connection level, setDBStatementConcentrator(2) •

In ODBC initialization file, LITERALREPLACEMENT=1 •

Beginning with DB2 12, CONCENTRATESTMT can be specified as a BIND option"



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
Attachments

  • image001.png (5.6k)

Daniel Luksetich

[External] DB2 z/OS Statement Concentrator
(in response to Bill Gallagher)
Have you check your client version? I strongly encourage the most recent version.



Interesting that I recently ran into a problem with statement concentration and extended indexes. Basically…watch out if you use statement concentration with some extended indexes. Explain shows index access, but at run time you could get a table space scan. IBM has told me that there is nothing they can do about it. Makes me think it’s time for IBM to rearchitect the optimizer. At least the QST->APS piece. Heck some runtime as well. I’d like to see query blocks talk to each other more….



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Gallagher,Bill R <[login to unmask email]>
Sent: Thursday, May 16, 2019 10:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] DB2 z/OS Statement Concentrator



Michael,



Thanks, yes I have seen the IBM whitepaper that you reference below.



I have used this successfully at a previous shop. I was able to enable it there by adding the appropriate line to the db2cli.ini file. We were at DB2 11 CM there.



We’re trying to do the same thing here under DB2 11 NFM. We’ve also added the appropriate line to the db2dsdriver.cfg file. But concentration is just not happening. We can’t for the life of us figure out why not, since everything appears to be configured properly on the client end.



I believe my colleague is opening a ticket with IBM to get some help.



Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

Travelers

1 Tower Square | PB02, Desk #203

Hartford, CT 06183

W: 860.500.4763







From: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Thursday, May 16, 2019 9:23 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [External] [DB2-L] - RE: DB2 z/OS Statement Concentrator



Bill,

I was at a site where we set it in the JDBC Config File. Some early doco on how to do it was either wrong or got changed.

IBM has a paper "DB2 for z/OS: Dynamic Statement Cache"

See if that helps. I quote from it:

"6.1.1 Statement concentration

If dynamic SQL statements are used with literals, and the literals change from one statement execution to another, the cache is not used very efficiently. Each statement is different, which means that each statement needs a full prepare and a separate entry in the DSC. If the access path is always the same, it makes sense to store the statement only once and reuse it. A common best practice is to use parameter markers instead of literals for best DSC efficiency, but sometimes applications run as “black box”. They are bought from third parties or vendors and cannot be changed easily. Using statement concentration can mitigate that problem.

The appropriate parameter settings are: * In SQL PREPARE statement, the ATTRIBUTES setting of CONCENTRATE STATEMENTS WITH LITERALS. Consider that ATTRIBUTES options must be in a host variable. *

In JDBC on connection level, setDBStatementConcentrator(2) *

In ODBC initialization file, LITERALREPLACEMENT=1 *

Beginning with DB2 12, CONCENTRATESTMT can be specified as a BIND option"



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

_____

This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

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

Bill Gallagher

[External] DB2 z/OS Statement Concentrator
(in response to Daniel Luksetich)
Hi Dan,

The client is pretty current:

Product name: "DB2 Connect Unlimited Edition for zSeries"
License type: "Client Device"
Expiry date: "Permanent"
Product identifier: "db2consv"
Version information: "11.1"

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration
Travelers
1 Tower Square | PB02, Desk #203
Hartford, CT 06183
W: 860.500.4763

[cid:[login to unmask email]

From: Daniel L Luksetich <[login to unmask email]>
Sent: Thursday, May 16, 2019 11:25 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] DB2 z/OS Statement Concentrator

Have you check your client version? I strongly encourage the most recent version.

Interesting that I recently ran into a problem with statement concentration and extended indexes. Basically…watch out if you use statement concentration with some extended indexes. Explain shows index access, but at run time you could get a table space scan. IBM has told me that there is nothing they can do about it. Makes me think it’s time for IBM to rearchitect the optimizer. At least the QST->APS piece. Heck some runtime as well. I’d like to see query blocks talk to each other more….

Cheers,
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IDUG DB2-L Administrator
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Gallagher,Bill R <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Thursday, May 16, 2019 10:06 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [External] DB2 z/OS Statement Concentrator

Michael,

Thanks, yes I have seen the IBM whitepaper that you reference below.

I have used this successfully at a previous shop. I was able to enable it there by adding the appropriate line to the db2cli.ini file. We were at DB2 11 CM there.

We’re trying to do the same thing here under DB2 11 NFM. We’ve also added the appropriate line to the db2dsdriver.cfg file. But concentration is just not happening. We can’t for the life of us figure out why not, since everything appears to be configured properly on the client end.

I believe my colleague is opening a ticket with IBM to get some help.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration
Travelers
1 Tower Square | PB02, Desk #203
Hartford, CT 06183
W: 860.500.4763

[cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Thursday, May 16, 2019 9:23 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [External] [DB2-L] - RE: DB2 z/OS Statement Concentrator


Bill,

I was at a site where we set it in the JDBC Config File. Some early doco on how to do it was either wrong or got changed.

IBM has a paper "DB2 for z/OS: Dynamic Statement Cache"

See if that helps. I quote from it:

"6.1.1 Statement concentration

If dynamic SQL statements are used with literals, and the literals change from one statement execution to another, the cache is not used very efficiently. Each statement is different, which means that each statement needs a full prepare and a separate entry in the DSC. If the access path is always the same, it makes sense to store the statement only once and reuse it. A common best practice is to use parameter markers instead of literals for best DSC efficiency, but sometimes applications run as “black box”. They are bought from third parties or vendors and cannot be changed easily. Using statement concentration can mitigate that problem.

The appropriate parameter settings are: • In SQL PREPARE statement, the ATTRIBUTES setting of CONCENTRATE STATEMENTS WITH LITERALS. Consider that ATTRIBUTES options must be in a host variable. •

In JDBC on connection level, setDBStatementConcentrator(2) •

In ODBC initialization file, LITERALREPLACEMENT=1 •

Beginning with DB2 12, CONCENTRATESTMT can be specified as a BIND option"



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
-----End Original Message-----

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

  • image001.png (5.6k)