Array Fetch Buffers

Array fetching at the database level and record caching allows the database driver to make better use of existing shared memory. This reduces the number of context switches (inter-process communications) and improves performance in when Lawson 4GL database APIs are used to fetch data, or when Lawson processes such as DrillAround, dbreorg, or OLEDB retrieve data.

An end user experiences this feature via better throughput on bulk data retrieval operations when the data is held in a SQL database. Inter-process communications (context switches) are costly. For every 10 records retrieved, 22 context switches take place. Implementation of array fetch capability make better use of existing shared memory.

The types of programs realizing performance benefits from array fetch implementation when retrieving data are:

  • Lawson 4GL programs using Lawson 4GL database APIs such as 850-FIND-BEGRNG (RPG: E@DBFINDBEGRNG) or 870-MODIFY-PREV (RPG: E@DBLOCKPRV) for set retrieval operations

  • The OLEDB provider

  • Lawson database utilities (for example, dbdump)

When ladb requests a set of data, the database driver (ibmdb) retrieves enough records to satisfy the full set API request, and caches them at the driver level. The database driver transfers as many records as possible to shared memory, depending on the size of the records being retrieved and the available shared memory. The cache created by ladb is cleared whenever an EndDBWork event occurs, such as a NOT FOUND reply.

The following process is initiated when the Lawson 4GL program PO20 needs to retrieve line items for a purchase order.

Process flow: Data retrieval with array fetching

  • PO20 sends an 850-FIND-BEGRNG (RPG: E@DBFINDBEGRNG) API request, resulting in an in-process call to the database client API, which in turn sends a message (inter-process communication) to the database driver.

  • The database driver converts the request into a SQL SELECT statement, and sends a message (inter-process communication) to the SQL database server requesting up to 10 records.

  • The SQL database server replies with a message containing 10 records which are cached at the database driver.

  • The database driver sends the first record to the database client library.

  • The database client library gives the record to PO20 by populating working storage.

  • PO20 requests the next line item by sending an 860-FIND-NXTRNG (RPG: E@DBFINDNXTRNG) API request, resulting in an in-process call to the database client API, which in turn sends a message (inter-process communication) to the database driver.

  • Instead of sending a message to the SQL database server, the database driver satisfies the request from the cache set up by the request in step 3, and returns one more record back to the database client library.

  • The database client library gives the next record over to PO20 by populating working storage.

The rest of the records are retrieved in the same manner. If more than 10 line items exist, the database driver cache is exhausted before a NOT FOUND is returned. This causes the next FindNextRngDBRec message to trigger another message to the SQL database server requesting up to 10 more records for its cache.

Tracing data retrieval paths with Lawson debugging tools

When debugging is enabled with the DEBUG=TRUE statement in the ladb.cfg file, the database driver logs the number of records returned for each set API request. When application debugging is enabled by the existence of the APPDEBUG file ($LAWDIR/system/APPDEBUG), ladb logs whenever data request is satisfied from the cache instead of direct retrieval from the database. For instructions on enabling debugging, see the Lawson Administration: Server Setup and Maintenance guide.