• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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:

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

    Further Reading

    You can check out all of the DB2 for i Services, along with the required TR for each release, at https://www.ibm.com/support/pages/node/1119123.

    For those of you in the UK, or within easy reach of the UK, keep up to date with user group events at http://www.i-ug.co.uk/.

    Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guruclassic, API, DB2 for i, FHGC, Four Hundred Guru Classic, IBM i, Java, JDBC, ODBC, PHP, RDi, Run SQL Scripts, SQL

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Guru Classic: Overlaid Packed Data In Data Structures Guru Classic: More iSphere Goodies

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 16

This Issue Sponsored By

  • RPG & DB2 Summit
  • RPG & DB2 Summit
  • RPG & DB2 Summit

Table of Contents

  • Guru Classic: More iSphere Goodies
  • Guru Classic: Tuning SQL Environments On i
  • Guru Classic: Overlaid Packed Data In Data Structures

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20
  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle