• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Tuning SQL Environments On i

    November 3, 2015 Paul Tuohy

    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
    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 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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Manta Technologies

    The Leader in IBM i Education!
    Need training on anything i?
    Manta is all you need.

    130 courses and competency exams on:
    · IBM i operations
    · System Management and Security
    · IBM i Programming Tools
    · Programming in RPG, COBOL, CL, Java
    · Web Development

    SQL, DB2, QueryProduct features:
    · Runs in every popular browser
    · Available 24/7/365
    · Free Student Reference Guides
    · Free Student Administration
    · Concurrent User License
    · Built-In IBM i Simulator

    You can download our 200-page catalog and take sample sessions at MantaTech.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    NGS:  Nov. 18 FREE Webinar: Migration Alternatives for Query/400 Users
    HelpSystems:  How do you use IBM i? Your peers want to know! Take the survey >
    Rocket Software:  Mobile app development and deployment solution for IBM i. Download FREE trial!

    First Open Source Conference For IBM i Shops Planned IBM i Executives: Where Are They Now?

    One thought on “Tuning SQL Environments On i”

    • Richard Bryant says:
      February 9, 2017 at 7:31 am

      Be careful, *ALL will change other services you may not want to change. I did this and lost access to JDBC authorize and RDI failed with error QZRCSRVS job not found. This IBM doc shows you how to reset – http://www-01.ibm.com/support/docview.wss?uid=nas8N1021000. I then ran the SQL script qualified with QZDASOINIT and QRWTSVR.

      Reply

    Leave a Reply Cancel reply

Volume 15, Number 23 -- November 3, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
Connectria
United Computer Group, Inc.

Table of Contents

  • Tuning SQL Environments On i
  • Why And How To Update The HMC
  • Extracting Sample Data From A DB2 for i Table

Content archive

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

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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