473,836 Members | 1,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_STATEME NT and EXPLAIN_INSTANC E 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
14 20404
> 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.ne t> 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
unerwartet e Token ".". Zu den möglichen Token gehören: ",".
SQLSTATE=426 01 "


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

Unfortunatel y, 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 blahhhblahhhhbl ahhhh

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
15285
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 retrieved? Best regards Bagieta =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= dbDeveloper - Multiple databases editor
3
5374
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 note that a particular query is reported as having a query cost of "71% relative to the batch" - however, this is nowhere near the slowest executing query in the batch - other queries which take over twice as long are reported as having costs in...
2
7373
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
1395
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
4105
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 rows from b,c,d,e,f,g,i,j,k,l before deleting the rows from table a. The explain plan shows the following:
1
3883
by: hastha23 | last post by:
Hi, What is explain plan? what is full table scan? Anybody .. Regards, Hastha23
7
5790
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 is a full table scan on both the tables. The query and the explain plan is below select * from history_table ht, small_table st where ht.columnA > st.columnB and sysdate between st.datetime1 and st.datetime2; Operation Object...
3
18625
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 did that but of no use. I also changed the name of the plan table in TOAD, still no use. New thing I heard recently is to run TOADPREP.SQL. But this script is not available on our server($ORACLE_HOME/rdbms/admin). Can someone please suggest me what...
0
927
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 compression and do not understand why cardinality is not the actual number of rows but 18458. here is the piece of explain plan. table ABC.XYZ is the table i am talking about ( right most in plan )
0
9666
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10838
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10250
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9369
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7788
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6977
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4447
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.