TechTip: Boost SQL Performance on DDS Databases PDF Print E-mail
Tips & Techniques - SQL
Written by Kent Milligan   
Friday, 20 May 2011 01:00

Support MC Press - Visit Our Sponsors


Forums Sponsor





Search Sponsor




Did you know that there are PTFs that can give you some 7.1 performance benefits even if you're still on 6.1?


One of the benefits of having a single integrated relational database in the IBM i operating system is that developers can freely mix and match different database interfaces. Database objects created with SQL can be accessed using the native record-level access interfaces. And database files created with Data Definition Specifications (DDS) can be accessed from SQL interfaces. There are a few exceptions, but this interface flexibility holds true most of the time.


While SQL statements can access objects created with DDS, there are cases where the SQL performance was not allowed to reach its full potential. Prior to the IBM i 7.1 release, performance may be limited whenever an SQL statement references a logical file on the FROM clause as shown in the following example:


 SELECT fld1, fld2 FROM myLF WHERE fld3>100


SQL statements that reference logical files on the FROM clause can have restricted performance because that reference forces the usage of the Classic Query Engine (CQE) instead of the newer SQL Query Engine (SQE).


Since the introduction of V5R2, IBM has chosen to deliver SQL performance enhancements primarily by enhancing SQE. As a result, SQE has a much wider array of algorithms and optimization techniques to choose from than CQE when implementing a query. With complex queries, this larger toolbox can result in SQE being able to run an SQL request substantially faster. At a recent conference, a customer who had upgraded to IBM i 7.1 shared that the response time of one SQL statement went from six hours to 20 minutes. That complex SQL statement was referencing a logical file on the FROM clause, so the dramatic performance advancement on IBM i 7.1 was a result of the SQL statement being processed by SQE instead of CQE. Obviously, the performance experiences with your SQL statements are not guaranteed the same improvement, but the potential is there for some performance boost.


Because not all of IBM's customers have upgraded to 7.1, IBM recently delivered some of the SQE support for logical files with PTFs for the IBM i 6.1 release. This performance enhancement is available by simply loading version 24 of the IBM i 6.1 Database Group PTF (SF99601), which can be accessed at IBM's Recommend Fixes Web page.    


The SQE support on IBM i 6.1 is limited to simple logical file references on read-only SELECT statements. That means the referenced logical file cannot contain any field mapping, derived fields, select/omit specifications, join specifications, or multiple record formats. The logical file definition can also not contain references to fields defined with the date, time, or timestamp data types. In addition, the logical file cannot reference a partitioned table. None of these restrictions exist with the IBM i 7.1 support in SQE, so there are advantages to upgrading to the IBM 7.1 release.


You should also be aware that IBM is now cataloging and detailing all of the DB2 enhancements delivered via PTF in a new DB2 Technology Updates wiki. So take some time to browse the wiki; there's a good chance that you'll be able to find other PTFs to simplify application development or boost application performance…such as this SQL Query Engine support for logical file references on IBM i 6.1.

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1


Kent Milligan
About the Author:

Kent Milligan is a Senior Technical Team Lead on the Emerging Technology Delivery team for the IBM Watson for Clinical Trial Matching solution. Prior to joining the Watson team in 2015, Kent spent the first 25 years of his career working as a DB2 for IBM i consultant and developer working out of the IBM Rochester lab.



Read Kent's "DB2 farewell" blog:


Last Updated on Tuesday, 28 February 2012 12:12
User Rating: / 4