Active_Job_Info() for Prestart Server Jobs
This article continues upon the series of blogs Dawn May has written related to prestart jobs, one of which detailed retrieving the current SQL statement for a job using QSYS2.ACTIVE_JOB_INFO()
QSYS2.ACTIVE_JOB_INFO() has a large number of columns that can be returned, some of which are uniquely interesting for prestart server jobs. In particular, this service (along with the QSYS2.GET_JOB_INFO() service), returns information about prestart job reuse counts. I’m not aware of any other interface that returns similar information. Specifically, I’m talking about the PRESTART_JOB_REUSE_COUNT and PRESTART_JOB_MAX_USE_COUNT columns. The PRESTART_JOB_REUSE_COUNT column tells you how many times that prestart job has been reused while the PRESTART_JOB_MAX_USE_COUNT column returns the configured maximum reuse count.
In addition to these columns, below are the other columns that I find interesting when I review prestart jobs.
1. JOB_TYPE—the job type is PJ for prestart server jobs.
2. SERVER_TYPE—the server type is a character string that describes the type of server job. The IBM i job name can be cryptic and the server type provides a more meaningful description than the job name. The information returned by QSYS2.ACTIVE_JOB_INFO() is the actual server type text, not the descriptive text shown in Navigator displays such as Server Jobs. For example, the Database Host Server has the server type of QIBM_OS400_QZBS_SVR_DATABASE and the Remote Command Server has a server type of QIBM_OS400_QZBS_SVR_RMTCMD. The blog Prestart Jobs and Server Jobsexplains the server type in more detail.
3. CLIENT_IP_ADDRESS—the client IP address identifies the IP address where the work request originated. This is most useful when the work comes from a specific client in the network. If you have a 2-tier or 3-tier application, the client IP address becomes less interesting as it may be the IP address of an intermediate system and all connections have the same IP address. Also, for requests which originate from within the system, you will see 127.0.0.1, which again is not very helpful. In addition to the client IP address, you can also get the client port and client host name.
4. SQL statement information—as I wrote in Retrieving the Current SQL Statement for a Job, you can access the current SQL statement, along with a lot of other information regarding that SQL statement with QSYS2.ACTIVE_JOB_INFO().
5. Client Special Registers—as I wrote in Client Special Registers are Special, you can retrieve the client special registers that are set for the job. This only applies to those jobs running SQL statements, and only if the client special registers have been set.
6. SERVER_MODE_CONNECTING_JOB for QSQSRVR jobs—this is of interest for the QSQSRVR jobs; it will show you the qualified job name for which this QSQSRVR job is handling SQL requests. The blog QSQSRVR Job Considerations discusses this topic further.