DB2 for i5/OS: User-Defined Servers to the Rescue

Abstract

DB2 for i5/OS customers are seeing an ever increasing use of SQL Server Mode. When trying to make sense of all the QSQSRVR jobs in the QSYSWRK subsystem, you might quickly come to the conclusion that it is nearly impossible to approach the question from the perspective of server jobs. Traditional approaches realized the analysis and understanding by starting with the client or application side. Over time, this focus on the application side for an explanation of QSQSRVR job activity has become increasingly more difficult because QSQSRVR jobs are being used by more applications, middleware, and IBM solutions. Viewing these server jobs from work management interfaces provides few clues about the origin and ownership of the activity.

In this Technote, we explain how you can work with applications to use different server types. When doing configuration in System i Navigator, an administrator can view the vast collection of QSQSRVR jobs in logical work groups. Using the approach described here, you can use this strategy to isolate problem ownership, target performance issues, or understand why QSQSRVR jobs are active.

Written by Scott Forstie
SQL Team Leader
IBM Rochester

Contents


QSQSRVR jobs are employed by many different types of applications. The QSQSRVR prestart job pool is a single set of prestart jobs, to be used by all SQL Server Mode activity across the entire machine, including any IASP configurations, for example:
  • SQL CLI applications, which enable the SQL_ATTR_SERVER_MODE environment attribute
  • Native JDBC applications
  • PHP applications, which use IBM DB2 extensions
  • WebSphere Application Server
  • IBM Directory Server
  • IBM Management Central

In cases where more than a single application instance is active on the machine, it is quite difficult to visually associate these QSYSWRK subsystem QSQSRVR jobs with the owning application. Stated another way, if an administrator approaches the System i platform and notices that a rogue QSQSRVR job is operating outside of normal parameters, how can the purpose of the specific QSQSRVR job be understood and associated with its owner?

Server type
Every job on the system includes a Server Type identity value. Server Type is a 30-character value. For non-server jobs, such as an interactive job, Server Type defaults to blanks. The Work Management API QWTCHGJB() gives the application provider the ability to imprint jobs with a unique Server Type value.

How the server type is externalized
The server type can be used within System i Navigator to construct user-defined server groups. The user-defined server centers around the unique Server Type value. By imprinting connections with a common application value, after it is configured, the user-defined server can be used to easily identify which active jobs are running on behalf of a specific application.

The ability to assign the server type is not limited to server jobs. The application itself can be changed to have the same value as the server jobs. If the application uses a batch immediate interface or spawn() API to initiate new jobs, those jobs can also be tagged with the same server type and thus be included in the user-defined server group.

Program enablement
One approach to deploy this solution is to build a program that is similar to the one shown at the end of this Technote (QCSRC.SRVRTYPE). After the program exists, the application can be updated to use SQL to CALL the program as a stored procedure:

CALL <lib>/SRVRTYPE

By using SQL as the mechanism to call the program that contains the call to the QWTCHGJB() API, the application seamlessly identifies the appropriate QSQSRVR jobs. The SRVRTYPE program can be instrumented to work for many application instances by changing the Server Type value to be an input parameter. This specific example uses a hard-coded Server Type value.

Conclusion
While the server type does not add any value for System i users of the 5250 panel, it provides a useful mechanism to logically group together work based upon an application instance identifier. After it is instrumented on the host and configured on System i Navigator, you can graphically review server group activity and directly interact with the jobs.

In the "rogue job" example mentioned previously, this approach to application grouping can greatly accelerate the time that is necessary to analyze the origin and ownership of QSQSRVR jobs. Best of all, the steps described in this Technote can be deployed in a System i environment at V5R3M0 or later.

Practical example
This example demonstrates how you can use a seldomly used System i Navigator feature to configure system activity into logical, application-oriented groups. The reason for forming these groups might be inspired by a desire to understand QSQSRVR job activity, but the groups themselves are not limited to QSQSRVR jobs. Any job that tags itself with the specific server type is displayed under the User Defined Server Group.

1. In System i Navigator, expand Network->Servers->User-Defined. Right-click and select New Server.

System i Navigator : User-Defined->New Server

2. In the New Server - Welcome window, click Next.

New Server - Welcome window

3. In the New Server - Name window, type a unique name for the server and click Next.

New Server - Name window

4. In the New Server - Description window, type the description and click Next.

New Server - Description window

5. In the New Server - Type window, specify the server job type. It is essential that the value match exactly the value that is passed to the QWTCHGJB() API. Click Next.

New Server - Typ window

6. In the New Server - Server ID window, type a server ID. The server ID is not important in this sense, because the user-defined server is not used to automatically start jobs, but rather to logically group jobs together. Click Next.

New Server - Server ID window

7. In the New Server - Program Name and Library window, enter a value for the server program and library. As with the Server ID, the server program is also unimportant. Click Next.

New Server - Program Name and Library window

8. In the New Server - Autostart window, accept the default value, which instructs TCP/IP to not take action. Click
Next.

New Server - Autostart window

9. In the New Server - Summary window, verify if the information is correct. Click Back to make any changes. Otherwise, click Finish.

New Server - Summary window

10. In System i Navigator, after the user-defined server group is configured, when you expand Servers-> User-Defined, you see the group in the right pane. Right-click the group name and select the Server Jobs option.

Selecting the Server Jobs

11. The Server Jobs window opens and shows the set of active jobs, which match the unique Server Type value. To update the list of active jobs, click the refresh icon to update the view. Direct job interaction is enabled.

Server Jobs window

12. To view database specific information, right-click the job name and select Details->SQL.

Server Jobs window

The SQL Details window opens in which you can view the job details.

SQL Details for Jobs window

Setting the Server Type value
Every job has a Server Type value, which is a 30-character value. System jobs and prestart jobs are given an initial default value. For prestart jobs, such as QSQSRVR, since all the QSQSRVR jobs normally have the same value, applications need to be changed to imprint the QSQSRVR jobs that they use with a specific value. Consider that a QSQSRVR job is assigned to the application whenever a connection is formed. When that connection exists, all SQL activity for that connection takes place in the QSQSRVR job.

The following example shows how a program can be called to set the Server Type value. For SQL users, an SQL CALL statement can be used as the mechanism for calling the program. Through the use of SQL, the application is able to set the value in the associated QSQSRVR jobs.

/*

Build commands:
CRTCMOD MODULE(SCOTTF/SRVRTYPE) SRCFILE(SCOTTF/QCSRC) OUTPUT(*print)
CRTPGM PGM(SCOTTF/SRVRTYPE) MODULE(SCOTTF/SRVRTYPE) ACTGRP(*CALLER)

*/
/* Change job API - server type */
#include <qusec.h>
#include <QWTCHGJB.h>
#include <stdio.h>

typedef struct jobChangeInfo
{
Qus_Job_Change_Information_t fieldNum;
Qus_JOBC0100_t format;
char data[31];
} jobChangeInfo_t;

int main (int argc, char *argv[])
{
Qus_EC_t EcStruc;
Qus_EC_t* PtrEcStruc; /* iSeries structure for API errors. */
jobChangeInfo_t chg = /* Job change information for QWTCHGJB */
{
1, /* Number of variable length records. */
46, /* Length of attribute information */
1911, /* Key - Server Type. */
'C', /* Type of Data - Character. */
0X40,0X40,0X40, /* Reserved */
30, /* Length of data with server name. */
"MY_SERVER_NAME_25 "};

PtrEcStruc = &EcStruc; /* API error code structure. */
PtrEcStruc->Bytes_Provided = 16; /* Initialize bytes provided. */
PtrEcStruc->Bytes_Available = 0;
/*-------------------------------------------------------------------*/
/* API used for defining a server name in the job. */
/*-------------------------------------------------------------------*/

QWTCHGJB("* ",
" ",
"JOBC0200",
&chg,
PtrEcStruc);

if ( EcStruc.Bytes_Available != 0 ) {
printf("Error changing job server name. Application ended.");
printf("Error message id %s sent from QWTCHGJB API.",
EcStruc.Exception_Id);
exit(-1);
}
return 0;
}


Reference material
For more information, refer to the following resources:

Special Notices

This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment. publib-b.boulder.ibm.com

Profile

Publish Date
11 October 2007


Rating:
(based on 1 review)


Author(s)

IBM Form Number
TIPS0659