Database Joins Overview

Careful implementation of database joins at the database driver level, combined with data caching strategies, can enhance the ability to quickly retrieve records from the database. The use of joins reduces the number of context switches that occur when records are retrieved from the database. Taking direct advantage of SQL database capabilities improves performance by putting more of the data retrieval work at SQL database server, instead of the application client.

Lawson 4GL database APIs have been enabled for joins at the database level, allowing any Lawson application using data retrieval APIs to be enabled for joins. This provides opportunity for performance benefits, based on your data processing needs, by implementing joins for targeted programs.

The implementation of joins within the database driver is transparent to Lawson applications—they function correctly whether a join is created or not. To use the generation of join statements by the database drivers, your database configuration must meet the criteria shown in the following list. The database driver checks for these conditions, and does not create invalid joins.

• The tables being joined must reside in the same SQL database.

• Joins are supported only for one-to-one relationships and self-joins are not allowed.

  Note: This limitation is due to the current Lawson caching implementation.

• The internal size of the shared memory buffer is the limitation of maximum join record length.

• Returned values must be less than 16K.

• A maximum of 10 relations can be evaluated in a join.

• Add a statement to a configuration file. Enabling Generation of Joins for an Application