Running Explain Plan Prompts to Create Explain Tables

Toad for IBM DB2
1

If I click “Execute Explain Plan”, I dont get the plan but prompt “Create Explain Tables”

Toad4DB2_1.jpeg

In the prompt above, I have to select one or both of the table spaces to enable Yes button.

I tried selecting both table spaces and clicking on Yes will create a script which when I run, it executes properly.

However, If after successfully executed scripts for Explain Tables, I try to again Execute Explain Plan, I get again the same prompt.

Bottom Line, I cannot Execute Explain Plan.

Toad for DB2 version 6.1.0.125

Thanks,

2

Can you please post the script that Toad generates to create the explain tables?

We would like to take a look at that. We are wondering if all of the tables actually got created too. Thanks.

3

In the past when I ran into this similar issue the workaround is to create the tables using the EXPLAIN.DDL. On AIX this will be located in INSTHOME/sqllib/misc.

Go to that directory and run the following:

db2 CONNECT TO database-name

db2 -tf EXPLAIN.DDL

Another option is to 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)))

Keep in mind if you created the Explain tables in DB2 V9.7 or lower, and you upgraded to DB2 V10.1 or higher, the Explain tables will need to be dropped and recreated.

www-01.ibm.com/…/t0059594.html

Hope this helps.