Qualifying tables for SQL Explain processing

In bind processing, the target PLAN_TABLE, which is the current sqlid.PLAN_TABLE, and the target tables that will be processed by the SQL statements are qualified separately. However, SQL EXPLAIN processing requires that you combine these processes when tables are qualified. If the qualifying process is not done correctly, the tables will be created with the wrong qualifier.

About this task

The PLAN_TABLE is qualified by the user ID in the owner parameter, and the application tables are qualified by the user ID that is specified for the qualifier parameter.

To ensure that the tables are qualified correctly, use one of the following methods:

Create a PLAN_TABLE under the ID that was used for the qualifier parameter and set the current SQLID to that value before issuing the TEST or EXPLAIN command.
Use the PLAN_TABLE from the owner parameter and create aliases or synonyms to the application tables using the SPUFI that is shown in the following figure to generate CREATE SYNONYM commands. This SPUFI only allows for one set of applications to be mapped to the OWNER.PLAN_TABLE at a time.
Specify OPTIONS CREATE TABLES before the TEST or EXPLAIN command. Specifying OPTIONS CREATE TABLES first creates the PLAN_TABLE and DSN_STATEMNT_TABLE that are required to process the TEST and EXPLAIN commands. These tables are dropped at the end of the process. They are created as implicit tables where the CREATE statement does not specify a table space.

The SPUFI shown in the following figure generates the synonyms that are required for a single set of application tables.

Figure 1. Synonym generation example

-- THIS WILL EXAMINE ALL THE TABLES FOR CREATOR ID = PUBLIC01         
-- AND CREATE A SYNONYM FOR THAT TABLE UNDER P390H IF                 
-- THERE ISN'T A SYNONUM OR ALIAS FOR THAT TABLE NAME ALREADY         
-- NOTICE THAT THE SUBSELECT FROM SYSTABLES DOESN'T QUALIFY           
-- THE TYPE VALUE BECAUSE EITHER AN ALIAS, TABLE OR VIEW              
-- WILL PREVENT THE CREATION OF A NEW SYNONYM                         
   SELECT 'CREATE SYNONYM ' CONCAT RTRIM(NAME) CONCAT ' FOR '         
     CONCAT RTRIM(CREATOR) CONCAT '.' CONCAT RTRIM(NAME)              
     CONCAT ';'                                                       
      FROM SYSIBM.SYSTABLES A                                         
        WHERE CREATOR = 'PUBLIC01'                                    
         AND NOT EXISTS (SELECT 1 FROM SYSIBM.SYSSYNONYMS B           
                  WHERE B.CREATOR = 'P390H'                           
                    AND B.NAME = A.NAME)                              
         AND NOT EXISTS (SELECT 1 FROM SYSIBM.SYSTABLES   C           
                  WHERE C.CREATOR = 'P390H'                           
                    AND C.NAME = A.NAME)                              
    ORDER BY 1