Prestart Jobs and Connection Pooling
Connection pooling is most often used for database connections to the QZDASOINIT or QZDASSINIT (secure server) jobs. It can also be used for other prestart server jobs.
By Dawn May01/10/2019
In the Prestart Job Messages blog, the example SQL shows how to summarize the CPIAD09 by current user profile for the various server job types. This allows you to see which users are driving the workload.
Let’s assume you would like to see how many connections are made each hour to identify your peak load times during the day. This example assumes the QSYS2.HISTORY_LOG_INFO() service was first used to create a temporary table named QHST in QTEMP, as was done with the example in the blog last week.
The MESSAGE_TIMESTAMP, which is the time the message was sent, is an SQL TIMESTAMP data type. The DATE and HOUR functions make it easy to get the date as well as the hour within the day. The GROUP BY by clause groups by the date first (in case there are multiple days), and the by the hour for each day.
The hour field is in 24-hour format and the count is the number of CPIAD09 messages that were sent in that hour. My test system is rather boring, but the results for a production system might be more interesting.
- Connections by job name
- Connections by subsystem
- Connections over a period of time. The above example was by the hour, but maybe you want more granularity and want to look at the number of connections every 10 minutes. Or maybe you want to review the number of connections over the course of a week to identify your busiest day.
- Connections from a specific remote IP address or range of IP addresses
- Connections from remote IP address by current user
If you have a good use of connection pooling, you will discover a great reduction in the CPIAD09 messages in the history log. The connections are kept in place, so the logging of the messages that happen when the connection is established is reduced. Not only will your performance be better, the number of messages in the history log will be significantly reduced.
Dawn May is an IBM i consultant. She owns Dawn May Consulting, LLC in the Greater Boston area. Dawn is a former IBM senior technical staff member.