I had a user who emailed me the other day that he was getting an error when trying to generate an explain plan for this simple SQL.
He was using IBM Data Studio to view the Explain plan. Below is the screen shot of the error.
So as any DBA would do, I asked him few questions:
1) When did you see this error? Date and time.
2) What is your user id?
3) Which database are you connecting?
4) Did the explain plan work before? He answered “no” to this.
A simple research on IBM Information Center reveals that ‘Explain’ is a database level privilege and could be granted by ‘Grant’ statement.
Here is what I used to grant explain privilege to this user:
1) db2 “connect to database dbname”
2) db2 “grant explain on database to user username”
I assumed this would work. I asked the user to test if the ‘Explain’ works but this time the user got a new error:
Although the error appears to point out that the use does not have INSERT privilege on SYSTOOLS.EXPLAIN_INSTANCE, fact is there are 9 Explain related tables in DB2 9.7 and the user is expected to have INSERT and SELECT on all (if not all majority of them) these explain tables and so I went ahead and granted these privileges on the following tables:
After I granted these privileges, the user was able to generate the explain plan in data studio.
Hope this helps in resolving issues around ability to view explain plans in DB2 9.7