DB2 V7.1 in OS/390

Hi,

I have a table TABA with 8 columns.
There is a unique composite index on colno 7,9,5,6 in that sequence.

when i do an explain on the following query,

select col5, col6
from TABA
where col7= ? and
col9=?

I get indexed access with matchcols as 2. And Index only='N'

Why is index only='N' here? What is the need to access tablespace?

I tried putting col5 and 6 in the where clause, that just increases the matchcols to 4 but Indexonly is still 'N'

What could be the reason?

table has around 10 million rows. Stats are mostly upto-date.

xamar

What are the data types for col5 and col6? Is there any compression or encryption being used? An edit procs or field procs?

Try substituting literals for the ? in the explain query. It may just be a bug in the explain.

I think the data types are char. About the other thing e.g. compression and procs, i will get back on monday. But do they make any difference?

And does it help substituting literals for '?' ? They are actually host variable.

thanks for replying though!
xamar

Hi
i'de check the types of the columns,
if it is varchar, then you should check a zparm name RETVLCFK,
it tell's db2 if it can retrieve varchar values from index or has
to read them from the data pages. If it is set to NO,
then DB2 will never read varchar values from the index and will
always read the data pages

bye

I would be careful before changing RETVLCFK:

From Redbook DB2 for z/OS and OS/390 Version 7 Performance Topics:

"In DB2 V6, you could only get an index-only access in this case by specifying RETVLCFK=YES. Many sites do not want to set the parameter to YES because it impacts every application program that includes in its select list a VARCHAR column that is an index key column. If an index-only access is chosen by DB2, then the VARCHAR values are returned to the application program padded with blanks and with the column length set to the maximum. When the VARCHAR is returned from the data row it is returned as a true variable length value without padding. Not all programs can handle this access path dependent variation in how data is returned from a SELECT."

I believe this is not issue with version 7 and RETVLCFK does not have to be set to YES to get index only access.

OK, here is the data.

col5 is INTEGER
col6 is TIMESTAMP
col7 is integer
col9 is char

NO varchar

No fieldproc or edit proc is defined on any of those 4 columns.

No compression. Does this matter? No encryption.

I tried substituting literals for '?' . It gives the same result in EXPLAIN.

I can't believe I found a bug in IBM's EXPLAIN

xamar

I would check with the IBM site or IBM support for an APAR. Of course, the SQL your posted is not the actual SQL that you used, nor did you show the actual DDL of table and index, so there may be some other explination.

I can't post the exact table structure due to security constraints etc.

I thought i posted enough information. Its true that there is a possibility that missed something.

One of them may be that the table has 2 more indexes. Some of them involve these columns. But since it doesn't use them for this query, I thought its not useful here.

There is field proc too but on a column that is not involved here.

So anyway, thanks Marcus and Momi!