Generate Execution Plans

To generate an execution plan in DB2, the monitoring user ID must have access to the explain_* tables.
Create the explain tables one of theses methods:
  • Call the SYSPROC.SYSINSTALLOBJECTS procedure: 
    {{}}

{{db2 CONNECT TO database-name
db2 }}

{{CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C',
CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))}}

    This call creates the explain tables under SYSTOOLS schema. To create them under a different schema, specify a schema name as the last parameter in the call.

  • Run the EXPLAIN.DDL command file: 
    {{}}
{{db2 CONNECT TO database-name
db2 -tf EXPLAIN.DDL}}

{{}}
Note: Explain plans will not function properly unless the monitoring user ID is granted SELECT privilege on every table being accessed in the SQL as well as the necessary explain_* tables.