Re: ?s regarding sparse indexes

<Vicki>
The first thought that comes to mind is that I was always taught not to use select/omit logicals.
</Vicki>
this doesn't change with sparse indexes coming up! One important reason is, that having lots of sparse indexes the cost of maintenance in case of write/delete operations might be much higher than the benefits for reading.

<Vicki>
I'm not a fan of creating sparse indexes to replace select/omit logicals.
</Vicki>
The one and only benefit for the replace might be, that you could get rid of DDS and SQL access might be more eficcient (neither documented nor tested by myself!!!) using a SQL sparse index compared to an DDS logical.

<Vicki>
But the idea of a sparse index also perplexes me for another reason. I thought the idea in SQL was always to reference the table - not a logical. And let the engine do the work. If you used sparse indexes wouldn't you have to reference the specific index?
</Vicki>
You can't use an index in SQL DML statements, this didn't change.
But the real important idea of sql is: You must not use a table in a DML statement (never ever!!!) Use Views to decouple database implementation fro programm logic! (That's why a table has public access exclude).

<Vicki>
Thoughts on best practices and references would be appreciated. I tried a google search and did not come up with much.
</Vicki>
Best practices:
- start with ensuring to have artificial primary keys for all tables
- define RI constraints for all of your foreign key relations
- get rid of all RLA is much more important than switching from DDS to SQL DDL
- create sparse indexes only if you see a measurable benefit.
- avoid sparse indexes for transaction files
- sparse indexes might help for large master file tables with low change activities
References:
IBM DB2 for i
indexing methods and strategies
Learn how to use DB2 indexes to boost performance
(your preferred search engine should find it)
This should be a starting point, though it is too much marketing and lacks on practical experience.

D*B