Purpose

When &#@SQL-SORT is enabled, it instructs the system to use the SQL database Sort/Select capability to improve performance when sorting in reports and updates. 

Syntax

&#@SQL-SORT

Remarks

This is only valid if issued in Sort/Select logic and if all files up to the sort_end_file are SQL database tables.  The old style PROIV sort will be performed unless these conditions are met.

The following rules apply to its use:

1. SQL_JOIN... Multiple files are joined in the same query requiring only one cursor.  The number of files to be joined is specified by the sort_end_file field.  The system will issue the join based on the keys of each file.  File modes must be either LOOKUP, UPDATE, or DELETE.  If transaction processing is enabled, all files must be in LOOKUP mode.  Files must not have Before_Read Sort_Logic specified.

2. WHERE Clause...The logic verb WHERE provides a compatible mechanism for performing an SQL or ISAM sort.  If the SQL_SORT flag is enabled, the system will construct the SQL_WHERE clause from the PROIV logical expression and pass it to the RDBMS server.  The RDBMS server then performs the selection.  If an SQL logical expression cannot be generated because a variable referenced in the WHERE clause does not belong to the files specified, the system will revert to an ISAM sort.  If an ISAM sort is being executed, the WHERE clause is treated as follows:  IF NOT (PROIV-logical-expression) DSEL ENDIF and the Sort/Select logic is called for each record, as is currently the case.

3. SQL_SORT...SQL_SORT provides the ability to have the RDBMS server perform the sort operation.  The overhead of constructing the temporary PRO_ISAM sort file and extra read for each record is eliminated.  In addition, most RDBMS servers are very efficient in sorting, especially when dealing with large volumes of data.  The system will construct an SQL statement that includes the ORDER BY clause, which is passed to the RDBMS.  The RDBMS server then performs the sort.  All records in the resulting set are therefore pre-sorted when the system retrieves the data.  If the SQL_SORT flag is disabled, PROIV will perform the sort in native mode.