1 / 52

DB2 LUW LOBs – Past, Present, and Future

DB2 LUW LOBs – Past, Present, and Future. Bill Minor IBM bminor@ca.ibm.com Jun 3, 2010 Platform: DB2 for Linux, UNIX, Windows. Abstract and Objectives.

tamal
Download Presentation

DB2 LUW LOBs – Past, Present, and Future

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 LUW LOBs –Past, Present, and Future Bill Minor IBM bminor@ca.ibm.com Jun 3, 2010 Platform: DB2 for Linux, UNIX, Windows

  2. Abstract and Objectives • This session will provide an informative overview of LOBs and their use in DB2 LUW. Topics of discussion will include storage and space management, concurrency (locking), monitoring, performance, utility and application considerations. Objective 1: Provide an introduction to LOBs in DB2 LUWObjective 2: Gain an understanding of LOB storage management and optimization Objective 3: Provide insights on how to maximize LOB performance.Objective 4: Provide useful examples that can be directly applied to any systemObjective 5: Highlight new features and enhancements specific to LOBs

  3. Overview of Presentation Flow • Introductions to LOBs • Using LOBs • LOB Architecture and Storage • Utilities • Considerations for Administration, Applications, Performance • LOB Inlining • Monitoring and Integrity Checking

  4. LOBs: Introduction • What are LOBs? • LOB is generic acronym for Large Object • Represents a class of DB2 built-in data types • These data types can have large values up to a maximum size of 2GB each • Typically used to store video, audio, image data • But being used more and more as a dump bucket to accommodate free form data without concern for what is inside the column • Typically LOB values read/write in whole, no piecemeal updates • Given the large value of LOBs, there are many special considerations for managing such data types

  5. Large Object Data Types: CLOB, DBCLOB, BLOB • CLOB or CHARACTER (CHAR) LARGE OBJECT • Character type for large objects compatible with CHAR data type • If the length specification is omitted, a length of 1 048 576 (1 megabyte) is assumed • DBCLOB • Character oriented type for large objects compatible with family of GRAPHIC data types • For a double-byte character large object string of the specified maximum length in double-byte characters • BLOB or BINARY LARGE OBJECT • Represents a byte oriented type that does not contain characters • For a binary large object string of the specified maximum length in bytes. The length may be in the range of 1 byte to 2 147 483 647 bytes • If the length specification is omitted, a length of 1 048 576 (1 megabyte) is assumed

  6. DDL With LOBs EXAMPLE: CREATE TABLE t1 (c1 INT, c2 CLOB(32M)) IN datSpace LONG IN lobSpace • Things to note: • LOB data can be separated into it’s own table space “LONG IN” clause • If no size for LOB specified, default is 1MB • Can choose to Not Log Lobs • Can choose a Compact storage format for LOBs (this is not compression) • Small LOBs can be inlined as of V9.7 • LOBs sizes can be ALTERed larger (shrink not allowed) • LOB columns cannot be indexed • There are catalog tables which have LOB columns

  7. Tables With LOB Columns • Just because you have not explicitly created tables with LOB columns does not mean your database isn’t using LOBs • Many business applications will create tables with LOBs • Business Objects, Cognos, FileNet, … • DB2 Catalog tables makes use of LOBs • SYSCAT.TABLES, INDEXES, ROUTINES, COLUMNS, VIEWS, TABOPTIONS, INDEXXMLPATTERNS, EVENTS, CHECKS, TRIGGERS, NICKNAMES, STATEMENTS, DATAPARTITIONEXPRESSION, XRSOBJECTCOMPONENTS, XDBMAPSHREDTREES, …. Example – Find all tables with LOB columns defined: db2 “select tabname,colname,inline_length,pctinlined from syscat.columns where typename=‘CLOB’ or typename=‘BLOB’ or typename=‘DBCLOB’”

  8. LOBs are BIG But They Do Have Limits • Maximum size of a LOB value is 2GB • LB Object – Max 4TB (regardless of page size) • LBA Object – Limited by the underlying table space • NOT LOGGED mandatory for LOB size > 1GB (SQL0355N) • This limit increased to 2GB in V9.5 FP5 and V9.7 FP1

  9. SQL Operations with LOBs • INSERT, SELECT, UPDATE, DELETE • Scalar operations: • SUBSTR, CONCAT, VALUE, LENGTH • Search: • LIKE, POSSTR • Apply a user defined function

  10. LOB Fetch • Keep in mind that there is no DB2 buffering of the LOB user data • That is, LOB user data does not go through the bufferpool • There are configuration and performance considerations since LOB data is large and must be directly fetched • Direct I/O activity can be observed through • Table Space Snapshot and MON_GET_TABLESPACE SQL routine (V9.7) • Elements to monitor: • DIRECT_READS • DIRECT_READ_REQS • DIRECT_READ_TIME • FS_CACHING • …

  11. Updating LOBs • LOB Manager supports concatenation ( “||” or “concat”) • Example: UPDATE t1 SET LobCol = LobCol concat :HostVar • In all other cases, UPDATE is treated as DELETE followed by INSERT • The old data is left intact on disk (with protection) and new data is inserted elsewhere. • Twice the disk space

  12. LOB CREATE TABLE: Logged/Not Logged .-LOGGED-----. .-NOT COMPACT-.     --●--+------------+--●--+-------------+--●---------------------|     '-NOT LOGGED-' '-COMPACT-----'   • LOGGED • Specifies that changes made to the column are to be written to the log. The data in such columns is then recoverable with database utilities (such as RESTORE DATABASE). LOGGED is the default • NOT LOGGED • Specifies that changes made to the column are not to be logged. This only applies to LOB data that is not inlined. NOT LOGGED has no effect on a commit or rollback operation; that is, the database's consistency is maintained even if a transaction is rolled back, regardless of whether or not the LOB value is logged. The implication of not logging is that during a roll forward operation, after a backup or load operation, the LOB data will be replaced by zeros for those LOB values that would have had log records replayed during the roll forward. During crash recovery, all committed changes and changes rolled back will reflect the expected results. • Cannot ALTER a LOB column to change these settings • DB CFG parameter: BLOCKNONLOGGED = YES (or NO) D:\>db2 "create table t2 (c1 int, c2 clob(3000) not logged)" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL20054N The table "lob options prohibited with blocknonlogged enabled" is in an invalid state for the operation. Reason code="". SQLSTATE=55019

  13. Logging of LOBs: Points to Note • With NOT LOGGED, rollforward recovery - including HADR replay, does not recovery the original LOB column value, but replaces the n-byte LOB with binary-0's (like using circular logged database) • So, the user data is lost and the only aspect of the data which is maintained is the original length • *Any application which defines a LOB column > 1GB can only be NOT LOGGED • Thus cannot be part of a highly available solution using HADR, nor can the database, or the table space containing LOB data, be recovered and rolled forward (even ROLLFORWARD STOP) to recover the original LOB data • Restriction of only be able to log LOBs up to 1GB has been lifted • Can log LOBs up to 2GB in size as of V9.5 FP5 and V9.7 FP1 • The data in LOB columns are not logged in a single log record, it is broken up into log records containing 32KB of data for each.

  14. LOB CREATE/ALTER TABLE: Compact/Not Compact • COMPACT • Specifies that the values in the LOB column should take up minimal disk space (free any extra disk pages in the last group used by the LOB value), rather than leave any leftover space at the end of the LOB storage area that might facilitate subsequent append operations. Note that storing data in this way may cause a performance penalty in any append (length-increasing) operations on the column. • NOT COMPACT • Specifies some space for insertions to assist in future changes to the LOB values in the column. This is the default.

  15. LOB Table Space Objects • There are always two objects associated with LOBs • LB or the raw LOB data itself • LOB data is not buffered in DB2 Bufferpool • LBA or the LOB Allocation Data • Contains all the space allocation structures for the LOB data (buffered) • In SMS table space, LOB objects have “.LB” file extension while LOB Allocation files have a “.LBA” file extension • In DMS table spaces, a map generated by db2dart /DHWM would show you what object extents for a particular table belong to the LOB data and LOB Allocation Maps (along with the Data, Index and XML extents – see next slide)

  16. DB2DART DMS Tablespace Map [0000] 65534 0x0e [0001] 65534 0x0e [0002] 65535 0x00 [0003] 4 0x40* [0004] 4 0x00* [0005] 5 0x40* [0006] 5 0x00* [0007] 5 0x41* [0008] 5 0x01* [0009] 6 0x40* [0010] 6 0x00* [0011] 6 0x41* [0012] 6 0x01* [0013] 7 0x40* [0014] 7 0x00* [0015] 7 0x43* [0016] 7 0x03* [0017] 7 0x44* [0018] 7 0x04* [0019] 7 0x41* [0020] 7 0x01* [0021] 8 0x40* [0022] 8 0x00* [0023] 8 0x43* [0024] 8 0x03* [0025] 8 0x44* [0026] 8 0x04* [0027] 8 0x41* [0028] 8 0x01* [0029] 9 0x40* [0030] 9 0x00* [0031] 9 0x41* [0032] 9 0x01* [0033] 10 0x40* [0034] 10 0x00* [0035] 10 0x41* [0036] 10 0x01* [0037] 11 0x40* [0038] 11 0x00* [0039] 12 0x40* [0040] 12 0x00* [0041] 12 0x41* [0042] 12 0x01* [0043] 13 0x40* [0044] 13 0x00* [0045] 14 0x40* [0046] 14 0x00* [0047] 15 0x40* [0048] 15 0x00* [0049] 16 0x40* [0050] 16 0x00* [0051] 17 0x40* [0052] 17 0x00* [0053] 7 0x03 [0054] 7 0x03 [0055] 18 0x40* [0056] 18 0x00* [0057] 19 0x40* [0058] 19 0x00* [0059] 19 0x43* [0060] 19 0x03* [0061] 19 0x44* [0062] 19 0x04* 0x04 is LOB Allocation Data Extent 0x03 is LOB Data Extent 0x00 is Data Extent 0x01 is Index Data Extent Extent number Extent Type Object ID

  17. Logging and Recovery • All changes made to the allocation pages are logged • Log inserted LOB data; Deletes not logged • If database is not recoverable (circular logging), the user data is not logged. Otherwise the user data will be logged except when the LOB column is NOT LOGGED. • User data log records are ignored during crash recovery • LOB data is guaranteed on disk at commit. If there is a system failure after commit, the LOB data will have been written out before the system failure. • LOB data is shadowed. • Original LOB data is never overwritten • New LOB data is written to new places • Buddy segments containing the old LOB data are protected until the transaction commits or rollbacks. If the transaction rollbacks, we only need to restore the original state of the buddy segments. The old LOB data is still left intact on disk.

  18. How is LOB Data Stored, Typically? Perm Data Table Space LOB Table Space Last Dept EmpID Photo LOB_1 LOB_2

  19. The LOB Descriptor • A LOB descriptor is a pointer to the location(s) of the LOB data in the LOB object • It is stored within the formatted data row, one descriptor per LOB column • The descriptor contains the following information: • Size of the user data in bytes • Location: information to ‘decode’ the object relative address(es) and size(s) of all the storage segments (“Buddy Segments”) that hold the user data • Book keeping fields e.g. a versioning field • LOB descriptors can vary in size – from 60 bytes to 312 bytes • The size is dictated by the defined size (DDL) of the LOB column (see next slide) • When fetching a row that contains LOB data, the descriptor for each LOB column is decoded in order to go locate and return the LOB data

  20. LOB Descriptor Sizes as a Function of LOB Size

  21. LOB Storage Management • LOB data is stored in an auxiliary object except when it is inlined ‘LOB data’(V9.7 – upcoming slides) • Management of LOB storage (that is not inlined) is based on a Buddy Space • Storage in a Buddy Space is allocated or ‘chunked out’ in various segment sizes (power of 2) • There are 17 levels of Buddy segments ranging from 1KB, 2KB, 4KB, 8KB, 16KB up to 64MB • This means that the smallest unit of storage allocated to a LOB is 1KB • How these chunk or segment sizes are allocated to a LOB depends on whether the LOB was defined as NOT COMPACT (the default) or COMPACT • The LOB Allocation structure (stored in the LOB descriptor in the data row) encodes a list of the location of all the Buddy Segments that are used to hold a particular LOB value • LOB Locking and searching for free space is based on this allocation of various segments • As of V9.5 all LOB locks have been eliminated – no consumption of locking resources (memory) and better space utilization • APAR LI74227 V9.5 FP5

  22. LOB Storage with COMPACT • Break down space requirement into smaller blocks • Example: A 13KB space requirement breaks down to 3 smaller space requirements – 1KB, 4KB and 8KB • No guarantee the small blocks are contiguous on disk, even after REORG • Possibly more I/O costs to read and write • Monitor Direct I/O via snapshot or admin routines (as noted previously)

  23. LOB Storage with NOT COMPACT • Default setting • Look for one single block that covers the space requirement • Example: 13KB LOB data will be contained in a 16KB free block. The extra 3KB free space is not used. • Less I/O cost for read and write compared to COMPACT case

  24. Size of LOB Objects – admin_get_tab_info( ) • Among other things, the administrative table function admin_get_tab_info can report on LOB storage • Units returned are in KB (so in example below “352 KB” and “4400 KB”) • Size reported is based on number of extents known to the table object including meta data Example: db2 "select data_object_p_size,lob_object_p_size from table(sysproc.admin_get_tab_info('BMINOR','TT1')) as t" DATA_OBJECT_P_SIZE LOB_OBJECT_P_SIZE -------------------- -------------------- 352 4400

  25. Utilities and LOBs • Bulk loading of LOB data: IMPORT and LOAD • Reclaiming LOB storage: REORG • RUNSTATS – • No stats are collected for LOBs; Optimizer does not cost LOB access • BACKUP and RESTORE

  26. IMPORT with LOBs • When large object (LOB) data is stored in the main input data file, the size of the data is limited to a max of about 32 KB • Therefore, to avoid truncation of LOB data larger than this, store LOB data separate from the main data file and use thelobsinfile file type modifier when importing LOB data • LOB Location Specifier (LLS) indicates where LOB data can be found • The LLS is a string representation of the location of a LOB in a file stored in the LOB file path • The format of an LLS is filename.ext.nnn.mmm/, where filename.ext is the name of the file that contains the LOB, nnn is the offset in bytes of the LOB within the file, and mmm is the length of the LOB in bytes • For example, if the string db2exp.001.123.456/ is stored in the data file, the LOB is located at offset 123 in the file db2exp.001, and is 456 bytes long • Can be used to store multiple LOBs in a single file • Export utility generates and stores LLS’s in output file when lobsinfile is specified on EXPORT

  27. Example: LOB IMPORT CREATE TABLE lobfun (c1 INT, c2 CLOB(1M)) IN userspace1 LONG IN lobspc INSERT INTO lobfun values (1, ‘a’) • Now let’s put a big LOB is this table, say, this ppt presentation “C14.ppt” • In this example, file to be imported is called “myprez.del”. Here are it’s contents 1, C14.ppt.000.800000/ “1” is column c1 data “C14.ppt” is my LOB data and we are going to import from offset “000” of that file (the beginning) and we are putting “800000” bytes of this LOB data into c2 NOTE: don’t forget the trailing delimiter “/” IMPORT FROM myprez.del OF DEL LOBS FROM /home/bminor/lobdatapath MODIFIED BY lobsinfile INSERT INTO lobfun

  28. EXPORT with LOBs • When exporting tables with large object (LOB) columns, the default action is to export a maximum of 32 KB per LOB value and to place it in the same file as the rest of the column data. • If you are exporting LOB values that exceed 32 KB, you should have the LOB data written to a separate file to avoid truncation. • To specify that LOB should be written to its own file, use the lobsinfile file type modifier. This modifier instructs the export utility to place the LOB data in the directories specified by the LOBS TO clause. • See EXPORT Command documentation for further elaboration.

  29. LOAD with LOBs • Very similar to IMPORT, need to make use of lobsinfile modifier • Example: TABLE1 has 3 columns: COL1 CHAR(4) NOT NULL WITH DEFAULT, LOB1 LOB, LOB2 LOB ASCFILE1 has 3 elements: ELE1 positions 01 to 04, ELE2 positions 06 to 13, ELE3 positions 15 to 22 The following files reside in either /u/user1 or /u/user1/bin and contain LOB data ASCFILE2, ASCFILE3, ASCFILE4, ASCFILE5, ASCFILE6, ASCFILE7 Data Records in ASCFILE1: 1...5....10...15...20...25...30. REC1 ASCFILE2 ASCFILE3 REC2 ASCFILE4 ASCFILE5 REC3 ASCFILE6 ASCFILE7 The following command loads the table from the file data : db2 load from ascfile1 of asc lobs from /u/user1, /u/user1/bin modified by lobsinfile reclen=22 method L (1 4, 6 13, 15 22) insert into table1

  30. REORG • In general, the Reorganize Utility is designed to: • Reclaim free space within a data object (table, index, lob, xml) • Physically recluster table data into a specific logical index order • LOB data is only reorganized to reclaim space (you don’t recluster LOB data) • For Table REORG there are several options that can be specified and the underlying reorganization mechanism can be effected in different ways • Table REORG is first classified in one of the following ways: • Classic or ‘Offline’ Table Reorg (ALLOW ACCESS READ or NONE) • Inplace or ‘Online’ Table Reorg (ALLOW ACCESS WRITE or READ) • Inplace/’Online’ Table Reorg does not reorganize LOB Data • LOB object data can only be reorganized via Classic/’Offline’ Table Reorg where the LONGLOBDATA clause is specified (no Inplace Table Reorg support)

  31. Classic/’Offline’ Table REORG Command >>-REORG-TABLE—table-name----+------------------+-------------- ‘-INDEX—index-name-’ >--+-------------------+--+------------------+--+-----------+-- +-ALLOW NO ACCESS---+ ‘-USE—tbspace-name-’ ‘-INDEXSCAN-’ ‘-ALLOW READ ACCESS-+ .-KEEPDICTIONARY--. >--+---------------------------------------+--+-----------------+---<< ‘-LONGLOBDATA--+----------------------+-’ ‘-RESETDICTIONARY-’ ‘-USE—longtbspace-name-’

  32. Backup and Restore Considerations • If a table contains large amounts of long field and LOB data, restoring it could be very time consuming • The RESTORE command provides the capability to restore selected table spaces. If the long field and LOB data is critical to your business, restoring these table spaces should be considered against the time required to complete the backup task for these table spaces. • By storing long field and LOB data in separate table spaces, the time required to complete the restore operation can be reduced by choosing not to restore the table spaces containing the long field and LOB data. • If the LOB data can be reproduced from a separate source, choose the NOT LOGGED option when creating or altering a table to include LOB columns. • If you choose not to restore the table spaces that contain long field and LOB data, but you need to restore the table spaces that contain the table, you must roll forward to the end of the logs so that all table spaces that contain table data are consistent. • Note: If you back up a table space that contains table data without the associated long or LOB fields, you cannot perform point-in-time rollforward recovery on that table space. All the table spaces for a table must be rolled forward simultaneously to the same point in time.

  33. Replication Considerations • The Capture program reads the LOB descriptor in the log records to determine if any data in the LOB column has changed and thus should be replicated, but does not copy the LOB data to the change-data (CD) tables. • When a LOB column changes, the Capture program sets an indicator in the CD table. When the Apply program reads this indicator, the Apply program then copies the entire LOB column (not just the changed portions of LOB columns) directly from the source table to the target table. • Because a LOB column can contain up to two gigabytes of data, you must ensure that you have sufficient network bandwidth for the Apply program. Likewise, your target tables must have sufficient disk space to accommodate LOB data.

  34. Tablespace Considerations • The Database Manager does not cache LOB data in it’s Bufferpool – LOB data must be retrieved from disk when it is required • However, if LOB data is stored in SMS or DMS file containers, file system caching might provide buffering and, as a result, better performance • FILE SYSTEM CACHING • Specifies that all I/O operations in the target table space are to be cached at the file system level. • NO FILE SYSTEM CACHING • Specifies that all I/O operations are to bypass the file system-level cache. • If neither table space option is specified, the default is: • FILE SYSTEM CACHING for JFS on AIX, Linux System z, all non-VxFS file systems on Solaris, HP-UX, SMS temporary table space files on all platforms, and all LOB and large data • NO FILE SYSTEM CACHING on all other platforms and file system types • Because system catalogs contain some LOB columns, you should keep them in DMS-file table spaces or in SMS table spaces • Separate LOB objects into their own table spaces: page size considerations, file system caching, Backup and restore, overall ease of management • Use LARGE table spaces

  35. Application Considerations • When developing applications that fetch and process LOB data, application designers and database administrators need to understand how LOB processing affects performance • When an application fetches LOB data, the Database Server must place the LOB data into it’s buffers before sending back to client application • To help improve performance and reduce resource consumption, application designers should only materialize LOB data when necessary • Applications can request LOB locators for LOBs that are stored in remote data sources • A LOB locator is a 4-byte value stored in a host variable • An application can use the LOB locator to refer to a LOB value (or LOB expression) held in the database system • Using a LOB locator, an application can manipulate the LOB value as if the LOB value was stored in a regular host variable • When you use LOB locators, there is no need to transport the LOB value from the data source server to the application (and possibly back again) • In a client-server environment the number of network trips between client and server can cause performance issues – want to leverage Blocking (next slides)

  36. 3 Total network flows = 10 x 3 = 30 – then retrieves lob data for the row 3 10x Blocking of Cursors with LOB Columns : V9.1 • CLI application where LOB columns are retrieved from a table with just 10 rows of data • Application requests blocking but blocking is not supported by the server (DB2 9) and hence disabled Network 1 App 2 DB2 9 DB2 client Network flows per row: sqlfetch – retrieves one row of data with lob locator 1 sqlgetdata – retrieves length of lob data. 2

  37. All 10 rows with LOB progressive refs 1 DB2 V9.5 Data for 1st LOB 2 2 Remaining LOBs (x9) 3 4 sqlfetch – retrieves initial block of non-LOB data; returns 1st to app 1 sqlgetdata – retrieves lob data for the row 2 sqlfetch – retrieves next row (non-LOB data) to app 3 9 X sqlgetdata – retrieves lob data for the row 4 Blocking of Cursors w LOB Cols: V9.5 (Typical) • CLI application where LOB columns are retrieved from a table with just 10 rows of data • Application requests blocking Network App DB2 client Network flows per row: Total network flows = 1 (initial block) + 1 (1st row LOB data) + 9 (remaining row’s LOB data)___________________________________ 11

  38. sqlfetch 1 All 10 rows and LOB data returned 1 sqlfetch 2 - 10 Network flows for first row: sqlfetch – retrieves all 10 rows from server including LOB data Network flows for next 9 rows: NONE! Subsequent sqlfetch’s from app. satisfied locally Blocking of Cursors with LOB Cols: V9.5(Best Case) • CLI application where lob columns are retrieved from a table with just 10 rows of data and blocking is requested by application • SQLBindCol( ) is used, application has pre-allocated buffers and bound them to columns Network App DB2 V9.5 DB2 client Total network flows = 1

  39. Partitioning Considerations: Table and Database • Use LONG IN clause on CREATE TABLE statement to specify a list of table spaces for the table where LOB data is to be stored (must be LARGE table space) • If you specify that long data be stored in a separate table space for one or more data partitions, you must do so for all the data partitions of the table Example: CREATE TABLE document(id INT, contents CLOB) PARTITION BY RANGE(id) (STARTING FROM 1 ENDING AT 100 IN tbsp1 LONG IN large1, STARTING FROM 101 ENDING AT 200 IN tbsp2 LONG IN large1, STARTING FROM 201 ENDING AT 300 IN tbsp3 LONG IN large2, STARTING FROM 301 ENDING AT 400 IN tbsp4 LONG IN large2); Note: Only a single LONG IN clause is allowed at the table level and for each data partition. • If large objects (LOBs) are being loaded from separate files (that is, if you are using the lobsinfile modifier through the load utility), all directories containing the LOB files must be read-accessible to all the database partitions where loading is taking place • The distribution key for a table in a table space on a partitioned database partition group is specified in the CREATE TABLE statement or the ALTER TABLE statement. If not specified, a distribution key for a table is created by default from the first column of the primary key. If no primary key is defined, the default distribution key is the first column defined in that table that has a data type other than a long or a LOB data type. Tables in partitioned databases must have at least one column that is neither a long nor a LOB data type.

  40. User-Temporary Table Support • LOBs are now supported in user temporary tables in V9.7 • Declared Global Temporary Tables (DGTTs) can contain LOB-type columns • LOB-type columns also supported for Created Global Temporary Tables (CGTTs) • Prior to Version V9.7, you could not store LOB data in declared temporary tables, so applications either had to work around not having the LOB data in a declared temporary table or had to use a regular table. • For both types of user-defined temporary tables, the values for LOB-type columns are stored in the same table space in which the temporary table is instantiated.

  41. Revisiting LOB Storage • Prior to V9.7 LOB is stored only in an auxiliary storage object i.e. not in the data record • Only a descriptor is stored in the data record i.e. a locator to where LOB data actually resides • LOB storage is allocated (‘chunked out’) in potential units of size 1KB,2KB,4KB,…,64MB db2 create table T1 (c1 int, c2 clob(13000)) in DATSP long in LOBSP db2 insert into T1 values (5, ‘abc’) db2 insert into T1 values (10, ‘abcdefghij’) LOB Tablespace LOBSP Data Tablespace DATSP LOB Object abc LOB Descriptor abcdefghij

  42. V9.7 LOB Inlining • Instead of strictly storing LOB data in the LOB storage object, the LOBs, if sufficiently sized, can be stored in the formatted rows of the base table • Dependant on page size, the maximum length a LOB can be to qualify for inlining is 32 669 bytes • LOB inlining is analogous to XML inlining for XML data Example: create table mytab1 (a int, b char(5), c clobinline length 1000) LOB storage object Base table, mytab1 a b c ‘bbbbbbbbbbbb…’ 3 ‘cat’ ‘aaaaaaaaaaa…’ 9 ‘dog’ ‘cccccccccccc…’ 27 ‘rat’

  43. LOB Inlining Benefits • If a table possesses LOB data that can be inlined, there are considerable benefits with respect to performance and storage use • Performance • Whenever LOBs are inserted/retrieved, a disk I/O cost is incurred each time since this data is not buffered (unlike with base table data) • When inlined, this I/O cost is reduced since this data get buffered along with the base table data they are inlined with • Storage • Storage allocated to the storage object is reduced by inlining XML/LOB data in the base table (though base table storage increases) • Inlining small XML/LOBs can result in a noticeable decrease in net total storage since the decrease in storage size is greater than the increase in base table storage size • XML/LOBs inlined within the base table data can be compressed

  44. A LOB Inlining ‘Proof Point’ (Customer Example) V9.1 - 110GB of data; 10 minutes to run audit report V9.7 – LOB Inlining and Compression: 10GB; runtime 2.5 minutes (cold bufferpool), 20 seconds (primed bufferpool)

  45. Considerations for LOB Inlining • Good candidates: • Fit within a specified data page size (4KB, 8KB, 16KB, 32KB) • Frequently accessed • Not already compressed • Guidance: Administrative Table Functions • ADMIN_EST_INLINE_LENGTH(<column name>) • Returns an estimate of the inline length required to inline the data, a negative value if data cannot be inlined, or if data already inlined, the actual inline length is returned • ADMIN_IS_INLINED (<column name>) • Will report whether the LOB/XML documents in a column are inlined or not Example: select admin_is_inlined(c1) as IS_INLINED, admin_est_inline_length(c1) as EST_INLINE_LENGTH from tab1 IS_INLINED EST_INLINE_LENGTH ---------- ----------------- 1 292 0 450 0 454

  46. LOB Inlining Upgrade Considerations • Implicit Inlining • For upgraded databases, the INLINE LENGTH default value is the maximum size of the LOB descriptor for the corresponding LOB column. LOB data is inlined when the length of the LOB data plus the overhead is less than the INLINE LENGTH value. Therefore, if the LOB data length plus the overhead is less than the LOB descriptor size for the LOB column, the LOB data is implicitly inlined in a table row after the database upgrade. • pkcachesz • For upgraded databases, the INLINE LENGTH default value is the maximum size of the LOB descriptor. LOB data is inlined when the length of the LOB data plus the overhead do not exceed the INLINE LENGTH value. Therefore, if the LOB data length plus the overhead is less than the LOB descriptor size for the LOB column, the LOB data is implicitly inlined in a table row after the database upgrade. Storing LOB data as inlined might require that you increase the pckcachesz database configuration parameter. By setting this parameter to AUTOMATIC, the new requirements are taken into account.

  47. Integrity Checking: db2dart and INSPECT • There is some sanity and cross reference checking capability in db2dart for inspecting LOB data • The /ROW option can be invoked to • Check the control information stored in the LOB descriptors ensuring that no two descriptors point to the same LOB data (i.e. LOB overwrite) • Ensure that the LOB storage recorded in the descriptor physically exists in the LOB storage area (table space) /ROW sum Identifies whether L/F descriptors, LOB descriptors and control information should be checked. (1) Checks control information in rows. (2) Checks long field and LOB descriptors. Example: db2dart /db <dbname> /row 1 db2dart /db <dbname> /row 2 db2dart /db <dbname> /row 3 • INSPECT provides online checking i.e. db2 inspect check database lob high results keep insp_out.bin

  48. Comparison of db2dart vs. INSPECT

  49. Sample Report from DB2 INSPECT db2 inspect check table name staff2 results keep insp.out DATABASE: SAMPLE VERSION : SQL09054 2009-06-17-10.16.29.430931 Action: CHECK TABLE Schema name: BMINOR Table name: STAFF2 Tablespace ID: 3 Object ID: 5 Result file name: insp.out Table phase start (ID Signed: 5, Unsigned: 5; Tablespace ID: 3) : BMINOR.STAFF2 Data phase start. Object: 5 Tablespace: 3 Traversing DAT extent map, anchor 608. Extent map traversal complete. DAT Object Summary: Total Pages 51 - Used Pages 51 - Free Space 3 % Data phase end. LOB phase start. Object: 5 Tablespace: 3 Traversing LOB extent map, anchor 704. Extent map traversal complete. Traversing LBA extent map, anchor 768. Extent map traversal complete. LOB Object Summary: Total Pages 32 - Used Pages 2 LBA Object Summary: Total Pages 2 - Used Pages 2 LOB phase end. Table phase end. Processing has completed. 2009-06-17-10.16.29.437998

  50. Some Best Practices for LOBs • Separate large LOB data into it’s own separate table space • Use table spaces of type LARGE • Ensure LOB tablespace has FILE SYSTEM CACHING enabled • Characterize the distribution of actual LOB value sizes and exploit LOB Inlining and Table Compression • Make use of Admin Table Functions to monitor LOB space consumption • Ensure Client applications make use of Blocking to minimize client-server network flow of LOB data

More Related