SELECT 'FOR UPDATE' statements in Gen generated code

Products

• Gen
• Gen - Workstation Toolset
• Gen - Run Time Distributed
• Gen - Host Encyclopedia

Issue/Introduction

Is there a setting in the model to turn on or off SELECT statements generated with 'FOR UPDATE' or is this forced by the code generator?

  7 | +- READ ea number_control
  8 | |       WHERE DESIRED ea number_control system_code IS EQUAL TO i number_control_update input_key
  9 | +- WHEN successful
 10 | | SET e number_control_update next_number TO ea number_control next_sequential_number
 12 | | +- UPDATE ea number_control
 13 | | |  SET next_sequential_number TO ea number_control next_sequential_number + 1
 
EXEC SQL DECLARE CUR_2142997848_1 CURSOR FOR                 
SELECT             >>>> generated from the READ                                                                                     
FROM                                                         
WHERE                                                        
     NUMBER_CONTROL01."SYSTEM_CODE" = :INPUT-KEY-001TP       
FOR UPDATE OF "SYSTEM_CODE_DESC",     <<< how can we turn this ON or OFF                        
              "NEXT_SEQ_NO"                                
OPTIMIZE FOR 2 ROWS                                          
END-EXEC

Environment

Release : 8.6

Resolution

There is no flag setting to turn SQL SELECT statement 'FOR UPDATE' ON or OFF. The 'FOR UPDATE' is forced by the code generator.

Several test cases were reviewed, varying the flag that sets the control of cursor generation property for the Gen READ statements. Regardless of what that flag setting is, the 'FOR UPDATE' clause being generated is based on the existence of a follow up Gen UPDATE statement for that same entity view that has been READ. The code generator looks ahead to determine if there is an UPDATE, if it exists then it creates a SQL CURSOR FOR UPDATE.  If the Gen UPDATE statement is removed, the SQL CURSOR FOR UPDATE is consequently removed from the generated SQL.   

One suggested method to prevent the 'FOR UPDATE' for a READ from being generated, is to have two entity views for the same entity. One entity view for the READ(s) followed by UPDATE, and one for just the READ(s). Given a condition use the entity view you need.