By default, is a DB2 table PC using a clustered index?

Question

This may be a dumb question, but I want to be 100% sure.

Are PK tables DB2 clustered index by default?

Answer 1

From: DB2 Docs - Cluster Indexes

Although a table can have multiple indexes, only one index can be a clustering index. If you do not define a clustering index for a table, DB2 recognizes the first index that is created in the table as an implicit clustering index when ordering data rows.

No, by default the primary key is NOT a clustered table index.

The first index created, whether unique or not, is an “implicit” clustering index, and DB2 tries to insert records as close as possible to the order of values ​​of that index.

If you later create another index and define it as clustering, then DB2 identifies it as a clustering index, but does not reorder the data that is already in the table. This can be done using the REORG utility.

Answer 2

From Publib (assuming DB2 for z / OS, version 9)

When the table has a clustering index, the INSERT statement causes DB2 to insert records as close as possible in the order of their index value. The first index that you define in the table serves implicitly as a clustering index unless you explicitly specify CLUSTER when you create or modify another index. For example, if you first define a unique index in the EMPNO column of the EMP table, DB2 inserts the rows in the EMP table in the order of the employee identification number, unless you explicitly specify another index as a clustering index.

You can see which index is the clustering index for the table (in this example, TEST.TABLE1 ) using the following query if you are on z / OS:

 SELECT NAME FROM SYSIBM.SYSINDEXES WHERE TBCREATOR = 'TEST' AND TBNAME = 'TABLE1' AND CLUSTERING = 'Y' 

And this is for Linux / Unix / Windows (LUW):

 SELECT * FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'TEST' AND TABNAME = 'TABLE1' AND INDEXTYPE = 'CLUS' 

Answer 3

DB2 does not create a clustered index for the PC by default.

Primary keys

A primary key is a special type of unique key and cannot contain null values. For example, the DEPTNO column in the DEPT table is the primary key.

A table can have at most one primary key. Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.

The unique index of the primary key is called the primary index. When a primary key is specified in a CREATE TABLE or ALTER TABLE statement, DB2 automatically creates a primary index if one of the following conditions is true:

DB2 is in new feature mode and the tablespace is implicitly created. DB2 is in new feature mode, the tablespace is explicitly created, and the schema processor is running. DB2 is in conversion mode, and the schema processor is running. If a unique index already exists in the primary key columns when defined in the ALTER TABLE statement, this unique index is designated as the primary index when DB2 is in new function mode and implicitly creates a table space.

See at: DB2 Keys