ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Prepare/Declare Statements in RPGLE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Prepare/Declare Statements in RPGLE

    I have been trying to find some information that would help me better understand how to use the SQL Prepare/Declare statements in an rpgle program. My understanding is that there is a performance benefit to using prepare in scenarios where an sql query will be executed multiple times. However, I am writing a service program used in IWS and would like to execute the prepare/declare statements only once. In a simple program it is easy to execute the prepare and declare at the start of the program I can't seem to find any information that would help me determine the following:

    1. Is there a keyword or function that I can call to determine if a cursor has been declared already. I would liken that to the if %open() function used on user open files to determine if it is necessary to open the file or not.

    2. Does RPG recognize if a prepare/declare pair has already been executed and skip over it again if the same procedure in a service program is executed and the prepare and declare statements are a part of that procedure?

    3. If my service program is set to close the SQL cursor when the activation group ends, can I simply set my own initialization routine in the service program that only executes once, or is there a timeout or a penalty to keeping that prepared statement opened indefinitely if that service program is always active? I'm a little fuzzy on when IWS might end the activation group for a service progam responding to restful requests.

    Any information would be greatly appreciated.

  • #2
    1. Using static instead of dynamic SQL is the best choice. Most statement you think must be prepared dynamically can be executed as static SQL. The only thing that cannot be handle with host variables are schemas/libraries and tables/views.
    2. If you are using dynamic SQL you build the SQL statement at runtime.This statement must be parsed, syntax checked and converted into an executable SQL statement (this step is done for static SQL already at compile time).
    If you want to prepare your SQL statement once and execute multiple times, you have to use parameter markers, i.e. instead of including the (host) variable values directly in the statement string you use parametermarkers (?). when opening the cursor you specify the host variables that include the values which include the values for the parameter markers (first parameter marker is replaced by the first host variable, the second parameteter marker is replaced with the second one etc.
    PREPARE and EXECUTE (if you do not have a cursor) or DECLARE, PREPARE, OPEN, FETCH, CLOSE should be seen as unit and run within the same procedure. If you want to check whether the SQL statement is changed, just save the previous statement (as a static variable) and compare it with the new statement. If it is the same statment you just do not execute the prepare statement.
    When executing a prepare statement the previous ODP (Open data path) is deleted and a FULL OPEN will be parformed. If you do not execute an PREPARE statement only OPEN (or EXECUTE) an existing ODP can be reused. Opening the ODP is the most timeconsuming part when excuting an SQL statement, so the number of FULL OPENS should be reduced to a minimum. If the ODP can be resused only a PSEUDO Open is performed, i.e. only the data is refreshed.

    Closing a cursor does normally not delete the ODP. The compile option CLOSQLCSR has nothint to do with the CLOSE Cursor statement. After a CLOSE Cursor statement the CURSOR can be reopened, i.e. the cursor data can get updated (PSEUDO Open). The CLOSQLCSR determines when the ODP will be deleted (... sure all cursors are close now!). If you use activation group new, and CLOSQLCSR *ENDACTGRP, all ODPs generated and used within the program are deleted at program end, and with each (new) execution a FULL OPEN must be performed.

    Birgitta

    Comment

    Working...
    X