Generate the SQL statements for database access

Products

• CA 2E

Issue/Introduction

For an internal effort, we are evaluating SQL as the way to access databases within our RPG programs.  Is there a setting withing CA 2E that will generate the SQL statements for database access instead of using the RPG opcodes like CHAIN, SETLL, READ, READE, UPDATE, DELETE etc?

Environment

CA 2E 8.7 and higher

Resolution

There are four distinct methods available to achieve the requirement of transitioning from DDS to SQL-based access:

1. Function Level Setting:
   You can modify the "Generation mode" value to 'S-SQL' within the "EDIT FUNCTION OPTIONS" screen for a specific function.
   Result: The entire function generates as SQLRPGLE, where all file accesses are handled via SQL and cursors.
   Advantage: There is no need to perform access path regeneration.
   Problem: All files, regardless of their original definition, are accessed exclusively through SQL Cursors in the function.
   Note: As we know, we can access the "EDIT FUNCTION OPTIONS" screen by taking option 'Z' on the function, followed by 'F7=Options'.
   
   
2. File Level (SQL) and Function Level ('S-SQL') Setting:
   This involves changing the "Generation mode" to "S-SQL" and the "Data access method" to either "G-DBFGEN" or "T-TABLE" in the "EDIT ACCESS PATH DETAILS" screen.
   Note: If DBFGEN is used, the SQLRPGLE uses an SQL view. If Table is used, it uses an SQL index. Almost both are similar.
   Requirement: The associated function must be generated as SQLRPGLE by setting its generation mode to 'S-SQL'.
   Problem: Every impacted program using this file must be regenerated. Furthermore, all files in the program will use SQL cursor access, even though some of the files used in the function are originally defined as DDS.
   Note: As we know, we can access "EDIT ACCESS PATH DETAILS" via option 'Z' on the access path.
   
   
3. File Level (SQL) and Function Level ('B-Mixed') Setting:
   This approach directly addresses your specific requirement. Change the "Generation mode" to "S-SQL", and the "Data access method" to "G-DBFGEN" or "T-TABLE" at the access path level, and the function generation mode must be set to "B-Mixed" in the "EDIT FUNCTION OPTIONS" screen.
   Advantage: Only files defined as SQL are accessed via SQL cursors. Files defined as DDS continue to be accessed via CHAIN operations, fulfilling your specific criteria.
   Problem: Requires regeneration of all impacted programs.
   Limitation: The 'B-Mixed' functionality is strictly restricted to EDTFIL and EEF function types.
   
   
4. DDL Data Access Method (The Incremental Approach)
   This method is used when one wishes to define access paths in SQL mode without necessarily recompiling all impacted programs immediately, i.e., still want to use CHAIN only in the source code of the function.
   Configuration: Set "Generation mode" to "L-DDL" and "Data access method" to "T-TABLE" in the access path details.
   Source Generation: The function source can be generated in either RPGLE or SQLRPGLE
   If model value YDDLDBA is set to "*RLA," the function generates CHAIN and SETLL (if function generation mode is 'D' or 'B').
   If model value YDDLDBA is set to "*SQL," the function generates SQL cursors (if generation mode is 'B' or 'S').
   Compatibility: DDL-related functions are compatible with both CHAIN and SQL cursors, similar to DDS.
   Advantages: No need to regenerate impacted programs; existing programs continue to use native I/O (CHAIN, SETLL, READ). Database tables become SQL tables, providing SQL performance at runtime. Provides a "buffer" for migration; users can move to SQL tables first without immediate code changes or massive testing efforts, then gradually transition functions to SQLRPGLE later. 

Additional Information

Control Database access (RLA vs SQL) based on Individual File Generation Mode

Implementing SQL/DDL

Summary of options:

1. If your primary goal is to transition the generated source code to SQLRPGLE without modifying existing access paths, the Function Level Setting (Option 1) is the most efficient choice, as it eliminates the need for access path regeneration; however, it forces all files within that function to be accessed via SQL cursors.
   
   
2. For those seeking a total transition where both the access paths and the source code utilize SQL, a combination of Option 2 and Option 3 is ideal, though this requires significant impact analysis, regeneration, and testing. Specifically, Option 3 ('B-Mixed') is the best fit for your exact requirement of mixing some files with SQL cursors and some files with native RPG CHAIN logic, though it is limited to EDTFIL and EEF function types.
   
   
3. If you prefer a phased migration that prioritizes database performance without immediate code changes, the DDL approach (Option 4) is superior. It allows you to convert tables to SQL while maintaining native CHAIN and SETLL operations in your existing programs, providing a stable intermediate stage before eventually moving to full SQLRPGLE.