Is the PK of a DB2 table a clustered index by default?

Question

This can be a silly question but I want to be sure 100%.

Is the PK of a DB2 table a clustered index by default?

Answer 1

From: DB2 docs - Clustering indexes

Although a table can have several 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 on the table as the implicit clustering index when it orders data rows.

So no, by default the Primary Key is NOT the clustered index of the table.

The first created index, unique or not, is the "implicit" clustering index and DB2 tries to insert the records as nearly as possible in the order of the values of this index.

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

Answer 2

From the Publib (this assumes DB2 for z/OS, version 9)

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

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

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

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

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