Users can choose between two query tuning switches – KEYCACHE JOIN and NO-KEYCACHE JOIN. These switches are mutually exclusive and are applicable only when the first GET statement is called using EXCLUSIVE-LOCK. The switches are described in the following sections.
KEYCAHE JOIN – To enable KEYCACHE join when an application has a -Dsrv switch PRGS_JOIN_NOKEYCACHE enabled, users should specify QUERY-TUNING (KEYCHACHE JOIN) in the ABL query. This will result in populating the key-cache for the Join buffer that is participating in a join query.
NO-KEYCAHE JOIN – To prevent or to turn off the populating of key-cache for the participating join buffer, the user should specify QUERY-TUNING (NO-KEYCACHE-JOIN) in the ABL query.
Note: When an ABL application has most of the GET statement called with NO-LOCK and SHARE-LOCK in that case specifying QUERY-TUNING(NO-KEYCACHE-JOIN) or -DsrvPRGRS_JOIN_NOKEYCACHE may improve the performance of application.
KEYCAHE JOIN – To enable KEYCACHE join when an application has a -Dsrv switch PRGS_JOIN_NOKEYCACHE enabled, users should specify QUERY-TUNING (KEYCHACHE JOIN) in the ABL query. This will result in populating the key-cache for the Join buffer that is participating in a join query.