The DB2 storage structure defines how data is logically and physically organized to ensure efficient access and management. Understanding the storage structure helps in designing, managing, and optimizing the performance of a database. The hierarchy organizes data from Storage Groups (highest level) down to Rows and Columns (lowest level).

The two major types of storage structure in DB2 are -

• Table storage structure - organizes data logically and physically.
• Index storage structure - organizes index logically and physically for efficient storage and retrieval.

Let's walk through each of these components, their purpose, and how they fit together.

Storage Group -

A Storage Group is a collection of disk volumes (physical storage devices) used to store data. Each volume should have a direct access. A storage group can be defined with maximum of 133 volumes.

Example - Suppose the storage group named DATA_SG includes the volumes: vol1,vol2.

Database -

A DB2 database is the top-level logical structure that contains all the necessary objects, such as tables, indexes, tablespaces, and buffer pools. It holds one or more tablespaces and is linked to a storage group.

Example - A database called LIBRARY_DB stores data related to a library management system.

Table Space -

A Tablespace is a logical storage container within a database that holds tables. It maps to the physical storage in the storage group. Tablespaces help organize data, ensuring that large objects (LOBs), and regular tables are managed efficiently.

Example - Creating a tablespace for storing readers data.

Index Space -

A Indexspace is a logical storage container within a database that holds index of the tables. Index space is similar to table space but only contains indexes in it. The Index space was not required to create it separately. When index created, index space will be created automatically.

Tables -

A Table is where the actual data is stored in the form of rows and columns. Each table resides in a tablespace, and tables are created for specific entities like customers, orders, or products.

Example - Creating a table with books information for a department.

Index -

INDEX is an ordered set of pointers to the rows of table data. DB2 uses indexes to uniqueness and improve the performance. An index is stored separately from the data in table. Index's are physically stored in its index space.

Example - Creating a index for each book in the department.

---