Db/2 – LUW – Clustered Primary Key

Background

To create a Clustered Primary Key in some database systems it is straight forward.

Database Systems

Declaration

SQL Server

Syntax


ALTER TABLE [schema].[table]
ADD  CONSTRAINT [constraint]
PRIMARY KEY CLUSTERED
(
   [column-1]
)

Sample


ALTER TABLE [Dimension].[Customer]
ADD  CONSTRAINT [PK_Dimension_Customer]
PRIMARY KEY CLUSTERED
(
	[Customer Key] ASC
)

Db/2

Outline

  1. Create Index with specific columns
  2. Add Primary Key with matching columns

Create Index

Syntax

Syntax


CREATE UNIQUE INDEX "index-schema"."index-name"
ON "table-schema"."table-name"
(
   [column-name-1]
)
CLUSTER
COLLECT SAMPLED DETAILED STATISTICS
COMPRESS YES
INCLUDE NULL KEYS ALLOW REVERSE SCANS 

Sample


CREATE UNIQUE INDEX "Dimension"."PK_Dimension_CustomerKey"
ON "Dimension"."Customer"
(
	"CUSTOMERKEY" ASC
)
CLUSTER
COLLECT SAMPLED DETAILED STATISTICS
COMPRESS YES
INCLUDE NULL KEYS ALLOW REVERSE SCANS 

Add Constraint

Syntax


ALTER TABLE "table-schema"."table-name"
ADD CONSTRAINT "constraint"
(
   [column-name-1]
)

Sample


ALTER TABLE "Dimension"."Customer"
ADD CONSTRAINT "PK_Dimension_Customer" PRIMARY KEY
(
	"CUSTOMERKEY"
)

Metadata

Db/2

Syntax


SELECT 

		  tblSI.INDSCHEMA
		, tblSI.INDNAME
		, tblSI.COLNAMES
		, tblSI.UNIQUERULE
		, tblSI.INDEXTYPE

FROM   SYSCAT.INDEXES tblSI

WHERE  tblSI.TABNAME = 'Customer'

Output

option.cluster.20180929.1114AM

Explanation

  1. SYSCAT.INDEXES
    • Columns
      • UNIQUERULE
        • P is for Primary Key
      • INDEXTYPE
        • CLUS ( Cluster )
        • REG ( Regular )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s