Tuning SQL Environments On i

One of the frustrating things about being a speaker at conferences is that when you want to attend another session with a topic that peaks 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 recently, when I was speaking at the excellent International i-Power 2015 conference.

Scott Forstie, IBM‘s DB2 for i business architect, was giving a presentation, “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 so why would I care about ops/admin services? But right at the very start, there was one item service that more then piqued my interest. So, with thanks to Scott, let me share it with you.

The Problem

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

Each of these connection methods can have different performance/tuning requirements. Power users need resources to know where 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 resource 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:

1. The user profile, group profile or supplemental profile
3. The job name–QZDASOINIT, QRWTSRVR or *ALL (for both)
4. 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 command will 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 command will 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.

Further Reading

You can check out all of the DB2 for i Services, along with the required TR for each release here.

For those of you in or near the U.K., keep up to date with user group events at http://www.i-ug.co.uk/.

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.