DB2

1. Materialized Query Table
2. Indexes in DB2

Materialized Query Table (MQT)

Materialized Query Table (MQT) is formed from a query’s output data. This data is summarized and pre-calculated one. MQTs are very helpful in replacing large queries having multiple joins and extensive aggregations fired frequently.

MQTS differ from Views by:

1. MQT is a physical entity
2. Data is stored once MQT is refreshed and is never deleted until user issues SQL statements to do so

Why MQT?

1. Since data in MQT is stored as table, it eliminates the overhead of firing the underlying query every time it is referenced.
2. Being a physical object, DB2 SQL optimizer considers MQTs during Query Optimization and uses it to fetch results when needed.
3. The Access plan is automatically re-written as needed.
4. Indexes can be created on MQTs (except Unique Index)

Defining an MQT:

MQT can be created/defined by Create Table or Alter Table SQL statements.

Syntax with Create Table Statement:

CREATE TABLE TABLE_NAME AS

(Select query)

DATA INITIALLY DEFERRED

REFRESH DEFERRED

[ENABLE/DISABLE QUERY OPTIMIZATION]

[MAINTAINED BY SYSTEM/USER]

Materialized Query Options:

1. Data Initially deferred

As the name suggests, this option prevents loading data while creating the MQT, rather data should be loaded with REFRESH TABLE command later, in case of system maintained MQT. If MQT is maintained by User, then insert statement can be used for populating MQTs.

1. Refresh deferred

In user maintained MQT the data present is snapshot of the data fetched by the query during refresh or last updated. It also lets user to run REFRESH TABLE command at any time.

1. Maintained by User/System
   • Maintained by System
     • This is the Default option for MQT. It allows user to execute only REFESH command.
   • Maintained by User
     • This option allows user to run Load commands, Insert, update and delete statements on the table along with select and Refresh command.

1. Enable/Disable query Optimization:
   1. Enable Query Optimization:
      • This is the default option. This allows DB2 to use MQT for query optimization.
   2. Disable Query Optimization:
      • This specifies to DB2 that this MQT cannot be used for Query Optimization.

When not to use Materialized Query Table:

1. When data is needed at real time.
2. When underlying tables can have high Insert/Update/Delete operations.
3. When cost of querying from underlying tables is less compared to maintaining a MQT data on Database.
4. When data from MQT is not queried frequently

Indexes

Indexing is important aspect to be considered while designing a Database. Indexe provides faster access to data.Even though indexes provide efficient access to data, while executing update, insert and delete it requires additional processing. DB2 have many index types, some are general and others are particular to some type of table spaces.

1. UNIQUE INDEX:

When primary key is declared for the table on column(s) then those column(s) must have unique index declared on them, unless unique index is declared explicitly, DB2 makes the table unavailable until it’s declared.

Sql syntax:

Create unique index index-name on

Table-name (column-name);

Eg:

Lets us consider the table employee with columns as empno, empname, empsal and emploc.

Employee table.

	Empno	Empname	Empsal	emploc
1	100001	Ran	23000.00	CHN
2	100104	Ram	32000.00	BAN
3	100050	Anve	42000.00	HYD
4	100030	Bala	20000.00	KOL
5	100232	Jack	35750.00	CHN
6	100055	Kim	55000.00	COM
7	100111	Ram	29005.00	CHN

Here the employee no is unique and is distinct for every employee, assume it as primary key. If it’s declared as primary key then a unique index has to be declared on it. It can be declared as given below:-

CREATE UNIQUE INDEX EMPINDEX

ON EMPLOYEE (EMPNO)

Now the db2 will create an index for the table employee on the column empno; it will internally sort it in ascending (implicitly, if not defined explicitly)

Index on this table would be:-

Empno
100001
100030
100044
100050
100055
100104
100111
100154
100232

The column defined as Primary-Key can’t be holding null values so those columns has to be  declare with the not null constraint to avoid null values. Unique indexes are an important part in implementing referential constraint as the table which is used as reference must have those columns as its primary key so they must be unique indexed.

2.  NON-UNIQUE INDEX:

This type of index can be created on any of the columns; this index allows columns to have duplicate values. This is used to improve the data access performance.

This index can be created by:-

CREATE INDEX NONINDEX

ON EMPLOYEE (EMPNAME)

This type of index has a disadvantage when declared on small tables; as the scan is more efficient than retrieving data using Index.

3. CLUSTERING INDEX:

This is one of the index types which db2 offers. This index determines how the rows are physically arranged in the table spaces. Clustering index only arranges the row in the physical memory.

The syntax for creating a clustering index is:

CREATE INDEX INDEX-NAME

ON TABLE-NAME

(COLUMN-NAME ASC/DESC)

CLUSTER;

Clustering index can be defined on segmented and partitioned table space. Clustering index can either be partitioning or secondary index on a partitioned table space. In most of the case the clustering index will the partitioning index, but if the clustering index is not a partitioning index then every partition in turn are arranged in the order of the clustering index.

This index also has performance advantage in operations that involves large data volume during ordering, grouping and comparing range of data. As the rows are present in an order in physical memory it will be easy for DB2 to pick the rows in the range; for example:

   Consider that an Clustering index is created in the column "Empsal",   if the below query is executed

                                               

SELECT EMPSAL

FROM EMPLOYEE

WHERE EMPSAL > 40000.00

The rows are sorted on the Empsal, DB2 has to find one row with Empsal value greater than 40000.00 and rest all the rows below it will satisfy the query.

                                               

3	100050	Anve	42000.00	HYD
4	100055	Kim	55000.00	COM

When insertion is done on a table with clustering index db2 inserts the new row in the order of the clustering index. By default the DB2 takes the first index declared on a table as clustering index until a clustering index is explicitly defined. There can be only one clustering index on the table.