Creating explain snapshots for dynamic SQL or XQuery statements

A number of steps are required to create explain snapshots for dynamic SQL or XQuery statements

Procedure

To create an explain snapshot for a dynamic SQL or XQuery statement:

  1. If the database manager has not already been started, issue the db2start command.
  2. Ensure that explain tables exist in your database.
    To do this, follow the instructions in Creating the explain tables.
  3. From the CLP prompt, connect to the database that you want to use.
    For example, to connect to the SAMPLE database, issue the connect to sample command.
  4. Create an explain snapshot for a dynamic SQL or XQuery statement, using either of the following commands from the CLP prompt:
    • To create an explain snapshot without executing the SQL or XQuery statement, issue the set current explain snapshot=explain command.
    • To create an explain snapshot and execute the SQL or XQuery statement, issue the set current explain snapshot=yes command.

    This command sets the explain special register. Once it is set, all subsequent SQL or XQuery statements are affected. For more information, see the CURRENT EXPLAIN SNAPSHOT special register and the SET CURRENT EXPLAIN SNAPSHOT statement.

  5. Submit your SQL or XQuery statements from the CLP prompt.
  6. Optional: To turn off the snapshot facility, issue the set current explain snapshot=no command after you submit your SQL or XQuery statements.