Creating explain snapshots for dynamic SQL statements

Follow these steps to create an explain snapshot for a dynamic SQL statement:

1. If DB2 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 explain tables.

3. From the DB2 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.

   To create the SAMPLE database, see the section on installing the SAMPLE database in the Administration Guide.

4. Create an explain snapshot for a dynamic SQL statement, using either of the following commands from the DB2 CLP prompt:

   • To create an explain snapshot without executing the SQL statement, issue the set current explain snapshot=explain command.

   • To create an explain snapshot and execute the SQL statement, issue the set current explain snapshot=yes command.

   This command sets the explain special register. Once it is set, all subsequent SQL statements are affected. For more information, see the sections on current explain snapshots in the SQL Reference.

5. Submit your SQL statements from the DB2 CLP prompt.

6. To view the access plan graph for the snapshot, refresh the Explained Statements History window (available from the Control Center), and double-click on the snapshot.

7. Optional. To turn off the snapshot facility, issue the set current explain snapshot=no command after you submit your SQL statements.