DB2 logical storage theory to define when creating table

In DB2, the must know key point is logical storage. When I asked DB2 developers  they totally confused about the concept of logical storage. There are two kinds of storage in any RDBMS. One is physical and the other one is logical.

Physical storage –> It is pure physical

On top of physical you will get logical storage.

Table storage

What did you get from the above pic.

….The point is final user will read it from only logical storage.

Sample Query..

Create table tablename
(various column names and attributes)
[in tablespace-name]
[Index in tablespace-name]

In the above SQL query both Table and Index defined in the same table-space. So here table-space is equal to logical storage.

What kind of controls do you have on table-spaces?

Yes, you need to have access to do any operation on the tables mentioned in the table-space.

You already know right?…admin has to give access.

Any access… DBA is final here.

There are essentially three types of tablespaces you can declare a table to use during its creation. First, you can nominate in which regular tablespace the normal data for a table is stored. You can optionally nominate a separate regular tablespace in which all the indexes for a table will be placed. Finally, you can allocate a large tablespace for housing the large objects of a table.

Also read: DBA top roles and responsibilities

You can find more information in the above video on how to manage table-spaces.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.