Index fragment page limit

Once in a while something comes along to make a DBA’s life easier.

Spot the difference between these similar pages in the 11.70 Info Center and the new 12.10 IBM Knowledge Center.

The eagle-eyed amongst you will immediately spot the new line:

Maximum number of pages per index fragment: 2,147,483,647

This is a 128 times improvement on the previous limit of 16,775,134, the same as the data pages per fragment limit, which limited an index fragment to just shy of 256 Gb in a 16 kb dbspace. The new limit of 32 Tb (again with a 16 kb dbspace) is much easier to work with. It applies only to detached indices.

Maybe it’s not the sexiest improvement but it actually arrived at the same time as storage pools in version 11.70 and so has been with us for a while. In my own test I was able to build an unfragmented detached index of over 32 Gb in a 2 kb dbspace in 11.70.FC7.

Should you rely (in version 11.70) on undocumented functionality? Without your own testing, maybe not. However, it’s good that this extra breathing space for DBAs exists and – with 12.10 – is documented and fully supported.