The first thought that comes to mind is that I was always taught not to use
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.
I'm not a fan of creating sparse indexes to replace select/omit logicals.
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.
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?
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).
Thoughts on best practices and references would be appreciated. I tried a
google search and did not come up with much.
- 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
- 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
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.