|
Problem IC67520
|
Status: Closed |
DB2EXFMT OR ACCESS PLAN GRAPH FROM OPTIM QUERY TUNER MIGHT FAIL WITH SQLCODE -551. |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
The db2exfmt explain table format command or Optim Query Tuner
(OQT)'s access plan graph tuning activity might fail depending
on the authorizations of the user who is executing the
operation. The problem can occur only when the tool is
exercised for the first time against a database, or when the
tool is used for the first time after the DB2 server has been
upgraded to a new fix pack level.
For db2exfmt, the user will observe:
SQL0001N Binding or precompilation did not complete
successfully.
The information will direct the user to a message file:
Error during bind.
Bind messages can be found in db2exfmt.msg
The message file will contain SQL0551N errors of the form:
LINE MESSAGES FOR db2exfmt.bnd
------
----------------------------------------------------------------
----
SQL0061W The binder is in progress.
29223 SQL0551N "ZURBIE" does not have the required
authorization
or privilege to perform operation "EXECUTE" on
object
"SYSPROC.EXPLAIN_FROM_SECTION".
SQLSTATE=42501
29251 SQL0551N "ZURBIE" does not have the required
authorization
or privilege to perform operation "EXECUTE" on
object
"SYSPROC.EXPLAIN_FROM_ACTIVITY".
SQLSTATE=42501
29279 SQL0551N "ZURBIE" does not have the required
authorization
or privilege to perform operation "EXECUTE" on
object
"SYSPROC.EXPLAIN_FROM_CATALOG".
SQLSTATE=42501
SQL0082C An error has occurred which has terminated
processing.
SQL0092N No package was created because of previous
errors.
SQL0091N Binding was ended with "5" errors and "0"
warnings.
For OQT, the exact error screen seen may differ depending on the
version of the DB2 server and/or the version of the OQT client.
A db2trc at the DB2 server will show a sqlcode -551 with
sqlerrmc: username EXECUTE SYSPROC.EXPLAIN_FROM_SECTION.
This problem was first introduced in DB2 V9.7 Fix Pack "0". |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* N/A *
****************************************************************
* PROBLEM DESCRIPTION: *
* The db2exfmt explain table format command or Optim Query *
* Tuner *
* (OQT)'s access plan graph tuning activity might fail *
* depending *
* on the authorizations of the user who is executing the *
* *
* operation. The problem can occur only when the tool is *
* *
* exercised for the first time against a database, or when the *
* *
* tool is used for the first time after the DB2 server has *
* been *
* upgraded to a new fix pack level. *
* *
* *
* *
* For db2exfmt, the user will observe: *
* *
* *
* *
* SQL0001N Binding or precompilation did not complete *
* *
* successfully. *
* *
* *
* *
* *
* *
* The information will direct the user to a message file: *
* *
* *
* *
* Error during bind. *
* *
* Bind messages can be found in db2exfmt.msg *
* *
* *
* *
* *
* *
* The message file will contain SQL0551N errors of the form: *
* *
* *
* *
* LINE MESSAGES FOR db2exfmt.bnd *
* *
* ------ *
* *
* --------------------------------------------------------------
* ---- *
* *
* SQL0061W The binder is in progress. *
* *
* 29223 SQL0551N "ZURBIE" does not have the required *
* *
* authorization *
* *
* or privilege to perform operation *
* "EXECUTE" on *
* object *
* *
* "SYSPROC.EXPLAIN_FROM_SECTION". *
* *
* SQLSTATE=42501 *
* *
* 29251 SQL0551N "ZURBIE" does not have the required *
* *
* authorization *
* *
* or privilege to perform operation *
* "EXECUTE" on *
* object *
* *
* "SYSPROC.EXPLAIN_FROM_ACTIVITY". *
* *
* SQLSTATE=42501 *
* *
* 29279 SQL0551N "ZURBIE" does not have the required *
* *
* authorization *
* *
* or privilege to perform operation *
* "EXECUTE" on *
* object *
* *
* "SYSPROC.EXPLAIN_FROM_CATALOG". *
* *
* SQLSTATE=42501 *
* *
* SQL0082C An error has occurred which has terminated *
* *
* processing. *
* *
* SQL0092N No package was created because of previous *
* *
* errors. *
* *
* SQL0091N Binding was ended with "5" errors and "0" *
* *
* warnings. *
* *
* *
* *
* *
* *
* *
* *
* For OQT, the exact error screen seen may differ depending on *
* the *
* version of the DB2 server and/or the version of the OQT *
* client. *
* A db2trc at the DB2 server will show a sqlcode -551 with *
* *
* sqlerrmc: username EXECUTE SYSPROC.EXPLAIN_FROM_SECTION. *
* *
* *
* *
* *
* *
* This problem was first introduced in DB2 V9.7 Fix Pack "0". *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.8 Fix Pack 4. *
**************************************************************** |
| Local Fix: |
For db2exfmt, have a database administrator execute: BIND
db2exfmt.bnd. After a successful bind of this bnd file, the
original user who observed the problem will be able to run
db2exfmt.
For OQT,
1. Have a database administrator (or the database creator) be
the first to exercise the access plan graph tuning activity for
the database. After first successful execution of access plan
graph, the original user who observed the problem will be able
to exercise access plan graph as well.
2. Have a database administrator execute: BIND
db2ExplainRtns.bnd. After a successful bind of this bnd file,
the original user who observed the problem will be able to
exercise access plan graph. |
| available fix packs: |
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
Problem was first fixed in Version 9.8 Fix Pack 4. |
| Workaround |
See Local Fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 29.03.2010
04.08.2011
04.08.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.4
|
 |
