Thu, Jun
4 New Articles

TechTip: Members Only? No Problem with DB2 Web Query

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Access your multi-member files, let your users dynamically select the member they want to query at run time, and create a report that uses the latest and greatest database engine technology.


Over the last several years, I have written several articles about DB2 for i and DB2 Web Query, some of which emphasized the importance of employing techniques to ensure that your queries are using the SQL Query Engine (SQE), the strategic database engine for DB2 for i. One of the requirements for SQE processing is that the database access method must be SQL. However, one of the very few drawbacks with SQL access on our beloved system is that it does not natively support the concept of physical file members. This means you cannot write an SQL Select statement that directly references a specific member. When the file is queried, SQL can access only the first member in the file. This in turn means that the DB2 Web Query CLI adapter (the one that generates SQL to satisfy the query request) cannot directly access a member either. If you have DB2 Web Query, this can be frustrating because you may have hundreds or even thousands of files that have valuable data tucked away in individual members.


Fortunately, all is not lost. DB2 Web Query provides another adapter, the DB Heritage File, that lets you access specific members. You can use this adapter to configure a DB2 Web Query synonym that references a specific physical file member. Any report that is based on such a synonym will generate a database request using the OPNQRYF command (which supports the direct access of a physical file member). Problem solved, right?


Well, yes and no. Yes, because this will work. You can create snazzy reports, graphs, and dashboards using DB Heritage File synonyms to effectively reach the data in members. But remember what I said about SQE? It will be used to process a query only if that query is based on an SQL statement. OPNQRYF is not SQL, thus the older Classic Query Engine (CQE) must be used to perform the query workload. While CQE is no slouch when it comes to handling database requests, in most cases, this means a slower-running query. It also means you can't take advantage of all the "goodies" that SQE provides, such as the SQE plan cache, maintained temporary indexes, and materialized query tables (MQTs). I could go on and on about the benefits of SQE, but just know this: IBM has not enhanced CQE in a long time and will not in the future because SQE is the strategic engine.


OK, cheer up. I didn't write this article to put you in a surly mood. Even though SQL does not support the direct access of a specific member, there is a way to "trick" it into doing just that. Ever heard of an SQL alias? An alias is an alternate name for a table or view. If you were to look up the CREATE ALIAS statement in the SQL reference manual, you would find this:


The CREATE ALIAS statement defines an alias on a table, partition of a table, view, or member of a database file at the current server.


Wait! Did you see that word? Member? Yes, you actually can create an SQL alias that points to a specific physical file member. You can then reference the alias in an SQL Select statement to gain access to the data in the specific member. To give you an example, let's say you have a multi-member physical file named ORDERS that resides in the QGPL library. ORDERS has several members, one for each year between 2001 and the current year. To create an alias that points to a specific member, you would execute an SQL statement like the following:




In this example, you created an SQL Alias named ORDERS_MBR that is based on the specific member named ORDERS2005. This means any SQL statement that references that alias (like the one below) will query only the data in the ORDERS2005 member.




Since SQL is used for data access, SQE can be used to handle the request.


In DB2 Web Query, you can also use the CLI adapter to create a synonym based on an SQL alias. This means you can create a DB2 Web Query report that is based on a specific member and uses SQE! Woohoo!


But wait, we're not done yet. I am often asked how a DB2 Web Query report developer can provide an interface that allows the end user to select only a specific member to query at run time. While the list of members for a physical file can be obtained from the system catalog SYSPARTITIONSTAT in the QSYS2 schema, the development tools in DB2 Web Query do not provide a facility that gives the end user this kind of interface. But in my experience with DB2 Web Query, whenever I can't do something with the tools, I often find I can accomplish what I want by writing my own code and integrating it into DB2 Web Query using views and stored procedures. This is another such case. Instead of basing my synonym on the SQL alias, I instead create one that references a stored procedure. The procedure does the underlying "dirty work": it accepts a member name as an input parameter, dynamically creates an alias (in QTEMP) that references the passed-in member name, declares a cursor that (when opened) will return all the rows and columns in the alias, and finally, opens the cursor to return the result to DB2 Web Query.


I can also create an SQL view over that SYSPARTITIONSTAT catalog to return the list of members for my physical file. Once a synonym has been created over this view, it can be used as the data source to populate my report's auto-prompt drop-down list. To illustrate this process, let's work through an example.

An Example

In the following example, I take you through all the steps required for creating a DB2 Web Query report that prompts the end user for the desired member to query, executes the query, and returns the results to the browser. Oh, and this query uses SQE too. An example of this report is shown in Figure 1.



Figure 1: Here are the results for our example. (Click images to enlarge.)


1. First, open an SQL interface (such as Run SQL Scripts from System i Navigator) and create an SQL stored procedure that accepts a member name as an input parameter. This procedure will create an SQL alias that points to the member value passed in. It then returns all rows and columns in the alias (because it opens a cursor based on the SELECT * FROM ORDERS_MBR statement). An example is shown below.












--Take the inputted member name and create an SQL alias






2. To test the stored procedure, call it from the SQL interface and make sure it returns the appropriate data.


3. Create a DB2 Web Query synonym over the stored procedure as shown in Figure 2.


Figure 2: Create a DB2 Web Query synonym.


4. Click the Next button to display the following screen. From here, find and select the stored procedure from the presented list. An example is shown in Figure 3.



Figure 3: Select the stored procedure.


5. Click the Next button to display the screen below.

As shown in Figure 4, specify a valid (member name) value for the input parameter. This is required because DB2 Web Query actually runs the stored procedure during this step so that it can generate a sample result set necessary for generating the format (columns) of the synonym.

It is also recommended that you supply a suffix of "_SP" to indicate that this is a stored procedure data source. This is not required, but it may be useful for your report developers. It will tell them what kind of data source the synonym is based on.


Figure 4: Specify a valid value for the input parameter.


6. Click the Create Synonym button to finish this task and close the browser window.


7. Return to your SQL interface and create an SQL view that returns all the member names for the multi-member file using the system catalog QSYS2/SYSPARTITIONSTAT. This view will be used to populate the drop-down list so the user can select which member to query at run time.






8. Create a DB2 Web Query synonym over this new SQL view. An example is provided in Figure 5.



Figure 5: Create a DB2 Web Query synonym.


9. Now you are ready to create your report. Open Report Assistant and select the new stored procedure synonym, as shown in Figure 6.



Figure 6: Select the new stored procedure synonym.

10. Recall that we want to dynamically prompt the users for the member name by allowing them to select from a list of members in a drop-down list. To set this up, go to the Selection Criteria tab, drag MEMBERNAME from list of Available Fields to Screening Conditions, and click the <Select values> link. See Figure 7.



Figure 7: Allow users to select members from a drop-down list.

11. As shown in Figure 8, select Parameter and click on the Auto Prompt icon.



Figure 8: Select Parameter and click on the Auto Prompt icon.

12. From the Data Source drop-down list, select the synonym of the SQL view created in step 8 and select the field TABLE_PARTITION. This will populate the drop-down list with all the member names for the ORDERS file. Figure 9 shows an example.



Figure 9: Populate the drop-down list with all the member names.

13. Click OK twice.

14. Finish your report definition by specifying Field selection, Report headings, and Report options.


15. Save your report.

16. Run your report. As shown in Figure 11, a drop-down list is presented to prompt you for the member to query.



Figure 10: You are prompted for the member to query.

17. Click the Run button. The results are shown in Figure 12.



Figure 11: You have your results!

Note: If you did not want a drop-down list for the member names, you would simply specify a parameter name in step 12 and skip steps 7, 8, and 12 . This would present a text box from which you could type in the member name.


What About Joins?

In the above example, all of the columns in my report definition were taken from the single physical file member (or segment). These columns were actually returned from the result set of the stored procedure that was called during report execution. For many reports, you may need to join to other related files in order to get all of the fields needed to satisfy the report requirements. To do this, you could certainly use the Join Options tab in Report Assistant to join the stored procedure segment to another file (thereby creating a second segment). The problem with this approach will be report performance. Because the data retrieval for the first join segment is buried in (and performed by) the stored procedure, there is no way for DB2 Web Query to generate a single SQL statement to perform the join. This means that the merging of the two join segments must be performed by the DB2 Web Query Reporting Server. While the performance of this may not be bad for smaller files, you are going to experience a longer response time for larger data sets.


In these situations, I strongly recommend that you specify all of the join syntax in the stored procedure itself. This will result in a single SQL statement being submitted to the DB2 engine. An example of the modified DECLARE CURSOR statement (from the stored procedure) is shown below.








Specifying the join in the stored procedure has several desirable effects. Here are a couple of them:


  • The report developer does not need to be intimate with the data model and know what files to join and what the correct join columns are.
  • The workload of processing the join logic will be pushed down to the database engine and will result in a faster-running report.


An example report that is based on columns from multiple join segments (returned from a stored procedure) is shown in Figure 13.



Figure 12: This report is based on columns from multiple join segments.


Membership Need Not Be Exclusive!

If your company has been running on the IBM i platform for awhile, chances are good that you have some multi-member physical files in your database. It is a very useful and common implementation; in fact, almost every customer I talk to has them in their shop. As such, many companies absolutely need to pull valuable business data from this type of data source. Armed with this technique, you can now use SQL and DB2 Web Query to extract this information, provide an interface that lets your end users access the data they need, and enjoy all the benefits of SQE processing.


Membership does have its privileges!

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



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: