Gen not always generating an SQL parameter marker (?)

Products

• Gen - Workstation Toolset

Issue/Introduction

Using Gen COBOL and Java applications to target DB2 on z/OS
An SQL parameter marker (?) needs to be used in Gen generated code, to improve the DB2 z/OS - Dynamic Cache Usage (to avoid doing too many PREPARE statements)
For Java it has been noticed that Gen only generates embedded SQL with a parameter marker when a view is used in the WHERE clause of an action block entity statement like a READ i.e. when a hardcoded value is used in the READ WHERE clause it does not generate embedded SQL with a parameter marker.
Can it be configured to be consistent and always generate a parameter marker?

Environment

Release: 8.6

Component: Gen Workstation Toolset

Resolution

The existence of SQL parameter markers completely depends upon the language being generated:

• Generated Java/JDBC and ODBC code uses SQL parameter markers.
• For generated COBOL code the embedded SQL is static SQL and does not use parameter markers or PREPARE commands.
  
  

For generated code which uses SQL parameter markers:
If  a hardcoded value (e.g., 101) is being used in  READ statement, then Gen will use that value in the generated SQL.  
If  a view is being used in the READ statement, then Gen will use a parameter marker.
So if PAD logic is coded like the following, a parameter marker will be used in the generated SQL.
 
  |  +- READ cobon 
  |  |        WHERE DESIRED cobon id IS EQUAL TO in cobon id 
  |  +- WHEN successful 
  |  +- WHEN not found
  |  +--

In this example, any value can be used just by passing in that value to the Action Block via the import view attribute "in cobon id".
Alternatively,  a local view can be defined to the value e.g., local cobon id. This local view could be set to the import view value or it could be set to a hardcoded value. Then this could be used in the READ statement.
  
  |  
  |  SET local cobon id TO in cobon id
  |            OR
  |  SET local cobon id TO 101
  |  
  |  +- READ cobon 
  |  |        WHERE DESIRED cobon id IS EQUAL TO local cobon id 
  |  +- WHEN successful 
  |  +- WHEN not found
  |  +--

In summary, as long as a view is used in a READ statement,  Gen will use a parameter marker in the generated SQL for the relevant languages which support it.

Additional Information

Lynn, February 28, 2023:
Made some minor changes post publication to improve understanding.
Previous article version stored on Wayback Machine Internet Archive: https://web.archive.org/web/20230228012149/https://knowledge.broadcom.com/external/article?articleId=251566