Is It Time to Leave Query/400 Behind? | Guide
Is it time to leave Query/400 behind?

Is It Time to Leave Query/400 Behind?


In 1988, a gallon of gas cost $1.59, “Faith” by George Michael was Billboard’s #1 song, and IBM released Query/400. While gas prices and musical tastes have changed, Query/400’s functionality is largely the same. It has gone from cutting-edge technology at the time of its release to a potential roadblock for organizations hoping to get more out of their data.

For over 30 years, many IBM i shops have defaulted to using Query/400 to access their data, fulfilling the basic need of generating simple reports from Db2 data. But in the new millennium, more and more organizations are finding that Query/400 can’t keep up with their requirements around data, reporting, and analytics.

When asked “what are your top concerns as you plan your IT environment?”, respondents to the annual IBM i Marketplace Survey from HelpSystems answered that modernizing applications was a top-three concern. With business intelligence playing a growing role in driving critical business decisions, modernizing your business intelligence application is a great place to start. It’s time to leave Query/400 behind.

If you’re ready to bring your business intelligence to the 21st century, this guide is for you! We’ll explore:

  • Limitations of Query/400
  • Signs that it’s time for a change
  • How to make the change
  • Benefits of a modern data access tool


Limitations of Query/400

Many organizations have had the same way of running queries for decades: Query/400, IBM’s native IBM i data access tool. It can provide the data and simple reporting, but as decision-making needs and user requests grow, it’s hard to keep up. Query/400 no longer provides the analytical capabilities organizations need for business intelligence.

The simple truth is that Query/400 is stuck in time. IBM hasn’t been making regular updates to the tool for quite some time. For example, it still uses Classic Query Engine (CQE), instead of the newer SQL Query Engine (SQE). CQE is no longer updated on a frequent basis either. So, as it remains frozen in time, those who depend on Query/400 to access their data miss out on the advanced functionality of more modern tools.

While IBM continues to release better and faster IBM Power hardware and exciting features in new versions of the IBM i operating system, Query/400 starts to show its age, looking more and more what it is: a decades-old tool that doesn’t match up with today’s business needs.

In the next few sections, we’ll explore the chief limitations of Query/400 to help you get a better understanding of why it’s holding many businesses back, including yours!

Incompatible with Microsoft Excel

Incompatible with Microsoft Excel

Microsoft Excel has become a must-have application for business users who need to manipulate and analyze data in a way that they’re comfortable.

Getting data out of your IBM i and into Excel using Query/400 is not impossible, but it does require a multi-step process that too many organizations have come to accept over time as a necessary evil. In reality, this process is inefficient and time-consuming for IT and it can delay business users in getting the timely data that they need to make informed business decisions.

Some business users will try to take a shortcut—transposing and manual entering data into spreadsheets themselves. Not only inefficient, this workaround also introduces the risk of human error and could compromise data integrity.

At a technical level, understanding why Query/400 and Excel don’t play nicely together comes down to a difference in coding language: Excel relies on American Standard Code for Information Interchange (ASCII) and IBM i relies on Extended Binary Coded Decimal Interchange Code (EBCDIC). So, even simple things—like whether a negative sign goes before or after a number—differ between the two. Another example is dates—in Query/400, you cannot convert your numerical data to function as a date. This all means that when you’re trying to get data from your IBM i to Excel, it takes a lot of extra effort.

IBM has released several kits and workarounds to address the issue over the years, but none are as straight-forward as the single-click-to-export-to-Excel functionality that dedicated data access tools can provide. Business users need data in Excel, and it’s better to get it to them before they really need it. If you’re using Query/400 to get data from your IBM i server, it’s cumbersome and takes more time than necessary.

TAKEAWAY: Giving business users the ability to access crucial business data from IBM i the way they want it—in Microsoft Excel—doesn’t need to be difficult.

Interface Is Not User-Friendly

Interface Is Not User-Friendly

Experienced IBM i pros may love the green screen, but if you show that to business users, they’ll be left scratching their heads. To non-technical users and even new IT recruits, who are aren’t used to keyboard-driven systems, the green screen comes across as clunky and outdated. Query/400 lacks a graphical user interface (GUI), meaning anything you need to do to get data must be done with the green screen.

While some seasoned IT professionals may be satisfied with the green screen, modernizing the interface gives multiple benefits to non-technical users and ultimately benefits the IT team as well. A GUI makes it easier for those with Windows skills to use their mouse to click, drag, and otherwise manipulate and analyze the data in a way that they are familiar with instead of relying on functions or commands.

Another benefit of using a tool with a GUI is that it keeps IBM i relevant in your organization. IT understands the power and strategic value of the IBM i platform—it is both highly securable and reliable. However, using a decades-old interface isn’t the best PR for the platform. Using a tool with a modern GUI allows business users to consume data visually in dashboards and analyze it on their own.

Being stuck on the green screen limits data access, which in turn limits the ability of your decision-makers to use business intelligence to make informed decisions that could move your organization forward.

TAKEAWAY: Business users are more comfortable analyzing their data in a Windows-style GUI where they can visualize, analyze, and interact with their data. Modernization is a top IT initiative; moving on from the green screen to a modern GUI is a good place to start!


Compound Queries Are Unscalable

Compound Queries Are Unscalable

Query/400 can only search a single partition at a time on the LOCAL database. That means someone in IT needs to create multiple queries and run them manually to report on and distribute the data. Not only inefficient, this model simply is not scalable. With Query/400’s limitation of only one query at a time, any time someone needs a slightly different set of data, you need to modify or run a new query!

Let’s say, for example, Fredo in Finance wants to make a trial balance and has a column of credits and debits that he wants to make into two columns. Query/400 makes it very difficult to split that data in one pass due to its limitations with queries and conditional logic.

Things like converting a character field to a numeric one, converting to dates, conditional logic, concatenating, and other familiar and frequently used functions each require their own step and their own query. This means that every time you need to refresh your data, you would need to run each individual query in the correct order to get a result. For a one-off data pull, it’s not ideal; for a set of data that gets pulled regularly, it’s simply unsustainable.

Using a tool that provides more advanced BI in fewer steps is a huge design time saver. Plus, these tools can shield end users from the complexity of the query and provide faster results by using the SQL query engine (SQE) instead of query Classic Query Engine (CQE), which is slower.

The need to run multiple queries is like making three right-hand turns in a car just to make a left. Why not just make the left turn from the start? That’s the benefit of modernizing. You get the data your organization needs in one step.

TAKEAWAY: With Query/400, you sometimes need to run multiple queries to get the data you need, slowing down and complicating what should be an easier process.

Data Integrity Potentially at Risk

Data Integrity Potentially at Risk

Runtime prompts allow users to input the variables they would like to query on without touching the underlying code. There are no runtime prompts in Query/400, so when business users need to change their data sets to analyze different results, it requires a modification to the code of the query itself!

Let’s say Erika in Accounts Receivable (AR) needs to search for a certain customer account, something she does dozens of times each day. With Query/400, IT has two options: elevate Erika’s security privileges so that she can modify the query on her own or volunteer to go into the query, enter the exact account number, and re-run the query every time she needs refreshed data.

In the first scenario, adjusting Erika’s security permissions increases the possibility of human error, putting your data integrity at risk. More importantly, it would erode your security posture and may even put you out of compliance with certain regulatory frameworks. There are no internal controls, so opening Query/400 to users in order to create or modify definitions also gives them access to all the data on your system—every field and file! In the second scenario, IT starts to drown in data requests. In either case, you end up with numerous, nearly identical queries that differ only in selection criteria, each with huge potential to bring your system to a halt if written poorly.

Without runtime prompts, Query/400 ultimately interferes with your organization’s ability to get business done efficiently. You have no secure way of allowing your users to be self-sufficient. Different users or departments cannot run existing views and return unique, tailored results without writing a new query themselves or asking IT to do it.

TAKEAWAY: Runtime prompts, which empower users to get their own data without the need to modify queries, are absent in Query/400.  Switching to a modern BI tool that has runtime prompts secures your query definitions, limits data inaccuracies, and gets data to your users faster.

Signs That It's Time for a Change

Change can be challenging, and when your organization has been using Query/400 for years, you need to have some compelling reasons to implement a new data access tool. But when your user expectations, data volume size, and data access needs have all changed, it’s time for your business intelligence tool to do the same.

Today's business users expect immediacy. They are used to having the entire internet in their pocket. They get box scores in real time and expect same-day delivery for online purchases. The same expectations hold true for their business data. They want the right information, right now, in the format they prefer—and they want to be able to access it themselves.

Modern data access tools make it easy for you to give secure data access directly to users, leading to significant time-savings for IT. Query/400, on the other hand, can make IT a data bottleneck, which can be frustrating for all involved. Additionally, Query/400 cannot deliver data in visual dashboards or in a way that can be manipulated by non-technical users (such as Excel). From a business user’s perspective, an upgrade from Query/400 will give them the freedom to get data when they need it and in the format they want.

For IT, making the switch off Query/400 would be a big benefit as well. Query/400 can only use the Classic Query Engine (CQE), which is slow and inefficient by modern standards. Query/400 also forces the manual running of multiple queries to get to a result, and it will take even more time to translate that data into actionable insights. All in all, upgrading will save your IT team time and help move business forward.

These are just a few of the signs that it may be time for a change from Query/400. Take our quiz to get a clear picture of other scenarios that may indicate that it’s time to implement a modern data access tool.

TAKEAWAY: When the status quo isn’t getting the job done any more and the reasons to make a change are mounting, then it’s time to look at a modern data access tool.  Making the change will save time for your IT team and give your business users more freedom to get the data they need quickly and securely.

You know it’s time for a change if you answer YES to any of the following:

What to do When Query/400 Talent Retires

The reality that all IBM i shops are facing is that much of the AS/400 talent with the experience and expertise they depend on are nearing retirement age. In the HelpSystems IBM i Marketplace Survey, nearly 50 percent of respondents reported that a skills shortage is one of their top concerns.

Just as updates to Query/400 functionality have fallen by the wayside over the last couple decades, documentation and support for Query/400 has also become thin. For many organizations, the AS/400 experts who are looking toward retirement are also the ones that know Query/400 the best and have been using it for years. When they leave, all that tribal knowledge goes with them.

When a less technical user needs to unlock the valuable data stored on IBM i, modern business intelligence tools remove the manual chokepoints and automatically check business values from different sources without interrupting data processing. This makes real-time data available when business users need to make decisions, even when no expert is available.

Introducing a modern data access tool—one with a GUI and some of the other, modern features that users expect (visualization/dashboards, exporting to Excel, etc.)—will ease you off any reliance you have on a tool that is impossible to maintain without a technical expert—like Query/400.

Losing your Query/400 experts shouldn’t mean losing access to your IBM i data. Make a plan today and read our next section on how to easily convert your queries with Sequel.

TAKEAWAY: The most successful companies use a modern business intelligence tool to replace Query/400 with online reporting. It’s best to implement the solution before your expert retires, so they can facilitate the transition and convert your existing queries.  

How to Convert Your Existing Queries with Sequel

Changing data access tools shouldn’t mean sacrificing the existing queries that you have invested time into creating. You can convert your existing queries from Query/400 in seconds with Sequel. Then, you can use Sequel to perform data manipulation and reporting tasks far beyond the abilities of Query/400.

There are two methods for importing and converting queries from Query/400: a green screen method and a GUI method. As you may expect, the green screen method is the preferred way for technical users to convert multiple queries at once. The GUI method is better for non-technical users to move queries over one at a time, as needed.

The green screen method is a single IBM i command line that allows you to select which queries you want to convert, whether you would like to run a report on queries upon import, and on which server you would like to keep the converted queries.

For those with a green screen aversion, the GUI method is a multi-step—but still simple—process to bring existing queries over on an ad hoc basis.

Change can be challenging. Switching business intelligence tools shouldn’t also mean losing all the work that has been put into writing your queries over the years. Sequel makes it simple. Once you’ve converted in your preferred method, you’ll be able to embrace the modern functionality that Sequel provides.

How to Move on from Query/400

1. Evaluate your Needs.
When it comes to your data access needs, do you know what your must-haves are?

2. Find your ROI.
Embarking on a project to replace your query tool can be daunting—and expensive. But it doesn’t have to be. Find your ROI for replacing Query/400.

3. Try out a replacement solution.
You need a replacement for Query/400. And you need a replacement that will truly work for your needs. So, give it a good test drive first.

Advantages of Sequel

With powerful query and reporting capabilities, Sequel makes it easy to access, analyze, and distribute data exactly how you want it. In addition to first-rate features, Sequel is backed by the best technical support in the business. 

Sequel encompasses all the features that your users have been asking for in a data access and reporting tool. Users can get data from Db2 for IBM i and other databases on their own, freeing up valuable IT time. Better even, data can be exported to Excel or visualized in intuitive dashboards. Plain and simple, Sequel can keep up with today’s business demands and set you up for success in the future.

Modern business intelligence solutions make it quick and easy to meet everyone’s needs for business data:

  • Decision-makers stay on top of operational data
  • Business users gain real-time visibility to their KPIs
  • IT teams can focus less on data requests and more on strategic projects

Leave your frustrations with Query/400 behind and bring your company’s business intelligence into the 21st century with Sequel.

Next Steps

Data drives decisions—and everyone wants a piece of it. Decision-makers want to take the temperature of the business. Managers want to make informed decisions quickly. Business users want real-time visibility. And they all count on IT departments to deliver accurate business data at the speed of light. The pressure is on for IT to get the right data in the right format to the right person at the right time, and it’s hard to get the job done with Query/400.

Sequel delivers affordable, IBM i-based business intelligence to IT, business users, and decision-makers alike. Today, thousands of customers worldwide count on Sequel for access to the data they need, when they need it.

Why Sequel?

Speedy Installation
Start running queries within an hour after installing Sequel on your IBM i server.

Integration with Microsoft Excel
Use Excel as a data source or access, analyze, and report on data directly in Excel.

Remote Database Access
Pull together data from your remote databases like Db2, Microsoft SQL Server, and Oracle on your IBM i.

Executive Dashboards
Create views, graphs, and gauges to summarize key data your stakeholders need to make business decisions.

Three Great Interfaces
Access data via the IBM i green screen, graphical user interface (GUI), or a browser interface. With the browser interface, you can even access your IBM i data on the go with your laptop, tablet, or smartphone.

Dynamic Data Analysis
Pivot and drill down via client tables, use predefined views with runtime prompts, or create ad hoc requests to easily analyze live metrics.

Customizable Reports
Design impressive graphical reports that are easy for anyone to interpret.

Related Products

Related Solutions

Stay up to date on what matters.