The WHERE Constraint

COBOL data processing is based on keyed read operations following a positioning operation. Records are read until the key value being processed changes. Because traditional COBOL data processing is based on a B+ tree file system, the overhead for such operations is relatively minor.

RDBMS data processing introduces a new level of complexity to data processing. The database's query optimizer receives the SQL query for the COBOL operation being performed and then builds a working set of data that satisfies that query. Because the database optimizer has many different possible execution methods, this can result in poor performance if the optimizer chooses a query execution path that is less than optimal.

Performance degradation may also result from the fact that queries generated by COBOL operations result in unbounded index queries. Unbounded queries are generated because COBOL positioning operations (Start Not Less Than and Start Not Greater Than) provide only one of the bounding conditions for the working set, instead of both an upper and lower boundary.

As an example, consider the case where an application needs to process all items in a warehouse on aisle 17, shelf 8, and bin 2. If each of these items is a field in a key, the COBOL program might generate the following query for a READ operation:

NOTE:

The following example is for the Oracle inteface. The SQL generated will be different for the different interfaces
 
 

SELECT 

    * FROM warehouse_items 

WHERE 

    aisle = 17 and 

    shelf = 8 and 

    bin = 2 

ORDER BY 

    aisle ASC, shelf ASC, bin ASC;

SELECT 

    * FROM warehouse_items 

WHERE 

    aisle = 17 and 

    shelf = 8 and 

    bin > 2 

ORDER BY 

    aisle ASC, shelf ASC, bin ASC;

This problem can be even more serious if the application is processing the last bin on a shelf. Because there are no more bins on that shelf, the query would drop down a level and generate the following:

SELECT 

    * FROM warehouse_items 

WHERE 

    aisle = 17 and 

    shelf > 8 

ORDER BY 

    aisle ASC, shelf ASC, bin ASC;

In reality, most of the time the database query optimizer will not build the entire working set if it has been properly tuned, but will instead perform INDEXED READS in order to process the query. This means that the query optimizer will traverse an index tree to retrieve the records, much as COBOL index files do, as opposed to using combinations of indexes and sort/merge operations.

It can be helpful for the COBOL developer to influence precisely which information is to be returned. If the application developer knows at compile time (or before the query is executed) the precise scope of the record processing that needs to be performed by the read operations, the developer can more finely tune the information being retrieved.

Acu4GL provides a method by which the COBOL programmer can provide additional information to the database query optimizer by providing more specific selection information to the Acu4GL interface. This selection information is added to the WHERE clause of the SQL queries generated by the Acu4GL interface. This can be particularly useful in providing upper boundaries to queries being generated, with the result that the working set is smaller.

The developer may provide upper boundaries on the key segments for a select, or any other selection criteria needed to constrain the working set to just the desired subset of rows. This additional information is added to generated queries with the AND condition. It is not possible for the application developer to specify a larger working set than would otherwise have resulted. The developer may constrain the working set only to a smaller subset.

Using WHERE Constraints From COBOL

The following steps are required for using the WHERE constraint.
 

Step One: Declare an external variable.

In order to make use of WHERE constraints from COBOL, the application must declare an external variable for communication with the Acu4GL interface. This variable is declared as follows:

77 a4gl-where-constraint pic x(300) external

Step Two: Modify your COBOL procedures.

Your COBOL application should move the information that you want added to the WHERE clause to the new external variable before a COBOL positioning operation such as START or READ is performed. The additional constraint will then be applied to any SQL read query performed on that file until a new positioning operation is performed.

The additional query information is also stored in Acu4GL's cursor cache so that if the same read conditions occur in later processing, the existing closed cursor can be reused with new bind variables instead of being regenerated.

Be sure to fill the external variable before a positioning operation (START or READ). The WHERE constraint affects only READ NEXT operations preceded by a positioning operation. The WHERE constraint does not affect a READ NEXT that was not preceded by a positioning operation (such as a READ NEXT without a START immediately after opening a file).

Example

In your COBOL program you include this statement:

Move "ftest_key > 3 and ftest_key < 6"  to A4GL_WHERE_CONSTRAINT.
Inspect A4GL_WHERE_CONSTRAINT replacing trailing spaces by low-values.
START FTEST-FILE KEY NOT LESS FTEST-KEY.

CURSOR 0:

SELECT *,ROWID FROM ftest WHERE (ftest_key1_seg1 = ? AND 

ftest_key1_seg2 >= ?) AND (ftest_key > 3 and ftest_key < 6) 
ORDER BY ftest_key1_seg1 ASC, ftest_key1_seg2 ASC;

SELECT *,ROWID FROM ftest WHERE (ftest_key1_seg1 > ?) AND 

(ftest_key > 3 and ftest_key < 6) ORDER BY ftest_key1_seg1 ASC,
ftest_key1_seg2 ASC;

* WHERE constraints added by the COBOL program may not be portable between databases.

* The application may specify conditions of such complexity that they confuse the database query optimizer, resulting in poor performance. Be sure to examine the results of the optimizer trace facilities to ensure optimal performance.

* Take care to prevent the COBOL application from sending information that will result in a syntax error that will not be detected until runtime.