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

Connection Pooling is the ability to keep a connection to the server in place.  Connections in the pool can be reused by the same application, avoiding overhead associated with creating a new connection for each request. Connection pooling improves performance by reducing the number of jobs started and ended, as well as reducing the overhead of full opens and program activations.   

Connection pooling is configured on the requester side. Some examples where connection pooling can be used are:

• IBM i Access ODBC
• IBM Toolbox for Java
• WebSphere Application Server
• Apache Tomcat

Connection pooling is most often used for database connections to the QZDASOINIT or QZDASSINIT (secure server) jobs. It can also be used for remote command jobs, file server jobs, and other prestart server jobs.

The CPIAD09 messages logged to the history log (reviewed in Prestart Job Messages) can be used to identify which servers would benefit most from connection pooling. If you have connection pooling set up, you can also review how effective your connection pooling configuration is.

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.

SELECT DATE(MESSAGE_TIMESTAMP) AS "Date", 

HOUR(MESSAGE_TIMESTAMP) AS "Hour", 

COUNT(*) AS "Count" 

FROM QTEMP.QHST 

WHERE MESSAGE_ID = 'CPIAD09' 

GROUP BY DATE(MESSAGE_TIMESTAMP), HOUR(MESSAGE_TIMESTAMP)

            ORDER BY 1 DESC, 2 ASC;

Below are some additional examples of how you may want to analyze the CPIAD09 messages:

• 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

You can use the example SQL above as a starting point for creating these additional queries. I find the SQL Reference very useful.

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. 

As with everything related to performance, you need to establish a baseline to determine if the results you review are typical or if some adjustments are required. 

---