DB2 Partitioning

Question

I know how partitioning in DB2 works but I am unaware about where this partition values exactly get stored. After writing a create partition query, for example:

CREATE TABLE orders(id INT, shipdate DATE, …)
  PARTITION BY RANGE(shipdate)
    (
    STARTING '1/1/2006' ENDING '12/31/2006' 
     EVERY 3 MONTHS
    )

after running the above query we know that partitions are created on order for every 3 month but when we run a select query the query engine refers this partitions. I am curious to know where this actually get stored, whether in the same table or DB2 has a different table where partition value for every table get stored.

Thanks,

The answer(s) to this are platform dependent, and it is a common concern. To perform a better service as a reference site, why don't we answer this for each platform, rather than simply ask which one the OP relates to. — WarrenT
"curious to know where this actually get stored" -- and what this is exactly? Are you asking about the partition range definitions or the partitioning key column values? — mustaccio
@mustaccio : I am talking about partition values i.e I guess the partitioning key column values according to you. When we create partitions there might be some place where this keys values get stored so that query engines can easily read this values.(For example: data with date '1/1/2006' starts from row number 1001 and like that). I hope I am able to make you understand my question. Looking forward for your help. Thanks — Explorer
Column values, whether they are partitioning keys or not, are stored, as always, in table rows of data objects. — mustaccio
You mean to say in the same table for which the partitions are created. (I just joined a new team which uses DB2 partitioning concepts and there are few tables for which I have to see what are the partitioning keys values and I don't know from where to fetch this data.) Could you please let me know a way in which I can get the partition key value pairs for tables which implements partitioning concept. Thanks again :) — Explorer

MichaelTiefenbacher MichaelTiefenbacher · Accepted Answer · 2016-02-21T19:21:52

table partitions in DB2 are stored in tablespaces. For regular tables (if table partitioning is not used) table data is stored in a single tablespace (not considering LOBs). For partitioned tables multiple tablespaces can used for its partitions. This is achieved by the "" clause of the CREATE TABLE statement.

CREATE TABLE parttab
...
in TBSP1, TBSP2, TBSP3

In this example the first partition will be stored in TBSP1, the second in TBSP2, The third in TBSP3, the fourth in TBSP1 and so on.

Table partitions are named in DB2 - by default PART1 ..PARTn - and all these details can be looked up in the system catalog view SYSCAT.DATAPARTITIONS including the specified partition ranges.

See also http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0021353.html?cp=SSEPGG_10.5.0%2F2-12-8-27&lang=en

The column used as partitioning key can be looked up in syscat.datapartitionexpression.

There is also a long syntax for creating partitioned tables where partition names can be explizitly specified as well as the tablespace where the partitions will get stored.

For applications partitioned tables look like a single normal table. Partitions can be detached from a partitioned table. In this case a partition is "disconnected" from the partitioned table and converted to a table without moving the data (or vice versa).

best regards Michael

DB2 Partitioning

3 Answers