Outline

• Logs and BSDS
• EDM Pool and Buffer Pool
• Back to table spaces

Introduction

• On Tuesday, we began looking at various persistent data structures used by DB2.
• We talked about databases, table spaces, and tables.
• We then looked at storage groups, indexes, the catalog, and the directory.
• Today, we are going to look at the Log and BSDS structures, the Buffer Pool and the EDM.

Logs

• DB2 uses logs for recovery from a system errors or disastrous conditions.
• Upon a change request or a system event, DB2 writes logs to the log output buffers. These log output buffers are externalized (written to DASD) to active logs when buffers are full or when a transaction is committed.
• By having these changes recorded, DB2 can re-create those changes for you in the event of a failure. It can also roll the changes back to a previous point in time.
• Old logs are offloaded to the archive logs, which are usually stored on slower devices such as tapes.
• The BSDS (bootstrap data set) is a VSAM key-sequenced data set (KSDS) that contains information that is critical to DB2, such as the names of the logs. DB2 uses information in the BSDS for system restarts and for any activity that requires reading the log.
• The BSDS acts as an index into the DB2 active and archive logs. The BSDS allows DB2 to determine from which log data set to retrieve a particular log record.
• The BSDS also contains an inventory of active and archive logs, a wrap-around inventory of all recent DB2 checkpoint activity, and the distributed data facility (DDF) communication record. This last record contains information that is necessary to use DB2 as a distributed server or requester.
• Because BSDS is so essential to DB2's recovery from system failures, two copies are automatically created at DB2 installation. If possible, the two copies should be placed onto separate volumes.

Log Structure

• The only part of this diagram we haven't talked about are the Outbuff and Inbuff. DB2 writes to the output buffer area whenever an update, commit, rollback, or checkpoint occurs. DB2 reads into the input buffer area during rollback, recovers utility jobs, and when it restarts. If you do lots of updating you need a large log output buffer area.

When is something written to the active log?

• A commit from one UR (Unit of Recovery) writes ALL log records that haven't yet been written from the output buffer. This includes even records from other URs. Transactions typically trigger this mechanism.
• When the WRITE THRESHOLD in the log output buffer area is reached, a write commences. A batch job that does not commit might trigger this.
• Log Write Ahead Force (LWAF) is a fundamental DB2 protocol: When the Buffer Manager wants to externalize a page to page set DASD, it must first force any log descriptions of the updates to log DASD (if they have not already been written).

In-memory Structures -- Buffer Pool

• Buffer pools are areas of virtual storage that temporarily store pages of table spaces or indexes. When an application program accesses a row of a table, DB2 places the page that contains that row in a buffer.
• Buffer pools are allocated from shared memory and can be accessed by concurrent threads.
• DB2 allows you to use up to 50 buffer pools that contain 4-KB buffers (BP0, BP1, ..., BP49) and up to 10 buffer pools each for 8-KB (BP8K0, BP8K1, ..., BP8K9), 16-KB (BP16K0, BP16K1, ..., BP16K9), and 32-KB (BP32K0, BP32K1, ..., BP32K9) buffers. You can set the size of each of those buffer pools separately during installation.
• If the requested data is already in a buffer, the application program does not have to wait for it to be retrieved from disk.
• If the row is changed, the data in the buffer must be written back to disk eventually. But that write operation might be delayed until DB2 takes a checkpoint, or until one of the related write thresholds is reached.
• The data remains in the buffer until DB2 decides to use the space for another page. Until that time, the data can be read or changed without a disk I/O operation.
• The LRU (Least Recently Used) algorithm is used for the buffer pool when free pages are not available to decide which page in the pool should be kicked out to disk.

Data Definition Language (DDL) and Buffer Pools

• A buffer pool is assigned to table space using CREATE TABLESPACE, CREATE INDEX, or ALTER TABLESPACE, ALTER INDEX:
  

  CREATE TABLESPACE DSN8S91D IN DSN8D91A 
  USING STOGROUP DSN8G910 
  PRIQTY 52 
  SECQTY 20 
  LOCKSIZE PAGE
  BUFFERPOOL BP1;
  
  CREATE UNIQUE INDEX DSN8910.XDEPT1 ON DSN8910.DEPT (DEPTNO ASC) 
  PADDED USING STOGROUP DSN8G910 PRIQTY 512 SECQTY 64
  BUFFERPOOL BP1;
  
  ALTER TABLESPACE DSN8D91A.DSN8S91D 
  BUFFERPOOL BP2 LOCKSIZE PAGE;
  
  ALTER INDEX DSN8910.XPROJ1 BUFFERPOOL BP1 
  COPY YES PIECESIZE 8M;
  

Types of Buffer Pool Pages

At any moment, a database buffer pool can have in-use, updated, and available pages.

• In-use pages -- Pages that are for read-only or intent-for-update. The data they contain are available for use by other applications.
• Updated pages -- Pages whose data has been changed but have not yet been written to disk.
• Available pages -- Pages that can be considered for new use, to be overwritten by an incoming page of new data. Both in-use pages and updated pages are unavailable in this sense; they are not considered for new use.

In-memory Data Structures -- EDM Pool

The environmental descriptor manager, or EDM, pool contains active and skeleton application plans and packages. EDM storage is composed of these five components, each of which is in a separate storage area:

• EDM skeleton pool -- Contains skeleton package tables (SKPTs) and skeleton cursor tables (SKCTs). Here SKCT: Internal representation of a plan. SKPT: Internal representation of a package. A plan or package is an executable module containing the access path logic produced by the DB2 optimizer. Before a DB2 for z/OS program (with static SQL) can be run, it must have a plan associated with it.
• EDM PT/CT pool -- Contains part of the cursor table (CTs) and package tables (PTs). CT: runtime copy of SKCT. PT: Runtime copy of SKPT
• EDM RDS Section pool -- Contains RDS sections. RDS sections are executable formats for SQL statements.
• EDM Dynamic Statement Cache -- Contains the skeleton package for dynamic cached statement.
• EDM DBD -- Contains database descriptors (DBDs)

Back to Table Space Details

• As I mentioned before, last Thursday, I has accidently skipped ahead in topics.
• On that day, we began talking about table spaces in detail, and, in particular, were looking at the five different types of DB2 table spaces (Segmented, Partitioned, LOB, XML, Universal) in turn.
• We had discussed the structure of Segmented Table Spaces and indicated how several tables can be stored in such table spaces, each as a link-list of segment. Here a segment was a collection of a fixed (per table space) collection pages.
• Let's now continue talking more about segmented table spaces...

Characteristics of Segmented Table Spaces

• The SEGSIZE keyword of CREATE TABLESPACE is used to say how many pages are in a segment. (Must be a multiple of 4 between 4 and 64)
• When DB2 scans all the rows for one table, only the segments that are assigned to that table need to be scanned.
• When DB2 locks a table, the lock does not interfere with access to segments of other tables.
• When DB2 drops a table, its segments become available for reuse immediately after the drop is committed.
• After all rows of a table are deleted, all segments except the first segment become available for reuse.
• A mass delete operates much more quickly and produces much less log information
• Some DB2 utilities, such as LOAD with the REPLACE option, RECOVER, and COPY, operate on only a table space or a partition, not on individual segments.

Partitioned Table Spaces

Definition of Partitioned Table Spaces

• For very large tables, it sometimes makes sense to split them into smaller pieces which will be faster to access.
• This can be done using a partitioned table space.
• A partitioned table space holds exactly one table, but its contents are split into partitions based on the value of a column usually. For instance, records before 1970, records from 1970 to 1980, etc.
• To create such a table space, you use the PARTITION clause of the CREATE TABLESPACE statement to define a partitioned table space.
• For each partition that you specify in the CREATE TABLESPACE statement, DB2 creates a separate data set.
• One assigns the number of partitions (from 1 to 4096), and one can assign partitions independently to different storage groups.
• The partitions are based on the boundary values defined for specific columns. Utilities and SQL statements can run concurrently on each partition.
• The maximum number of partitions in a table space depends on the data set size (DSSIZE parameter) and the page size.
• The size of the tables space depends on the data set size and on how many partitions are in the table space.

More on Partitioning

• If you create a table within a partitioned table space you then need to say how it is partitioned.
• This is done using the PARTITION BY clause of the CREATE TABLE statement.
• Indexes that are defined on a partitioned table are classified according to their logical attributes and physical attributes.
• The logical attribute of an index on a partitioned table pertains to the index is on a column of a table on which one is partitioning.
• The physical attribute of an index on a partitioned table pertains to whether the index is physically partitioned.
• A partitioned index is an index that is physically partitioned. Both indexes on partitioned columns and secondary indexes can be partitioned.
• The following figure illustrates the difference between a partitioned and a non-partitioned index.

LOB Tablespaces

• Large object (LOB) table spaces (also known as auxiliary table spaces) are necessary for holding large object data, such as graphics, video, or very large text strings. If the data does not fit entirely within a data page, one or more columns can be defined as LOB columns.
• LOB objects can do more than store large object data. One can also define LOB columns for infrequently accessed data; the result is faster table space scans on the remaining data in the base table. The table space scan is faster because potentially fewer pages are accessed
• A LOB table space always has a direct relationship with the table space that contains the logical LOB column values. The table space that contains the table with the LOB columns is, in this context, the base table space.
• LOB table spaces can be either implicitly or explicitly defined.
• If the base table space is not a partitioned table space, each LOB table space is associated with one column of LOBs in a base table. If the base table space is a partitioned table space, each column of LOBs in each partition is associated with a LOB table space.