DB2 - L

 View Only
  • 1.  Db2 LUW - create index w disallow reverse scans

    Posted Mar 16, 2022 02:18 PM
    Hi all,

    I occasionally support a quiet Db2 LUW database.  
    I am reviewing some existing indexes (before creating a new index) and I see some old indexes have been created with "disallow reverse scans"

    Why would one create index and "disallow reverse scan".  The default is "allow reverse scan".  Off hand that seems better.
    Is there some occasional advantage to disallow reverse scan? 
    Or perhaps my example index was created a long time ago and that was the only option in the past?   
    It seems new indexes should "allow reverse scans'....


    Ibm remove preview
    The CREATE INDEX statement is used to define an index on a database table.
    View this on Ibm >

    Brian Laube Manulife Financial

    Db2 Z DBA (mostly)

  • 2.  RE: Db2 LUW - create index w disallow reverse scans

    Posted Mar 16, 2022 02:44 PM
    Hi Brian,
    I believe ALLOW REVERSE SCANS was added to LUW at about the same time the Index Advisor came out. I want to say about V6 or V7. The rename to the Design Advisor was about V8 or V9, but I await a correction on this.

    ALLOW REVERSE SCANS has always been the default, and it makes sense. Over the past 20 years, this discussion has come up occasionally, but I just take the default.

    I did a big presentation at IOD 2007 together with Danny, a friend from the Toronto Lab. It has been in the product for a long time.

    I hope this helps.

    Martin Hubel
    MHC Inc.

    +1 905-764-7498
    +1 416-670-7498 Mobile
    Skype: db2hubel

    IBM Gold Consultant
    Lifetime IBM Champion

    Host of the DB2Night Show

    My 19 Db2 Certifications include:
    Db2 LUW 9.7 Advanced DBA
    Db2 LUW 10.1 & 10.5 DBA
    Db2 z/OS 10 & 11 DBA
    Db2 9.7 Solutions Developer

  • 3.  RE: Db2 LUW - create index w disallow reverse scans

    Posted Mar 16, 2022 04:09 PM
    Hey Brian and Martin, might the index perhaps be a bit smaller or "compact" assuming there are no reverse-scan pointers?  Might also make an index build a bit faster too.  But I don't think it adds up to a large reduction in space or time to build; however those are the only reasons I can think of for not allowing reverse scan indexing these days.
    And to quote Martin .... I too "await a correction on this."
    Jim Bean
    Performance / Forensics Team
    Cigna Technology
    Work 860.902.3714
    Confidential, unpublished property of Cigna. Do not duplicate or distribute.
    Use and distribution limited solely to authorized personnel. © Copyright 2022 Cigna

    CONFIDENTIALITY NOTICE: If you have received this email in error,
    please immediately notify the sender by e-mail at the address shown. 
    This email transmission may contain confidential information.  This
    information is intended only for the use of the individual(s) or entity to
    whom it is intended even if addressed incorrectly.  Please delete it from
    your files if you are not the intended recipient.  Thank you for your
    compliance.  Copyright (c) 2022 Cigna

  • 4.  RE: Db2 LUW - create index w disallow reverse scans

    Posted Mar 18, 2022 08:59 AM

    Hi Brian, hi Martin,

    just out of curiosity I checked my old PDF manuals, which I still keep on disk. ;-)

    • In DB2 8.x DISALLOW REVERSE SCANS was still default.
    • In DB2 9.1 ALLOW REVERSE SCANS became default, if it was not explicitly stated during index creation.
    • The Index Advisor and the Configuration Advisor were introduced in DB2 V8.1 in January 2003.
    • The syntax CREATE INDEX ... ALLOW REVERSE SCANS was already available in Db2 7.1 (the oldest manuals at hand right now).
    • In DB2 9.7 the Type-1 indexes became invalid https://www.ibm.com/docs/en/db2/9.7?topic=tasks-converting-type-1-indexes-type-2-indexes
    • If you want to check, when and how many versions your database was migrated, you might want to check
      select * from sysibm.sysversions
      The oldest databases I found at a customer were created with Db2 8.1 and migrated since then.

    Below is an old slide where I had a brief summary of new features in the different versions over time.


    Roland Schock
    ARS Computer und Consulting GmbH

  • 5.  RE: Db2 LUW - create index w disallow reverse scans

    Posted Mar 19, 2022 09:51 AM
    Disallow was used by some companies to reduce lock contention way back....some
    Never changed it back. have a client now that was still unknowingly using it....Phil



    IBM Champion (2009-2022)

    IBM Gold Consultant

    Certified Information Systems Security Professional(CISSP)

    Certification Number 539059

    Certified DB2 DBA v10.5

    Certified Database Adminstrator, DB2 11.1

    IBM DB2 LUW Support Page -- https://www.ibm.com/mysupport/s/topic/0TO500000001fUNGAY/db2-linux-unix-and-windows?language=en_US&productId=01t50000004WmTkAAK


    Skype: DB2LUW

    Twitter: DB2LUW

    Direct +1.610.451.5801

    IDUG DB2-L Hall of Fame


    17 year IBM Business Partner 

    This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system

     Sent from my iPhone

  • 6.  RE: Db2 LUW - create index w disallow reverse scans

    Posted Mar 16, 2022 04:37 PM
    I believe there was a time in older DB2 LUW versions where reverse scans were not an option. If you had upgraded DB2 from one of these older versions reverse scans would not have automatically been enabled.