Query/400 is IBM's licensed program product to provide quick, ad hoc reporting capabilities to the DB2/400 (also known as DB2 for OS/400) database. It's a menu-driven query tool that builds interactive reports that can be displayed on the screen, printed, or outputted as unique databases. It uses IBM's powerful Query Optimizer to find the fastest, most efficient pathways to DB2/400's relational database. Finally, it's a product that puts the power of DB2/400 directly into the hands of the users. It's easy to use, fast, and fully functional. Query definitions can be saved and incorporated into the daily routines of the organization.
This article examines Query/400 and shows how to build a simple query report by joining two relational database files. If you've never worked with Query/400 before, you'll be surprised at the flexibility of this product. With a little practice, your users can build sophisticated reports with little or no programming skills.
A Simple Example
Our simple example will use two files: a Vendor Master file called VNDRMST and a Vendor History file called VNDRHST, which contains the record of transactions purchased from various vendors. We'll create a report that shows all the transactions of Vendor Number 30 in reverse chronological order by date of purchase.
To create this report without Query/400, a programmer would normally perform three unique steps:
1. Create an override to the database file using the Override Database File (OVRDBF) command.
2. Build an open data path (ODP) by joining the VNDRMST file to the VNDRHST file using the Open Query File (OPNQRYF) command.
3. Write an RPG program to display or print the records.
Your users can do all this quickly using only Query/400. All it takes is a little exposure and a modicum of training.
What You Need: Query/400
First of all, you should realize that the full implementation of Query/400 is not part of the base operating system, but an add-on IBM program product (5763QU1). It's just one of seven query products and utilities that IBM provides for DB2/400. (These products include SQL/400, SQL/400 Query Manager, Query/38, the OPNQRYF command, PC Support File Transfer, OS/400 Query Management, and the Query/400 product reviewed here).
This has created a bit of confusion. Is Query/400 the same as SQL/400? What about OPNQRYF or PC Support's File Transfer Function? Is there a significant difference? To answer these questions, you need to understand the DB2/400 Query Optimizer.
The DB2/400 database has basic built-in query capabilities that allow the user to build virtual database pathways called Open Data Paths (ODPs). To do this, the AS/400 uses a portion of the underlying OS/400 code, called the Query Optimizer. All of IBM's utilities use the Query Optimizer, including SQL/400, OPNQRYF, PC Support File Transfer, and Query/400. So, the capability to search and query is native to DB2/400.
The utilities to build the search and query specifications, however, are the IBM program products themselves. In other words, IBM's program products, such as SQL/400, PC Support/400 File Transfer, and Query/400, are user front-ends to create the query code. Once the query code is created and compiled using one of these front-ends?such as the Query/400 example illustrated here?it will run on any AS/400. That means you can distribute the resulting Query/400 code objects just as you might distribute any other program: as compiled object code without the source (see the "Running Queries Outside of Query/400" sidebar for more information).
Building the Simple Query?An Overview
We begin creating our sample query by keying in the Work with Queries (WRKQRY) command. Since we're creating a query source definition, we're prompted to provide a name for the new query and to identify a library into which to place the definition. We can create, change, copy, delete, display, print, or run a definition through this maintenance prompt. Since we want to "create" a new definition, we'll take option 1 (see 1).
We begin creating our sample query by keying in the Work with Queries (WRKQRY) command. Since we're creating a query source definition, we're prompted to provide a name for the new query and to identify a library into which to place the definition. We can create, change, copy, delete, display, print, or run a definition through this maintenance prompt. Since we want to "create" a new definition, we'll take option 1 (see Figure 1).
2 shows the Define the Query screen with a list of potential tasks. There are quite a few options, including specifying the file or files to query, selecting records, sorting, and report formatting. We don't have to complete all the tasks in order for our query to function. Query/400 has a "natural" set of defaults, so the only thing we have to do is identify the database file we want to investigate.
Figure 2 shows the Define the Query screen with a list of potential tasks. There are quite a few options, including specifying the file or files to query, selecting records, sorting, and report formatting. We don't have to complete all the tasks in order for our query to function. Query/400 has a "natural" set of defaults, so the only thing we have to do is identify the database file we want to investigate.
Specifying Files?The Simple Join
Press the Enter key and specify the files to be queried. If you're selecting more than one file, you must use the Add file function (F9). 3 shows that I've selected the VNDRMST file and the VNDRHST file. You can select the specific file member and format within the database, but you'll most likely always use the default *FIRST. The File ID field allows you to specify a tag to help you identify the individual fields within the database files. This is important because, by convention, separate fields within separate database files sometimes have the same field names. The use of a File ID allows us to identify which field belongs to which file. We'll see how this works later on.
Press the Enter key and specify the files to be queried. If you're selecting more than one file, you must use the Add file function (F9). Figure 3 shows that I've selected the VNDRMST file and the VNDRHST file. You can select the specific file member and format within the database, but you'll most likely always use the default *FIRST. The File ID field allows you to specify a tag to help you identify the individual fields within the database files. This is important because, by convention, separate fields within separate database files sometimes have the same field names. The use of a File ID allows us to identify which field belongs to which file. We'll see how this works later on.
Normally, if we were querying a single file, our file selection process would be complete. Since we're specifying more than one file (VNDRMST and VNDRHST), however, we have to identify exactly how these two databases are related. (Query/400 assumes we want to join the files.) In our example, pressing the Enter key will present us with a join selection screen (see 4).
Normally, if we were querying a single file, our file selection process would be complete. Since we're specifying more than one file (VNDRMST and VNDRHST), however, we have to identify exactly how these two databases are related. (Query/400 assumes we want to join the files.) In our example, pressing the Enter key will present us with a join selection screen (see Figure 4).
In Query/400, you can join up to 32 files in three different manners: by matching all records that have a common join field; by matching all records against a file that is designated as the "primary" file; or by showing all records that are unmatched against the primary file. The primary file might be thought of as the root reference point containing the common references to the other files, such as the VNDRMST file used in our example.
The join criteria can get pretty esoteric, so, for the purposes of our example, we'll keep it simple. In this example, we want to find every VNDRHST record that is matched against our primary VNDRMST file.
Query/400 then needs to know how to join VNDRMST to the VNDRHST file. This is the purpose of the Specify How to Join Files Screen (see 5). All the fields are available as join fields?not just particular "key" fields. These fields are listed on the bottom half of the prompt shown in 5. If you don't remember what the fields represent, you can use the F11 key to display their text descriptions.
Query/400 then needs to know how to join VNDRMST to the VNDRHST file. This is the purpose of the Specify How to Join Files Screen (see Figure 5). All the fields are available as join fields?not just particular "key" fields. These fields are listed on the bottom half of the prompt shown in Figure 5. If you don't remember what the fields represent, you can use the F11 key to display their text descriptions.
The File ID parameter we talked about earlier is where that tag comes in handy. In our example, Query/400 automatically identified the VNDRMST file as T01 and the VNDRHST file as T02. Within these two databases, both files have a Vendor Number field called VNDRNO. Query/400 allows us to join these two files using the literal names T01.VNDRNO and T02.VNDRNO.
Query/400 performs this join based on the relationships of fields between each joined file. We can join based upon these fields being Equal, Not Equal, or Less Than, for example. This makes Query/400 more powerful than some other products that join files based on the presence of matching fields.
In our simple example, we're joining the VNDRMST file (T01) to the VNDRHST file (T02) if the VNDRNO fields within each file are the same (EQ).
Selecting Fields and Query Records
Pressing the Enter key establishes the join between these two files and makes all the associated fields within the files available to our query. If the database has been robustly defined, this could present us with an overwhelming number of fields for our report, probably more than we actually need. Query/400 gives us the ability to winnow out and use only those fields we need. This is accomplished with the Select and Sequence Fields option shown in 6.
Pressing the Enter key establishes the join between these two files and makes all the associated fields within the files available to our query. If the database has been robustly defined, this could present us with an overwhelming number of fields for our report, probably more than we actually need. Query/400 gives us the ability to winnow out and use only those fields we need. This is accomplished with the Select and Sequence Fields option shown in Figure 6.
In our example, we're creating an activity report of purchases from the VNDRHST file. The fields we'll select are VNDRNO, Vendor Name, a description of the purchase, a PO number, and a purchase date. By putting in sequence numbers beside the fields, we're not only selecting the field to be used in the report, but we're also placing them in the order in which we want them to appear. The use of the T01 or T02 tags in the example points Query/400 to the appropriate database file.
Within our example, we want to see only the purchases from a particular vendor whose VNDRNO is 30. No doubt, this is a very limited query. However, if you look at the sidebar, you'll see how you can create user-definable selection criteria with any VNDRNO from a basic query such as this.
To extract the VNDRNO 30 records from history, choose the Select Records prompt (see 7). Select Records provides us with full Boolean selection criteria of all the fields selected from the files. You can build up quite complex selection criteria using and/or statement extensions. This selection criteria is not available for all the fields present within all the database files, but merely those we've previously selected and chosen to use in the report back at the Select Fields prompt. This is because Query/400 builds an ODP to the various joined files based upon the fields selected.
To extract the VNDRNO 30 records from history, choose the Select Records prompt (see Figure 7). Select Records provides us with full Boolean selection criteria of all the fields selected from the files. You can build up quite complex selection criteria using and/or statement extensions. This selection criteria is not available for all the fields present within all the database files, but merely those we've previously selected and chosen to use in the report back at the Select Fields prompt. This is because Query/400 builds an ODP to the various joined files based upon the fields selected.
Sorting Fields
In our sample query, we want to order our records in reverse chronological order so we can see the most recently purchased product. 8 shows how this is accomplished. You sort all of the fields selected in any order you want and in ascending (A) or descending (D) sequence.
In our sample query, we want to order our records in reverse chronological order so we can see the most recently purchased product. Figure 8 shows how this is accomplished. You sort all of the fields selected in any order you want and in ascending (A) or descending (D) sequence.
We could run this query and have the results displayed on our screen. Before doing that, look at the Specify Report Column Formatting function shown in 9. This function allows us to customize the headings of each field within the report, much like a simple report designer. If your DB2/400 database has been defined through DDS to have specific column headings (using the COLHDG DDS keyword), those column headings will be the defaults displayed in the report. Too often, a lazy programmer will neglect to provide adequate or appropriate headings for each field in the database. This?unfortunately?forces Query/400 to use the actual field names of each field for the column headings, which often doesn't present a very friendly heading. Therefore, customizing the report columns is an essential part of creating a usable query.
We could run this query and have the results displayed on our screen. Before doing that, look at the Specify Report Column Formatting function shown in Figure 9. This function allows us to customize the headings of each field within the report, much like a simple report designer. If your DB2/400 database has been defined through DDS to have specific column headings (using the COLHDG DDS keyword), those column headings will be the defaults displayed in the report. Too often, a lazy programmer will neglect to provide adequate or appropriate headings for each field in the database. This?unfortunately?forces Query/400 to use the actual field names of each field for the column headings, which often doesn't present a very friendly heading. Therefore, customizing the report columns is an essential part of creating a usable query.
Query/400 automatically places two spaces between each column heading. We can increase or decrease this distance as we like. The three blank lines beside each field allow us to have up to three lines to describe each column. If we want to truncate the field on the report, we can change the field length. Finally, we can press the F16 key to format a numeric field to use standard RPG edit codes (such as suppress zeros or add dollar signs) or to create custom edit words for the display of the digits.
Testing the Query
You're probably wondering what our simple query looks like. We can take a look at the query by pressing the F5 key any time after the files have been selected. Pressing F5 builds the ODP interactively and displays the result onto the screen. Of course, building an ODP interactively is resource-intensive, so, if your users do this a lot while they're fine-tuning their queries, you may notice a sudden degradation of the system.
A less intensive means of fine-tuning the query is to use the Report Layout function, which is activated by the F13 key. This function doesn't interactively build the ODP, but it presents a display with all the field positions represented on-screen, complete with field column headings. If you find your users abusing the F5 function, point them to this option instead.
Defining Output Types and Forms
The final step is to tell Query/400 what to do with the output of our query. We can make the output always go to a display, a printer, or to another file. This is shown in 10. Query/400 defaults its output to the display, which makes building quick status reports for viewing from a screen pretty easy.
The final step is to tell Query/400 what to do with the output of our query. We can make the output always go to a display, a printer, or to another file. This is shown in Figure 10. Query/400 defaults its output to the display, which makes building quick status reports for viewing from a screen pretty easy.
If we want to send the report to a printer, a series of other options follows, enabling us to specify report headings and line wrapping, among other things. Many of these functions are defined in the Specify Report Breaks option, so you can determine exactly how you want your report to appear.
You can use the Summary Only form for output. This option combines with the use of report breaks to create summaries of columns and then print only the totals.
Also, take note of the output type of database file. This option will allow you to create a new file containing only the fields referenced in the query definition. One great use of this function is to create quick extracts of databases for distribution to other report writers or PC utilities, such as spreadsheets. The output is a complete DB2/400 file with only the fields you identify. See the sidebar for more information.
Other Query/400 Capabilities
Some of the other functions Query/400 supports include the following:
? Defining Result Fields, by which we can create new in-memory fields and field derivatives from two or more database fields.
? Change the Collating Sequence, by which we can modify the normal sorting hierarchy of DB2/400.
? Create Custom Report Headings for our specific query.
? Change the Form Size, so we can create rudimentary labels or other custom forms.
Also, look at the sidebar, "Running Queries Outside of Query/400," to see how you can integrate Query/400 into your daily routine. You'll want to experiment with these capabilities after you've mastered the basics.
Saving the Query Definition
Pressing F3 will prompt you to verify the name of your query definition and to give it a meaningful object description (see 11). If you're programming this query for production use, you can assign all the appropriate levels of authority just as you might with any other high-level language (HLL) program. Saving the definition actually builds the Query/400 code and saves it as a query definition object (*QRYDFN), ready for use or distribution. To make changes to the definition, go back through the WRKQRY command, use Option 2 (Change), and specify the Query name and its library. It's not much different from working with any program object on the AS/400.
Pressing F3 will prompt you to verify the name of your query definition and to give it a meaningful object description (see Figure 11). If you're programming this query for production use, you can assign all the appropriate levels of authority just as you might with any other high-level language (HLL) program. Saving the definition actually builds the Query/400 code and saves it as a query definition object (*QRYDFN), ready for use or distribution. To make changes to the definition, go back through the WRKQRY command, use Option 2 (Change), and specify the Query name and its library. It's not much different from working with any program object on the AS/400.
Query/400 Wrap-up
Query/400 is one of those all-purpose database tools that make DB2/400 one of the most popular and powerful databases in the midrange marketplace. It's quick, easy to use, and highly functional. If you've already purchased it in your shop, try giving it to the users and watch as their creativity blossoms. If you've not yet taken the plunge, then think about all of that user creativity you're wasting. Building queries with Query/400 is the simplest, fastest way to get your users the information they need to do their jobs.
Thomas M. Stockwell is a senior technical editor for Midrange Computing.
Getting Started with Query/400
Running Queries Outside of Query/400
Once a Query/400 definition has been created by Query/400, it can be distributed as object code and run from any AS/400. The command to do this is RUNQRY [library name/query name].
There are some distinct advantages to distributing queries in this manner. First of all, the RUNQRY command will allow the user to prompt for different selection criteria beyond the original definition. For instance, in our example, we selected a specific VNDRNO of 30, but the user isn't limited to that Vendor number. By keying
RUNQRY VNDRHST001 RCDSLT(*YES)
the user will be launched directly into the Record Selection screen of the query specification (see 7). This will allow the user to create a completely different set of record selection criteria, making the initial query definition do double duty as a template for a similar report.
the user will be launched directly into the Record Selection screen of the query specification (see Figure 7). This will allow the user to create a completely different set of record selection criteria, making the initial query definition do double duty as a template for a similar report.
Second, if your users want to make a database extract and they know of a query that closely resembles the needed information, it's a snap to change the output by typing this:
RUNQRY VNDRHST001 OUTTYPE(*OUTFILE) + OUTFILE(filename)
A new database file will be created, and no new programming has been involved.
You may want to take a closer look at all the various options available to the RUNQRY command. It brings the power of the query definitions created by Query/400 back down to the command line, where nearly anyone can customize them.
Getting Started with Query/400
Figure 1: Work with Queries
Getting Started with Query/400
Figure 2: Define the Query
Getting Started with Query/400
Figure 3: Specify File Selections
Getting Started with Query/400
Figure 4: Specify Type of Join
Getting Started with Query/400
Figure 5: Specify How to Join Files
Getting Started with Query/400
Figure 6: Select and Sequence Fields
Getting Started with Query/400
Figure 7: Select Records
Getting Started with Query/400
Figure 8: Select Sort Fields
Getting Started with Query/400
Figure 9: Specify Report Column Formatting
Getting Started with Query/400
Figure 10: Select Output Type and Output Form
Getting Started with Query/400
Figure 11: Exit Query
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online