Control Fetch and Insert Size for a Program

If you determine, through the Analyze Timed Statistics feature (analyze_stats.pl) or some other method, that a particular program is spending more time retrieving data than it should, the problem might be that the ARRAYBUFSIZE variable has been set inappropriately for the program.

The ARRAYBUFSIZE variable in the ORACLE database driver configuration file lets you control the number of records that are retrieved when a program performs a FindNlt, FindBegRng, or FndSubRng call to the database. The setting you make in the database driver configuration file applies to all programs in a data area. The default for this setting is 10.

However, depending on how a particular program functions, the setting in the database driver configuration file might not be appropriate. The most likely reason for this is that the program is retrieving data in a way that is not typical, and therefore the number of records to be retrieved at one time should also be different. You can create a configuration file for the program (program.cfg) that contains a setting for ARRAYBUFSIZE. This configuration file setting overrides the setting in the database driver configuration file for the specific program only. If a configuration file has already been created for the program, add the setting for ARRAYBUFSIZE to this file. For information about program configuration files, see Lawson Administration: Server Setup and Maintenance.

If you have determined that you need to change ARRAYBUFSIZE for a particular program, set the variable in the program configuration file to be the opposite of what it is in the database driver configuration file. For example, if the database driver configuration file setting is 10, trying setting it to 50 in the program configuration file.

The INSERTBUFSIZE variable can also be set for a specific program, although the situation where this might be needed is much less common. The same general guidelines apply for INSERTBUFSIZE as for ARRAYSIZE. Use the Analyze Timed Statistics feature to determine if a program is spending an inordinate amount of time inserting rows. If it is, create (or update) a program configuration file with a setting for INSERTBUFSIZE that is the opposite of the setting in the database driver configuration file. Bear in mind that using a larger value for INSERTBUFSIZE increases the memory used for the buffer.

Example of program-specific buffer settings

The following configuration file entries create a default array buffer size of 10, with override values of 1 for GL292 and 100 for HR11:

  • %LAWDIR%\ dataarea \ORACLE

    ARRAYBUFSIZE=10
  • %LAWDIR%\ dataarea \glsrc\GL292.cfg

    ARRAYBUFSIZE=1
  • %LAWDIR%\ dataarea \hrsrc\HR11.cfg

    ARRAYBUFSIZE=100