Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006

    Unanswered: db2advis and explain options

    Is there a way that I can simulate an index and explain the SQL to get the access plan if the index actually existed?
    What I want to do is use db2advis and get a recommendation to create and index. Then do an explain on the SQL, as if the index exists. I don't want to create the index and then do the explain to see the effects of creating the index.

  2. #2
    Join Date
    Sep 2004
    No, you have to create the index.
    But you may use the "DEFER YES" option to the "CREATE INDEX" statement to avoid the overhead of actually physically building the index. (At least, on z/OS; I don't know if this is possible on other platforms.)
    Just drop the index after having run EXPLAIN.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 1
    DEFER YES is not supported on DB2 for Linux/Unix/Windows. But in create index documentation there is a statement:
    For compatibility with DB2 for OS/390:
      o The following syntax is accepted as the default behavior:
      DEFER NO
    Savethytrees, what is your operating system and version?

    Hope this helps,

  4. #4
    Join Date
    Dec 2006
    Sorry for not giving that basic info.
    Its DB2 ESE V8.2 FP4 AIX 5.3.
    There is a way by which we can get recommended indexes for a given SQL while doing explian. This reduces the need to do a db2advise everytime you think there might be a problem and index is required. You run the explain and the Adives_index table is populated for the given SQL.
    But I cannot figure out if there is a way to get the access plan which includes the index that doesn't exists.

Posting Permissions

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