29
Fri, Nov
0 New Articles

Query Manager Made Simple

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

Does this scenario sound familiar? Your company has a few super-users who have created oodles of custom reports using Query/400. Now they want these reports made available to other users or run as part of your nightly processing. Did I mention that they want to be able to enter selections for date range or other criteria each time the report is run? If you’ve come across such a situation, you know your options. You write an RPG program to replace the query; put the query itself on a menu, using RUNQRY with the RCDSLT(*YES) option so that the record-selections screen is displayed before running the query; or lock the person who wrote the query in a closet and throw away the key.

Each one of these options has drawbacks. If you write an RPG program, it may take days to replace a query that took someone only minutes to create. Using RCDSLT(*YES) is less time consuming; however, the record selection screen can often be awkward and doesn’t allow for any type of validation. In addition, the query runs interactively, which can be a major drain on your system’s resources. As for the last option, that could involve jail time and would waste valuable closet space.

There is another option. Query Management gives you the flexibility of an RPG program and, with the addition of DB2 UDB for AS/400 Query Manager, is as easy to use as Query/400. Query Management is included as a component of OS/400 and gives you a set of commands that can be used to run, create, or retrieve source for Query Management objects. DB2 UDB for AS/400 Query Manager, on the other hand, is a separately licensed program product that provides a more user-friendly interface to Query Management objects. It allows you to customize Query Management queries using standard SQL commands. You can also pass parameters to a Query Management query, so you can create a front-end to prompt for selection criteria, then submit the report to batch with that selection criteria.

However, there are drawbacks to using Query Management queries. For example, you can’t do outer joins—that is, a query where you want all records from your primary file, regardless of whether or not matching records exist in your secondary files. But the pros far outweigh the cons, and after reading this article, you’ll see why.

Query Manager Basics

Before creating a report using Query Manager, take a look at the components of a Query Manager report. The two basic parts of a report in Query Manager are the Query Management query, which describes your data, and the Query Management form, which



formats the report. The Query Management query itself is basically an SQL statement that defines what data appears on the report and the file, or files, containing this data. The Query Management form defines page formatting, such as page headings, column headings, column formatting, subtotals, groupings, and level-break text. Think of the Query Management query as the right side of the brain and the Query Management form as the left side. Now that you have a good picture of what makes up Query Manager, you can start building reports with Query Manager.

Morphing Query/400 Queries

One of the great things about Query Management is that you can easily convert a Query/400 query definition into a Query Management query and/or a Query Management report form. To follow along with the examples in this article, you should first create two source physical files: QQMQRYSRC and QQMFORMSRC. Be sure to replace the default source file record length of 92 with a record length of 91. This will prevent the Query Management commands from returning a warning message when they are used. These files will be used to store the source members for your Query Manager objects. You will also want to select a Query/400 definition to work with.

Converting a Query/400 object is a four-step process. The first step is to retrieve the source for the Query Management query by using the Retrieve Query Management Query (RTVQMQRY) command. This command extracts SQL source for a Query Management query from your Query/400 query definition into a source file. The key is to specify *YES for the Allow Information from QRYDFN (ALWQRYDFN) command. Type the following command:

RTVQMQRY +

QMQRY(yourlib/qryname) +

SRCFILE(yourlib/QQMQRYSRC) +

ALWQRYDFN(*YES)

The second step is to retrieve the form for this query into a source file by using the Retrieve Query Management Form (RTVQMFORM) command. Enter the command like this:

RTVQMFORM +

QMFORM(yourlib/qryname) +

SRCFILE(yourlib/QQMFORMSRC) +

ALWQRYDFN(*YES)

The third step is to create the Query Manager query by using the Create Query Management Query (CRTQMQRY) command, which should look like this:

CRTQMQRY +

QMQRY(yourlib/qryname) +

SRCFILE(yourlib/QQMQRYSRC)

The fourth and final step is to create the Query Management form by using the Create Query Management Form command(CRTQMFORM). That command should look like this:

CRTQMFORM +

QMFORM(yourlib/qryname) +

SRCFILE(yourlib/QQMFORMSRC)



That is all that’s required to convert a Query/400 query definition into a Query Manager report. Now I’ll explain how to work with these objects in Query Manager.

Working with Query Manager Queries

As I mentioned, you could create Query Manager objects by creating the source code, but an easier way is with the Start Query Manager (STRQM) command. Figure 1 (page 79) shows the main menu for DB2 for AS/400 Query Manager. Option 1 allows you to display and modify a Query Manager query. (Remember that the Query Manager query defines what appears on the report, while the Query Manager report form defines formatting.) The screen displayed in Figure 1 shows all Query Manager query objects in the current library. The TYPE field shows either SQL or PROMPT. The difference between these two is that a PROMPTED QM query uses prompts to guide query definitions where SQL statements define a QM query.

When creating new Query Manager queries from this screen, you are probably better off creating them in PROMPT mode. It’s simpler to create a query this way, and you can convert a prompted Query Manager query to SQL by taking option 10. For now, look for the Query Manager query you created earlier from your converted Query/400 query, and take option 2. The screen displayed will look similar to the SEU editing panel, and contain the converted SQL code from your original query definition. Pressing F15 from this screen will check the syntax of your SQL code. Pressing F5 will display a prompt where you specify a Query Manager report form and whether to run the query interactively or in batch.

You can also pass parameters to a Query Manager query. In the example shown in Figure 2, at line 10, the WHERE clause contains the parameters &INVOICE and &CONM. Exit the Edit Query screen by pressing F3. Then press F12 to go back out to the Query Manager menu screen.

Query Manager Report Forms

To Work with Query Manager report forms, take option 2 from the main screen of DB2 Query Manager for AS/400. Now locate the form you created earlier, and type option 2 to edit. The resulting screen is similar to that of Query/400 but has a few more bells and whistles. Type a 1 next to Edit column formatting. Here you define column attributes, including field size, edit codes, and column headings. Press F12 to go back to the previous screen, and type a 1 next to Edit page heading. This screen allows you to define headings for your report. This is one of the areas where Query Manager is more flexible than Query/400. On this screen, you can not only define static text and page number or date and time information, but you can also grab data from your query and display it in the page heading. This is done by referencing the column (field) from the Edit column formatting screen in the format &col, where col represents a field name. You can also define multiple lines of page-heading text to appear on the report. (I’ll cover this later.)

For now, press F12 to go pack to the previous screen. Edit page footing works the same way as the previous option, except that the information entered is printed at the bottom of each page. Edit final text allows you to define what text to print at the end of the report. This feature is similar to Edit page heading/footing but supports page-number or date/time information.

The Edit break text option can only be used if you have one or more break levels defined on your report. Break levels are defined on the Edit column formatting screen by entering a value from BREAK1 (highest level) through BREAK6 in the usage column. Once you have defined break levels, you can specify heading and footing text to appear before or after each level break on the report. The break heading/footing options allow flexibility in printing column data, with the added bonus of being able to skip to a new page before the heading or after the footing, as well as optionally reprinting the column headings. These options allow you to customize the look of your Query Manager report.



Finally, formatting options allow you to specify line spacing, separators, column headings, and break text.

Separators appear on the report as a series of equal sign (=) characters. Now that you have examined each section of the Query Manager report, you can put it all to use.

Putting It All Together

To follow the example in this article, go to the Midrange Computing Web site (www.midrangecomputing.com/mc) and download the code associated with this article. In this example, you will use Query Manager to create a customer invoice form. To do this, you need to create three files: a customer master (CUSTMAST) file, an invoice header (INVHDR) file, and an invoice detail (INVDTL) file. To create these files, put the SQL code from the member called CRTFILES into a source file named QSQLSRC. Now compile the CL program CTF001CL using the Create CL Program (CRTCLPGM) command:

CRTCLPGM +

PGM(libname/CTF001CL) +

SRCFILE(libname/QCLSRC)

Next, type CALL CTF001CL PARM(yourlib), where yourlib is the library that contains the QSQLSRC file you created.

Now copy the source member INVOICEQMQ to your QQMQRYSRC file and copy INVOICEQMF to your QQMFORMSRC file. Create the Query Manager query INVOICEQMQ and the INVOICEQMF form by typing the following commands:

CRTQMQRY +

QMQRY(yourlib/INVOICEQMQ) +

SRCFILE(yourlib/QQMQRYSRC)

CRTQMFORM QMFORM(yourlib/INVOICEQMF) +

SRCFILE(yourlib/QQMFORMSRC)

Next, look at the Query Manager query and form. Type STRQM to bring up the Query Manager/400 main menu. Enter option 1 to display the Work with Query Manager Queries screen. Use option 2 on this screen to modify the design of the INVOICEQMQ query. You will be presented with an editor screen similar to that of SEU. The SQL code displayed joins the invoice-header, detail, and customer-master files.

Notice that the final field selected is &CONM AS COMPNAME. This creates a parameter that allows you to pass a company name to the query that displays on the top of the report form. You’ll also notice that the WHERE clause contains the condition WHERE (INV# = &INVOICE). This creates a parameter called INVOICE, which allows us to specify the invoice number that is printed.

Pressing F5 prompts for any parameters used and displays the results of the SQL statements. You must enclose text fields like the company name in single quote (‘) characters when prompted or when supplying the values through the SETVAR parameter on the STRQMQRY command. If not, Query Manager will mistake the value as a field name and an error will be returned. In my example, when prompted for INVOICE, enter a number from 99 to 102 to display sample invoice data created with by the CTF001CL program.

Press F3 to exit this display, then press F12 to return to the main menu of Query Manager/400. Take option 2 to show the Work with Query Manager Report Forms display. Place a 2 in the option field next to the form named INVOICEQMF to modify the design of the invoice form.



Now take option 1 to edit the column formatting. You will notice that the customer name and address fields have the usage keyword OMIT. This prevents these fields from printing on every line of the printed output but allows access to these fields for printing on the page heading. The invoice quantity (INVQTY) and invoice amount (INVAMT) fields have their usage keywords set to SUM, which causes summaries to be displayed for these fields at the end of each invoice. Press F12 to return to the Select Report Format prompt.

Place a 1 next to the Edit Page Headings option. You’ll notice that you are able to define multiple lines of text that display the customer name and address in addition to the invoice number, invoice date, and other header information. These can be left-justified, right-justified, or centered, which really allows you to customize the page headings. The number of text lines used for the page headings is limited only by the size of the printed page. Refer to the columns from a Query Manager query using an ampersand (&) followed by the column number. The column number should match the sequence shown on the column formatting display. Press F12 to exit this screen.

Next, place a 1 next to the Edit final text option. Blank lines before is set to BOTTOM. This causes the total values to print at the bottom of the page. Press F12 to exit, and continue to press F3 until you return to a command prompt.

Now that you have examined the objects, try printing the invoice. Use the Start Query Management Query (STRQMQRY) command:

STRQMQRY +

QMQRY(INVOICEQMQ) +

OUTPUT(*PRINT)+

QMFORM(*LIBL/INVOICEQMF) +

DATETIME(*NO) +

PAGNBR(*NO) +

SETVAR((INVOICE &INVNA) +

(CONM ‘’’WIDGET WAREHOUSE’’’))

The CL program PIN001CL, on the Midrange Computing Web site, prints a sample invoice. To run this program, type CALL PIN001CL PARM(101). The parameter supplied is the invoice number. Your printed output should resemble the invoice shown in Figure 3 (page 81). That is all that’s required to generate an invoice from Query Manager/400.

More Powerful Queries

It’s easy to convert a Query/400 query definition into Query Manager. Query Manager’s flexibility makes it almost as capable as an RPG program for reports. The more you explore Query Manager, the more you will be amazed at what you can do.

The next time you must generate a custom report, consider using Query Manager instead of an RPG program or Query/400. Query Manager will help you to better manage your queries—and your time.

REFERENCES AND RELATED MATERIALS

• DB2 for AS/400 Query Manager Use (SC41-5212-01)
• DB2 UDB for AS/400 Query Management Programming (SC41-5703-01)



Query_Manager_Made_Simple06-00.png 545x294

Figure 1: Type STRQM to access the main menu of DB2 for AS/400 Query Manager.

H QM4 05 Q 01 E V W E R 01 03 01/03/12 12:59
V 1001 050 Sample Invoice QM Query

SELECT

A.CUSTNO, A.CUSNAM, A.CUSAD1, A.CUSAD2, A.CUSCTY, A.CUSSTT, A.CUSZIP,

A.CUSPHN, B.INV#, B.INVDTE, B.INVCUS, C.IND#, C.INDLIN, C.INVDES,

C.INVQTY, C.INVPRC, INVQTY * INVPRC AS INVAMT, &CONM AS COMPNAME

FROM CUSTMAST A,

INVHDR B,

INVDTL C

WHERE (INV# = &INVOICE)

AND (CUSTNO = INVCUS)

AND (INV# = IND#)

Figure 2: This is sample source for a Query Management query.

3

BILL TO: W-s Widget World INVOICE WIDGET WAREHOUSE

1600 Pennsylvania Avenue INVOICE #: 101

Washington, DC 11111 INVOICE DATE: 3,172,000

PHONE: (800)555-1212

Line # Item Description Qty. Price/EA Line Amount

-

1 WIDGETS-8 INCH 12.00 2.00 24.0000

2 FREIGHT 1.00 5.75 5.7500

====== =============

13.00 29.7500

Figure 3: The Query Manager report form INVOICEQMF creates an invoice.



Mike Faust

Mike Faust is a senior consultant/analyst for Retail Technologies Corporation in Orlando, Florida. Mike is also the author of the books Active Server Pages Primer, The iSeries and AS/400 Programmer's Guide to Cool Things, JavaScript for the Business Developer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..


MC Press books written by Mike Faust available now on the MC Press Bookstore.

Active Server Pages Primer Active Server Pages Primer
Learn how to make the most of ASP while creating a fully functional ASP "shopping cart" application.
List Price $79.00

Now On Sale

JavaScript for the Business Developer JavaScript for the Business Developer
Learn how JavaScript can help you create dynamic business applications with Web browser interfaces.
List Price $44.95

Now On Sale

SQL Built-in Functions and Stored Procedures SQL Built-in Functions and Stored Procedures
Unleash the full power of SQL with these highly useful tools.
List Price $49.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

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: