24
Mon, Jun
3 New Articles

Maximize SQL Query Engine (SQE) Usage of Your DB2 Web Query Reports

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times
Ensure top-notch DB2 Web Query reports by using QAQQINI, exit points, database monitors, and Visual Explain.

 

In July of 2007, IBM announced DB2 Web Query for System i, a product positioned as a query and reporting tool for the System i and the strategic enhancement to the Query/400 tool. DB2 Web Query has many advantages over Query/400. Most notably, it provides modernized development and end-user interfaces via a Web browser as well as modern report output formats such as HTML to the browser, Excel spreadsheets, and PDF. For more information on DB2 Web Query, download the IBM Redbook titled Getting Started with DB2 Web Query for System i.

 

In this article, I explain how DB2 Web Query accesses data from the DB2 for i5/OS database and how you can use various tools available on the System i to influence certain optimization behaviors. The ultimate objective is to ensure that DB2 Web Query reports are using the latest IBM technologies and obtaining optimal data access performance.

 

When accessing data from DB2 for i5/OS, DB2 Web Query uses one of three data adapters:

 

·        DB2 CLI—Generates an SQL statement and submits a Call Level Interface (CLI) API request to the database engine.

 

·        DB Heritage File—Generates an OPNQRYF CL command to submit to the database engine. Used to access multi-member files and multi-member logical files.

 

·        Query/400—Uses the RUNQRY CL command to run an existing Query/400 query (*QRYDFN object).

 

Of the three adapters, only DB2 CLI can leverage the database's new SQL Query Engine (SQE). SQE is a totally redesigned query engine and was first introduced with V5R2 of i5/OS. It employs efficient, advanced techniques when optimizing queries, creating access plans, and accessing data. Prior to SQE, all query requests were processed by the Classic Query Engine (CQE).

 

DB2 for i5/OS actually uses both engines to handle all query requests. As of V5R4, the most common items and environments that prevent SQE from executing an SQL statement have been these:

 

·        Non-SQL interfaces (Query/400, OPNQRYF, QQQQry API)

 

·        Upper, Lower, or Translate function

 

·        National language sort sequence

 

·        User-defined table function (UDTF)

 

·        DDS LOGICAL file references on FROM clause

 

·        Select-Omit or derived logical files (non-standard indexes) defined on underlying table

 

This means that even though DB2 Web Query generates and submits an SQL statement to the database, there may still be cases in which the request is processed by CQE. For most of the restrictions listed above, the only way to obtain SQE processing is to completely remove the offending condition. However, if you are one of many shops that uses Select-Omit logical files in your applications, read on, as there is a method to allow peaceful Select-Omit logical file and SQE coexistence.

Avoiding CQE

Many legacy System i applications rely heavily on the use of Select-Omit logical files; therefore, the underlying database tables, when referenced by an SQL statement, are ineligible for SQE processing. It is important to clarify that the mere existence of a Select-Omit logical over a table will cause the query to go down CQE, even when it's the table, not the logical file itself, that is referenced in the statement. Consequently, when any SQL interface (including DB2 Web Query via the DB2 CLI adapter) references such a table, the query will be optimized and processed by CQE. In most cases, this is not the desired behavior.

 

In a DB2 Web Query environment, you can influence the optimizer to ignore the fact that these Select-Omit logical files exist, thereby clearing the path for SQE processing. While it is true that a possible remedy would be to remove those Select-Omit logical files, it is a solution fraught with risk because existing applications likely rely on the business logic contained in these files. It is better to craft a solution that will not impact the behavior and environment of other applications.

 

To minimize disruption while accomplishing the stated objective, two System i technologies can be used together:

  • QAQQINI table

     

  • CLI connect exit point

     

QAQQINI

QAQQINI is known as the query options table. It is a special table that DB2 for i5/OS uses to change/override the default behavior of the query engines (both CQE and SQE). Each system is shipped with a template QAQQINI file in the QSYS library. If this file is copied to the QUSRSYS library, its options are in effect for all users on the system. However, if you do not want its settings activated globally, you can copy the file to another library and use the CHGQRYA command to instruct the database to only use the settings for the file in that specific library and only for the job that issued the command.  

 

One of the QAQQINI settings is IGNORE_DERIVED_INDEX. The default value of this setting is currently *NO, which means that the optimizer will not ignore derived indexes, so impacted queries must be processed by CQE. Changing this value to *YES allows SQE to process the query even when a derived key or select/omit index exists over a table in the query. When the query is run with this setting in place, SQE ignores the derived indexes over the referenced table and is allowed to process the request (barring any other SQE inhibitors).

 

Normally, you do not have to use QAQQINI as a database-tuning mechanism. Running without a QAQQINI file (which would be the same as running with the file using its default settings) provides good performance to the majority of System i customers. In fact, an important change planned for V6R1 is to change the default of the IGNORE_DERIVED_INDEX to *YES. As a result, all derived indexes and Select-Omit logical files will be ignored by default, and SQE processing can occur without intervention. But in the interim, you can use the technique described in this article.

CLI Connect Exit Point

DB2 Web Query has three adapters that you can use to process a query request. When creating a new report, unless you need to access a multi-member file, a multi-format logical file, or an existing Query/400 object, the adapter you will be using is the DB2 CLI adapter. As described previously, when this adapter is used, the reporting server component of DB2 Web Query will generate an SQL statement to satisfy the request and submit the statement via a CLI request. These requests are handled by the QSQSRVR (pre-start) jobs on the System i. This is known as "server mode," and each time one of these jobs is used in this mode, a connection event occurs. The CLI connect exit point allows control to be passed to a user-written program each time one of these connection event occurs. By now, you probably see where this is going: Use this exit point to invoke a program each time a DB2 Web Query report is run. This program will direct the database to use a version of QAQQINI that instructs the database to ignore derived logical files, clearing the path for SQE processing.

 

By marrying these two technologies, we can allow other existing applications to run and continue to use Select-Omit logical files, while enabling DB2 Web Query requests to use SQE.

Determining Whether a Solution Is Needed

Before implementing the solution proposed in this article, you may want to first determine whether the described Select-Omit logical file and CQE scenario is occurring in your DB2 Web Query environment. Note: These same detection steps could be used for any SQL workload.

 

To do this, take the following steps:

 

 

  1. Start an SQL performance monitor.

     

Sign on to a 5250 session and start a database monitor, using some of the new filtering options available in V5R4. In the following example, I use the FTRFILE and FTRUSER parameter to filter what data is collected. For DB2 Web Query report testing, database monitor data is only collected for all files queried in library QZRE for queries requested by user profile COBBG.

STRDBMON OUTFILE(QGPL/CQE_TEST1) JOB(*ALL) FTRFILE(QZRE/*ALL) FTRUSER(COBB)

 

Note: You can also use iSeries Navigator to start a database monitor. This tool provides a graphical, wizard-like interface to start the monitor. For more information, consult the IBM Redbook OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4.

 

 

  1. Run your DB2 Web Query report(s).

     

    3.      Analyze results.

From an SQL interface (STRSQL or the Run SQL Scripts window in iSeries Navigator), analyze the data collected in the database monitor collection. The following SQL statement can be used for this type of analysis:

 

WITH STATEMENTS AS (                                            
  SELECT DISTINCT QQUCNT, QQJFLD, QQ1000                        
  FROM cqe_test1                                               
  WHERE QQRID=1000 AND  QQC21 IN ('OP','SI','UP','IN','DL')  )  
SELECT                                                           
   a.qvc43,                                                     
   COUNT(*) AS COUNT,                                           
   B.QQ1000 AS STATEMENT                                        
FROM cqe_test1  A, STATEMENTS B                                
   WHERE QQRID = 3014                                           
   AND QQc16 ='N'  AND A.QQJFLD=B.QQJFLD                        
GROUP BY a.qvc43, B.QQ1000                                      
ORDER BY 2 DESC    

 

 

After running this statement against the collected monitor data, look for rows where the QVC43 column equals DK. This value indicates that CQE processing occurred because "An index with derived key or select/omit was found over a queried table." Here is an example of how this would look:

 010908Cobb_CQE_Analysis.gif

(Click images to enlarge.)
 

For more information on the QVC43 column and the other types of information captured in the SQL performance monitor collection, see the online IBM publication DB2 Universal Database for iSeries Database Performance and Query Optimization, which can be downloaded from the i5/OS Information Center Web site.

 

In addition, you can use the iSeries Navigator tools to find and analyze the data collected in the database monitor and run the Visual Explain tool to see the optimization details of the captured query. In the example shown in Figure 1, notice that the reason SQE was not used is "Derived Key of Select/Omit Index Exists."

 

 010908Cobb_Figure1_Visual_Explain_CQE.gif

 

Figure 1: The Visual Explain tool allows you to see the optimization details of the captured query.

 

Note: The Visual Explain query engine feedback indicators require that you install the latest iSeries Access fixpack and Database Group PTFs.

Setting Up the Environment

Once you have determined that the proposed solution is needed in your environment and you're armed with the knowledge of the technologies to be used, the next step is to perform the tasks necessary to implement it:

 

  1. Create and update a new version of QAQQINI.

     

  2. Create the program to be called by exit point.

     

  3. Activate the exit point.

     


Create and Update a New Version of QAQQINI

Since you want to ignore only derived indexes for DB2 Web Query requests, a global version of QAQQINI in the QUSRSYS library is undesirable. You want to activate this setting only for DB2 Web Query requests made through the CLI interface. To do that, you first need to create an "isolated" version of the query options file by following these steps:

 

  1. Create a new copy of the file. From the command line, issue this command:
    CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(COBBG) DATA(*NO) TRG(*YES)

     

  2. Populate the new file with the IGNORE_DERIVED_INDEX setting. From an SQL interface, issue this statement:
    INSERT INTO COBBG/QAQQINI VALUES('IGNORE_DERIVED_INDEX', '*YES', NULL)                                                           

     

Create the Program to Be Called by Exit Point

The next step is to create a CL program that will be called by the exit point. When creating the program for this exit point, the only requirement is that you must specify an input parameter for the user profile that is running the job (opening the connection). The example program shown below uses the CHGQRYA command to instruct the database to use the specified query options file and honor its settings for the current connection:

 

                                                
PGM        PARM(&CURUSER)                     

DCL        VAR(&CURUSER) TYPE(*CHAR) LEN(10)
DCL        VAR(&FORUSER) TYPE(*CHAR) LEN(10)
                                  
RTVDTAARA  DTAARA(COBBG/CLIEXITDA (1 10)) RTNVAR(&FORUSER)
IF         COND((&FORUSER = '*ALL') *OR (&FORUSER = +
             &CURUSER)) THEN(CHGQRYA QRYOPTLIB(COBBG))

 

ENDPGM       

 

 

Notice in the example program that a data area object is being used as a "switch" to effectively turn off/on the activation of the query options file. If the value in the data area is *ALL or equals the name of the current user profile, the CHGQRYA command is executed to instruct the database optimizer to use this query options file for the current connection. If the data area contains any other value, the switch is turned off and the CHGQRYA command is not executed. This method makes it easy to enable and disable the functionality.

 

Note: The example program uses the data area simply to demonstrate this switching technique. It is not required for the exit program and could be eliminated if desired.

 

 

To create this supporting data area, issue the following command:
CRTDTAARA DTAARA(CLIEXITDA) TYPE(*CHAR) LEN(10) VALUE(*ALL)

 

 

When you're finished writing the program, issue the CRTCLPGM command against the CL source member to create the new program object.

Activate the Exit Point

The final step is actually activating the exit point so that the program is called each time a CLI connect event occurs. To do this, issue the following i5/OS command (specifying the exit program name and library created previously):

ADDEXITPGM EXITPNT(QIBM_QSQ_CLI_CONNECT) FORMAT(CLIC0100) PGMNBR(1) PGM(COBBG/CLIEXIT)                           

Verifying the Results

Once everything is in place, the verification process described previously can be repeated to determine whether the solution had the desired effect:
  1. Start another database monitor.

     

  2. Run the DB2 Web Query report(s) again.

     

  3. Analyze/compare results using SQL analysis statement and Visual Explain.

     

 

After running Visual Explain against the same queries in the captured database monitor, you should see some differences. For those same queries, the monitor data should no longer contain rows in which the value of the QVC43 column is DK. The Visual Explain graphical rendering should also look different. An example is shown in Figure 2.

 

 010908Cobb_Figure2_Visual_Explain_SQE.gif

 

Figure 2: The Visual Explain graphics of the SQE processed query should look different now.

 

 

Several interesting things should be pointed out from this Visual Explain example:

 

  • The Ignore Derived Index setting was implemented.

     

  • The Query Engine used was SQE.

     

  • The access plan is different from the one generated by the CQE optimizer. As mentioned previously, the SQE optimizer was redesigned with the intent of formulating more-optimal access plans. Though your results may vary, a better plan should result in a better-performing query.

     

Other Considerations

You should consider several other factors when putting this solution into practice. 

Exit Program Already Defined

It is quite possible that you have other applications that are using CLI to access information in the database, and as such, you already have a program defined for the QIBM_QSQ_CLI_CONNECT exit point. Prior to implementing the solution, this can be verified by taking these steps:

 

  1. Issue the command WRKREGINF EXITPNT(QIBM_QSQ_CLI_CONNECT).

     

  2. From the Work with Registration Information screen, select option 8 to see if a program is already defined for this exit point.

     

If there is another exit program defined and you don't wish to disrupt this process, implementing this solution will require program consolidation. This means you will have to incorporate the CHGQRYA command into the existing exit point program.

 

Indexing Strategies

Because the IGNORE_DERIVED_INDEX setting does exactly that (forces SQE to ignore all non-standard indexes), the new optimizer cannot use this index during query implementation. In some cases, this may in fact mean that the optimal index exists but cannot be used by SQE because it is being ignored. Consequently, it is suggested that you do some additional analysis to determine if the key fields of those indexes should be used to create new standard indexes or DDS logical files (without the Select-Omit or derivation fields). Doing so would mean that SQE could recognize and use these indexes.

 

Here are some ideas for this kind of analysis:

 

·        Determine whether the CQE access plan used any non-standard indexes.

 

·        Run the query and look for index advisories issued by the SQE optimizer. This can be done using Visual Explain or the new V5R4 Index Advisor interface.

 

·        Experiment! Create the non-standard indexes as standard ones and see if SQE uses them. If it does not, simply delete them.

 

For more information on indexing strategies, download IBM's white paper "Indexing and statistics strategies for DB2 for i5/OS."

 

Other Ideas for Exit Point Integration

The CLI exit point can be used in a DB2 Web Query environment for more than just influencing SQE usage. You could also use it to accomplish other objectives:

 

·        Activate the Symmetric Multiprocessing (SMP) feature so that the database engine can use more than one processor to handle your DB2 Web Query requests.

 

·        Activate the optimizer's inclusion of Materialized Query Tables (MQTs) when considering what resources to use in its access plan.

 

·        Activate the QAQQINI QUERY_TIME_LIMIT option. This setting specifies a time limit that queries are allowed to run. If activated, the optimizer compares its time-to-run estimate with the specified setting. If the estimate exceeds the setting, the query is not allowed to run.

Getting the Best Results

In this article, I have introduced you to several tools available in the System i toolset: the QAQQINI query options file to influence optimizer behavior, exit points to provide customized end-user control at key system intervals, database monitors to capture optimizer decisions, and Visual Explain to drill down to see the details of those decisions. When used together, these tools can help ensure that your DB2 Web Query reports and graphs are using the latest and greatest IBM technologies.

Gene Cobb

Gene Cobb is a DB2 for i5/OS Technology Specialist in IBM's ISV Business Strategy & Enablement for System i group. He has worked on IBM midrange systems since 1988, including over 10 years in the IBM Client Technology Center (now known as IBM Systems and Technology Group Lab Services). While in Lab Services, he assisted customers with application design and development using RPG, DB2 for i5/OS, CallPath/400, and Lotus Domino. His current responsibilities include providing consulting services to System i developers, with a special emphasis in application and database modernization. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: