This web version of the article contains a sidebar (below) that has source code examples for creating the service program and external stored procedure.

Acme Enterprises is currently in the process of modernizing its existing System i applications following the database modernization strategy documented in the IBM Redbook Modernizing IBM eServer iSeries Application Data Access - a Roadmap Cornerstone (www.redbooks.ibm.com/abstracts/SG246393.html?Open). Acme has successfully transformed a majority of its DDS-generated database objects to SQL DDL-generated database objects and has seen major improvements in database reads, changes, and associated access path maintenance.

As part of this database transformation project, Acme Enterprises has put in place a process for the continued collection of performance data that includes system, application, and database statistics. I covered this step in "Analyzing Data Access Methods" (August 2006, article ID 20627 at SystemiNetwork.com). The Acme database team uses this information to identify candidates for application rewrite. In particular, a number of programs appearing in several "top 10" hit lists contain these characteristics:

• high resource costs associated with calls made to QSYS/QQQOPNQF (OPNQRYF — i5/OS command) in several interactive programs that let users filter and reorder the displayed lists
• excessive elapsed times associated with calls to QSYS/QDBPUT (write a record) in several programs associated with EDI order processing
• excessive CPU and synchronous I/O operations associated with calls to QSYS/QDBGETKY (retrieve a record randomly by key) in several interactive and batch programs in which the RPG READE operation is being used to read multiple records

Many of these programs are crucial to Acme's business. Although the interactive response times were acceptable, the programs would not scale when tested with anticipated increases in business volumes. In fact, the batch programs, using a combination of random I/O and single-record writes, would simply not complete in the required runtime window. The Acme team realized that modernizing would involve rewriting the existing programs.

Now that the conversion from DDS-created database objects to SQL DDL-created database objects is complete, the physical file (PF) objects are no longer accessed directly. All Record Level Access (RLA) is performed using a DDS logical file (LF) created over the SQL PF objects. The next step in the modernization process (referred to later as Stage 2) is to reengineer the HLL programs to use SQL data-access methods to provide the following benefits:

• increase reusability of existing components in both legacy and future applications
• increase the existing program life expectancy
• take advantage of enhanced data access methods available to SQL only
• reduce maintenance delays and overhead associated with altering the database

To achieve these benefits and to minimize the impact (and cost) of program modification, Acme has adopted the following RLA-to-SQL reengineering strategy (Figure 1 displays an overview):

1. Reverse engineer the DDS LF file that the HLL program uses to an SQL view.



2. Create a source module that contains the SQL statements to access the SQL view. This module will be referred to as a data access module.



3. Create a source module to intercept the HLL I/O operations and execute the corresponding SQL procedure. This module will be referred to as a bridge module.



4. Bind the data access module and bridge module into a single program. In RPG, this program will be a SPECIAL device on the RPG F-spec.



5. Modify the original HLL program to call the new program using RPG SPECIAL file processing (for more about this method, see "Special Files Can Do It All" at itjungle.com/fhg/fhg041305-story01.html).

Creating the SQL View

In i5/OS, an SQL view is implemented as a single-member, non-keyed LF object that contains no data. Because all SQL view LFs are non-keyed, they do not contain a (keyed) access path. The absence of an access path means no index maintenance for the SQL view. The absence of index maintenance means that the view itself doesn't impose any system overhead when adding, deleting, or updating the underlying base table(s). At Acme, because the initial SQL views are based on existing applications using keyed LFs, no additional indexes are required during the Stage 2 reengineering process. In fact, the proper use of SQL views and existing indexes can substantially reduce the overhead of temporary object creation and may result in the elimination of less-efficient DDS keyed LFs.

An SQL view's underlying LF contains the text of the SQL Create View DDL statement and a record format. The format describes the attributes of the columns (or fields) that make up the virtual table. This means that an HLL program can reference an SQL view to declare an externally defined structure. Figure 2 contains some code snippets for externally describing structures in HLL program source (see Embedded SQL Programming for additional examples — publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/rzajp/rzajp.pdf).

Using this approach, Acme's database engineer (DBE) can create highly complex views that mask the complexity of the underlying database from the end user. The DBE can use the host structure (or any column within the structure) on subsequent Select Into or Fetch Into statements without having to know column types or sizes. Program variables can be defined based on the attributes of a field in the structure. In many cases, adding, dropping, or altering definitions of column(s) in the table(s) referenced by a view does not require re-creation of the view or recompilation of programs referencing the view in an externally defined structure. However, when a referenced view's columns are added, dropped, or altered, it's generally a good idea, but not always necessary, to recompile programs referencing the view in an externally defined structure.

Once created, the SQL view can be accessed like an actual base table (PF object), with some restrictions on insert, update, and delete operations on some views. Specifying an SQL view on the From clause of an SQL Select statement generally allows optimization by the new SQL Query Engine (SQE) optimizer. In fact, the SQE optimizer merges the SQL Select statement contained within the view with the SQL Select statement used to access the view, to create a composite SQL Select statement. This composite is then optimized as if it were a single SQL Select statement. Further, an SQL view can contain complex Case statements and/or functions to facilitate the conversion of data from one form to another.

Reverse Engineering an Existing Object

As of V5R4, the Query Dispatcher does not submit an SQL statement to the new SQE Optimizer if a DDS LF is specified on an SQL Select statement From clause (see "Exploiting the SQL Query Engine," September 2006, article ID 20648). Acme Enterprises runs on V5R3 and has no near-term plans to migrate to the next release. To take advantage of this new SQL technology, Acme has decreed that all SQL access must be done using SQL views, not DDS LFs.

In V5R1, IBM added the Generate Data Definition Language (QSQGNDDL) API to assist in reverse engineering existing DDS-defined database objects into SQL DDL statement equivalents. The iSeries Navigator tool has an interface to the QSQGNDDL API that you can use to generate the CREATE VIEW DDL statement from an existing DDS LF. The Acme developers employed this method to create the initial SQL view for testing purposes. The developers also created an external stored procedure to call the QSQGNDDL API directly from either the Rational Software Development Platform (aka WDSc) or PDM. The web version of this article contains a sidebar that has source code examples for creating the service program and external stored procedure.

Each generated SQL view had to be renamed to avoid duplicate object names. During the reengineering process, Acme realized that some DDS LFs shared the same format. The Acme team decided to create one SQL view for each shared format. Figure 3 shows the Acme database naming convention.

The DBE realized that a single bridge (i.e., SPECIAL device) program could be reused by several different application programs that had been using shared-format LFs or OPNQRYF commands that referenced the same format. In essence, each program passes a text string containing an equivalent SQL statement to the bridge program. The bridge program hands the statement off to a service program that contains the data modules that perform the dynamic Prepare along with the Declare, Open, Fetch, and Close SQL statements. The result-set rows are returned to the bridge program, which passes the data back (one row per Read) to the calling program.

This process worked extremely well when creating simple SQL views for the existing DDS LFs being accessed directly by an HLL program. Unfortunately, a number of programs used the advanced techniques that the OPNQRYF command offers to create dynamic files. In these cases, the SQL view had to be created manually.

Creating a View Containing a Derived Table

One of the more frequently used interactive applications at Acme was the Customer Open Order Summary List. Figure 4 shows a portion of the list displayed by this program. The original team of Acme developers used the OPNQRYF command to let end users subset and/or order the data in the list. The following example OPNQRYF command creates the ODP used by the Customer Open Orders Summary List program:

OPNQRYF    FILE((CUSTMAST) (CUSTORDS))
OPTION(*INP)
FORMAT(OPENORDS)
QRYSLT('REGION = "' || &REGIONCODE || '"')
KEYFLD((TOTAL_ORD$ *DESCEND))
JFLD((CUSTMAST/CUSTKEY CUSTORDS/CUSTKEY))
GRPFLD(CUSTKEY CUSTOMER REGION)

The FORMAT keyword identifies the database object (OPENORDS) that contains the field definitions for the output of the OPNQRYF command. The object OPENORDS is a PF that contains no data. Its only purpose is to define the fields contained in the result set produced by joining and grouping data from the files referenced in the query. That is, it represents a derived table, also referred to as a nested table expression.

A derived table defines the structure of one or more dynamic tables used in a query. The actual data defined by the derived table OPENORDS does not exist until the query is executed. In OPNQRYF, the result set is defined by a combination of the FORMAT, GRPFLD, and MAPFLD keywords. Figure 5 shows the DDS source for the OPENORDS PF.

Notice that the columns TOTAL_ORDS and TOTAL_ORD$ in the DDS source file merely define the column attributes. The fact that they actually contain the COUNT of the orders and the SUM of TOTREVENUE (as defined by the OPNQRYF MAPFLD ((TOTAL_ORDS'%COUNT' *DEC 9 0) (TOTAL_ORD$ '%SUM(TOTREVENUE)' *DEC 31 2)) parameters) isn't specified in DDS, because there are no DDS equivalents for these derivations or, for that matter, any aggregate functions. For that reason, the OPENORDS object cannot be reverse engineered to produce the complete SQL view needed to replace the use of OPNQRYF. Attempting to use the QSQGNDDL API to create an SQL view from an existing PF object will fail with the following message: "SQL7041 40 OPENORDS not valid for object type VIEW." The Acme DBE must create this view from scratch.

The first step in defining the SQL view is to create an SQL Select statement that corresponds to the OPNQRYF command. Figure 6 contains the original OPNQRYF statement with the SQL equivalent (watch the DB2 for i5/OS website at www-03.ibm.com/servers/eserver/iseries/db2/awp.html for an upcoming white paper on converting OPNQRYF to SQL).

The query involves grouping, and thus every column in the result set must be either a grouping field or the result of an aggregate function. In this case, the original developer added CUSTOMER and REGION to the GRPFLD clause, not a function such as MAX.

Figure 7 shows the iSeries Navigator Visual Explain (VE) diagram of the plan generated by the Classic Query Engine (CQE) optimizer for the SQL version of the OPNQRYF command. The CQE optimizer was used to implement the SQL statement because CUSTMAST and CUSTORDS are DDS LFs. This is an exact equivalent of the OPNQRYF query plan, because OPNQRYF always uses CQE. A temporary index-from-index is created over the CUST_MAST table to satisfy the grouping requirement. Records are then joined between CUSTMAST and CUSTORDS and grouped by CUSTKEY, CUSTOMER, and REGION. Finally, the grouped rows are sorted in descending sequence by TOTAL_ORDS. (Note: A temporary structure is required to complete the sort. It is not shown because I used the Basic option to display the VE diagram.)

The implementation in Figure 7 has two main drawbacks: (1) use of the CQE optimizer and (2) use of a temporary structure (an index in this case). One huge difference between SQE and CQE is the creation and reuse of temporary structures. A temporary structure generated from a CQE implementation may be reused within the same job, but the actual result cannot. Both the temporary structure and the result generated by SQE can be reused within the same job. And as of V5R3, a temporary result that SQE generates can be shared across jobs. In this case, the Acme DBE avoids CQE by modifying the SQL statement to access the base tables directly:

SELECT
  CM.CUSTKEY, CM.CUSTOMER,
  CM.REGION,
  DECIMAL(COUNT(*),9,0) AS TOTAL_ORDS,
  DECIMAL(SUM(CO.TOTREVENUE),31,2) AS TOTAL_ORD$
FROM CUST_MAST CM JOIN CUST_ORDS CO
  USING (CUSTKEY)
WHERE REGION = :REGIONCODE
GROUP BY CM.CUSTKEY, CUSTOMER, REGION
ORDER BY TOTAL_ORD$ DESC

The VE diagram in Figure 8 represents the SQE implementation of the preceding SQL statement. (Note: The SQE VE diagram does display the temporary structure used when you choose the Basic option.) The SQE optimizer has generated a plan that does the join of the two tables first and then creates a temporary distinct hash table that is input to the sort. As of V5R3, the SQE optimizer lacks the ability to generate plans that contain temporary index builds. Beginning with V5R4, SQE does create temporary indexes. Unlike CQE, these Maintainable Temporary Indexes are available systemwide, and they remain when the query ends. The V5R4 Information Center (publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp) has additional information about the differences between CQE and SQE temporary indexes. Select Database|Performance and query optimization|Query Engine Overview|SQE and CQE Engines.

Although SQE is now optimizing the SQL statement, this change does not address the problem of creating a temporary object. The creation of temporary objects results in excessive use of system resources and less throughput in interactive environments. To avoid creating another permanent index, the DBE uses a derived table to eliminate the temporary object. Our first form of the Select statement was

SELECT  CM.CUSTKEY,
           CM.CUSTOMER,
           CM.REGION,
           DECIMAL(COUNT(*),9,0) AS TOTAL_ORDS,
           DECIMAL(SUM(CO.TOTREVENUE),31,2)
              AS TOTAL_ORD$
     FROM  CUSTMAST CM JOIN CUSTORDS CO
        USING (CUSTKEY)
       WHERE REGION = :REGIONCODE
       GROUP BY CM.CUSTKEY, CUSTOMER, REGION
 ORDER BY TOTAL_ORD$ DESC

Conceptually, this statement first joins each order row with the matching customer master row, selects the rows for the specific region, and then calculates aggregate functions (i.e., count and sum) for each customer master. The final step is to sort the results.

The solution can be expressed more efficiently by first producing a temporary result set from the set of orders. This result set has one row for each customer record in the orders table. Each row has the customer ID and the value of the aggregate functions for that customer. This smaller result set can then be joined to the customer master to enable selection by the specified region. The final step is to sort the results. The SQL statement for this approach is

SELECT
  CM.CUSTKEY,
  CUSTOMER,
  REGION,
  TOTAL_ORDS,
  TOTAL_ORD$
FROM
(SELECT CUSTKEY, DECIMAL(COUNT(*),9,0)
   AS TOTAL_ORDS, DECIMAL(SUM(TOTREVENUE),31,2)
   AS TOTAL_ORD$ FROMCUST_ORDS
   GROUP BY CUSTKEY) CO
JOIN
   CUST_MAST CM USING (CUSTKEY)
WHERE REGION = :REGIONCODE
ORDER BY TOTAL_ORD$ DESC ;

Figure 9 contains the VE diagram representing the SQE implementation of the preceding SQL statement. Notice that the grouping function is now performed solely on the table CUST_ORDS using an existing index over the CUSTKEY column. The grouped rows are then joined to CUST_MAST. This technique eliminates the temporary hash table or index requirement. A temporary sorted list structure (keyed on TOTAL_ORD$) is created to contain the joined records and is scanned returning the result set. You can't eliminate the temporary structure because of the Order By requirement to sort the result of the summation in descending order. However, it is a much more efficient use of resources than a temporary index or table.

The final step is to create an SQL view containing our optimized SQL statement. The name of the view will be OPENORDSR, based on the record format name of the OPENORDS file. The following is the SQL DDL Create View statement:

CREATE VIEW OPENORDSR AS SELECT
  CM.CUSTKEY,
  CM.CUSTOMER,
  CM.REGION,
  TOTAL_ORDS,
  TOTAL_ORD$
FROM
  (SELECT CUSTKEY, DECIMAL(COUNT(*),9,0)
  AS TOTAL_ORDS, DECIMAL(SUM(TOTREVENUE),31,2)
  AS TOTAL_ORD$ FROM CUST_ORDS
  GROUP BY CUSTKEY) CO JOIN CUST_MAST CM
  USING (CUSTKEY);

Notice that the SQL Select statement within the view does not have the Where or Order By clauses. The Order By clause is not allowed in a Create View statement. The Where clause was omitted because host variables cannot be specified within a view. The application program dynamically supplies the Where and Order By clauses, as in the following example code snippets. The first is static embedded SQL using a host variable:

SELECT * FROM OPENORDSR
WHERE REGION = :RegionCode
ORDER BY TOTAL_ORD$ DESC

Next is dynamic embedded SQL using a parameter marker (?):

SELECT * FROM OPENORDSR
WHERE REGION = ?
ORDER BY TOTAL_ORD$ DESC

OPEN Dynamic_Cursor USING :RegionCode

The complexity of the SQL Select statement is hidden from the end user. The row selection (Where) and sort (Order By) criteria are supplied with the embedded SQL Select statement.

Figure 10 displays the VE diagram representing the SQE implementation of either of the preceding embedded SQL statements. The implementation plan is the same as the SQL Select statement that was executed "standalone." The VE diagram verifies that the SQE optimizer combined the SQL Select statement contained in the view with the SQL Select statement that the application program provided to create a composite SQL Select statement.

View Combining Multiple Tables

The Acme DBE identified several programs that contained combinations of the Create Duplicate Object (CRTDUPOBJ) command, multiple OPNQRYF commands, and the Copy From Query File (CPYFRMQRYF) command. These commands were executed before calling the Customer Open Orders Summary List. Figure 11 is an example of one such program. (Note: Error handling and some parameters have been removed.) The program performs the following steps (the letters in the list match callouts in the code):

1. Duplicate an empty file (PARTORDERD) into library QTEMP.



2. Use OPNQRYF to create a derived table containing a distinct list of ORDERKEY values defined by a combination of the KEYFLD((ORDERKEY)) and UNIQUEKEY(*ALL) values within the OPNQRYF statement.



3. Copy the distinct list into the PARTORDERD table created in QTEMP.



4. Join the temporary table to CUSTMAST and CUSTORDS to produce the open data path (ODP) that program HLL_P2 uses.



5. Call program HLL_P2 to produce the Customer Open Orders Summary List for those customers ordering a specific part.

The first step to producing a single view for this process is to understand the nature of the temporary table PARTORDERD. Figure 12 shows the DDS describing this file. Like the OPENORDS file in the previous case, PARTORDERD is a file used to describe the FORMAT of a derived table to OPNQRYF. It is also used as the basis for creating a temporary file (in the QTEMP library) that will contain the results of the CPYFRMQRYF command. You can replace this process of CRTDUPOBJ, OPNQRYF, and then CPYFRMQRYF with a single Create Table As statement (Figure 13).

The SQL Create Table As statement in Figure 13 creates a new table, PARTORDERD in library QTEMP. The attributes of columns ORDERKEY and PARTKEY come from the ORDER_DETL PF. The rows matching the selection criteria PARTKEY = :PartNumber are inserted into the PARTORDERD table as a result of the WITH DATA clause at the end of the Create Table As statement. The table will contain only one row for each distinct ORDERKEY/PARTKEY pair. Figure 14 shows the SQL Select statement equivalent to the second OPNQRYF command that joins CUSTMAST and CUSTORDS to the temporary file.

Figure 15 contains the VE diagram of the plan representing the CQE implementation of the preceding SQL statement (mimicking the OPNQRYF implementation). Notice that the CQE plan calls for a full-table scan of temporary file PARTORDERD. The results of the three-table join are written to a hash table for grouping. The hash table is then sorted to produce the final result.

Besides the use of the CQE optimizer, three additional problems occur as a result of implementing the preceding multiple-SQL-statement approach. First, the Create Table As statement does not have a REPLACE DATA capability. Thus, the DBE must execute an SQL Delete statement followed by an SQL Insert statement (with the same Select statement as the SQL Create Table As) to replace the data in the temporary table after it has been created, or use the SQL Drop Table statement to delete the temporary file after each use.

Second, explicitly creating temporary objects is just as resource intensive (if not more so) than implicitly creating a temporary structure. In fact, a temporary table created in QTEMP cannot be shared across jobs; thus, each user of the program will create and populate his or her own table. This situation is made worse if each user is running under the same profile or a group profile that owns all temporary objects.

Last, a temporary table is not optimized, meaning it has no supporting indexes. So, the only method available for accessing the temporary table is a full-table scan either to read and select the data or to build another temporary structure based on that temporary table. The result can be inconsistent response times when a certain part number has only six associated orders versus another part number that has 60,000 associated orders. The temporary structure must be populated first. Fortunately, SQL provides a means to combine multiple SQL Select statements in a single SQL statement.

Get With the Program

The SQL With statement can define and join multiple derived result sets to existing tables or views. This combined SQL full Select statement is referred to as a common table expression. Figure 16 contains a comparison between the two-step SQL process (which is a one-to-one conversion of the legacy code) and a single SQL With statement.

The With clause, which serves a purpose similar to the Create Table As statement in our initial approach, contains a common table expression that defines a virtual table named PART_ORDERED. With this technique, we no longer need the temporary table PARTORDERD, and the DBE has more flexibility in defining the virtual table. In this case, the DBE decides to produce a distinct list of customer keys (CUSTKEY) by joining ORDR_DETL to CUST_ORDS using ORDERKEY, which is defined in both tables. Here, the DBE uses the actual PFs (tables) to ensure an SQE implementation.

Based on the Where clause in the definition of PART_ ORDERED, only the rows from ORDR_DETL matching the part number host variable are joined to the CUST_ORDS table. The use of DISTINCT is required because a customer may have multiple orders containing the same part number.

During the analysis of this legacy application, the DBE noticed that the final result of the second OPNQRYF was identical to the view OPENORDSR created in the previous case. In fact, the same program (Customer Open Orders Summary List) was being called. Because the OPENORDRS view contains the CUSTKEY column, the DBE completed the Select statement by joining the virtual table PART_ORDERED to the SQL view OPENORDRS using CUSTKEY. The DBE did not need to specify the Group By clause because this was also defined in the OPENORDRS view.

The VE diagram in Figure 17 represents the SQE implementation of the preceding SQL With statement. The SQE optimizer generates the following plan:

1. Use an existing index to select rows from CUST_MAST.



2. For each row selected from CUST_MAST, join it to a temporary distinct list. This temporary structure replaces the temporary table created in QTEMP. The temporary structure is created as follows:



1. Use an existing index over ORDR_DETL to select the orders matching the requested part number.
2. For each index entry selected, join it to the CUST_ORDS table using an existing index over the order key.
3. Create a distinct list from the joined rows.


3. 3. For each row joined between CUST_MAST and the distinct list, join it to summary row produced from the CUST_ORDS table.

Once again, the SQE optimizer was able to merge the SQL derived table with the SQL Select statement in the view and optimize the composite as a single query. The final step is to create a view containing the prior SQL statement. The name of the view will be PARTORDRDR, based on the record format name of the PARTORDRD PF structure. The following is the SQL DDL Create View statement:

CREATE VIEW PARTORDRDR (CUSTKEY,
  CUSTOMER,
  REGION,
  TOTAL_ORDS,
  TOTAL_ORD$,
  PARTKEY)
AS
WITH PART_ORDERED AS (
      SELECT DISTINCT CUSTKEY , PARTKEY
      FROM ORDR_DETL OD JOIN CUST_ORDS CO
      USING (ORDERKEY)  )
SELECT OO.*, PARTKEY FROM OPENORDSR OO
JOIN PART_ORDERED PO USING (CUSTKEY);

The view now contains the column PARTKEY, which is required because the row selection (Where clause) supplied with the embedded SQL Select statement must specify columns defined in the view. Once again, the view does not have the Order By clause, based on current view restrictions. The following is an example of a static embedded SQL statement specifying the Where and Order By clauses:

SELECT * FROM PARTORDRDR
WHERE REGION = :RegionCode
AND PARTKEY = :PartNumber
ORDER BY TOTAL_ORD$ DESC;

The VE diagram in Figure 18 shows the SQE implementation of the preceding embedded SQL statement.

The VE diagram verifies that the SQE optimizer combined the SQL Select statement contained in the view with the SQL Select statement provided by the application program to create a composite SQL Select statement. However, in this case, the SQE optimizer chose a different implementation. The creation of the distinct list was placed in the first position and then joined to CUST_MAST, which further reduced resource requirements.

Testing the View

A huge advantage that SQL views have over OPNQRYF (and RLA for that matter) is the ability to test the view without having to write a single line of program code. In fact, all the VE diagrams in this article (with the exception of the embedded SQL examples) were generated by running the SQL statement by itself within the iSeries Navigator Run SQL scripts window.

Figure 19 is a VE diagram of an SQL statement run against the OPENORDRDR view. The DBE can now use the features and functions of VE to tune the SQL statement before turning the view over to the application developers. In addition, end users using their query tool of choice can now access the optimized view.

A modeled test environment is perfectly acceptable for achieving a proper implementation if you have a good understanding of the DB2 for i5/OS SQL Optimizer and you know whether the target environment is interactive (FIRSTIO) or batch (ALLIO). In essence, when developing for FIRSTIO, the basic rule is no temporaries or table scans. When you are ready to implement (i.e., go live), simply validate the statement against the production tables using the iNav Run SQL Scripts Explain Only function.

Performance Results

The conversion of existing legacy programs to take advantage of modern methods and techniques is providing Acme Enterprises with valuable resource savings both in CPU and elapsed time. The chart in Figure 20 compares the different types of legacy programs with an SQL alternative.

Acme developers achieved nearly a 2-to-1 improvement by converting simple (single shared format) and complex (multi-step programs) to use advanced SQL function. Their approach included the use of the SQL With statement and blocked Fetch and Insert techniques, along with reusable ODP support.

Acme developers have found that attempting to convert RLA to SQL one for one may not necessarily improve performance. To get the performance improvements shown in the performance comparisons, you must take advantage of the multi-row and multi-table access methods available to SQL. Besides blocked Fetch and Insert, this includes joins.

As of V5R4, the maximum number of tables that can be referenced in a view is 256. Do not be alarmed. The maximum number of tables that can be referenced in an SQL statement, as of V5R4, is 1,000. Just keep in mind that you must limit your SQL statements to two- million characters (as of V5R4).

The types of legacy applications that are the best candidates for SQL rewrite are those that use the OPNQRYF, CPYFRMQRYF, RUNQRY, CPYF, and/or CRTDUPOBJ commands, and HLL programs that perform large numbers of "un-blocked" reads (especially RPG READE) and/or un-blocked writes, process DDS join LFs, use DDS select/omit LFs, or use multi-member PFs.

The performance comparisons in Figure 20 were done using existing indexes. For SQL, all keyed LFs were migrated to equivalent SQL indexes as per the methodology described in the Modernizing Data Access Redbook. In essence, this means that there is additional room for tuning the programs. By adopting a database modernization strategy similar to Acme's, your shop could also see significant performance improvements.

Dan Cruikshank has been an IT professional since 1972 and an IBM employee since 1998. He has served in several capacities in both operations and programming, many application migrations from various platforms to the IBM System i family. Since 1993, he has been focused primarily on resolving System i application and system performance issues at several IBM customer accounts. In 1999, he also took on the role of instructor for the IBM DB2 UDB for iSeries SQL Optimization Workshop.

---