<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)
