Troubleshooting
Problem
How can the subsystem in which QSQSRVR jobs run be changed from the default of QSYSWRK to a custom subsystem?
Resolving The Problem
The subsystem in which QSQSRVR (server mode SQL CLI connection) jobs run can be configured via addition of environment variable "QIBM_SRVRMODE_SBS".
The v610 PTF si33949 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.
To enable the switch, 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, 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 cannot 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 use 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. |
For jobs using native JDBC, a connection property allows specification of the subsystem for the QSQSRVR job. It is documented in the JDBC driver connection properties section of IBM Documentation. The property name is "servermode subsystem".
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwEgAAI","label":"Data Access-\u003ESQL CLI"}],"ARM Case Number":"TS009555305","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
622139773
Was this topic helpful?
Document Information
Modified date:
03 November 2023
UID
nas8N1011197