DB2 Explain - DB2 Database

Richard DAddese

Has anyone ever experienced a situation where DB2 EXPLAIN shows a
certain index being used, but when it executes, the index is not being
used and a tablespace scan occurs instead? The package was bound with
NOREOPT(VARS). Thanks.

Nov 12 '05 #1

Subscribe Post Reply

5097

norab

"Richard DAddese" <g2****@clarica.com> wrote in message
news:f9**************************@posting.google.c om...

Has anyone ever experienced a situation where DB2 EXPLAIN shows a
certain index being used, but when it executes, the index is not being
used and a tablespace scan occurs instead? The package was bound with
NOREOPT(VARS). Thanks.

Index could be corrupt. Try db2dart.

Nov 12 '05 #2

Joachim Klassen

g2****@clarica.com (Richard DAddese) wrote in message news:<f9**************************@posting.google. com>...

Has anyone ever experienced a situation where DB2 EXPLAIN shows a
certain index being used, but when it executes, the index is not being
used and a tablespace scan occurs instead? The package was bound with
NOREOPT(VARS). Thanks.

Which OS ? IIRC - on DB2 z/OS (DB2/390 or DB2/MVS) this can happen if
DB2 chooses a LIST PREFETCH (which needs a RID Sort) and the sort can
not be done in main memory. If this happens then Index access will be
changed to a tablespace scan at runtime. (At least this was true in
DB2/MVS Version 5 or earlier).

HTH
Joachim

Nov 12 '05 #3

Similar topics

Understanding Explain?

by: Bruce D | last post by:

I'm trying to understand MySQL better. I can't seem to figure out why the second query takes 2 minutes and the first query takes 12 seconds. It should be noted that the table, KBM, has 250...

MySQL Database

log_duration and \timing times repeatably much higher than "Total runtime" from explain analyze

by: Greg Stark | last post by:

This query is odd, it seems to be taking over a second according to my log_duration logs and according to psql's \timing numbers. However explain analyze says it's running in about a third of a...

PostgreSQL Database

show explain plan

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...

DB2 Database

visual explain

by: Buck Nuggets | last post by:

Since upgrading to db2 8.2.1 I've been having odd problems with visual explain: 1. tables appear to require schema prefixes: SQL0204N "BUCK.DIM_SENSOR" is an undefined name. SQLSTATE=42704...

DB2 Database

Looking for help building 'rhdb-explain' Visual Explain tool

by: Jeff Boes | last post by:

I'm hoping there's someone here with experience in building the Visual Explain tool from Red Hat. I downloaded it and the J2 SDK, but when I attempt to follow the build instructions, I get messages...

PostgreSQL Database

EXPLAIN ANALYZE total runtime != walltime

by: Jon Lapham | last post by:

I have been using the EXPLAIN ANALYZE command to debug some performance bottlenecks in my database. In doing so, I have found an oddity (to me anyway). The "19ms" total runtime reported below...

PostgreSQL Database

Getting SQL0901N error with SQL State: 58004 on running explain plans

by: marklawford | last post by:

Not having earned my DBA badge from the scouts just yet I'm a little lost with an error I'm getting. We've just upgraded our development database from 7.2 to 8.2 as the first step in upgrading...

DB2 Database

a strange sql explain

by: heming_g | last post by:

two tables with the same table structure : tb_xxx and tb_xxx_tmp in table tb_xxx , column "listno" is the primary key of itself and foreign key of dozen of tables . here is my sql .. "...

DB2 Database

explain - invalid token

by: kabotnet | last post by:

Hi, I'm new in db2, I'm trying to execute EXPLAIN command on some queries but i have error like: And message similar to: Token EXPLAIN is not valid, valid tokens ( END GET SET CALL DROP FREE...

DB2 Database

Explain of update-statement without commit

by: w.l.fischer | last post by:

Hi, the following sequence: set current explain mode yes; set current explain snapshot yes; update ...; set current explain mode no;

DB2 Database

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

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...

Windows Server

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server