Outline

• DB2 Features
• DB2 and its Environment
• Kinds of Tablespaces
• Start how Tablespaces are Organized Internally in DB2
• Varian Demo

DB2 Features

• 64-bit storage, machines with 64 GB ram, 16 exabytes of virtual address space
• High-speed communication - support for high-speed TCP/IP between Linux zSeries and other z/OS systems
• Dynamic workload management - the storage manager of the z/OS manages resources dynamically based on workload priorities
• Processor improvements - uses the system z9 Integrated Information Processor (zIIP) which is geared toward DB2 and related workloads in addition to having general processors
• Trusted data platform - provides a secure environment both within z/OS and for network transactions
• SQL enhancements - has support for new decimal and geometric data types, as well as improved support for migration from other platforms. It has a very good query reporting tool: QMF (Query Management Facility) whcih integrates with WebSphere
• Open software technology - Enterprise JavaBeans, XML, Unicode, service oriented architecture (SOA)
• DB2 in a Parallel Sysplex environment - gives the ability to dynamically add new DB2 for z/OS systems to a sharing group

DB2 and Its Environment

• DB2 operates as a formal subsystem of z/OS. That is, it is a subordinate subsystem capable of interacting independently.
• DB2 can work efficiently with other z/OS components such as the z/OS security server and z/OS Parallel Environment
• It can also interact with other environments using attachment facilities. These other environments are shown in the diagram above: CICS, IMS, WebSphere, and TSO (Time-sharing Option) and interact with Windows and Unix through clients.

The System View

User Interaction with DB2

• A user can execute code on DB2 using SPUFI (SQL Processing Using File Input).
• A major responsibility of any program is to group its statements into Logical Units of Work (LUW).
• User written programs and applications contains SQL statements. These statements are interpreted by DB2 into access paths, executed and used to return a result set to the user.

Programming Interfaces

The following systems have developer communities to support DB2 integration:

• COBOL, PL/I, C, C++
• REXX, APL2, Assembler, Fortran
• Java (JDBC / SQLJ)
• .NET (C#, VB .NET)
• PHP
• Perl
• Python
• Ruby on Rails, etc

DB2 Utilities

• Later in the semester we will talk about various utilities that DB2 comes with.
• Utilities are "special" programs that can be run by submitting JCL or calling the DSNUTILS stored procedure. They run in batch and get their control information via control statements (not SQL).
• Utilities are used, for example, to take backups of tables (COPY utility), to reorganize table data (REORG), or to load them with data from sequential data sets (LOAD utility).

Example of What SPUFI looks like

Results of SPUFI Query

An Example 3270 Transaction

DB2 Data Structures

• DB2 uses data structures to organize the data it stores.
• These include tables, table spaces, indexes, index spaces, keys, views, and databases.
• Some of these data structures contain other structures.
• For example, a DB2 database may contain a non-partitioned table space that contains a table. Or it may contains a partitioned table space that contains several table partitions.
• Similarly, one can have an Index Space containing a single Index, or an Partitioned Index Space containing several index partitions.

Table Spaces

• A table space is a DB2 storage structure that is used to store the records in one or more tables.
• A table space can consist of a number of VSAM (Virtual storage access method) data sets. (Different VSAM data sets have different formats for storing rows)
• As a general rule a DB2 instance should have no more than 50-100 table spaces.
• Tablespaces can be compressed.

Table Space Organization

• Tablespaces are divided into equal-sized units, called pages, which are written to or read from disk in one operation.
• Pages comes in 4 KB, 8 KB, 16 KB, or 32 KB sizes (all pages of a given table space the same size)
• Associated with DB2 instance is a TEMP database.
• This database must have at least one table space with 8kb pages.
• You can explicitly define a table space by using the CREATE TABLESPACE statement, which can specify the database to which the table space belongs and the storage group that it uses.
• Alternatively, you can let DB2 implicitly create a table space for you by issuing a CREATE TABLE statement that does not specify an existing table space. In this case, DB2 assigns the table space to the default database and the default storage group.

Types of Tablespaces

Tablespaces come in different flavors which (except for XML) can be specified when you do CREATE TABLESPACE:

• Segmented table spaces
• Partitioned table spaces
• Large OBject table spaces (LOB)
• XML table spaces
• Universal table spaces

Segmented Table spaces

Segmented table spaces can be used to store more than one (usually relatively small) table:

Segments are grouped by page. Each segment holds records from only one table. Each segment contains the same number of pages, and each table uses only as many segments as it needs. Segments belonging to the same table are chained together.

Segmented table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets.

More Facts about Segmented Tablespaces

• When DB2 drops a table, its segments become available for reuse immediately after the drop is committed.
• When you use an INSERT statement, a MERGE statement, or the LOAD utility to insert records into a table, records from the same table are sometimes stored in different segments. You can reorganize the table space to move segments of the same table together.