Outline

• Finish LOB Table Spaces
• XML Table Spaces
• Quiz
• Universal Table Spaces
• Types of DB2 request

Introduction

• Last Thursday, we finished giving an overview of DB2 storage structures and resumed our discussion of table spaces.
• We finished talking about segmented table spaces, then talked about partitioned table spaces, and finally began talking about LOB table spaces and said how they are associated with a LOB column of a table.
• Let's keep look at some more properties of LOB table spaces...

How much data can be stored in a LOB column?

This depends on the kind of table space the base table is in. In all cases, though, the actual LOB data is stored in its own table space.

Partitioning and LOBs

More LOB data in each column can be stored in a partitioned table space because each partition must have a LOB table space. One can assign the number of partitions (from 1 to 4096).

XML Table Spaces

• XML data is text data that has been marked up with tags of the form <some tag name> </some tag name> to indicate additional semantic information.
• For example, a web page might be an XML document (depending on if it validates as XHTML), RSS feeds are XML documents, etc.
• DB2 supports creating tables which can be used to store XML data.
• Because nestings of XML tags induce a tree structure on a text document, techniques for querying such columns especially for large documents benefit from different kinds of optimization than just vanilla SQL.
• To allow for this, DB2 treats XML columns in a similar fashion to LOB data: Only a reference to a given XML document is actually stored as the value of XML column of a row.
• This reference then points to the XML document which lives in a separate XML table space.

More on XML Tables Spaces

• An XML table space inherits several attributes from the base table space, such as:
  • LOG
  • CCSID - Coded Character Set Identifier (16 bits to say the character set)
  • LOCKMAX
• Tables that contain XML columns also have the following implicitly created objects:
  • A hidden column to store the document ID.
  • A unique index on the document ID (document ID index).
  • The base table will have an indicator column for each XML column containing a null bit, invalid bit, and a few reserved bytes.

XML Table Spaces and Partitioning

• Unlike LOB columns on a partitioned table, XML columns on a partitioned table only result in one XML table space per column.

Quiz

Which of the following statements is true?

1. DB2 only supports one type of index.
2. The EDM pool might be used to store internal representation of query plans.
3. Users can directly query the DB2 Directory

Universal Table Spaces

• This kind of table space combines the benefits of segmented space management with partitioned table space organization.
• It uses the segmented space map page organization.
• There are two types of universal table spaces(UTS):
  • Partition-by-range universal table space -- you specify the partitions by specifying both keywords SEGSIZE and NUMPARTS on a CREATE TABLESPACE statement
  • Partition-by-growth universal table space -- divides the available space into separate partitions, but these are managed by DB2

Partition-by-Range

• Range-partitioned table spaces are one type of universal table space. Range- partitioned table spaces are based on partitioning ranges.
• A range-partitioned universal table space uses the segmented space map page organization. However, it contains a single table, which makes it similar to the regular partitioned table space.
• The new range-partitioned table space does not replace the existing partitioned table space, and operations that are supported on a regular partitioned or segmented table space are supported on a range-partitioned table space.

Partition-by-Growth

• The table space starts with one partition. Additional partitions will be added on demand until the maximum partition is reached.
• These were introduced in Version 9.
• A partition-by- growth table space can grow up to 128 TB, and its maximum size is determined by MAXPARTITIONS, DSSIZE, and page size.
• Partition-by-growth table spaces are best used when a table is expected to exceed 64 GB and does not have a suitable partitioning key for the table.

Partition-by-Range and Auxiliary Objects

• LOB table spaces -- Each LOB table space can be defined by user via SQLRULES
• XML AUX table spaces -- The type of table space is also PBR UTS; XML rows are in the same part as base rows.

Partition-by-Growth and Auxiliary Objects

• LOB table spaces -- Can be defined by user for the first partition via SQLRULES; aside from the first partition, all LOB table spaces are implicitly defined by DB2
• XML AUX table spaces -- The type of table space is also PBG; Base and XML table spaces grow independently; XML documents can span partitions

Views

• Views are DB2 structures which act as tables that can be queried; however, they are defined in terms of SQL queries on base tables and other views.
• Their definition is stored in the DB2 catalog which are resolved into base table references when the view is bound.
• Users see views as real tables
• Views are useful for security, ease of use, data independence reasons.

Example View

The following view might be used for people who don't have access to certain privileged columns:

DB2 Address Space and System Architecture

• We are now going to start looking at the system address space of DB2
• Then look at the major DB2 components in each address space and their functions
• Finally, we are going to look at update execution process and the DDL process
• Before we begin, we are going to look at how DB2 handles requests.

Distinct Sources of Requests

• There're two distinct types of requestors to DB2:
  • Conventional users and application programs who inquire or modify DB2 data
  • A Database Administrator: a person who is responsible for the structure or schema of a database
• Database administrators use the Data Definition Language (DDL) of SQL to define and modify schemas. DBAs also use the Data Control Language (DCL) of SQL to assign schema privileges to other user, and remove schema privileges from others.
• Examples of DDL statement include: CREATE, ALTER, DROP,...,etc.
• Example of DCL statement include: GRANT, REVOKE.
• On the other hand, most requests on a database are from conventional users or applications.
• These requests are typically Data Manipulation Language (DML) requests to either get data (SELECT) or to modify it (INSERT, DELETE, UPDATE).
• Next day, we will begin to look at how these different kinds of requests are handled within the system architecture of DB2.