What Engine Powers Your Queries?

It’s time once again to open the hood (or bonnet if you’re from the British Isles) of DB2 for i and take a look at the engine that is running your queries.

When you issue a “query”, regardless of interface, a component known as the “optimizer” must take your request and in effect build the logic and define the operations that will handle that request. The output of this optimization process is known as a “plan”. Think of it as containing all of the information, methods and strategies required to get you an answer set, based on your data and your system. Once the plan is established it is handed over to the database for execution whereby the methods are invoked, and the strategies implemented by the low level system licensed internal code (SLIC) “primitives”. We can refer to this entire stack as the DB2 for i “query engine”.

Starting way back in V5R2 and continuing through 7.1, there have actually been two query engines residing in DB2 for i. And which one you get to use is of great importance. 

The Tale of Two Engines

A few years prior to the general availability of version 5 release 2 of OS/400 (now known as IBM i), a team of very smart software engineers got together in Rochester Minnesota to plan and plot the design of a new query optimizer and database engine. One that would replace the aging, monolithic and inflexible components used since the inception of AS/400, and in some cases the System/38. The result of many months of scheming and clever coding was a new state of the art optimizer and database engine. This major accomplishment ultimately became the solution for allowing DB2 for i to handle widely varying SQL requests as well as drive more data centric features and functions into the operating system. You see, back then it was becoming obvious that dynamic SQL was emerging as THE way to access information, and the SQL requests being constructed by both humans and machines were more and more complex. Recall that all of the major and minor applications coming into existence at time were now making use of ODBC and JDBC connections in a client / server architecture. Another real example of how IBM i handled this phenomenon is DB2’s single SQL statement limit of 2MB. Imagine one statement over 2 million characters in length! By the way, all of the database limits can be found in appendix A of the DB2 for i SQL Reference manual.

Given the scope, depth and breadth of a new SQL query engine, IBM decided to take a phased approach and introduce the new code over a series of releases. Another big requirement – an AS/400 hallmark really - included a minimum (or no) disruption to existing applications. In other words, no rewrite or recompile to take advantage of the new support (unlike other major operating systems). In effect, the application developer and/or user would not be aware of the new engine except when it came to better performance, better scalability and more options available for data centric processing. To pull this off, DB2 for i kept the original query engine in place, while the new engine was being introduced. To assist with explaining the query processing paths, names were given to each engine.

SQE represents the new, SQL focused query engine.

CQE represents the classic, original and stablized query engine.

The magic of how these two engines can reside side by side and are used appropriately is uncovered by attending a fine conference or class that the IBM DB2 for i Center of Excellence supports. 

Does It Really Matter?

SQL is the strategic database interface, for IBM i and the rest of the industry.

SQE is the strategic query engine for IBM i.

SQE is there to handle the queries coming through any SQL interface. This includes, but is not limited to: embedded static or dynamic SQL in high level language programs such as RPG, COBOL and C, as well as the DRDA, ODBC, JDBC, CLI and PHP interfaces.

The queries issued via QUERY/400, OPNQRYF and the QQQQRY API continue to go through CQE. These applications and interfaces do not take advantage of all the advances in SQL functionality, nor do they benefit from the unique self-learning, self-adapting and self-tuning behavior of SQE. 

   

Furthermore, these query interfaces (QUERY/400, OPNQRYF, QQQQRY API) have not been enhanced for many years. Virtually all of the cool DB2 for i enhancements over the last 15 years are only available via SQL.

Even if you do use SQL to issue your query, the request might be handled by CQE, not SQE. Again, this is due to the phased availability of the new engine over several releases, and the self-imposed limitations placed on the new SQL query engine.

Generally speaking:

All SQL queries running in IBM i 7.1 make use of SQE.

All SQL queries running in IBM i 6.1 make use of SQE except when you reference a logical file on the FROM clause (it’s best practice to reference a physical file, table or view on the FROM clause). 

What Should You Do?

 

• First and foremost, move to IBM i 7.1 as soon as possible.

 

• Embrace SQL as your data access interface for any new applications.

 

• Embrace SQL as your language for moving to data centric programming.

 

• Modernize and re-engineer your applications and reporting systems.

 

• Ask your solution vendor if they make use of SQL.

Pay Attention

If your package or solution is using QUERY/400, OPNQRYF or the QQQQRY API, you are NOT getting the most out of DB2 for i. You are banking on a solution that is not keeping up with the industry, nor taking advantage of the state of the art relational database technology. You are forsaking capability already bought and paid for. You have a high performance, high output engine sitting idle on your machine room floor.

I find it very unfortunate that some so called “modern and new” solutions are not using SQL to issue queries. Frankly, you should too. So, it’s a simple question to ask your vendor: “does this solution use SQL, yes or no”. If the answer is “no”, my recommendation is to find a solution that does.

And if you find yourself moving data out of IBM i to another database platform because you’re not able to meet business requirements, please STOP! Take a look at what is actually available in your IBM i system. Recall and review all of the benefits you've received over the years. It's very likely you have everything you need to do better, to do more, without the risk and expense of moving off the platform.

Don't ask WHY i...  ask WHY not i

For example, all those hundreds (or likely thousands) of QUERY/400 definitions can be identified, analyzed and modernized to provide more value; all while retaining the business logic that is still relevant. There’s no need to run QUERY/400 “reports” that only extract data for an Excel spreadsheet sitting all naked and exposed in someone's PC. By moving your query definitions to SQL, you’ll be able to make use of everything DB2 for i has to offer, including the high performance query engine known as SQE. Not to mention IBM i rock solid stability and security.

If you need more information on the features, functions and benefits of DB2 for i, or any kind of assistance with keeping IBM i providing value in your establishment, please contact me.