Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    64

    Unanswered: Access Path Problem in EXPLAIN

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Posts
    64

    Re

    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

  4. #4
    Join Date
    Jun 2004
    Posts
    1
    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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2004
    Posts
    64

    Reply

    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

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Apr 2004
    Posts
    64

    Re

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •