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 are 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.