467,864 Members | 1,814 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,864 developers. It's quick & easy.

granting db2expln for the public?

Hello,

I have DB2 v8.1 for Linux for a class environment; each user has
his/her own database as dbadm and they are suppose to study queries
from another database.

They have the right permissions to this database so they can do the
SELECTS but they cannot alter the tables.

Now I want them to be able to study the query plans.

My question is how to grant them permission to run db2expln.
From the documentation (for example:
http://webdocs.caspur.it/ibm/web/udb...0/db2d0369.htm) they have
to have EXECUTE authority for the db2expln package.

Now, I'm not able to do that; the syntax is: db2 GRANT EXECUTE ON
PACKAGE name TO PUBLIC

what's the package name for db2expln? I've tried "db2expln",
"db2expln.pkga" and it's not found.

If after the permission error message I grant execute to the mention
package it works, but the name is of the form "NULLID.SQLNAE00" and it
changes.
I've been able to get query plans in part by:
Creating plan table by running EXPLAIN.DDL.
then: $ db2 set current explain mode explain
and finally they can run db2exfmt.

(I had to struggle with the permissions, finally by granting CREATE
TABLE permissions to the common database it worked)
Nov 12 '05 #1
  • viewed: 9322
Share:
2 Replies
<snip>
Note:
Not all of the tables above are created by default. To create them, run
the EXPLAIN.DDL script found in the misc subdirectory of the sqllib
subdirectory.
Explain tables might be common to more than one user. However, the explain
tables can be defined for one user, and then aliases can be defined for each
additional user using the same name to point to the defined tables. Each
user sharing the common explain tables must have insert permission on those
tables.

<snip>

<snip2>
The db2expln program connects and uses the |db2expln.bnd, db2exsrv.bnd, and
|db2exdyn.bnd files to bind itself to a database the first |time the
database is accessed.
|To run db2expln, you must have the SELECT privilege on |the system catalog
views as well as the EXECUTE privilege for the |db2expln, db2exsrv, and
db2exdyn |packages. To run dynexpln, you must have BINDADD authority |for
the database, and the schema you are using to connect to the database must
|exist or you must have the IMPLICIT_SCHEMA authority for the database. |To
explain dynamic SQL using either db2expln or |dynexpln, you must also have
any privileges needed for the SQL |statements being explained. (Note that if
you have SYSADM or DBADM |authority, you will automatically have all these
authorization |levels.)

<snip2>
BIND ... Grant ... TO ...
may not be necessary if you have SQLNAE00 under NULLID.

If i remember correctly, this utility is 'auto-bind'.

If you want to know the bind file to package name link, use this under
sqllib/bnd
DDCSPKGN db2expln.bnd

Bind File Package Name
------------------------------ ------------------------------
db2expln.bnd SQLNAE00
I think all you need is

-- do this section for each user of the group students

db2 create alias someuser.EXPLAIN_ARGUMENT FOR SharedSchema.EXPLAIN_ARGUMENT

db2 create alias someuser.EXPLAIN_INSTANCE FOR SharedSchema.EXPLAIN_INSTANCE

db2 create alias someuser.EXPLAIN_OBJECT FOR SharedSchema.EXPLAIN_OBJECT

db2 create alias someuser.EXPLAIN_OPERATOR FOR SharedSchema.EXPLAIN_OPERATOR

db2 create alias someuser.EXPLAIN_PREDICATE FOR
SharedSchema.EXPLAIN_PREDICATE

db2 create alias someuser.EXPLAIN_STATEMENT FOR
SharedSchema.EXPLAIN_STATEMENT

db2 create alias someuser.EXPLAIN_STREAM FOR SharedSchema.EXPLAIN_STREAM

-- then

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_ARGUMENT TO GROUP students

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_INSTANCE TO GROUP students

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_OBJECT TO GROUP students

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_OPERATOR TO GROUP students

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_PREDICATE TO GROUP students

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_STATEMENT TO GROUP students

DB2 GRANT INSERT ON TABLE SharedSchema.EXPLAIN_STREAM TO GROUP students

-- then.... maybe this....

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_ARGUMENT TO GROUP students

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_INSTANCE TO GROUP students

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_OBJECT TO GROUP students

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_OPERATOR TO GROUP students

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_PREDICATE TO GROUP students

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_STATEMENT TO GROUP students

DB2 GRANT SELECT ON TABLE SharedSchema.EXPLAIN_STREAM TO GROUP students

-- test with a student account

db2 explain all for values 1

PM

"LazyAnt" <so***********@hotmail.com> a écrit dans le message de
news:f6**************************@posting.google.c om...
Hello,

I have DB2 v8.1 for Linux for a class environment; each user has
his/her own database as dbadm and they are suppose to study queries
from another database.

They have the right permissions to this database so they can do the
SELECTS but they cannot alter the tables.

Now I want them to be able to study the query plans.

My question is how to grant them permission to run db2expln.
From the documentation (for example:
http://webdocs.caspur.it/ibm/web/udb...0/db2d0369.htm) they have
to have EXECUTE authority for the db2expln package.

Now, I'm not able to do that; the syntax is: db2 GRANT EXECUTE ON
PACKAGE name TO PUBLIC

what's the package name for db2expln? I've tried "db2expln",
"db2expln.pkga" and it's not found.

If after the permission error message I grant execute to the mention
package it works, but the name is of the form "NULLID.SQLNAE00" and it
changes.
I've been able to get query plans in part by:
Creating plan table by running EXPLAIN.DDL.
then: $ db2 set current explain mode explain
and finally they can run db2exfmt.

(I had to struggle with the permissions, finally by granting CREATE
TABLE permissions to the common database it worked)

Nov 12 '05 #2
thank you very much!

"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by APAPF | last post: by
3 posts views Thread by Terry | last post: by
reply views Thread by Charles A. Lackman | last post: by
4 posts views Thread by tuarek | last post: by
1 post views Thread by jefftyzzer | last post: by
1 post views Thread by Troels Arvin | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.