Why not have DATE as an Index field?

[Bill Dennis]

The Client online help for creating an AppGrp says that date fields should almost always be filters, not indexes.  Is this reasoning something only a DBA would understand?

Maybe because the segment table points to tables by date range already?

We use Single Table for All Loads. Is a date index OK here?

Any  comments welcome.

Thanks,
Bill

[Justin Derrick]

Hi Bill.

Indexes are best used for fields that are 'mostly unique' -- like a customer account number.

The great thing about the most recent versions of CMOD is that you can turn indexing on or off at any time.  So, while I would normally recommend indexing date fields, you can try running queries with it on or off and compare response times.  Just be forewarned that if your App Group has a lot of documents, building the index is non-trivial, and can take a considerable amount of time depending on your CPU speed and IO bandwidth.

Hope this helps!

-JD.

[leodejong]

Bill,

I assume you are using CMOD on z/Os. In that environment i would definitely put an index on the Date column in combination with "Single table for all loads". Software segmentation is typically a unix solution for partitioning. On z/OS i would suggest using DB2 native partitioning if the table gets too large.  Leo