StarQuest Technical Documents

Creating a subsystem for SQDR Plus jobs (IBM DB2 for i)

Last Update: 25 June 2018
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL040

Abstract

SQDR Plus obtains change data from the IBM i server using iAccess jobs for remote command (QZRCSRVS), database (QZDASOINIT) and file access (QPWFSERVSO). Routing connections to a particular subsystem based on the user ID or originating IP address allows you to tune performance on a busy system.

This technical document describes how to create a new subsystem and route SQDR Plus connections based on the user ID.

Solution

• Prerequisites
• Create the new subsystem and configure routing
• Use the new subsystem
• Tuning the subsystem
• Starting the subsystem at IPL time
• Routing DRDA jobs
• References

Prerequisites

To route by user ID, you must be running one of the following:

• i 7.1 with technical refresh TR10, DB2 group PTF level 38, and PTF SI59913
• i 7.2 with technical refresh TR2, DB2 group PTF level 9, and PTF SI59914
• i 7.3 with PTF SI59915

Routing by IP address has been supported since OS/400 v4r4, and is configured using IBM Navigator for i. See the references below for details.

You also need sufficient authority on the IBM i system to make the system changes described below.

Create the new subsystem

1. Create a library:
   CRTLIB SQDR
2. Create a class. The class defines certain performance characteristics for your subsystem including run priority, time slice, and default wait times:

   CRTCLS SQDR/SQDR RUNPTY(20) TIMESLICE(2000) DFTWAIT(30) TEXT('SQDR Class')
   

3. On systems with elevated security, examine the authority of the newly-created class; modify it to match the same authority as QSYSCLS20:
   GRTOBJAUT OBJ(SQDR/SQDR) OBJTYPE(*CLS) USER(*PUBLIC) AUT(*USE)
4. Copy the QUSRWRK subsystem description:
   CRTDUPOBJ OBJ(QUSRWRK) FROMLIB(QSYS) OBJTYPE(*SBSD) TOLIB(SQDR) NEWOBJ(SQDR)
5. Replace the Job Queue entry in the subsystem:
   RMVJOBQE SBSD(SQDR/SQDR) JOBQ(QUSRNOMAX)
   CRTJOBQ JOBQ(SQDR/SQDR)
   ADDJOBQE SBSD(SQDR/SQDR) JOBQ(SQDR/SQDR) MAXACT(*NOMAX)
   
6. Add a prestart job entry for QPWFSERVSO (File). Note that prestart job entries for the other iAccess host servers were included when we copied QUSRWRK, but the default location of QPWFSERVSO is in the QSERVER subsystem.
   ADDPJE SBSD(SQDR/SQDR) PGM(QSYS/QPWFSERVSO)
7. Use WRKSBSD (SQDR/SQDR) to examine the other prestart job entries and change the associated class if desired by removing and recreating the prestart job entry e.g.
   RMVPJE SBSD(SQDR/SQDR) PGM(QZRCSRVS)
   ADDPJE SBSD(SQDR/SQDR) PGM(QSYS/QZRCSRVS)
   

Use the new subsystem

1. Run the following in STRSQL or another ad-hoc query tool to configure the routing:
   CALL QSYS2.SET_SERVER_SBS_ROUTING('SQDR', '*ALL', 'SQDR')
2. Verify the routing change:
   SELECT * FROM QSYS2.SERVER_SBS_ROUTING WHERE AUTHORIZATION_NAME='SQDR'
3. Start the new subsystem
   STRSBS SBSD(SQDR/SQDR)
4. Use the SQDR Control Center to restart the SQDR Plus agent.
5. Verify that the SQDR jobs are running in the new subsystem with:
   WRKACTJOB SBSD(SQDR)
   then select F14 to include Prestart jobs
   You should see that all the jobs from your SQDR user are now running in the new subsystem.
   

Tuning the subsystem

Now that the SQDR jobs are separated into their own subsystem, you can adjust performance attributes, such as run priority, time slice, default wait, maximum CPU and others are performance attributes specified in the class description. Storage pools and maximum jobs are specified on the subsystem description

For instance, if the default class description has a run priority of 50:

DSPCLS CLS(SQDR/SQDR)

Run priority . . . . . . . . . . . . . . . . . . : 50
Time slice in milliseconds . . . . . . . . . . . : 2000
Default wait time in seconds . . . . . . . . . . : 30

Then use the following command to change future jobs to a run priority of 20 (the default priority for interactive jobs in the QINTER subsystem). It is not necessary to restart the subsystem or the SQDR Plus agent.

CHGCLS CLS(SQDR/SQDR) RUNPTY(20)

Starting the subsystem at IPL time

Edit your IPL startup program (QSTRUP) to automatically start the subsystem at IPL time.

Routing DRDA jobs

In addition to routing iAccess jobs (used by SQDR Plus), you can use the same technique to route DRDA (QRWTSRVR) jobs (e.g. using StarSQL from the SQDR service). In addition to the subsystem configuration, verify that the PTF (or superseding PTF) for APAR SE67211 (SI64754 for i 7.1; SI64753 for i 7.2; SI64752 for i 7.3) is applied. These PTF's (or superseding) are included in recent cumulative PTF packages.

References

• Routing Connections to Subsystems Based On User ID
• SET_SERVER_SBS_ROUTING procedure
  
• QSYS2.SET_SERVER_SBS_ROUTING() - procedure (TR Update wiki)
• Host Server Customization, Routing Connections By IP Address
• Routing server jobs based on client IP address
• Creating a user-defined server subsystem
• Changing the IPL Start-Up Program