A database is an organized collection of related objects. Broadly the database objects can be classified as :
Tables
Aliases
Synonyms
Views
Indexes
Table spaces
Index spaces
Databases
Storage groups.
In this post we will see types of tables. In subsequent posts, we will visit the other DB objects
Tables: A table is an unordered set of data records. It consists of columns—each based on a data type—and rows. DB2 supports several types of tables classified as below.
Permanent (or base)Tables– These are the tables that hold persistent Data. These bases tables are further classified as Regular Table, Multidimensional clustering (MDC),Insert Time clustering (ITC), Range clustered table(RCT), Partitioned Table, Temporal Tables.
Auxiliary Tables: used for storing Large object data is not actually stored in the table in which it is defined. The defined LOB column holds information about the LOB, while the LOB itself is stored in another location. The normal place for this data storage is a LOB table space that defines the physical storage that will hold an auxiliary table related to the base column and table.
Because the actual LOB is stored in a separate table, if we have a large variable character column in use (that is infrequently accessed), it can be converted to a LOB. Then it is kept separately. This might speed up table space scans.
Temporary (declared or global) tables: As the name suggests, this type of table is created for temporary purpose from withing the application program. There are two types of temporary tables supported by IBM DB2. Created temporary tables, which you define using a CREATE GLOBAL TEMPORARY TABLE statement
Declared temporary tables, which you define using a DECLARE GLOBAL TEMPORARY TABLE statement
Materialized query tables: Materialized query tables are objects that are created to allow whole or partial queries to be pre-computed so that the
computed results can be used to answer future queries. MQTs provide a way to save the results of prior queries and reuse
the common results in subsequent queries. This feature helps avoid redundant scanning, aggregating, and joins
Clone tables: A clone table can be created by specifying the ADD CLONE keywords on an ALTER TABLE statement. The clone table receives a distinct name from the base table on which it is defined, but it is defined within the same schema and has the same structure as the base table, and it exists within the same table space as the base table. The clone table inherits the column names, data types, null attributes, check constraints, indexes, of the base table. Once created, a clone table can be manipulated independently of the base table, and you can use an SQL EXCHANGE statement to exchange data between the base table and the clone table.
XML tables: XML table spaces are created implicitly when a columns is created with an XML data type.
We use the DDL statements like CREATE TABLE, CREATE AUXILIARY, TABLE, or CREATE GLOBAL TEMPORARY TABLE statement to create these tables, and each is a logical representation of the way the data is physically stored on disk.
Leave a comment