DB2 create table and insert in tablespace

Question

my DBA created three different tablespaces in DB2 in order to store the same table (TABLE_IN_TBS), switching on a date field.

CREATE LARGE TABLESPACE "TBS_x" IN DATABASE PARTITION GROUP NODO0 PAGESIZE 32768 MANAGED BY DATABASE
     USING (FILE 'x.LRG' 1G) ON DBPARTITIONNUMS (0)
     EXTENTSIZE 32
     PREFETCHSIZE AUTOMATIC
     BUFFERPOOL BP32K0
     OVERHEAD 12.670000
     TRANSFERRATE 0.180000
     AUTORESIZE YES
     MAXSIZE 30 G
     NO FILE SYSTEM CACHING
     DROPPED TABLE RECOVERY ON;

Then I have TBS_x in which data in predefined range will be add, TBS_x+1 for the next range and so on..

My question is: I have to create the same table TABLE_IN_TBS in the different tablespaces? With the following syntax?

    CREATE TABLE TABLE_IN_TBS
  (
  SomeColumns....
  ) TABLESPACE TBS_x;

And in which way I can refer to different tablespaces in order to insert data in the right tablespace based on my date field to switch?

score 2 · Accepted Answer · answered Oct 01 '13 at 00:18

I think you're talking about a partitioned table. It is defined like this:

CREATE TABLE TABLE_IN_TBS
(
  SomeColumns....
) 
PARTITION BY (your_date_column)
(STARTING FROM '2013-09-30' ENDING AT '2013-09-30' IN TABLESPACE TBS_1),
(STARTING FROM '2013-10-01' ENDING AT '2013-10-01' IN TABLESPACE TBS_2),
...;

DB2 create table and insert in tablespace

1 Answers1