Correctly ending a called RPG/400 program that uses SQL

SQL run time builds and maintains data areas (internal SQLDAs) for each SQL statement that contains host variables.

These internal SQLDAs are built the first time the statement is run and then reused on subsequent executions of the statement to increase performance. The internal SQLDAs can be reused as long as there is at least one SQL program active. The SQL precompiler allocates static storage used by SQL run time to manage the internal SQLDAs properly.

If an RPG/400® program containing SQL is called from another program that also contains SQL, the RPG/400 program should not set the Last Record (LR) indicator on. Setting the LR indicator on causes the static storage to be re-initialized the next time the RPG/400 program is run. Re-initializing the static storage causes the internal SQLDAs to be rebuilt, thus causing a performance degradation.

An RPG/400 program containing SQL statements that is called by a program that also contains SQL statements, should be ended one of two ways:

By the RETRN statement
By setting the RT indicator on.

This allows the internal SQLDAs to be used again and reduces the total run time.