In this topic, we described about the below sections -
DB2 is a database that requires a storage structure to store the Database objects(tables, indexes, views etc) on a storage device like disks, tapes, etc.
DB2 storage structure depends on the z/OS system installation. The DB2 administrator decides what storage structure is suitable based on the project requirements.
In DB2, the storage structure is a set of one or more VSAM datasets. These VSAM datasets can hold the tables and indexes.
Most of the systems use the VSAM dataset to store the tables and indexes in the mainframe environment.
A storage structure is also called a page set.
The two major types of storage structure in DB2 for z/OS are -
- Table spaces
- Index spaces
The storage structure hierarchy is shown below -
STORAGE GROUP -
- Storage group is the collection of the same type of volumes. In other words, A Storage group is a set of storage objects of the same kind.
- A Storage group can have data sets in which the tables and indexes are actually stored.
- The volumes should have direct access.
- One storage group can contain a maximum of 133 volumes in it.
- The storage group concept is explained in detail here .
- A Database is a physical object, and it contains the memory area.
- A database is a set of structures, and a structure can contain tables, tablespaces and indexes etc. DB2 can control the data by using Database.
- The database name is the logical name to the Database.
- Once the table is created in Database, the table refers to and can be accessed by using the Database name.
- The database concept is explained in detail here .
TABLE SPACE -
- Table space is a set of volumes or disks where the tables are actually stored.
- Table space contains only tables created with the specific table space name.
- One table space can have one or more tables. One table space can store in more than one VSAM file.
- Table space is divided into equal parts called as pages.
- The user should have CREATE TABLESPACE privilege to create the storage group.
- CREATE TABLESPACE is used to create table space.
- The table space concept is explained in detail here .
- The table is the set of rows and columns that are logically related.
- In other words, the table is the logical structure of storing data.
- The table name is an identifier of up to 128 characters.
- The SQL identifier used to qualify the table name is called as SCHEMA.
- CREATE TABLE s used to create the table.
- SYSADM and SYSCTRL authority is required to create a table.
- If the user doesn’t have the above-specified authority, it returns a -551 error when the CREATE TABLE statement is triggered.
- The table concept is explained in detail here .
- VIEW is the table that can be derived from one or more tables. In other words, we can define the view as "the Table defined from one or more tables based on selection criteria".
- A view can create using a maximum of up to 15 tables.
- VIEW can omit the columns from base tables and can only create with required columns of the base tables.
- CREATE VIEW statement is used to create the VIEW.
- If a VIEW is created with all columns of a table, we can insert a row by referring to the view name in the INSERT statement.
- VIEW concept is explained in detail here .