Guru Classic: Tuning SQL Environments On i

March 4, 2020 Paul Tuohy

Author’s Note: This article was originally published in November 2015. This was one of the first DB2 for i Services that I used in anger. I have used a lot since.

One of the frustrating things about being a speaker at conferences is that when you want to attend another session with a topic that piques your interest, it invariably clashes with when you are speaking. But every now and again, the scheduling gods work in your favor. Such was the case when I spoke at the excellent International i-Power 2015 conference at Wyboston Lakes Executive Centre in the UK.

Scott Forstie, IBM’s DB2 for i Business Architect, was giving a presentation on “IBM i Services – SQL interfaces into the IBM i operating system.” I had recently done an iTalk with Scott where we had chatted on this very topic and I was anxious to learn more.

I must admit that my interest in the topic was more on the use of SQL as opposed to what the services were. After all, I am a developer and what would I care about ops/admin services. But right at the very start, there was one service that more than piqued my interest. So, with thanks to Scott, let me share this with you.

The Problem

There are now myriad ways that users can connect to the database on i. Developers using Run SQL Scripts, Data Studio, the Database Perspective in RDi or some other database development client. A PHP or Java application connecting to the database from a server running on i. A client application connecting to the i through a JDBC or ODBC connection. Users using a reporting tool that constructs SQL statements on the fly. And I am sure there are a host of other possibilities that I am missing!

Each of these connection methods can have different performance/tuning requirements. Power users need resource whereas a query tool should not be allowed to overwhelm the system.

The problem is that the server jobs that do all of the SQL processing for the client requests will be QZDASOINIT or QRWTSRVR jobs running in the QUSRWRK subsystem.

There was no way of tuning which jobs should be associated with which client requests.

But, as of V7R1 TR10 or V7R2 TR2, you can now have QZDASOINIT and/or QRWTSRVR jobs running in specified subsystems based on the user profile (or group profile or supplemental profile) used to make the database connection. You now have very specific control over database connections to i.

Creating A Subsystem

I, of course, am a power user. I deserve my own subsystem with all of the resources that my SQL creations demand.

To start, using our friendly command line, we define a subsystem and a corresponding job queue.

CRTSBSD SBSD(QGPL/ALLFORPAUL) POOLS((1 *BASE))
        TEXT('Subsystem for Pauls SQL Masterpieces')
CRTJOBQ JOBQ(QGPL/ALLFORPAUL) 
        TEXT('JOBQ for Pauls SQL Masterpieces')              
ADDJOBQE SBSD(QGPL/ALLFORPAUL) JOBQ(QGPL/ALLFORPAUL)
         MAXACT(100) SEQNBR(40)

We follow up by defining a class. The class object will define the time slice and run priority for the QZDASOINIT and QRWTSRVR jobs.

CRTCLS CLS(QGPL/ALLFORPAUL) RUNPTY(55) TIMESLICE(100) 
       TEXT('Class for Pauls SQL Masterpieces')

With the class object in place, we need to update the subsystem description to ensure that the class is used for the QZDASOINIT and QRWTSRVR jobs when they are running in the subsystem.

ADDPJE SBSD(QGPL/ALLFORPAUL) PGM(QSYS/QRWTSRVR) 
       JOBD(QGPL/QDFTSVR) CLS(QGPL/ALLFORPAUL) 
ADDPJE SBSD(QGPL/ALLFORPAUL) PGM(QSYS/QZDASOINIT)
       JOBD(QGPL/QDFTSVR) CLS(QGPL/ALLFORPAUL)

Ready to go. Start up the subsystem.

STRSBS SBSD(QGPL/ALLFORPAUL)

Changing The Routing

V7R1 TR10/V7R2 TR2 introduced a new stored procedure, QSYS2.SET_SERVER_SBS_ROUTING, which can be used to identify which subsystem the QZDASOINIT and/or QRWTSRVR jobs should run in for a specific user profile.

In this example, to ensure that the QZDASOINIT and QRWTSRVR run in the ALLFORPAUL subsystem when my profile make a connection to the database, I run the following in Run SQL Scripts:

CALL QSYS2.SET_SERVER_SBS_ROUTING('PAULT','*ALL','ALLFORPAUL');

The three parameters are:

The user profile, group profile or supplemental profile.
The job name — QZDASOINIT, QRWTSRVR or *ALL (for both).
The name of the subsystem.

Although the configuration details are stored within the *USRPRF object, there is a catalog, QSYS2.SERVER_SBS_ROUTING, which is used to access the alternative subsystem user configuration.

select * from QSYS2.SERVER_SBS_ROUTING;

This would show us:

Be aware that this select statement may take a while to run. QSYS2.SERVER_SBS_ROUTING is a view over a table function that retrieves the routing information from the user profile objects; therefore, the user profile objects are being accessed to retrieve the information, which may take a while.

Checking It Out

We could, of course, use the Work with Active Jobs (WRKACTJOB) command to see all of the jobs running in the ALLFORPAUL subsystem. But let’s take the opportunity to look at one of the other SQL services added in TR10/TR2. QSYS2.ACTIVE_JOB_INFO() is a User Defined Table Function that returns information similar to the detail seen from the WRKACTJOB command and the Open List of Jobs (QGYOLJOB) API.

SELECT JOB_NAME, AUTHORIZATION_NAME,
       ELAPSED_CPU_PERCENTAGE, 
       ELAPSED_TOTAL_DISK_IO_COUNT, 
       ELAPSED_PAGE_FAULT_COUNT 
FROM TABLE(ACTIVE_JOB_INFO(
             SUBSYSTEM_LIST_FILTER => 'ALLFORPAUL')) x  
ORDER BY ELAPSED_CPU_PERCENTAGE DESC 
FETCH FIRST 20 ROWS ONLY;

This would show us:

Bits And Pieces

What if the subsystem has not been started when a profile, for which a routing entry has been defined, connects to the database? No problem, the routing will just default back to the QUSRWRK subsystem.

I really like the fact that the routing can be based on a group profile or supplemental profile. You do not have to go to the individual profile level for routing definitions.

For most of us, at the moment, all of the SQL jobs running in QUSRWRK works just fine. But as the use of database grows on your system, there will come the time when you will need to tune for different connection environments. The solution is to hand.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search