IBM i5/OS V5R4 prestart job and database connection tuning.

Database connection tuning is an important part of application performance tuning, particularly, when applications uses connection pool. I noticed that some iSeries user does not care about this too much as I can not get answer when I ask them what is the limitation of database connection iSeries can support. The answer may not be simple. Or it can be as simple as no limitation as the default maximum number of QSQSRVR job and QZDASOINIT job is *NOMAX. This wont be good answer as the number of job should be optimized to a number, which fits your applications requirement.

On IBM i5/OS, there are two jobs used by database connection. They are SQL server mode prestarted job QSQSRVR and Database server prestart job QZDASOINIT job. QSQSRVR job is used when Database connection is coming through Command Level Interface (CLI). For example, PHP DB2 extension uses CLI to connect to DB2. Therefore, its corresponding job is QSQSRVR. Native iSeries JDBC driver (type 2) is using QSQSRVR job too. QZDASOINIT job is used when the connection is from ODBC or type 4 JDBC. Therefore, access from jt400.jar goes through QZDASOINIT job. When the type 4 JDBC is configured to use SSL, QZDASSINIT job is used instead of QZDASOINIT.

Both QSQSRVR job and QZDASOINIT job have their deamon job, which creates them. QSQSRVR job's deamon job is QYPSJSVR. Command WRKJOB JOB(QYPSJSVR/QYPSJSVR) can be used to do further investigate. For QSQSRVR job itself, we can use command WRKSBSD SBSD(QSYS/QSYSWRK) or command WRKJOB JOB(QSQSRVR) to do deeper investigation. For QZDASOINIT job, its deamon job is QZDASRVSD. We can use command WRKJOB JOB(QZDASRVSD) or WRKSBSD SBSD(QSYS/QSERVER) work with it further. Command CHGPJE can be used to change the configuration of prestart jobs. Also, don't forget that iNavigator is a convenience tool for us to investigate too: Work Management -> Server Jobs . The database server prestart jobs (QZDAINIT, QZDASOINIT, and QZDASSINIT) by default are shipped to run in subsystem QSERVER QZDAINIT) and QUSRWRK (QZDASOINIT and QZDASSINIT).

Also, there is another prestart job called QRWTSRVR, which is used by DB2 Connect driver. DB2 Connect uses DRDA protocol that is used to reduce the cost and complexity of accessing data in different DB2 supporting DRDA. In fact, in Work Management menu of iNavigator, you can see that iNavigator uses QRWTSRVR job. However, its license fee is expensive for enterprise edition. QRWTSRVR job typically run in QSYSWRK subsystem.

I made a diagram to present these three ways to connect to DB2 for iSeries. It is helpful for having a clear concept how the database connection is connect to DB2 and what is under those licensed and unlicensed program indeed. For example, people always ask why IBM gives type 4 JDBC driver for free and charge DB2 Connect. From this post, we can see jt400.jar, php db2 extension, and DB2 Connect use different protocol connect to DB2 for iSeries and different server jobs serve them. Furthermore, different protocols are designed for different usage. For example, DRDA is designed to support topology in complicate distributed network environment.

And, in Zend server for IBMi, there is a package called i5 Toolkit. It can be used to connect to i5/OS and access data too as DB2 for i5/OS is part of OS. A daemon called i5_COMD server is shipped together with Zend Server. i5 toolkit client communicate with it-COMD server for accessing i5 Data object. However, I feel it emulates 5250 terminal protocol and uses Remot Command Server prestart job QZRCSRVS. I am not able to discuss it here. I will spend some time to dig it later.

Here are some links from IBM for prestarted job on i5/OS V5R4 and checking job log of the appropriate "Host Server" and a PTF for improved management of QSQSRVR jobs.