Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
Posted: Thu Oct 14, 2004 4:32 am Post subject: REOPT VARS, SYSPACKDEP, USAGE of index
Hi,
I have the problem to find out, which indices are unused or wether changes to an Index (DROP Index and (RE-)CREATE INDEX with additional fields) will effect any DB2 packages.
Fact is: The usage of an index in an special package is stored in DB2 Catalog, table SYSPACKDEP.
What will happen, if packages are bound with REOPT VARS ?
In this situation DB2 may choose an other access path during runtime than at bind time selected.
I'm now not sure about the content of SYSPACKDEP.
Is ist possible, that DB2 chooses at runtime of an package, bound with REOPT VARS parameter, an index, with is not stored in SYSPACKDEP during bindtime ?
If so, this means: If an index can'n be found in SYSPACKDEP, the index is not used in packages bound without REOPT VARS, but may be used in packages bound with REOPT VARS.
Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
Posted: Thu Oct 14, 2004 7:15 am Post subject:
Hi Kolusu,
thank you for your qick reply.
We uses reopt(vars) with static sql (for only some special queries (*)). This works perfect. Yours links are known to me.
But what about the content of sysibm.syspackdep. Is the content only the indizes used at bind time ? What about indizes used at runtime, because of reopt (vars) finds out an better access path ?
regards,
bauer
(*) Queries to select for example order number, with various inputs / input cominations like order number, order adress, partnumber, delivery date etc. We use one join for all tables, several where clauses. Lokks like this:
select ..... from table1, table2, table3 ......... where order number like :HOSTVAR1 and part number like :HOSTVAR2 and .......
Using REOPT (VARS) the DB2 systems checks content of HOSTVAR1, HOSTVAR2 ...... and select is performed using index for ordernumber, or partnumber ......
Advantage: Not a lot of numbers for special cursors for each request, all combinations of search fields are possible
the entries in SYSPACKDEP reflect the results of the optimize step during bind processing (as stored in PLAN_TABLE). With reopt vars you ask for a re-optimization at runtime, which of course could temporarily alter the access path.
Therefor entries in SYSPACKDEP are only a hint for a physical db designer, since access paths for dynamic SQL are'nt recorded there anyway.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
