db2advis and explain options

savethytrees · #1 (**permalink**) 01-18-07, 16:17

Hi,
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.

Peter.Vanroose · #2 (**permalink**) 01-22-07, 02:36

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.

grofaty · #3 (**permalink**) 01-22-07, 03:55

Hi,
DEFER YES is not supported on DB2 for Linux/Unix/Windows. But in create index documentation there is a statement:

Code:

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,
Grofaty

savethytrees · #4 (**permalink**) 01-24-07, 13:56

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech