Access Your iSeries Data Quickly and Simply

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

To complement the reports and inquiries provided by their standard ERP system, most iSeries shops use some kind of reporting tool to provide extra information to their users.

Whether we use Query to drive an iSeries report, SQL or OPNQRYF to provide green-screen or Web inquiries, or a PC data analysis tool via ODBC, the system always uses the same API set to retrieve the data and return it to the calling application. Probably the most important component—and frequently the least understood element—of this API set is the Query Optimizer. Although this routine has only one purpose (namely, to decide the quickest method of accessing the data requested), it is one of the most sophisticated and intelligent routines that runs on the iSeries.

The likelihood is that the Query Optimizer is sitting in the background all the time on your system, being called many times every hour and making thousands of decisions every day about how to access your data in the most efficient method.

By default, this logic is hidden from view. This article will show you how to easily see the decisions the optimizer makes and how to improve its performance by judiciously creating logical files or indexes. At the end, you will see a real example of how this works. First, let's look at how to access the decisions the Query Optimizer has made.

(Note: Please bear in mind that whenever I refer to Query, this is a generic term meaning Query400, SQL, or OPNQRYF.)

How to Gather Information About Query Optimizer Decisions

As is always the case with the iSeries, there is more than one method of achieving this. If you turn on debug for a job by using STRDBG UPDPROD(*YES) and then run a query, the decisions the optimizer makes will be placed into the joblog. Alternatively, you can modify the query options file QAQQINI to log the information. You could use APIs in your code to do this, but by far the easiest method I have found is to use the STRDBMON command:


Running this command just once will cause OS/400 or i5/OS to log details of all query optimization decisions made across your whole system to a file called DBMON in library DBQRYLOG. The library has to already exist, but the file will be created if necessary. This file will give you all the information you could ever require about the use of queries on your system. And the command will stay in force until you stop it, even after a system IPL. When you wish to end the monitoring, just enter this command:


The file is structured so that there are different record formats, providing varied information about actual query usage. The first field on the file (QQRID) is the record identifier. An ID of 1000 is the "header" information about the query and can be used to identify the user and job details of the query, and the date and time the query started, and the source of the query (Query, SQL, or OPNQRYF). Similarly, if you look at records with an ID of 3007, field QQ1000 contains details of all the access paths that were considered by the optimizer. Details here are the library name and file name followed by a code. A code of zero means that this access path was used by the query. A non-zero value means that this access path was not selected—for example, a code of 5 means that the keys of the access path do not match, while a code of 19 means the access path cannot be used for a join as it contains select omits.

The records that we are going to concentrate on here are those in which Query Optimizer recommends that an access path be created. These are any that have a Y in field QQIDXA.

How to Interpret the Query Optimizer Data

By far the biggest impact on your data retrieval performance will be the creation of the indexes recommended by Query Optimizer. In order to get the most meaningful information, I recommend that you let the monitor process take place for at least two weeks. This is because you want to create the minimum number of indexes to achieve the most impact. For example, if on day one the Query Optimizer recommended you create an index over your outstanding orders file by sales area, you could go ahead and create an appropriate index and some queries would run faster immediately. It may well be, though, that overnight some jobs are run that recommend building an index on sales rep within sales area. Similarly, when you get to the end-of-week routines, the optimizer may recommend an index keyed on customer within sales rep within sales area. If you created an index at each stage, the system would be maintaining three indexes, rather than just one if you waited until the end of the week.

To see which indexes have been recommended, run the following SQL statement (or the equivalent query):

WHERE QQIDXA='Y'                                

This statement pulls out records from the monitor file where the optimizer has recommended creation of an index to improve performance (QQIDXA = 'Y'). It displays the library (QQPTLN) and physical file (QQPTFN) used and the recommended keys (QQIDXD). When you run this statement, you will most likely see a status message saying "Building access path" or "Building hash table." This is because the optimizer has decided to build an index to present the data in the sequence you requested.

If you still have the DBMON logging active and you run the SQL statement again, you should see entries for the first run of the SQL statement, recommending that you build an access path keyed on QQPTLN, QQPTFN, and QQIDXD. (On your system, the access path recommendation may be different; this will be entirely dependent upon the makeup of the data in the log file. If yours is different, please proceed but assume your recommendations are the same as my example.) As a working example of what this article is all about, let's follow its recommendations and create an index to do this using SQL:


This creates an index called DBMONL1 in library DBQRYLOG. It's based on file DBMON in library DBQRYLOG, and its key fields are QQPTLN, QQPTFN, and QQIDXD. You could achieve a similar result by keying in DDS and creating a logical file.

When you run the SQL SELECT statement again, you should notice that the query is satisfied much more quickly, although there may still be a slight delay because you're doing a record selection on the file. To go that extra mile, delete the index:


Then, re-create it with the record selection field specified as an extra key.


The statement will now run just a little bit faster. This is because the database engine can now read just the records required rather than reading them all and performing dynamic record selection.

How to Decide Which Logical Files/Indexes to Create

It's impractical to create an index for every recommendation issued by the optimizer. If you did, you would end up with thousands of extra logicals on your system. What you need to decide is what will give you the most benefit for the least overhead. My favorite way of doing this is to summarize the log file, grouping by physical file library and name and also by access path recommendation. If you also total the number of rows, you can see which access paths the system is spending the most time building. Here's the SQL statement to achieve this:


Look for the entries with the highest number of rows and check to see if there is another physical file that may have the same recommended keys but with some extra trailing keys. Then, create the index using either SQL or DDS.

Worried About Logical File Proliferation?

In all my time working on the iSeries and its predecessors, I have heard many conflicting opinions about how many logical files, or indexes, it is wise to have over any particular physical. I remember one respected figure saying that under no circumstances should you ever have more than 20. When considering this issue, though, the main criterion is undoubtedly how volatile the physical file is. Once the logical has been created, the only overhead associated with it is when a record is inserted or deleted or whenever a key value in the logical changes.

So, for example, if you had a very large table containing all the previous years' sales history, which you added to only once a year, you could create as many logicals as you wished over this data because the only overhead would be when the records were being added. At the opposite end of the spectrum would be something like an inventory movements file, in which every transaction is recorded and you may be adding thousands of records every hour. Having many hundreds of logicals over this file would probably result in some kind of performance degradation.

To view the IBM documentation for STRDBMON and the other methods, click here.

Steve Close is technical director of Utilities 400 Inc. of Millbrook, New York, and Utilities 400 Limited, England. He has been involved in the development and installation of system management and optimization products for the iSeries, AS/400, and System/38 since 1979. Steve can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..



Support MC Press Online





  • White Paper: Node.js for Enterprise IBM i Modernization

    SB Profound WP 5539

    If your business is thinking about modernizing your legacy IBM i (also known as AS/400 or iSeries) applications, you will want to read this white paper first!

    Download this paper and learn how Node.js can ensure that you:
    - Modernize on-time and budget - no more lengthy, costly, disruptive app rewrites!
    - Retain your IBM i systems of record
    - Find and hire new development talent
    - Integrate new Node.js applications with your existing RPG, Java, .Net, and PHP apps
    - Extend your IBM i capabilties to include Watson API, Cloud, and Internet of Things

    Read Node.js for Enterprise IBM i Modernization Now!


  • Profound Logic Solution Guide

    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 companyare not aligned with the current IT environment.

    Get your copy of this important guide today!


  • 2022 IBM i Marketplace Survey Results

    Fortra2022 marks the eighth edition of the IBM i Marketplace Survey Results. Each year, Fortra captures data on how businesses use the IBM i platform and the IT and cybersecurity initiatives it supports.

    Over the years, this survey has become a true industry benchmark, revealing to readers the trends that are shaping and driving the market and providing insight into what the future may bring for this technology.

  • Brunswick bowls a perfect 300 with LANSA!

    FortraBrunswick is the leader in bowling products, services, and industry expertise for the development and renovation of new and existing bowling centers and mixed-use recreation facilities across the entertainment industry. However, the lifeblood of Brunswick’s capital equipment business was running on a 15-year-old software application written in Visual Basic 6 (VB6) with a SQL Server back-end. The application was at the end of its life and needed to be replaced.
    With the help of Visual LANSA, they found an easy-to-use, long-term platform that enabled their team to collaborate, innovate, and integrate with existing systems and databases within a single platform.
    Read the case study to learn how they achieved success and increased the speed of development by 30% with Visual LANSA.


  • The Power of Coding in a Low-Code Solution

    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:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Why Migrate When You Can Modernize?

    LANSABusiness 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.
    In this white paper, you’ll learn how to think of these issues as opportunities rather than problems. We’ll explore motivations to migrate or modernize, their risks and considerations you should be aware of before embarking on a (migration or modernization) project.
    Lastly, we’ll discuss how modernizing IBM i applications with optimized business workflows, integration with other technologies and new mobile and web user interfaces will enable IT – and the business – to experience time-added value and much more.


  • UPDATED: Developer Kit: Making a Business Case for Modernization and Beyond

    Profound Logic Software, Inc.Having trouble getting management approval for modernization projects? The problem may be you're not speaking enough "business" to them.

    This Developer Kit provides you study-backed data and a ready-to-use business case template to help get your very next development project approved!

  • What to Do When Your AS/400 Talent Retires

    FortraIT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators is small.

    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:

    • Why IBM i skills depletion is a top concern
    • How leading organizations are coping
    • Where automation will make the biggest impact


  • Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools

    Profound Logic Software, Inc.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. In Part 2, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Attend this webinar to learn:

    • Different tools to develop Node.js applications on IBM i
    • Debugging Node.js
    • The basics of Git and tools to help those new to it
    • Using as a pre-built development environment



  • Expert Tips for IBM i Security: Beyond the Basics

    SB PowerTech WC GenericIn this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

    Don't miss this chance to take your knowledge of IBM i security beyond the basics.



  • 5 IBM i Security Quick Wins

    SB PowerTech WC GenericIn today’s threat landscape, upper management is laser-focused on cybersecurity. You need to make progress in securing your systems—and make it fast.
    There’s no shortage of actions you could take, but what tactics will actually deliver the results you need? And how can you find a security strategy that fits your budget and time constraints?
    Join top IBM i security expert Robin Tatam as he outlines the five fastest and most impactful changes you can make to strengthen IBM i security this year.
    Your system didn’t become unsecure overnight and you won’t be able to turn it around overnight either. But quick wins are possible with IBM i security, and Robin Tatam will show you how to achieve them.

  • Security Bulletin: Malware Infection Discovered on IBM i Server!

    SB PowerTech WC GenericMalicious programs can bring entire businesses to their knees—and IBM i shops are not immune. It’s critical to grasp the true impact malware can have on IBM i and the network that connects to it. Attend this webinar to gain a thorough understanding of the relationships between:

    • Viruses, native objects, and the integrated file system (IFS)
    • Power Systems and Windows-based viruses and malware
    • PC-based anti-virus scanning versus native IBM i scanning

    There are a number of ways you can minimize your exposure to viruses. IBM i security expert Sandi Moore explains the facts, including how to ensure you're fully protected and compliant with regulations such as PCI.



  • Encryption on IBM i Simplified

    SB PowerTech WC GenericDB2 Field Procedures (FieldProcs) were introduced in IBM i 7.1 and have greatly simplified encryption, often without requiring any application changes. Now you can quickly encrypt sensitive data on the IBM i including PII, PCI, PHI data in your physical files and tables.
    Watch this webinar to learn how you can quickly implement encryption on the IBM i. During the webinar, security expert Robin Tatam will show you how to:

    • Use Field Procedures to automate encryption and decryption
    • Restrict and mask field level access by user or group
    • Meet compliance requirements with effective key management and audit trails


  • Lessons Learned from IBM i Cyber Attacks

    SB PowerTech WC GenericDespite the many options IBM has provided to protect your systems and data, many organizations still struggle to apply appropriate security controls.
    In this webinar, you'll get insight into how the criminals accessed these systems, the fallout from these attacks, and how the incidents could have been avoided by following security best practices.

    • Learn which security gaps cyber criminals love most
    • Find out how other IBM i organizations have fallen victim
    • Get the details on policies and processes you can implement to protect your organization, even when staff works from home

    You will learn the steps you can take to avoid the mistakes made in these examples, as well as other inadequate and misconfigured settings that put businesses at risk.



  • The Power of Coding in a Low-Code Solution

    SB PowerTech WC GenericWhen 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:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • The Biggest Mistakes in IBM i Security

    SB Profound WC Generic The Biggest Mistakes in IBM i Security
    Here’s the harsh reality: cybersecurity pros have to get their jobs right every single day, while an attacker only has to succeed once to do incredible damage.
    Whether that’s thousands of exposed records, millions of dollars in fines and legal fees, or diminished share value, it’s easy to judge organizations that fall victim. IBM i enjoys an enviable reputation for security, but no system is impervious to mistakes.
    Join this webinar to learn about the biggest errors made when securing a Power Systems server.
    This knowledge is critical for ensuring integrity of your application data and preventing you from becoming the next Equifax. It’s also essential for complying with all formal regulations, including SOX, PCI, GDPR, and HIPAA
    Watch Now.

  • Comply in 5! Well, actually UNDER 5 minutes!!

    SB CYBRA PPL 5382

    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.

    Request your trial now!

  • Backup and Recovery on IBM i: Your Strategy for the Unexpected

    FortraRobot automates the routine 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:
    - Simplified backup procedures
    - Easy data encryption
    - Save media management
    - Guided restoration
    - Seamless product integration
    Make sure your data survives when catastrophe hits. Try the Robot Backup and Recovery Solution FREE for 30 days.

  • Manage IBM i Messages by Exception with Robot

    SB HelpSystems SC 5413Managing messages on your IBM i can be more than a full-time job if you have to do it manually. 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:
    - Automated message management
    - Tailored notifications and automatic escalation
    - System-wide control of your IBM i partitions
    - Two-way system notifications from your mobile device
    - Seamless product integration
    Try the Robot Message Management Solution FREE for 30 days.

  • Easiest Way to Save Money? Stop Printing IBM i Reports

    FortraRobot 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:

    - Automated report distribution
    - View online without delay
    - Browser interface to make notes
    - Custom retention capabilities
    - Seamless product integration
    Rerun another report? Never again. Try the Robot Report Management Solution FREE for 30 days.

  • Hassle-Free IBM i Operations around the Clock

    SB HelpSystems SC 5413For over 30 years, Robot has been a leader in systems management for IBM i.
    Manage your job schedule with the Robot Job Scheduling Solution. Key features include:
    - Automated batch, interactive, and cross-platform scheduling
    - Event-driven dependency processing
    - Centralized monitoring and reporting
    - Audit log and ready-to-use reports
    - Seamless product integration
    Scale your software, not your staff. Try the Robot Job Scheduling Solution FREE for 30 days.