By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,854 IT Pros & Developers. It's quick & easy.

show explain plan

P: n/a
Hello all,

I don't succeed in displaying the explain plan. I use IBM DB2 Universal
Database 8.2.

I tried to do the example given in the online help for "Visual Explain".
The tables EXPLAIN_STATEMENT and EXPLAIN_INSTANCE exist. With VESAMPL.DDL, I
loaded the predefined execution plans.
In the next step, I'ld like to display the loaded access plans. So, I right
clicked on "Show Explained Statements History" and got the result:
"[IBM][CLI Driver][DB2/NT] SQL0104N Auf "ION FROM [...]" folgte das
unerwartete Token ".". Zu den möglichen Token gehören: ",". SQLSTATE=42601
"
I am sorry, but I work with a German Edition of DB2, so the error messages
are in German as well. But I hope, there are some people who understand
them.
Could anyone help me what went wrong there and why the Visual Explain
doesn't work here?

My next try was to use db2expl to show the explain plan.
So I opened db2cmd and went to the bin directory and called: db2expln -d
SAMPLE -t "SELECT * FROM cl_sched".
Unfortunately, I now got the error message: "The server was unable to
establish a buffer for error reporting."
So also here, the display of the explain plan wasn't successful.

It would be great if anyone knows an idea how to display the explain plan.

Cheers,
Ina
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Ina Schmitz wrote:
Hello all,

I don't succeed in displaying the explain plan. I use IBM DB2 Universal
Database 8.2.

I tried to do the example given in the online help for "Visual Explain".
The tables EXPLAIN_STATEMENT and EXPLAIN_INSTANCE exist. With VESAMPL.DDL, I
loaded the predefined execution plans.
In the next step, I'ld like to display the loaded access plans. So, I right
clicked on "Show Explained Statements History" and got the result:
"[IBM][CLI Driver][DB2/NT] SQL0104N Auf "ION FROM [...]" folgte das
unerwartete Token ".". Zu den möglichen Token gehören: ",". SQLSTATE=42601
"
I am sorry, but I work with a German Edition of DB2, so the error messages
are in German as well. But I hope, there are some people who understand
them.
Could anyone help me what went wrong there and why the Visual Explain
doesn't work here?

My next try was to use db2expl to show the explain plan.
So I opened db2cmd and went to the bin directory and called: db2expln -d
SAMPLE -t "SELECT * FROM cl_sched".
Unfortunately, I now got the error message: "The server was unable to
establish a buffer for error reporting."
So also here, the display of the explain plan wasn't successful.

It would be great if anyone knows an idea how to display the explain plan.

Cheers,
Ina

Call DB2 support !

indika
Nov 12 '05 #2

P: n/a
> Call DB2 support !
Not really a help, because I am writing on my diploma and just have the
evaluation version of DB2. As a student, I don't have the money to spend on
support.

Hope, someone else could help me.

Cheers,
ina
Nov 12 '05 #3

P: n/a
Ina Schmitz wrote:
Call DB2 support !


Not really a help, because I am writing on my diploma and just have the
evaluation version of DB2. As a student, I don't have the money to spend on
support.

Hope, someone else could help me.

Cheers,
ina

Very dubious. Let's approach thsi in a more constructive fashion:
cd to sqllib/misc
connect to your database.
set your current schema to something else than your regular userid.
"ina2" for the sake of the argument

db2 connect to <db>
db2 set current schema ina2
db2 -tvf EXPLAIN.DDL
So.. now on to a fresh start (remember to qualify the tables, you're in
ina2..)
db2 "explain plan for select ....."
db2exfmt -d <db> -e ina2 -o test.exfmt
<accept all defaults>
Assuming that works try the same explain plan /db2 exfmt in yoru regular
schema. If it fails you may have somehow corrupted the explain tables.
Just drop them and rerun EXPLAIN.DDL.

If that doesn't work things will get ore interesting :-)

Cheers
Serge

PS; I do have one more far-fetched suspicion. If you have funny
characters in one of your identifiers (apostrophe, double quotes, ...)
there is always a chance that the tool isn't hardened enough against
thouse. That would be DB2 bug then.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
Hello again,

thanks for the answer. Well, I get already some output, but not the expected
one... perhaps you could give me some further help?

[...]
db2 connect to <db>
db2 set current schema ina2
db2 -tvf EXPLAIN.DDL I followed these commands. Then I created a table in the schema ina2:
create table t(test varchar(2));
Just to have a table to put a query onto it. db2 "explain plan for select * from ina2.t"
db2exfmt -d <db> -e ina2 -o test.exfmt
<accept all defaults>

Now I get the following output in test.exfmt:

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
No Explain instances (plans) were found for database sample, source pattern:
%.% for explain table schema INA2.

Well, I suppose the explain plan is not found or even not generated
correctly. How can I verify if it is generated correctly?

Would be glad, if you have some more ideas to find out where the problem
ist.

Cheers,
Ina
Nov 12 '05 #5

P: n/a
Ina Schmitz wrote:
Hello all,

I don't succeed in displaying the explain plan. I use IBM DB2 Universal
Database 8.2.

I tried to do the example given in the online help for "Visual Explain".
The tables EXPLAIN_STATEMENT and EXPLAIN_INSTANCE exist. With VESAMPL.DDL,
I loaded the predefined execution plans.
In the next step, I'ld like to display the loaded access plans. So, I
right clicked on "Show Explained Statements History" and got the result:
"[IBM][CLI Driver][DB2/NT] SQL0104N Auf "ION FROM [...]" folgte das
unerwartete Token ".". Zu den möglichen Token gehören: ",".
SQLSTATE=42601 "
Is this the exact error message or was there something else in the [...]?
Do you know the exact SQL statement that caused the error to be raised?

You might also want to increase the DIAGLEVEL and have a look at the
db2diag.log to see if something more interesting is in there.

$ db2 update dbm cfg using diaglevel 4
<run your things>
$ db2diag -H 1h
My next try was to use db2expl to show the explain plan.
So I opened db2cmd and went to the bin directory and called: db2expln -d
SAMPLE -t "SELECT * FROM cl_sched".
Unfortunately, I now got the error message: "The server was unable to
establish a buffer for error reporting."


Was that the exact error message? The strange thing here is that the
message is in English, so it doesn't seem to be a message returned by DB2.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
Knut Stolze wrote:

Was that the exact error message? The strange thing here is that the
message is in English, so it doesn't seem to be a message returned by DB2.

... could have been a -901. Which would still be good to know...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7

P: n/a
hm, i thought i already posted this, but apparently it didn't get through.
On second thought now it makes sense to me why i never get answers to the
questions i posted:)

When I run db2expln with some invalid option (ina's problem is that she used
db2expln and failed to specify -q in front of the dynamic query) i will get
"The server was unable to establish a buffer for error reporting.", instead
of an normall error message telling me bad syntax or whatever.
However, this only happens until a succesfull db2expln is run once, after
that the normal error messages are returned for invalid options or bad
syntax of db2expln. after that you can;t replicate the "buffer error"
problem until rebooting the computer (or killing god knows what process, but
not db2)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3b*************@individual.net...
Knut Stolze wrote:

Was that the exact error message? The strange thing here is that the
message is in English, so it doesn't seem to be a message returned by DB2.

.. could have been a -901. Which would still be good to know...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #8

P: n/a
>> "[IBM][CLI Driver][DB2/NT] SQL0104N Auf "ION FROM [...]" folgte das
unerwartete Token ".". Zu den möglichen Token gehören: ",".
SQLSTATE=42601 "
Is this the exact error message or was there something else in the [...]?

in the [...] there is the user name (I just didn't want to post it).
Do you know the exact SQL statement that caused the error to be raised?

Yes, it's just a simple select: select * from test
Unfortunately, I now got the error message: "The server was unable to
establish a buffer for error reporting."

Was that the exact error message? The strange thing here is that the
message is in English, so it doesn't seem to be a message returned by DB2.

Yes. In the output file, nothing else is in it! db2cmd.exe gives me as
result:
"
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Enter up to 26 character Explain timestamp (Default -1) ==>
Enter up to 8 character source name (SOURCE_NAME, Default %) ==>
Enter source schema (SOURCE_SCHEMA, Default %) ==>
Enter section number (0 for all, Default 0) ==>
No Explain instances (plans) were found for database sample, source pattern:
%.%
for explain table schema INA2.
If this is not the correct explain table schema, please specify the correct
sche
ma
using the -e option on the command line or correct the setting of the
variable
USER or USERNAME so the correct schema can be found.
Output is in test.exfmt.
Executing Connect Reset -- Connect Reset was Successful."

In my opinion, it looks like there are no explain plans found. What else do
I have to do? With db2 list tables, I see that a lot of tables EXPLAIN_...
exist.

Hope for some more good hints...

Cheers,
Ina
Nov 12 '05 #9

P: n/a
>> Was that the exact error message? The strange thing here is that the
message is in English, so it doesn't seem to be a message returned by
DB2.

.. could have been a -901. Which would still be good to know...

I don't know which error number it is. In the output file produced by
db2exfmt
"The server was unable to establish a buffer for error reporting."
was the only line in it, without any further information, sorry.

Cheers,
Ina
Nov 12 '05 #10

P: n/a
> When I run db2expln with some invalid option (ina's problem is that she
used
db2expln and failed to specify -q in front of the dynamic query) i will
get
"The server was unable to establish a buffer for error reporting.",
instead
of an normall error message telling me bad syntax or whatever.

This would really be a good idea of looking for the error, thanks. Anyways,
I didn't succeed yet :-(
Where exactly do I have to put the -q option to?
db2exfmt doen't know this option.

Cheers,
Ina
Nov 12 '05 #11

P: n/a
your posting was about gettig the error when running db2expln not db2exfmt

instead of db2expln -d SAMPLE -t "SELECT * FROM cl_sched".
try
db2expln -d SAMPLE -t -q "SELECT * FROM cl_sched" assuming cl_sched is a
valid table in your schema
"Ina Schmitz" <we*@inalein.net> wrote in message
news:d3*************@news.t-online.com...
When I run db2expln with some invalid option (ina's problem is that she
used
db2expln and failed to specify -q in front of the dynamic query) i will
get
"The server was unable to establish a buffer for error reporting.",
instead
of an normall error message telling me bad syntax or whatever. This would really be a good idea of looking for the error, thanks.

Anyways, I didn't succeed yet :-(
Where exactly do I have to put the -q option to?
db2exfmt doen't know this option.

Cheers,
Ina

Nov 12 '05 #12

P: n/a
Ina Schmitz wrote:
"[IBM][CLI Driver][DB2/NT] SQL0104N Auf "ION FROM [...]" folgte das
unerwartete Token ".". Zu den möglichen Token gehören: ",".
SQLSTATE=42601 "


Is this the exact error message or was there something else in the [...]?


in the [...] there is the user name (I just didn't want to post it).

Do you know the exact SQL statement that caused the error to be raised?


Yes, it's just a simple select: select * from test

Unfortunately, I now got the error message: "The server was unable to
establish a buffer for error reporting."


Was that the exact error message? The strange thing here is that the
message is in English, so it doesn't seem to be a message returned by DB2.


Yes. In the output file, nothing else is in it! db2cmd.exe gives me as
result:
"
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Enter up to 26 character Explain timestamp (Default -1) ==>
Enter up to 8 character source name (SOURCE_NAME, Default %) ==>
Enter source schema (SOURCE_SCHEMA, Default %) ==>
Enter section number (0 for all, Default 0) ==>
No Explain instances (plans) were found for database sample, source pattern:
%.%
for explain table schema INA2.
If this is not the correct explain table schema, please specify the correct
sche
ma
using the -e option on the command line or correct the setting of the
variable
USER or USERNAME so the correct schema can be found.
Output is in test.exfmt.
Executing Connect Reset -- Connect Reset was Successful."

In my opinion, it looks like there are no explain plans found. What else do
I have to do? With db2 list tables, I see that a lot of tables EXPLAIN_...
exist.

Hope for some more good hints...

Cheers,
Ina

you better pay the the price for ibm support !
if it's really your diploma you can go to your local admin if they have
db2 you have luck if not pay it !!! we developer need the money.
and dont tell me lies like i have no money you have a computer this
costs money or not !
everytime this blahhhblahhhhblahhhh

cu,

indika
Nov 12 '05 #13

P: n/a
Ina Schmitz wrote:
When I run db2expln with some invalid option (ina's problem is that she
used
db2expln and failed to specify -q in front of the dynamic query) i will
get
"The server was unable to establish a buffer for error reporting.",
instead
of an normall error message telling me bad syntax or whatever.


This would really be a good idea of looking for the error, thanks. Anyways,
I didn't succeed yet :-(
Where exactly do I have to put the -q option to?
db2exfmt doen't know this option.

Cheers,
Ina

Let's take this offline. Check your inbox

Gruss
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #14

P: n/a
Serge Rielau wrote:
Ina Schmitz wrote:
When I run db2expln with some invalid option (ina's problem is that
she used
db2expln and failed to specify -q in front of the dynamic query) i
will get
"The server was unable to establish a buffer for error reporting.",
instead
of an normall error message telling me bad syntax or whatever.

This would really be a good idea of looking for the error, thanks.
Anyways, I didn't succeed yet :-(
Where exactly do I have to put the -q option to?
db2exfmt doen't know this option.

Cheers,
Ina

Apparently Ina used a schema with a blank: "X Y".
It seems the tooling shared by visual explain and db2exfmt has a bug
causing it to fail to quote the schema name.
So:
SELECT 1 FROM X Y.T
will, of course, result in a syntax error.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.