TechTip: Boost SQL Performance on DDS Databases PDF Print E-mail
Tips & Techniques - SQL
Written by Kent Milligan   
Friday, 20 May 2011 00: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 DB2 consultant on the DB2 for i Center of Excellence team within the IBM Lab Services Power Systems Delivery Practice.  His primary responsibility is helping software developers use the latest DB2 technologies and port applications from other databases to DB2 for i. After graduating from the University of Iowa in 1989, Kent spent the first eight years of his IBM career as a member of the DB2 development team in Rochester. He speaks and writes regularly on various DB2 for i relational database topics.

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