Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006

    Unanswered: Table scan/Index scan

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

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

    The HRM asked me to look into the matter, but since I am no expert in the 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 
      in which the host variable is declared as
         WS-PART   PIC X(12).
     This wil be executed using :
       1)  an index scan
       2)  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 version 8 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 ??

    Your help is appreciated

  2. #2
    Join Date
    Dec 2005
    If Table has only one column PARTNO, it will be index scan but if table contains more columns then it will be first index scan then fetch/join to get other columns.

  3. #3
    Join Date
    Dec 2005
    the given SELECT may use an index independent of the DB2 version as the host variable is shorter than the column.
    If the host variable is longer than the column, an index could only be used in version 8.

    Of cource, a tablespace-scan can be chosen nevertheless ( e.g. if RUNSTATS indicates, that the table is empty ...)

  4. #4
    Join Date
    Jan 2006
    The question really was if the behaviour of DB2 has changed between version 7 and version 8.
    My HRM always counts "table scan" as the right answer. Is this still correct with V8 ??
    Or should it be index scan ?


  5. #5
    Join Date
    Nov 2005
    It is a loaded question. Your HR department is playing Russian Rullet. There are times when it will do a table scan and there are time when it will do an index scan regardless what dblevel you are running. Non technical people administering a test?
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os

Posting Permissions

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