- SAP Managed Tags:
Motivation
In this blog post, I’ll discuss space and performance considerations of large objects (LOBs) in SAP environments on Db2 for Linux, UNIX, and Windows (LUW). You will learn how to address potential issues by configuration and maintenance. Key topics are , compactness, and inlining.
Note that not all information about LOBs is easily accessible. For convenience, in SAP environments the table function LOBALLOC assists administrators of Db2 LUW databases with investigating LOB space allocation.
How can you maintain the LOB object of a table?
You have the following options:
- Run a REORG
- Modify the LOB compactness
- Modify the inline length
As all of these options may have considerable impact on daily operation, and since they also affect the space allocation, it is important to have some knowledge about the effect of these actions. I will demonstrate how the LOBALLOC table function can assist you.
Background
A table in Db2 LUW consists of the following objects:
- Data object
- Index object if indices have been created
- LOB object if the table has LOB fields
In SAP environments, almost all tables have an index object. An index object of a table consists of all indices defined for the table. An LOB object is only allocated if the table has one or more LOB fields, i.e. fields of data type BLOB, CLOB, or DBCLOB. The LOB object of a table
- contains all LOB fields of the table
- is located in a tablespace that is determined at table creation time
- typically holds data larger than the page size of the data object
While data and index objects are organised in pages and extents that are cached in the bufferpool, any LOB data is accessed from the file system directly. It is never cached in the bufferpool.
A table row with a non-empty LOB entry has a pointer in the data page that allows you to identify the location of the LOB entry. This pointer includes additional information, like the LOB size. Therefore, querying the LOB size does not require reading the LOB data itself.
Inlining
An individual LOB entry can be very large (up to 2 GB) or occupy only a few bytes. As the pointer to the LOB entry of a row is stored in the data page of the table, the space required for this pointer must be reserved. If the data of the LOB field is not larger than this pointer, the data is inlined. This means that the LOB portion of the row is directly stored in the data page.
Space consumption
LOB data is stored in segments of size [a power of 2] x 1024 bytes. The maximum segment size is 64 MB.
You can define LOB fields with the COMPACT keyword, or with NOT COMPACT which is the default. If defined with COMPACT, the space allocation for this field is optimised. The segments then use the next 1 KB boundary.
Compactness implies that the Db2 engine spends more time searching for free space during INSERT and UPDATE operations of LOB data. Note that compactness optimizes the space usage of an LOB field. There is no option to compress the LOB data itself.
For more information about LOB allocation, see https://www.ibm.com/support/pages/lob-space-allocation-and-management.
How do you get information on LOB objects?
The catalog tables of Db2 LUW do not provide any details on LOB objects other than their tablespace.
You can find out about the total size of an LOB object of a table using table function ADMIN_GET_TAB_INFO. The function ADMIN_IS_INLINED tells you whether an LOB field of a table row is inlined or not.
Maintaining an LOB object
Changing the inline length of an LOB field
You can modify the inline length of an LOB field of a table using an ALTER TABLE statement like this:
alter table <TABLE> alter column <COLUMN> set inline_length <n>
However, without further action, the modified LOB settings will apply to newly added table entries only. The changes apply only to new table entries and not to the table content that already exists.
If the LOB field belongs to a queue table where the content is very dynamic, after a certain amount of time, all old table entries should get deleted as part of the application design. All new LOB entries of the table will have the new inline length settings automatically.
If, on the other hand, the table content is very static, or if the data in the table will remain for an extended period of time, it takes extra effort to make the change effective for all LOB data in the table. For this, you need to reorganise the table including the LOB object as described in the next section.
Note:
- If you increase the number of inlined LOB entries, you potentially increase the amount of data that is being cached in the bufferpool. As a result, increasing the inline length of an LOB field potentially increases the load onto the bufferpool area. You might need to increase the memory configuration accordingly.
Changing the compactness of an LOB field
The compactness setting of an existing LOB field is not changeable. If you need to modify this setting, the easiest way is to create a new table using the desired setting for this LOB field. The procedure ADMIN_MOVE_TABLE helps you copy the table content into the new structure and drop the original table. Read on to learn more about the procedure.
Reorganising an LOB object
Let’s assume you have a table with one or more LOB fields. If you have changed the inline length of an LOB field, and you want to make all entries in the LOB field aware of this change, you need to reorganize the LOB data.
However, the most common scenario is that update, insert, and delete operations result in an increased amount of free space in the LOB object. Such an LOB object is also a candidate for reorganisation.
What You Should Consider Before Reorganisations
There are several options to reorganize tables and LOBs. Before you decide to reorganize a table, see also SAP Note 1942183.
Db2 REORG
LOB objects potentially become quite large. In particular, candidates for a REORG are often large and may have sizes up to the range of terabytes. Note that a Db2 REORG of the LOB object of a table requires the additional clause LONGLOBDATA. This REORG is offline which means that during the REORG the table cannot be accessed. With a runtime that can take several days, or even weeks, this option often is ruled out.
In addition, if you need to restore a backup image and perform a ROLLFORWARD operation that covers a table REORG, this ROLLFORWARD is slowed down considerably which potentially increases the time required for DB recovery. The slow rollforward also affects HADR environments and may lead to repeated "hickups" of the DB for the time the REORG activity is rolled forward.
ADMIN_MOVE_TABLE and DB6CONV
Another option is to move the table into a new table using the Db2 procedure ADMIN_MOVE_TABLE. SAP Netweaver environments use a wrapper of ADMIN_MOVE_TABLE, see report DB6CONV which is available as attachment to SAP Note 1513862. During the use of the Db2 procedure, the table is fully operable and accessible. For a large table the process might take long, but it prevents the problems of a Db2 REORG approach.
In SAP Netweaver environments, DB6CONV is the recommended and frequently used method to reorganize tables.
Investigating an LOB object
As any reorganisation of an LOB object takes a long time and consumes a lot of resources, you are immediately faced with the question of how to decide whether a reorganisation makes sense or not. As mentioned, apart from the information provided by table function ADMIN_GET_TAB_INFO and function ADMIN_IS_INLINED, there is little that can be said about an LOB object. However, with the information about space allocation for LOBs depending on their compactness setting it is possible to derive further details.
Table function SAPTOOLS.LOBALLOC – check LOB free space
SAP environments can make use of table function LOBALLOC that is defined in schema SAPTOOLS (see SAP Note 3301718). The output fields are the following:
Here, the logical and physical LOB size is derived from table function ADMIN_GET_TAB_INFO. The output field LOB_OBJECT_O_SIZE is calculated using one of the 2 formulae fragments below for each entry that is not found to be inlined (the scalar function ceil() returns the integral part of a decimal number):
- For COMPACT='N': 2 ^ ceil( log2 ( <field length> ) -10 )
- For COMPACT='Y': ceil( (<field length> + 1023)/1024 )
This is the field length rounded up to the next 1k boundary.
For the calculation, these numbers are summed up. The calculation also takes into account that the minimum allocation is 1k, while the maximum segment size is 64m. For reference, see https://www.ibm.com/docs/en/db2/11.5?topic=tables-space-requirements.
Example
For illustration, consider a table named LOBTEST0 in database schema MSCHUENE created as follows. The COMPACT and INLINE_LENGTH values for the LOB field are taken from the database catalog. The number of LOBs not inlined and the maximum LOB length are queried from the table using the functions ADMIN_IS_INLINED and LENGTH on the LOB field.
The table contains two CLOB fields, C1 and C3 with a size of 1gb and 1mb, respectively. Let’s assume the table is populated with data.
A basic check of the LOB object then shows the size of the LOB object (LOB_OBJECT_L_SIZE, LOB_OBJECT_P_SIZE) and the optimal (LOB_OBJECT_O_SIZE) size being almost equal. The optimal LOB size is the minimum size of the LOB field that can be reached with the current compactness and inline length. Note that this is a calculated, theoretical value. Therefore, small deviations are to be expected.
As a conclusion, in this scenario it is not possible to get space from the LOB object back to the tablespace by simply using reorganization activities.
Note that in the output of the table function, the size information for the LOB data is only available for the table, but not for the LOB fields themselves. However, the field LOB_OBJECT_O_SIZE gives you the calculated optimal size of each of the LOB fields since the table function evaluates them from the lengths of the individual LOBs of each data row.
Table function SAPTOOLS.LOBALLOC – optimize LOB free space and performance
As mentioned earlier in this blog post, the database can place small entries of an LOB field in the data pages of a table. This depends on the length of the LOB entry and the size of the inline length settings of the field being checked. Another option to affect the LOB size is the compactness.
The table function LOBALLOC can calculate the optimal LOB size of a table using modified (test) settings of the inline length and the compactness of the LOB fields. These test settings are specified in an additional column using XML format.
With the XML field XML_LOB_PARAMS, you can specify test values for the inline length and the compactness for multiple LOB fields in a single call.
The output fields then include the test settings marked in red below:
The test settings can change the values of fields marked blue:
- Optimal LOB size (LOB_OBJECT_O_SIZE)
- For a modified inline length, the number of LOB fields that are not inlined (CNT_NOT_INLINED). The compactness setting cannot modify this value.
You can call the table function LOBALLOC to test alternative values for compactness and inline length, see the examples below.
Check the increased inline length
How does the optimal size of the LOB object change if you increase the inline length of field C1 of table LOBTEST0 that was used above? The SQL command for this request is the following:
The XML data specifies the field name and the attribute change that we want to verify. As a result, you will get the following output:
With the modified inline length, the optimal LOB size (values of LOB_OBJECT_O_SIZE in the red boxes of the screenshot above) is only slightly smaller than the values of LOB_OBJECT_L_SIZE and LOB_OBJECT_P_SIZE. Indeed, the number of entries of table field C1 that are not inlined (output field CNT_NOT_INLINED) undergoes only a small change from 13369 to 13291.
Finally, the table function also shows you the number of entries that are not inlineable (field NOT_INLINEABLE) as the entries are larger than the maximum inline length (MAX_INLINE_LENGTH) that can be defined at the page size that is currently used.
Check the changed compactness
In the same way as with the inline length, you can check the effect of changed compactness. Below you can see how the LOB allocation is checked with COMPACT = 'Y' for field C1:
Since the compactness affects only the allocation of LOB segments, the only value that can change is the calculated optimal allocation. From the areas marked in red, you can easily see that the optimal LOB size of the table is considerably reduced.
Check multiple columns in a single SQL statement
The LOBALLOC table function can give the results for multiple changes on more than one LOB column as shown in the following example below:
For a better understanding, the XML specifications for each LOB field are shown in a structured manner. The part for the individual LOB fields is marked in red.
LOBALLOC in a DPF environment
What does the output of LOBALLOC look like in a DPF environment? See the following example:
Table DB2TEST.LOBALLOC is distributed across 4 partitions. The lines marked blue indicate rows that contain information by member, while the row marked red contains the total figures across all members. The information that is relevant for these rows are the LOB sizes (LOB_OBJECT_L_SIZE, LOB_OBJECT_P_SIZE, LOB_OBJECT_O_SIZE).
Conclusion
The LOB object of a table often has a large size. Maintaining LOB objects requires time and resources.
- Reorganising the LOB object of a table implies that the table is inaccessible during the runtime of the REORG. A good alternative is to use the ADMIN_MOVE_TABLE procedure.
- Options to influence the space allocation of an LOB object are the INLINE LENGTH and the COMPACT settings of the LOB fields of a table. However, changes of these settings require a REORG or ADMIN_MOVE_TABLE operation to make the change effective.
- Db2 LUW does not provide an easy way to check the details of the space allocation of an LOB object. However, in SAP environments, you can use the LOBALLOC table function that helps you determine if a REORG or ADMIN_MOVE_TABLE operation can reduce the space allocation of the LOB object.
- The LOBALLOC table function provides some insight into the allocation of the LOB object of a table. It can also assist you in determining the effect of changes to the INLINE LENGTH or COMPACT setting of LOB fields.
