By default, the Natural SQL driver manages a table with the 16 most recently used Natural statements. All statements in this table are marked as prepared, which indicates that the statement can be executed immediately without being compiled by the database system.
To ensure maximum performance, the dynamic parameters
SQLRELCMD and SQLMAXSTMT are
provided. These parameters configure the handling of the SQL driver's statement
table. Note that these parameters are not profile
parameters.
This parameter determines when commands are to be released from the SQL statement table.
Possible values:
ENDGP (default): if a generated program terminates, all
statements from this program that are in the statement table are removed from
the table.
NEVER: No statement will be deleted from the table.
This parameter determines the size of the statement table.
Possible values:
1 to 64 (default: 16)
If you set the SQLMAXSTMT parameter, keep the
following in mind:
Resource consumption may be higher if you are keeping more prepared statements in the table.
If the size of the statement table exceeds the limit of dynamic SQL statements in the target database, the application will receive SQL errors.
It depends on the database whether there is a real benefit from the
SQLMAXSTMT optimization.
In general, performance in batch-type applications will be improved
if the number of PREPARE statements is minimized, while
performance in online applications will probably be worse because of the
increased resource consumption of the target database.
To set the above parameters dynamically, enter them when starting Natural:
natural sqlrelcmd=never sqlmaxstmt=40
Natural will then start with a statement table size of 40 and the statement table will only be cleared when Natural is terminated.