DB2 for i5/OS: SQL Server Mode Primer

Published on 03 August 2007

View in HTML

Share this page:

IBM Form #: TIPS0658

Authors: Scott Forstie

Abstract

SQL Server Mode is a database application environment that is unique to the IBM System i platform. When SQL Server Mode is used, database connections establish a one-to-one relationship with a QSQSRVR server job. When SQL statements are executed in the client job, the corresponding QSQSRVR server job does the work. SQL Server Mode is generally used by multi-threaded applications to achieve better performance and to separate transaction management between connections.

SQL Server Mode is an environment that can be enabled programmatically or can be implicitly started when using native JDBC. When the connection is disconnected, the associated QSQSRVR job is returned to the QSYSWRK prestart job pool where it waits until it is used by a different connection.

In this Technote, we explain the benefits and restrictions of SQL Server Mode. We also explain how to enable SQL Server Mode on DB2 for i5/OS.

By Scott Forstie

SQL Development Team Leader

IBM Rochester

Benefits of SQL Server Mode
SQL Server Mode offers the following benefits, which are also reasons why you may want to consider using it:

Transaction management: Since each connection uses its own QSQSRVR job, transactions can be committed or rolled back without impacting other connections. Non-server mode jobs are permitted to have only one active transaction per activation group.
Performance: Applications that manage multiple connections can easily achieve parallel processing through SQL Server Mode, when different threads are working at servicing unique connections.
Connection management: Since connections to the database manager establish the QSQSRVR job or jobs, the application can establish multiple connections to the database. Non-server mode jobs are permitted to have only one connection to the database.

Enabling SQL Server Mode
There are two ways to programmatically request the use of SQL Server Mode within a job:

SQLSetEnvAttr() SQL CLI API, via the SQL_ATTR_SERVER_MODE attribute
QWTCHGJB() Work Management API, via the "Server mode for Structured Query Language" key 1922 within the JOBC0200 format

Whenever a connection is established using SQL Server Mode, the SQL7908 completion message is sent to the job log indicating the name of the QSQSRVR job that is being used, for example:
Job 188233/QUSER/QSQSRVR used for SQL server mode processing.

As stated before, using the native JDBC driver (also known as IBM Developer Kit for Java - licensed program 57xxJV1) implicitly enables SQL Server Mode.

Restrictions when using SQL Server Mode
The following restrictions exist when using SQL Server Mode:

The request to begin using SQL Server Mode must be made either:
- Prior to any SQL activity within the job
- When there are no active transactions within the job
Native database interfaces do not recognize SQL Server Mode and therefore execute within the initiating job. To intermix SQL and native database under SQL Server Mode requires analysis.
By definition, commitment control is active only in the QSQSRVR jobs. As such, commitment control should not be started in the SQL Server Mode job.
Non-SQL interfaces into commitment control should not be used from the SQL Server Mode job. For example, the COMMIT CL command should not be used.
After SQL Server Mode is established, it prevents interactive SQL (STRSQL command) from being used for the life of the job.
SQL CLI applications should serialize use of CLI connections across threads within the SQL Server Mode job. SQL CLI does not protect the application from thread safety issues if the application attempts to use a specific connection simultaneously from multiple threads.

QSQSRVR thread attributes
When the QSQSRVR job is established for the client connection request, the following thread attributes are inherited from the thread that initiated the connection request:

Current user profile
Run priority

QSQSRVR job attributes
Similarly, some of the job attributes are inherited from the user profile. After swapping the QSQSRVR job to the user profile that originated the connection request, Work Management APIs are called to pick up job attributes from the user profile. The attributes are not strictly inherited from the job that initiated the connection request, but rather are found directly within the user profile,

The following attributes are taken from the user profile:

Coded Character Set ID (CCSID)
Country Identifier
Character Identifier Control
Home directory
Language Identifier
Output queue name
Output queue priority
Print text
Printer device name
Sort sequence table
Status message handling
Job accounting code
Current library

The following attributes are general purpose attributes:

The job message queue full action is set to *WRAP.
The job name (QSQSRVR) is fixed.
The subsystem name (QSYSWRK) is fixed.
The job description (QGPL/QDFTJOBD) is fixed.
The QSQSRVR prestart job pool is automatically started, if needed, upon the first Server Mode connection request.
The QSQSRVR prestart job pool is configurable.
CHGPJE SBSD(QSYS/QSYSWRK) PGM(QSYS/QSQSRVR) STRJOBS(*YES) INLJOBS(xx) THRESHOLD(xx) ADLJOBS(xx) MAXUSE(xx or *NOMAX)

Exit program support
The SQL Server Mode (QSQSRVR) jobs have exit program support through the QIBM_QSQ_CLI_CONNECT exit program. This exit point provides the registration of a single exit program. The program must follow the linkage format defined by the CLIC0100 format, which defines a single CHAR(10) user name input parameter. The user name information can be used to construct an exit program that takes selective action, based upon the user name. The CLI Connection exit program can be used to adjust the job attributes and establish environment variables or any other program defined action.

Work active job QSQSRVR activity

Given the ever increasing set of SQL Server Mode users, it is important to understand how to interpret the activity when viewing the QSQSRVR jobs as a whole. The activity can be broken into several categories when viewing active QSQSRVR jobs by using WRKACTJOB SBS(QSYSWRK) or iSeries Navigator.

Subsystem or job	Current user	Status	Likely purpose
QSQSRVR	QUSER	PSRW	Prestart wait job. These are pre-allocated jobs that are waiting for a connection. QUSER appears because no work is happening in the job.
QSQSRVR	QDIRSRV	CNDW	Condition wait job. This job started on behalf of the IBM Directory Server. You can find the controlling job with this command: WRKJOB JOB(QDIRSRV/QDIRSRV)
QSQSRVR	QSECOFR	CNDW	Condition wait job. This job is started on behalf of Management Central. You can find the controlling job with this command: WRKJOB JOB(QYPSJSVR/QYPSJSVR) Management Central starts 18 SQL Server Mode connections. Each of the two servers (QYPSSRV and QYPSJSVR) currently establishes nine connections with a QSQSRVR job to process its SQL requests to these databases. These jobs are necessary for the proper functioning of Management Central.
QSQSRVR	JOE	TIMW	An active connection established by user JOE. The status changes as the connection is used.

Conclusion
DB2 for i5/OS application provider use of SQL Server Mode continues to become more commonplace. This primer has provided the basic details of SQL Server Mode to allow those who construct, maintain, or monitor applications to understand the system, application, and QSQSRVR job activity.

Special Notices

The material included in this document is in DRAFT form and is provided 'as is' without warranty of any kind. IBM is not responsible for the accuracy or completeness of the material, and may update the document at any time. The final, published document may not include any, or all, of the material included herein. Client assumes all risks associated with Client's use of this document.