GENLTOPT variable to prevent link table optimization invalid SQL

Products

• Gen - Workstation Toolset
• Gen

Issue/Introduction

When the Gen 8.6 generator attempts to generate SQL code for link tables, there are some situations where incorrect SQL is generated as a result for READ, READ EACH,  SUMMARIZE, or SUMMARIZE EACH statements that involve link tables. Link tables may result from a many-to-many relationship between two entities defined in the data model and implemented in the Data Structure List.  

This SQL may results in runtime errors.

For example in Oracle: 

ORA-00904: "xxxxxx": invalid identifier    
SQL-02114: Invalid SQL Cursor usage: trying to CLOSE a CLOSEd cursor          

For example in MS/SQL:

Identifier "XXXXXX" could not be bound.
SQLCode  : 8180 - SQLState : 42000

For example in DB2:

SQLCODE=-206
SQLSTATE=42703

Environment

Gen 8.6

Cause

In Gen r8.0 changes were made to the generator optimizer to handle link tables more efficiently. The link table is the implementation result in the Data Structure List (DSL) for a many-to-many relationship. The generator optimizer changes may result in runtime errors.

Resolution

To handle these situations a change was introduced which resulted in the GENLPOPT variable to enabled the optimizer to be turned off. 

To turn the SQL optimization off, create a system environment variable named: GENLTOPT and set this variable to 'N' or 'NO'.   By default SQL optimization is ON.  Next, launch the Toolset and generate the Pstep or Action Block in question that contains referencing the link table. 

Gen also includes a .bat file named noltopt.bat located in "%Gen86%\Gen" ("C:\Program Files (x86)\CA\Gen86\Gen" by default). This .bat file will also set the set GENLTOPT=N and then launch the Toolset when the .bat file is executed.

If using this variable to generate Action Blocks or PSteps,  performance improvements that result from the Link Table SQL Optimization feature will be lost.

Additional Information

Gen Edge Communities