2024 - Setting Up Connection Pooling on IBM i

If your log files are getting filled up with messages about connections to the IBM i/iSeries/AS400, you can reduce the number of messages and number of connections by

• Setting up connection pooling for your IBM i/iSeries/AS400 source connection
• Configuring the QZDASOINIT pool on the iSeries/AS400 side

Setting Up Connection Pooling for your IBM i/iSeries/AS400 Source Connection

Connection pooling for your source connection is managed by the database connection tool. When connecting to the IBM i/iSeries/AS00, you would typically be using the Syniti Ritmo/i .NET data provider which is provided with Syniti DR/DBMoto. Connection pooling allows up to 10 pooled connections with identical connection strings to be made without opening an additional connection. To set up connection pooling:

1. In the Syniti DR/DBMoto Management Center, expand the tree to display your source connection to the IBM i/iSeries/AS400.
2. Select the connection.
3. From the right mouse button menu, choose Connection Properties.
4. In the dialog, select the entry Connection.
5. Click the button which appears next to the Connection value to open the Connection Properties dialog for the data provider.
   
   This dialog displays all the connection properties used by the data provider to connect to the IBM i/iSeries/AS400.
6. In the Connection String list, scroll down to the Pooling entry.
7. Set the value to True by clicking in the value field, then choosing True from the drop-down list.
8. Click OK to save your changes.

Configuring the QZDASOINIT Pool

QZDASOINIT jobs are the jobs on IBM i/iSeries/AS400 side serving the clients using DB2 via Optimized Database Server (Host Database Server). Every job represents one client connection (so you can have more than 1 job from every client box, depending on how many connections have been opened). They usually run under the QUSRWRK subsystem. They're described as BATCHI jobs. BATCHI = BCI.BCI is Batch Immediate. These jobs use the Batch CPW capability of the machine, and not the interactive 5250 CPW capability.

QZDASOINIT are Pre-Started Jobs (PJ), which means that you can have one or more of them running in a pool even if no client is connected, waiting for clients to ask for connections. The number of Pre-Started Jobs is controlled by the subsystem description, so it is the QUSRWRK description that needs to be modified.

This is a task for an iSeries/As400 system admininstrator.The system administrator should make sure that there is an adequate number of database server jobs ready to service client requests. Otherwise, the server needs to start new instances of the QZDASOINIT job which unnecessarily consumes system resources and slows down the connection time.

To view the current settings for the QZDASOINIT prestart job, you can use the command:

DSPSBSD SBSD(QUSRWRK)

and select Option 10 for Prestart job entries.

Then, select Option 5 on the QZDASOINIT program to display the details.

The command:

CHGPJE SBSD(QUSRWRK) PGM(QSYS/QZDASOINIT)

can be used to change parameters.

Meaningful parameters:

 Initial number of jobs
  Number of jobs that initially started when the prestart jobs first started.
  This value has a default value of 1 which is unrealistically low
  for most multi-tier applications.
  The developer should set this value to the number of active database
  connections that the application would require at any given time.
  For instance, if 100 active users are expected that, on average,
  require two database connections, set this parameter to 200.
  
 Additional number of jobs
  Additional number of prestart jobs that are started when the number of
  available prestart jobs drops below the Threshold parameter.
  
 Maximum number of jobs 
   Maximum number of prestart jobs that can be active at the same time for this entry.