logo

Vinchin Blog

Vinchin Blog View DB2 distribution keys and partition keys

View DB2 distribution keys and partition keys

2021-08-19

View the distribution key and partition key of the table through the system catalog views:


--Distribution key

select tabschema,tabname,colno,colname,partkeyseq

from syscat.columns

and partkeyseq> 0

order by 1,2

with ur;


Regarding the distribution key, the description is as follows


1. Partkeyseq field description of syscat.columns:


--Partkeyseq is a field of 0, which represents a non-distributed key;

--partkeyseq non-zero fields are distribution keys.

--A certain table has n distribution keys (joint distribution keys), then the partkeyseq of its distribution keys is: 1, 2, 3, ... ,n. The partkeyseq of other non-distributed keys is 0


2. For the DPF database, if the distribution key is not specified when creating the table, the first field will be used as the distribution key by default.


--Partition key

select tabschema,tabname,datapartitionkeyseq,cast(cast(datapartitionexpression as clob(100)) as varchar(100)) partitionkey

from syscat.datapartitionexpression

order by 1,2

with ur;


p.s.


The differences and descriptions among database partitioning feature (DPF), multidimensional clustering (MDC), and table partitioning (TP) are as follows:


DB2 FEATURES

Clause in CREATE TABLEDB2 Feature Name
DISTRIBUTE BY HASHDPF —— database partitioning feature
ORGANIZE BY DIMENSIONMDC —— multidimensional clustering
PARTITION BY RANGETP —— table partitioning


  • Tag:
  • Trending

Interested Blogs More

DOWNLOAD NOW YOU CAN ENJOY A 60-DAYS FULL-FEATURED FREE TRIAL !