DB2 Explain — What privileges are needed?

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.

1

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.

etc.

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:

2

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:

SYSTOOLS.EXPLAIN_ARGUMENT

SYSTOOLS.EXPLAIN_DIAGNOSTIC
SYSTOOLS.EXPLAIN_DIAGNOSTIC_DATA
SYSTOOLS.EXPLAIN_INSTANCE
SYSTOOLS.EXPLAIN_OBJECT
SYSTOOLS.EXPLAIN_OPERATOR
SYSTOOLS.EXPLAIN_PREDICATE
SYSTOOLS.EXPLAIN_STATEMENT
SYSTOOLS.EXPLAIN_STREAM

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s