Thanks for sending the class to me Yaniv.
Life is always easier when you don't have to guess what is happening.
The 2 options list above are not really options, but rather the steps you should take when defining any type of index for Cache SQL Storage. Defining the Index def helps us correctly report info to external databases, but it is the map in the storage that the query optimizer is looking for, so #1 is required for all types of indices.
Here is the property and index
Property StatusCode As %String(COLLATION = "EXACT");
Index StatusCodeIdx On StatusCode [ Type = bitmap ];
When you want to define a bitmap you do not add the IDKey as a subscript, it will be generated in the data. For Yaniv's class this is what the bitmap storage def will look like:
If you do not want to use EXACT collation then the Collation that is defined in the Property needs to match the collation defined in the map. If no collation is listed for a property the default is SQLUPPER, so when the Property looks like this:
Property StatusCode As %String;
the Map needs to have a Subscript that looks like this:
Also when ever you define a bitmap in a class you should also define a Bitmap Extent. The class compiler does this automatically for Default Storage, but for Cache SQL Storage you need to define the extent map. The <Type> is bitmapextent and no fields are listed as subscripts:
If you are using Objects or SQL to modify the data then these indices will be maintained by the generated class code. If you application is still doing Global sets and kills then you will need to write code to maintain the indices. You can look at the class Mapping.BitMapExample that is in The Art of Mapping Globals to Classes (5 of 3) to see what that code would look like.
If you have any questions please let me know.
Here is my version of Yaniv's class