DB2 Version 9.7 for Linux, UNIX, and Windows

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


>>-ADMIN_EST_INLINE_LENGTH--(--column-name--)------------------><

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: Indicates that the inputs are NULL.
-1: Indicates that the data cannot be inlined because there is no valid inline length that would allow the column value to be inlined.
-2: Indicates that the estimated inline length of the document cannot be determined because the document was inserted and stored in a release before DB2® for Linux, UNIX, and Windows Version 9.7.

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 only supported when the XML documents were inserted using DB2 for Linux, UNIX, and Windows 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.

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