Performance enhancements with the JOIN keyword

The CM/PILOT component supports the use of the JOIN keyword, which can significantly reduce the execution time of a DML statement that searches multiple tables.

The following rules govern the use of the JOIN keyword:

The JOIN keyword must follow the object type in the DML statement and must precede a SET, INCLUDE, or WHERE clause.
You can use either the actual Db2 catalog names or the CM/PILOT DML object-type keywords with the JOIN keyword and in the WHERE clause.
You must use name prefixes if ambiguity exists in the DML statement. In the preceding example, name prefixes are required because the DBNAME attribute is common to both of the catalog tables that are used in the WHERE clause.
You can use the JOIN keyword only to improve the performance of the WHERE clause. You cannot update any attribute of the specific catalog table following the JOIN keyword. For example, an error will occur in the example shown in the following figure because the SQTY attribute is not an attribute of TABLESPACES (SYSIBM.SYSTABLESPACE).

Incorrect use of the JOIN keyword

MIGRATE TABLESPACES
JOIN TABLEPARTS
SET CREATOR = 'ABCD',
    SQTY = 123
    WHERE TABLEPARTS.DBNAME = TABLESPACES.DBNAME AND
          TSNAME = NAME AND
          STORNAME = 'ABCDEFG';

DML for migrating table spaces

Example

The following DML statement migrates table spaces whose partitions use a specific storage group.

MIGRATE TABLESPACES INCLUDE ALL
        WHERE EXISTS (SELECT * FROM SYSIBM.SYSTABLEPART
        WHERE SYSIBM.SYSTABLEPART.DBNAME =
              SYSIBM.SYSTABLESPACE.DBNAME AND
              NAME = TSNAME AND
              STORNAME = 'ABCDEFG');

DML for migrating table spaces with a JOIN

Example

The following DML statement modifies the DML statement with a JOIN keyword, which should significantly reduce execution time.

This example uses CM/PILOT DML object-type keywords for the actual catalog table names.

MIGRATE TABLESPACES
JOIN TABLEPARTS INCLUDE ALL
        WHERE TABLEPARTS.DBNAME = TABLESPACES.DBNAME AND
              TSNAME = NAME AND
              STORNAME = 'ABCDEFG';

DML for replicating changes in multiple databases

Example

The following statement uses a JOIN statement 3 to create CD entries for a new work ID. Both the SYSIBM.SYSDATABASE table and a LIKE statement with a wildcard character (%) are used to select all of the databases with names that are prefixed with AAMX1.

REPLICATE WORKID
SET WKOWNER='RIHJCB'
    WHERE WKOWNER='JCB'
      AND WKNAME='BWSDB001';
CHANGE DATABASES
JOIN SYSIBM.SYSDATABASE
SET DBNAME=SYSIBM.SYSDATABASE.NAME
    WHERE DBNAME='AALURU34' AND
          SYSIBM.SYSDATABASE.NAME LIKE 'AAMX1%';

DML for replicating work IDs to multiple databases

Example

The following statement is similar to the preceding example, except that it illustrates the creation of a new work ID for each database that is named like AAMX1. The name for each of the new work IDs is the name of the original work ID suffixed with the name of the database.

To connect the changed database CD table entry with the replicated work ID, you must specify, in the SET statement, the association between the CD entry names or owners and the work ID names and owners.

REPLICATE WORKID
JOIN SYSIBM.SYSDATABASE
SET WKOWNER='RIHJCB',
    WKNAME='BWSDB001' CONCAT
    SUBSTR(SYSIBM.SYSDATABASE.NAME,1,8)
    WHERE WKOWNER='JCB'
      AND WKNAME='BWSDB001' AND
          SYSIBM.SYSDATABASE.NAME LIKE 'AAMX1%';
CHANGE DATABASES
SET DBNAME=SYSIBM.SYSDATABASE.NAME
    CDNAME='BWSDB001' CONCAT
    SUBSTR(SYSIBM.SYSDATABASE.NAME,1,8)
    WHERE DBNAME='AALURU34' AND
          SYSIBM.SYSDATABASE.NAME LIKE 'AAMX1%';

Performance enhancements with the JOIN keyword

DML for migrating table spaces

DML for migrating table spaces with a JOIN

DML for replicating changes in multiple databases

DML for replicating work IDs to multiple databases

On this page