IBM Support

QSQSRVR Subsystem Customization

Troubleshooting


Problem

This document explains that the subsystem in which QSQSRVR server mode SQL CLI connection jobs run can be configured on current releases of IBM i.

Resolving The Problem

This document explains that the subsystem in which QSQSRVR server mode SQL CLI connection jobs run can be configured on current releases of IBM i.

The functionality added in V6R1 allows the subsystem customization using an environment variable and, in V7R1, it can also be configured for native JDBC using a connection property.

Note: A PTF for V6R1 provides an environment variable mechanism to select the server job.

You should refer to PTF SI33949.

The PTF cover letter states the following:

In some environments, it would be better to have SQL server mode work occur in the same subsystem as the job which is initiating the work.  Currently, QSQSRVR prestart jobs use the QSYSWRK subsystem for all SQL server mode connections.  This PTF provides a switch, in the form of an environment variable, which allows the database to be directed to use the same subsystem as the job which is forming the SQL server mode connection, instead
of relying upon QSYSWRK.


A Correction For APAR SE36073 follows:

To enable the switch, you should issue one of the following commands:

ADDENVVAR ENVVAR(QIBM_SRVRMODE_SBS) VALUE('*SAME') LEVEL(*SYS)

or

ADDENVVAR ENVVAR(QIBM_SRVRMODE_SBS) VALUE('') LEVEL(*SYS)


To disable the switch, you should issue the following command:

RMVENVVAR ENVVAR(QIBM_SRVRMODE_SBS) LEVEL(*SYS)

Notes:

1.The subsystem choice is made upon first use of SQL server mode within a job and does not change for the life of that job.
2.The ADDENVVAR command could be used with LEVEL(*JOB), if the same subsystem choice can not be made at a system wide level.
3.If anything other than '*SAME' or '' appear within the environment variable, QSYSWRK will continue to be used for SQL server mode connections.
4.QSQSRVR jobs in QSYSWRK rely upon a prestart job entry for optimal performance.  For any performance critical SQL server mode activity, a prestart job entry for QSQSRVR should be created for the subsystem used by the application.

For example:
ENDSBS <subsystem-name>
ADDPJE SBSD(<library>/<subsystem-description-name>)
PGM(QSYS/QSQSRVR) STRJOBS(*YES) INLJOBS(x) THRESHOLD(y)
ADLJOBS(z) MAXUSE(*NOMAX)
STRSBS <subsystem-name>

If a prestart job entry does not exist for QSQSRVR within the subsystem, the QSQSRVR job will utilize a Batch Immediate job (BCI) instead of a Prestart job (PJ).
5.If the subsystem name of the current job is equal to 'QHTTPSVR' or 'ZEND', the environment variable will have no effect and QSYSWRK will continue to be used.

Note: The ability for JDBC to choose the server mode subsystem was added to V7R1.

A native JDBC connection property was added to allow specification of the subsystem. It is documented in the JDBC driver connection properties section of the IBM i Infocenter as follows:

servermode subsystem

*SAME, subsystem name

This property specifies the subsystem in which the associated QSQSRVR jobs will run. The default behavior is to have the jobs run in the QSYSWRK subsystem. If the value *SAME is used, then the QSQSRVR jobs will run in the same subsystem as the job using the native JDBC driver.
In order for a QSQSRVR job to run in a different subsystem, a QSQSRVR prestart job entry for that subsystem must exist.

The following commands can be used to create a QSQSRVR prestart job entry:

ENDSBS sbs

ADDPJE SBSD(library/sbsd)
PGM(QSYS/QSQSRVR) STRJOBS(*YES) INLJOBS(
x)
THRESHOLD(
y) ADLJOBS(z) MAXUSE(*NOMAX)

STRSBS
sbs

Where sbs is the subsystem, library is the library in which the subsystem description sbsd is located in, and x, y, and z are numeric values for the corresponding parameters on the Add Prestart Job Entry (DDPJE) command.

If a prestart job entry does not exist for QSQSRVR within the subsystem, the QSQSRVR job will utilize a Batch Immediate job (BCI) instead of a Prestart job (PJ). This Batch Immediate job typically runs in the same subsystem as the job using the native JDBC driver.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0;6.1.1;7.1.0","Edition":""},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""}]

Historical Number

622139773

Document Information

Modified date:
18 December 2019

UID

nas8N1011197