I recently wrote about LOB Inlining, a new feature in DB2 9.7. It allows to store LOBs together with the actual row data and thereby save space and in most cases improve performance. XML Inlining has been around since DB2 9.5 and proved very valuable for many customers. A common usability issue, and we can argue whether small or big, was that it was hard for XML data to estimate whether it got inlined or not. This was caused by the fact that when you insert XML data it is in text form and then is parsed and converted into the internal, native format. Thereby it can shrink or grow, depending on the data. No simple tooling was available to estimate whether it would be inlined or whether it actually got inlined.
Well, the XML parsing and the resulting size changes are still present in DB2 9.7 (we still have native XML support), but
tooling that can be used for both XML and LOB data has been added in the form of two admin functions:
- ADMIN_EST_INLINE_LENGTH works on both a XML or LOB column that is passed in as parameter. The return function returns either the estimated length of the column value for each of the column values or an indicator of whether the length would be too big or the estimate cannot be performed.
- ADMIN_IS_INLINED basically returns either a 1 or 0 (yes/no) for each column value of whether it has been inlined or not.
Both functions are not meant to be run on your production system with millions of rows per table, but for either test environments or for [performance] problem diagnostics (think of a nicely written WHERE clause attached to filter down the number of rows). The functions help DBAs to determine a good inline length and also to verify whether values are then actually inlined as planned.
To make usage of the usability function even more usable (nice sentence, right?) a
new sample program has been added that demonstrates how the two admin functions can be applied to XML and LOB columns.