473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

show explain plan

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
14 20221
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
> 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
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
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
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
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
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
>> "[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
>> 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
> 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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Bagieta | last post by:
Hello. Does someone of you know if is there a 'explain plan' like function in SQL Server (similar to explain plan in Oracle or DB2) If so then how it works, where stores data and how it can be...
3
by: Will Atkinson | last post by:
Hi All, I'm a relative newbie to SQL Server, so please forgive me if this is a daft question... When I set "Show Execution Plan" on in Query Analyzer, and execute a (fairly complex) sproc, I...
2
by: Chris Breemer | last post by:
I'm looking for a DB2 "explain plan" command on the As/400. Anybody know if this exists and where to find it ? TIA Chris
0
by: spencer | last post by:
Are there any recommended books or articles that give a really good description of the output in a db2 explain plan? Serge perhaps you can write an article if there is not one! Spencer
3
by: dunleav1 | last post by:
Here's the statement: delete from table a where id1=4; Table a is a parent to table b,c,d,e,f,g,h,i,j,k,l Table a key is id1,id2 table b,c,d,e,f,g,h,i,j,k,l key is id2,id3. I delete all the...
1
by: hastha23 | last post by:
Hi, What is explain plan? what is full table scan? Anybody .. Regards, Hastha23
7
by: skaushik | last post by:
Hi all, I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table column but the explain plan tells that there...
3
by: raviva | last post by:
Hi, I want the explain plan. But when I clicked on the explain plan it says ORA-02404: specified plan table not found. I investigated on net and some books. I was asked to load the utlxplan.sql. I...
0
by: db2admin | last post by:
hello, I have compressed table 442992 rows and when i run explain plan on this table for some SQL using this table, i see table scan and cardinality on top of table node as 18458. I am new to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.