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.