Table scan <--> Index scan, Version dependent ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dia - 14 Jan 2006 16:55 GMT

At the company I work job applicants are required to do a little test.

The human resource manager recently had a candidate who claimed one of
the questions was ambiguous.

Dependent upon the version of DB2 one or the other answer of a multiple
choice question could be right, the candidate said.

The HRM asked me to look into the matter, but since I am no expert in
the workings of DB2 I submit the question to you hoping any of you can
give me a definitive answer.

The question on the exam is :

! Given a table PARTS with a primary key
! PARTNO CHAR(15)
!
! A select
!
! SELECT * FROM PARTS
! WHERE PARTNO = :WS-PART
!
! in which the host variable is declared as
!
! WS-PART PIC X(12).
!
! Which is true ?
!
! 1) This will be executed using an index scan
! 2) This will be executed using a table scan
! 3) This will give a compilation error

The candidate claimed that a table scan was used prior to Version 8 of
DB2
while starting at V8 an index scan is used.

Can anybody confirm this ??

Does anybody know of a manual or an other source of information from
IBM where this is described ??

What is the influence of the host variable being shorter than the
column declaration ?

I would very much appreciate your help to get our test right
Dia

Reply to this Message

Mark A - 14 Jan 2006 19:54 GMT

> At the company I work job applicants are required to do a little test.
>
[quoted text clipped - 42 lines]
> I would very much appreciate your help to get our test right
> Dia

I assume you are talking about DB2 for z/OS?

Reply to this Message

Dia - 15 Jan 2006 07:31 GMT

Yes, I am.

Does it make a difference whether it runs on z/OS, Windows, OS/400, AIX
or something else.

I would think that DB2 UDB is the same on all platforms, or am I being
naive ?

But in the context of this question, assume z/OS.

Reply to this Message

Serge Rielau - 15 Jan 2006 09:03 GMT

> Yes, I am.
>
> Does it make a difference whether it runs on z/OS, Windows, OS/400, AIX
> or something else.
> I would think that DB2 UDB is the same on all platforms, or am I being
> naive ?

Yes, you are... :-)
There is DB2 for iSeries, DB2 for zOS and DB2 for Linux/Unix/Windows.
A total of 3 engines.
DB2 for LUW will not care for the length mismatch of the hostvar.

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Serge Rielau - 15 Jan 2006 02:08 GMT

> At the company I work job applicants are required to do a little test.
>
[quoted text clipped - 42 lines]
> I would very much appreciate your help to get our test right
> Dia

Just like Mark A, I assume this is DB2 for zOS?
Either way plans are not only dependent on whatever fucntion teh
optimizer provides (which of course gets improved over time), but also
by statisticts.
If the table has so few rows that it's content fits onto a single page,
then a table scan may actually be at least if not more efficient as as
ISCAN/FETCH combination.

I have hard time believing that DB2 for zOS V7 would do a table scan for
a PK access given a reasonably sized table.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Mark A - 15 Jan 2006 02:19 GMT

> Just like Mark A, I assume this is DB2 for zOS?
> Either way plans are not only dependent on whatever fucntion teh optimizer
[quoted text clipped - 9 lines]
> Cheers
> Serge

The key issue in the question is that the host variable which is compared in
the predicate is a different length than table column. I know that at one
time, DB2 for z/OS would not allow DB2 to use an index because of the length
mis-match.

So the question is whether this restriction has been lifted, and was it
lifted in version 8 of DB2 for z/OS. There definitely are some enhancements
in V8 relating to padding of varchar columns that are indexed, but a quick
glance of the manual did not reveal to me whether this affects different
lengths of fixed length char columns.

Reply to this Message

Dia - 15 Jan 2006 08:04 GMT

| The key issue in the question is that the host variable which is compared in
| the predicate is a different length than table column. I know that at one
| time, DB2 for z/OS would not allow DB2 to use an index because of the length
| mis-match.

Your right. The question seems to test the knowledge about the host
variable being shorter and the effect it has.

Conceptually : Is the effect the same as
WHERE PARTNO LIKE ( WS-PART concat "% ")
??
(this is meant as pseudo-code,
not actual SQL or COBOL)
It's not in the question, but I am asking myself.

Implementation : Is it implemented by a table scan in version 7
and an index scan in version 8 ??
From some of the answers I understand the table size
plays a role.
So let us assume a manufacturer holding 100 000
different parts in stock
and each record holding 200 bytes.

Reply to this Message

jrauhe - 15 Jan 2006 15:28 GMT

From the DB2 Administration Guide V7, section: 5.8.1.2: Are all
predicates coded correctly?:

(quote)
Declared lengths of host variables: Make sure that the declared length
of any host variable is no greater than the length attribute of the
data column it is compared to. If the declared length is greater, the
predicate is stage 2 and cannot be a matching predicate for an index
scan.
(unquote)

- same goes for DB2 V8

/jrauhe

Reply to this Message

Dia - 16 Jan 2006 10:11 GMT

To summerize the reactions I 've had until now:
-- I cannot get a definitive answer wheter the optimizer has changed
its approach between version 7 & 8, but it is unlikely it has. (!!?)
-- the size of the table plays an important role in wheter a table scan
or index scan is used.

I deduce from this that this is not a very good exam question. Too
dependent on unknowns.

Could anybody suggest better questions to test somebodies DB2
knowledge.

Reply to this Message

Knut Stolze - 16 Jan 2006 11:05 GMT

> I deduce from this that this is not a very good exam question. Too
> dependent on unknowns.

That is definitively true!

> Could anybody suggest better questions to test somebodies DB2
> knowledge.

The question is what you want to test for. Personally, I don't think that
it is a good test to ask for the exact meanings of a very specific option
for the BACKUP command or how the optimizer behaves in certain situations.
I would more focus on the general concepts used by DB2.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Mark A - 16 Jan 2006 15:07 GMT

> To summerize the reactions I 've had until now:
> -- I cannot get a definitive answer wheter the optimizer has changed
[quoted text clipped - 7 lines]
> Could anybody suggest better questions to test somebodies DB2
> knowledge.

If in fact DB2 for z/OS still has the restriction that the host variable in
the predicate must be the same length as the index column in order for DB2
to use and index on that column, then I think it is a good question. Back
when I worked a lot with DB2 for z/OS that was a common problem.

But to determine whether the answer is still correct for Version 8, I would
try it and see what happens.

Reply to this Message