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:
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.
DB2 Database Administrator
State of Connecticut