ADMIN_EST_INLINE_LENGTH function - Estimate length required to inline data
The ADMIN_EST_INLINE_LENGTH function returns an estimate of the inline length that is required to inline the data stored in an XML column, BLOB column, CLOB column, or DBCLOB column.
If the data cannot be inlined, the function returns a negative value.
If the data is already inlined, the function returns the actual length of the inlined data.
Syntax
The schema is SYSIBM.
Return value
This function returns either an INTEGER value that represents the estimated inline length (in bytes) of the data, or one of the following values:
- NULL
- The inputs are NULL.
- -1
- The data cannot be inlined because there is no valid inline length that would allow the column value to be inlined.
- -2
- The estimated inline length of the document cannot be determined because the document was inserted and stored using an earlier product release.
Function parameters
- column-name
- Identifies a column of the base table with a data type of XML, BLOB, CLOB, or DBCLOB (SQLSTATE 42884). The column must directly or indirectly reference the column of a base table that is not generated based on an expression (SQLSTATE 42815).
Example
Example 1: The following example returns the estimated inline length of three XML documents that are contained in XML column xml_doc1 of TAB1 table.
db2 => SELECT PK, ADMIN_IS_INLINED(xml_doc1) as IS_INLINED,
ADMIN_EST_INLINE_LENGTH(xml_doc1) as EST_INLINE_LENGTH
from TAB1
This query results in the following output:
PK IS_INLINED EST_INLINE_LENGTH
----------- ---------- -----------------
1 1 292
2 0 450
3 0 454
3 record(s) selected.
In the example, the ADMIN_IS_INLINED function indicates that the first document is inlined. Therefore, the ADMIN_EST_INLINE_LENGTH function returns the actual length of the inlined XML document. The second document is not inlined, so the ADMIN_EST_INLINE_LENGTH function returns the estimated inline length that is required to inline the second XML document.
Example 2: The following example returns the estimated inline length of one XML document that is contained in the XML column xml_doc1 of the TAB1 table. This example includes a predicate.
db2 => SELECT PK, ADMIN_IS_INLINED(xml_doc1) as IS_INLINED,
ADMIN_EST_INLINE_LENGTH(xml_doc1) as EST_INLINE_LENGTH
from TAB1 where PK=2
This query results in the following output:
PK IS_INLINED EST_INLINE_LENGTH
----------- ---------- -----------------
2 0 450
1 record(s) selected.
Example 3: The following example returns the estimated inline length of three CLOB data that are contained in CLOB column clob_1 of the TAB1 table.
db2 => SELECT PK, ADMIN_IS_INLINED(clob_1) as IS_INLINED,
ADMIN_EST_INLINE_LENGTH(clob_1) as EST_INLINE_LENGTH
from TAB1
This query results in the following output:
PK IS_INLINED EST_INLINE_LENGTH
----------- ---------- -----------------
1 1 68
2 0 3665
3 0 -1
3 record(s) selected.
Usage notes
- XML columns are supported only when the XML documents were inserted using Db2® Version 9.7 or later. XML documents inserted before this release have a different storage format. When the ADMIN_EST_INLINE_LENGTH function encounters an incorrect storage format, it returns a value of -2.
- If you plan to increase the column inline length, remember that this length cannot be reduced.
- Increasing the inline length also increases the total row size
and might affect the performance of buffer pools. The total row size
has the following limits.
Table 1. Row size limits Page size Row size limit Inline length limit 4K 4005 4001 8K 8101 8097 16K 16 293 16 289 32K 32 677 32 673 - The estimated inline length might not be accurate if the XML storage object page size is not same as the base table page size.