DB2 Table Storage Structure
DB2 table storage structure is a set of one or more VSAM datasets.
The VSAM dataset is used to store the tables.
A storage structure also called as a page set.
In db2, all the databases, table spaces and index spaces will be referred as DB2 Objects.
There are two types of storage structures based on the type of storage.
- Table space
- 2. Index space
The hierarchy for the DB2 table space is,
Storage Group | | \/ Database | | \/ Table space | | \/ Tables, views etc,.
Storage group is nothing but the collection of same type volumes.
In other words, A Storage group is nothing but a set of storage objects.
A Storage group can have data sets in which the tables and indexes actually stored.
The volumes must have the direct access.
One storage group can contain maximum 133 volumes in it.
Storage group concept explained in detail here .
A Database is the physical object and it contains the memory area.
Database name is nothing but the logical name to database where the tables are going to store.
Database is a set of structures, and structure can contains tables, table spaces and indexes etc,.
DB2 can control the data by using Database.
Database is a collection of tables and its indexes.
Once the table created in the database, the table will be referred and can be accessed by the Database name.
Database concept explained in detail here .
Table space is nothing but the space where the tables are stored.
A table space is a set of volumes or disks where the tables actually stored.
Table space contains all the tables in the database.
One table space can contain one or more tables.
One table space can be stored on more than one VSAM file.
Table space can be divided into equal parts, called as pages.
One operation on database can read one page at a time.
To create the storage group, user required CREATETS privilege is required apart from the above two authorities mentioned in storage space and database.
CREATE TABLESPACE is used to create table space.
Table spaces are of three types.
1. Simple table space
2. Segmented table space
3. Partitioned table space
Table space concept explained in detail here .
Table is nothing but the set of rows and columns that are logically related.
In another words, table is nothing logical structure of storing data.
Table name is an identifier 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.
Table definition must include the table name and the attributes of its columns.
SYSADM and SYSCTRL authority is required for to create a table.
If the ID didn’t have the above specified authority, then it will return -551 error when the CREATE TABLE triggered.
Table’s concept explained in detail here .
VIEW is nothing but the table which can be derived from one or more tables.
In other words, the view can be defined as “ The Table which can be derived from one or more tables based on selection criteria”.
MAX Up to 15 tables can be used to create the view.
VIEW can combine the data from different tables.
VIEW can omit the columns from base tables and can only create on with some columns of the base tables.
CREATE VIEW is used to create the VIEW.
A VIEW can be created on single table or more than one table.
If a VIEW created with all the tables of a single table, then the data can be inserted by referring the view name in INSERT.
VIEW concept explained in detail here .
If any questions on the above, post your queries on DB2 Discussion forum
DB2 Introduction DB2 Storage Group