Mainframe, MVS and zOS Discussion

We have a COBOL + DB2 setup and performance is mostly fine, but during peak hours, some jobs slow down drastically. How do you normally go about isolating DB2 performance issues in such cases? Is it usually access path-related or can something else be impacting it?

You need a Performance Monitor to see what Workload is active. We have good Experience with BMC Apptune, where you can see the SQL Statements and their Resource Consumption.

Hello.

There can be multiple things that will affect program performance under these conditions.

Assuming that you have identified a number of your programs which are having problems, the first thing it could be is that the programs are poorly written from a performance point of view. This can simply be that the programs are doing their work in a way that is inefficient, or they are doing things which will cause delays such as running queries against tables using predicates that don't reference indexed columns. If the program is not performing as well as expected on a lightly loaded system, it's going to have a hard time on a maxed-out system.

Next potential problem is data organisation. If the tables are regularly being updated, the clustering of the data and the indexes will be affected potentially resulting in slower access times. This will particularly be the case if you are doing a lot of inserts or modifying variable length fields as this will result in data being inserted/moved to places which are not conducive to your clustering. This is why regular reorg jobs are recommended to keep your data and indexes clustered thus providing the best possible performance.

Connected to data organisation is bind information. When you bind your package into the database, DB2 calculates the best method for running each of the statements in your program and stores this information in the system catalogue. The methods it decides are best will be based on the data organisation described in the catalogue at that particular time. If the data was poorly organised at bind time, or it was well organised before and is poorly organised now, DB2 may have selected or be operating using an access plan that is not suited to the condition of the data now with the result that performance is hit. You can potentially improve things by rebinding the affected programs, however, there's no guarantee that you'll get a better outcome.

The final consideration is locking. It may be that the programs that you are seeing issues with are not the true cause of the problem but it is their interaction with other programs which is causing delays. For example, if program A tries to grab a read lock on page 91 but transaction B is currently holding an exclusive lock on that particular page then program A will wait until transaction B releases that lock. It will look like program A is the problem, but it's really the two programs stepping on each others' toes. Now this might not normally happen when the machine is less loaded because transaction B can execute its update more quickly than when the machine is under load, but if the transaction is waiting for resources as a result of the system being at 100% the odds that it will hit a lock or something hits a lock that it holds increase. Worst case scenario is things start crashing out on SQLCODE -911 which is a deadlock (a situation where the locking cannot be successfully resolved).

What's happening on your system will require some analysis to work out what is causing the delays. It may be that simply reorging a particular table/index, or rescheduling some jobs will solve the problem. Alternatively, some tweeks to your programs might do the trick. Look for performance tools that will help you and get your DBA involved as they should be aware of the state of the databases and what's being done with regards reorgs.

It is also worth pointing out that there may be nothing you can do about it. If the machine is spending a lot of time running at or near 100% load, then it's long past time to upgrade the hardware. Unlike what some people will tell you, having a machine constantly running under full load is not a sign that you are making good use of it but an indication that work you want to do on it is being delayed due to lack of resources.