In DB2 10 you can now define LOBs to also be „inline“.
This small feature is actually fantastic for performance and general use and I urge all DB2 users to evaluate using them!
What they enable is the use of just the „base“ table space and not the aux (LOB) space at all! This is a very good thing as LOB access can(!) sometimes be painfully slow and cause bottlenecks in the processing.
In the DB2 10 Performance guide Chapter 4.3 there is a nice list of benefits:
Inline LOBs offer the following performance advantages over LOBs that are stored in auxiliary tables (sometimes called outline LOBs):
- Disk space savings because two LOBs cannot share a page on a LOB table space
- Disk space savings because the inline portion of a LOB can be compressed
- Synchronous I/Os to the AUX index and LOB table space are avoided
- CPU savings associated with accessing the AUX index and LOB table space
- Sequential and dynamic prefetch I/O for LOBs
- Improved effectiveness of FETCH CONTINUE when scanning rows
- Index on expression can be enabled for LOB data
Note the LAST one which I think is actually one of the best reasons!!!
Obviously there are a few „considerations“:
- Check how long your LOBs are now and try to size for the 80 / 20 rule
- Remember that the Page size and the related BP size will need to be adjusted and tuned
- If you rarely use the LOB column then don’t bother!
- If going from „old“ style space to an inline LOB with compression then you must do a sort of triple jump:
a. ALTER to get a UTS and also in RRF format – REORG to action
b. ALTER to get INLINE LOB usage – REORG to action
c. Now REORG it again to actually get COMPRESSION as that is not done by the earlier REORGs!
An example SQL from the DB2 Performance chapter 4.3
Here’s an example SQL that I took from the DB2 Performance chapter 4.3 and changed a little to report more info:
WITH LOB_DIST_TABLE (LOB_LENGTH
,LOB_COUNT)
AS (
SELECT LOBCOL_LENGTH
,COUNT(*)
FROM (
SELECT ((LENGTH(STATEMENT) / 1000) + 1) * 1000
AS LOBCOL_LENGTH
FROM SYSIBM.SYSPACKSTMT
) LOB_COL_LENGTH_TABLE
GROUP BY LOBCOL_LENGTH
)
SELECT '01000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 1000
UNION ALL
SELECT '02000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 2000
UNION ALL
SELECT '04000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 4000
UNION ALL
SELECT '08000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 8000
UNION ALL
SELECT '12000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 12000
UNION ALL
SELECT '16000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 16000
UNION ALL
SELECT '20000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 20000
UNION ALL
SELECT '24000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 24000
UNION ALL
SELECT '28000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 28000
UNION ALL
SELECT '32000' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH <= 32000
UNION ALL
SELECT '99999' AS SIZE
,COALESCE(SUM(LOB_COUNT) , 0) AS COUNT
,(COALESCE(SUM(LOB_COUNT) , 0) * 100.00) /
(SELECT SUM(LOB_COUNT) FROM LOB_DIST_TABLE) AS PERCENTAGE
FROM LOB_DIST_TABLE
WHERE LOB_LENGTH > 32000
ORDER BY 1
;Now it returns this data:
---------+---------+---------+------- SIZE COUNT PERCENTAGE ---------+---------+---------+------- 01000 112102 95.66 02000 115957 98.95 04000 116771 99.65 08000 117044 99.88 12000 117110 99.94 16000 117140 99.96 20000 117173 99.99 24000 117174 99.99 28000 117175 99.99 32000 117176 100.00 99999 0 .00
DSNE610I NUMBER OF ROWS DISPLAYED IS 11
As can be seen just changing the inline LOB length to be 1000 bytes would „hit“ over 95% of the rows!
This might even mean no change to page size and/or bufferpool would be needed!
Remember to change the driver CTE to be your candidate LOB column and table as here I have used STATEMENT and SYSIBM.SYSPACKAGE.
Return the LOB column names and tables needed
Here’s a little query, taken from the performance book and enhanced/corrected, to return the LOB column names and tables needed:
SELECT SUBSTR(CO.NAME , 1 , 30) AS COLUMN_NAME
,STRIP(CO.TBCREATOR) CONCAT '.' CONCAT STRIP(CO.TBNAME)
AS TABLE_NAME
FROM SYSIBM.SYSCOLUMNS CO
WHERE CO.COLTYPE IN ('BLOB' , 'CLOB' , 'DBCLOB')
AND NOT EXISTS (SELECT TB.NAME
FROM SYSIBM.SYSTABLES TB
WHERE TB.NAME = CO.TBNAME
AND TB.CREATOR = CO.TBCREATOR
AND TB.TYPE = 'X')
ORDER BY 2 , 1
;
Create an index
One very nice feature is the ability to create an index on expression:
CREATE INDEX IQATW005.LOB_IOE_IX
ON IQA0610.IQATW005
(CHAR(SUBSTR(SQL_TEXT , 1 , 254)) )
USING STOGROUP SYSDEFLT
PRIQTY 14400
SECQTY 14400
ERASE NO
FREEPAGE 5
PCTFREE 5
BUFFERPOOL BP0
CLOSE YES
;Only available for SUBSTR and with fixed start and end but still very useful as you can now „scan“ the text in your WHERE clauses!
The performance benefits can be immense for in-line LOBs and so I recommend a quick test and then roll-out to production!
As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect