Recommended Tuning for DB2

You can apply the following additional tuning when you use the DB2® database for document storage.

You can configure Sterling B2B Integrator to use DB2 for document storage, where both the metadata and the full payload persists in the database. The main Sterling B2B Integrator table that stores this data is TRANS_DATA where it is stored in a BLOB column. This is a heavy transaction table where the data insertion and retrieval occurs. You can also notice that the frequent LOB retrieval adds extra IO bottleneck to the database.

The DB2 database handles LOB (large object) in a different manner. You can store an LOB (character large object (CLOB), binary large object (BLOB), or double byte large object (DBCLOB)) with the regular row data (inline option) or outside the regular row-based storage. If the data is stored outside the regular row data then that storage details are stored in the row descriptor. The size of this descriptor varies from 60 to 312 bytes, depending on the size defined for the LOB in the table definition. LOB inlining option is evaluated for every row, if a row's LOB is within an inline size, then you can inline as part of the row on the regular data page. If the LOB data is larger than the inline size, then none of the LOB is inlined, which means DB2 does not inline only a part of the LOB. If the actual LOB size is less than the descriptor size, it is implicitly inlined, even if no inlining is specified for the column.

For most of the workload mix using LOB, inlining provides measurable performance benefits. This performance benefit is mainly because accessing data in the buffer pool is faster than accessing LOB data stored outside the row in which case there is extra IO required to read from disk. But, using inlining unwisely can also introduce other performance issues. You must consider the following factors when you want to inline TRANS_DATA LOB column.

LOB inlining strategy must fit as much LOB data as inline without moving to the bigger tablespace. Bigger tablespace incurs performance and storage overhead.
Note: You must consider moving to the bigger tablespace only if it is necessary and before moving the changes to production, you must ensure to run performance tests to validate the overall performance gain.
LOB inline length must be chosen rightly. Ideally, you can go with the minimum required inline length that fits around 90% of your data. Because of the DB2 "all-or-nothing” inlining behavior, you must understand the data distribution of the LOB column. This information can be determined using standard SQL as shown below.
Audit SQL to show the data distribution by size, count and inlined status:
```
select is_inlined, est_inline_length, count(*)
from (
SELECT wf_id, ADMIN_IS_INLINED(DATA_OBJECT) as IS_INLINED,
ADMIN_EST_INLINE_LENGTH(DATA_OBJECT) as EST_INLINE_LENGTH
from <schema>.TRANS_DATA)
group by is_inlined, est_inline_length
order by 2
```
Note:
- The maximum inline length for a column is limited by the page size of tablespace in which TRANS_DATA table resides, and the column size occupied by all the other columns and the size of the row descriptors.
- Scanning very large tables to find LOB length statistics can be expensive, so if needed, you can sample the results with TABLESAMPLE option and fetch meaningful results.
Sample DDL to change the inline size for TRANS_DATA table:
```
ALTER TABLE <schema>.TRANS_DATA ALTER COLUMN DATA_OBJECT SET INLINE LENGTH 1024
```

Note: You must test these recommended settings in your test environment before applying in production.