How can I know the execution plan of a SQL statement? 1

[011179]

Dear All,

How can I know the execution plan of a SQL statement in DB2 V7.2 ??

Thanks!

 

[MarcLodge]

I've not used DB2 7.2 so pardon my question but, does it have plan_table and the EXPLAIN facility available?

 

[011179]

I know DB2 V7.2 have plan_table and EXPLAIN facility from the documentation!
But I don't know how to use it and how to obtain the execution plan by using the tools! Would you mind tell me that the method to obtain the execution plan of a SQL Statement in DB2??

many thanks!!

 

[matchum]

If you do not already have the explain tables, their DDL in in EXPLAIN.DDL so go into DB2 command mode and issue the command:
db2 -tf EXPLAIN.DDL

If you are using the control centre, you navigate to the db in question and rm-click to bring up the facilities, one of which is "Explain DDL". Follow the GUI from there and you get the explain tables populated and automatically analysed, including CPU; elapsed with colour gradation and expandability (I rarely use it but for faulty indices it is brilliantly simple).

If you are NOT using the GUI, you have db2expln and dynexpln etc. Refer to the Admin guide for specific details.

 

[011179]

Thank you very much!