473,230 Members | 1,840 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,230 software developers and data experts.

interpertting a db2expln

Hi,

I'm trying to interpet a db2 explain. I'm trying to determine if the SQL is
being executed as I expect/hope.

The sql is generally:

select key from table where key1=value and key2 > value2 and key2 < maxkey
order by key2 fetch first 1 row only

2 notes: key1 = value is 99% of the table and the max key is more or less
useless b/c its always the last possible key, making the statement for
practical purposes:

select key from table key2 > value2 order by key2 fetch first 1 row only

By big question is: If their were 26 rows w/ keys A-Z , and the value of
value2 is B, does the explain show that the sql statement still inserts C-Z
into the temp table and sort it?

Below is the explain of the statement in question.

Thanks!
Frank

--

-------------------- SECTION ---------------------------------------
Section = 17
SQL Statement:

SELECT LFKEY, CDATA INTO :H00038 :H00039 , :H00018 :H00019
FROM RCFGTD
WHERE LFPFX=:H00004 :H00005 AND LFKEY >= :H00038 :H00039 AND LFKEY
<= :H00020 :H00021
ORDER BY lfkey
FETCH FIRST 1 ROW ONLY
Section Code Page = 819

Estimated Cost = 78.628693
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = PRODPCIA.RCFGTD ID = 22,115
| | #Columns = 0
| | Index Scan: Name = PRODPCIA.LNG01_RCFGTD_PK ID = 1
| | | Regular Index (Clustered)
| | | Index Columns:
| | | | 1: LFPFX (Ascending)
| | | | 2: LFKEY (Ascending)
| | | #Key Columns = 2
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | Index-Only Access
| | | Index Prefetch: None
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 43
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = PRODPCIA.RCFGTD ID = 22,115
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: 5 Pages
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 3
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: LFKEY (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 3504
| | Sort Limited To Estimated Row Count
| Piped
Access Temp Table ID = t2
| #Columns = 2
| Single Record
| Relation Scan
| | Prefetch: Eligible
Return Data to Application
| #Columns = 2

End of section
Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
NLJOIN
( 4)
/ \
TBSCAN RIDSCN
( 5) ( 7)
| |
TFunc: SORT
SYSIBM ( 8)
GENROW |
IXSCAN
( 9)
/ \
Index: Table:
PRODPCIA PRODPCIA
LNG01_RCFGTD_PK RCFGTD


Nov 12 '05 #1
2 6589
Feed the query into
db2 EXPLAIN PLAN FOR <stmt>
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
Hey Serge,

Is there actually somewhere a good document describing the explain plan and
what each section means etc..?
For example, MAXPAGES. From what I understand so far is the optimizer
setting the maximum number of pages to prefetch correct?
But I've seen a setting of like 1000, so that would mean to maximum fetch
1000 pages correct?
Thus if I have a prefetchsize of 32, I would get 32 prefetch requests
correct?

So if it would for example say 16, would this mean that although my
prefetchsize if set to 32, the optimizer will override this and only get 16
pages?

I can not find any good documentation explaining every item within the
explain output, and what it means etc..

Thanks.

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:c1**********@hanover.torolab.ibm.com...
Feed the query into
db2 EXPLAIN PLAN FOR <stmt>
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: LazyAnt | last post by:
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...
3
by: Terry | last post by:
Hi All, Im trying to run db2expln over some sql in order to find out why its so slow. I receive the following error message. LINE MESSAGES FOR DYNEXPLN.sqc ------ ...
14
by: Ina Schmitz | last post by:
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...
2
by: Ion | last post by:
Hi, I have a sql procedure that (among others) deletes a row in a table (very small). The table has a foreign key - ON DELETE NO ACTION that points to a large table (child). There is an index...
4
by: tuarek | last post by:
Hi all, Can you suggest any reference on analyzing db2expln results? Regards, Mehmet
12
by: Joachim Pense | last post by:
Is there recommended way to get the execution plan for queries involving global temporary tables (from the UNIX command line or within a script)? I run the queries in Perl scripts, and the only...
1
by: jefftyzzer | last post by:
Colleagues: Can anyone tell me what the number to the right of "Index Prefetch: Eligible" designates in the output from db2expln, e.g., "Index Prefetch: Eligible 14409"? I see such a number...
1
by: Troels Arvin | last post by:
Hello, In a DB2 LUW v. 8.1.1.96, I have a hard time giving users access to the db2expln utility. Let's say that user FOOBAR needs to be able to run the db2expln utility. I do: - provide...
5
by: srilata83 | last post by:
Hi, I am trying to query a big table which contains huge data,I just tried to check the no of records of table, i have run the query for COUNT(*) in QMF, But i am not getting the result , i am...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.