Database Joins Process Flow

Lawson applications navigate data based on predefined database relationships within Lawson metadata. Lawson database drivers generate SQL statements using the Lawson prefix as the table aliases. Predefined metadata definitions (data relationships) are used by Lawson APIs to retrieve data, rather than relying on ad hoc queries. This allows the database driver to understand which joins are good candidates and how they should be implemented.

The database driver retrieves data from the database when an application initiates a request. The retrieved data is managed by array fetching buffers and Smart I/O caches to dramatically reduce the number of inter-process communications (context switches). The following diagram shows how the system handles the data request using joined tables.

Process flow: Data retrieval with database joins implemented
  • A Lawson application sends an 850-FIND-BEGRNG API request that includes related records from separate tables.

    This initiates a call to the Lawson database driver (ibmdb), which in turn creates a SQL SELECT statement with a left join to obtain relevant data from the related tables.

  • The data is retrieved from the database and flat rows are returned to the array fetch buffer.

  • Data from the array fetch buffer is distributed to Smart I/O buffers.

  • The initial record sought by the application (the "root row") is returned.

  • The application reads the related row to obtain the data from the Smart I/O buffer.

  • The application requests another set of data with an 860-FIND-NXTRNG API.

  • Array Fetch buffer is read and rows are distributed to Smart I/O buffers.

  • The application requests more data, and the process iterates from step 3 through step 8.