Shulou(Shulou.com)06/01 Report--

Next, in order to improve database performance, we put different partitions under different table spaces. First create 6 tablespaces, 3 data tablespaces, and 3 index tablespaces:

Db2 "create tablespace ts_dat managed by database using (file'/ home/db2inst1/data/ts_dat' 100m)"

Db2 "create tablespace ts_dat1 managed by database using (file'/ home/db2inst1/data/ts_dat1' 100m)"

Db2 "create tablespace ts_dat2 managed by database using (file'/ home/db2inst1/data/ts_dat2' 100m)"

Db2 "create tablespace ts_idx managed by database using (file'/ home/db2inst1/data/ts_idx' 100m)"

Db2 "create tablespace ts_idx1 managed by database using (file'/ home/db2inst1/data/ts_idx1' 100m)"

Db2 "create tablespace ts_idx2 managed by database using (file'/ home/db2inst1/data/ts_idx2' 100m)"

Do not need to execute after the first execution

-- DDL for Bufferpools--

CREATE BUFFERPOOL "BP32K_DATA" SIZE 32768 PAGESIZE 32768

CREATE BUFFERPOOL "BP32K_INDEX" SIZE 32768 PAGESIZE 32768

-- Minic Storage Groups--

ALTER STOGROUP "IBMSTOGROUP" OVERHEAD 6.725000 DEVICE READ RATE 100.000000 DATA TAG NONE SET AS DEFAULT

Create a user temporary tablespace

CREATE USER TEMPORARY TABLESPACE "USER_TMP" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY SYSTEM

-- modify the table empty name TABLESPACE "USER_TMP"

USING ('/ home/db2inst1/data/usrtmp')-modify path'/ home/db2inst1/data/usrtmp'

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

Create a system temporary tablespace

CREATE TEMPORARY TABLESPACE "TMP_SYSTEM" PAGESIZE 32768 MANAGED BY SYSTEM

USING ('/ home/db2inst1/data/SYStmp')

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

Create a normal tablespace

CREATE LARGE TABLESPACE "IDM_DATATABLE" PAGESIZE 32768 MANAGED BY DATABASE

-modify tablespace TABLESPACE "IDM_DATATABLE"

USING (file'/ home/db2inst1/data/IDM_datatable' 100m)-modify path and size

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

CREATE TABLESPACE "IDMINDX" PAGESIZE 32768 MANAGED BY DATABASE

USING (file'/ home/db2inst1/data/IDMINDX' 100m)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

CREATE LARGE TABLESPACE "IDMDTL1" PAGESIZE 32768 MANAGED BY DATABASE

USING (file'/ home/db2inst1/data/IDMDTL1' 100m)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

CREATE LARGE TABLESPACE "IDMDTL2" PAGESIZE 32768 MANAGED BY DATABASE

USING (file'/ home/db2inst1/data/IDMDTL2' 100m)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

CREATE LARGE TABLESPACE "IDMDTL3" PAGESIZE 32768 MANAGED BY DATABASE

USING (file'/ home/db2inst1/data/IDMDTL3' 100m)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF

Partition table type

1. Automatic zoning

2. Manual zoning

PARTITION BY RANGE (partition column)

(PART partition name STARTING start ENDING end IN tablespace INDEX IN index locked in tablespace)

Omit starting, the next one ends the last one

STARTING (2017 Jue 1) ENDING (2017 Jing 6)

ENDING (2017, 9)

ENDING (2017, 12)

ENDING (2018, 12))

Omit ending, the end is the beginning of the previous one

PARTITION BY RANGE (COL35)

(PART PART0 STARTING ('2017-01-01') IN IDMDTL1

PART PART1 STARTING ('2017-04-01') IN IDMDTL2

PART PART2 STARTING ('2017-07-01') IN IDMDTL3

PART PART3 STARTING ('2017-10-01') ENDING ('2017-12-31') IN IDMDTL1

EEXCLUSIVE partition 2018-02-01 this is excluded

PART IDM_TRANSDTL_PART201801 STARTING ('2018-01-01') ENDING ('2018-02-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX

The following data types are not supported by the table partitioning key:

User-defined types (structured)

LONG VARCHAR

LONG VARCHAR FOR BIT DATA

BLOB

BINARY LARGE OBJECT

CLOB

CHARACTER LARGE OBJECT

DBCLOB

LONG VARGRAPHIC

REF

C variable length string

Pascal variable length string

XML

Db2 "describe DATA PARTITIONS for table parttabtest02 show detail"

Example:

Example 1. The table space is evenly distributed on the table space.

CREATE TABLE parttabtest01

(ID INTEGER NOT NULL

SALES_PERSON VARCHAR (50)

REGION VARCHAR (50)

SALES_DATE DATE)

IN ts_dat1,ts_dat2-specifies that the table is on the partition

INDEX IN ts_idx-on the specified index partition

PARTITION BY RANGE (SALES_DATE)

(STARTING MINVALUE, STARTING'1 MONTH,ENDING MAXVALUE, 2012 'ENDING' 12, 2012 'EVERY 1 MONTH,ENDING MAXVALUE)

Example 2.

Similarly, we can add the tablespace name after the partition to explicitly specify the tablespace for the partition.

For partitions that do not specify a table space in the create partition statement, use the table space specified in CREATE TABLE.

CREATE TABLE parttabtest02

(ID INTEGER NOT NULL

SALES_PERSON VARCHAR (50)

REGION VARCHAR (50)

SALES_DATE DATE)

IN TS_DAT

INDEX IN TS_IDX

PARTITION BY RANGE (SALES_DATE)

(PART PJAN STARTING'1 ENDING '3max 31max 2017' IN TS_DAT1 INDEX IN TS_IDX1,- specifies the partition on the specified tablespace

PART PFEB STARTING'4 ENDING 1max 2017 'ENDING' 7 Universe 31 IN TS_DAT1 INDEX IN TS_IDX1

PART PMAR STARTING'8 ENDING 1max 2017 'ENDING' 12 Universe 31 IN TS_DAT2 INDEX IN TS_IDX2

PART PAPR STARTING'1 ENDING'11 ENDING '2018)

Example 3,

If you choose to use the EVERY clause of the CREATE TABLE statement to automatically generate data partitions, only one column can be used as the table partitioning key.

If you choose to manually generate data partitions by specifying each range in the PARTITION BY clause of the CREATE TABLE statement

Then you can use multiple columns as table partitioning keys, as shown in the following example:

CREATE TABLE parttabtest03

(ID INTEGER NOT NULL

SALES_PERSON VARCHAR (50)

REGION VARCHAR (50)

SALES_YEAR INT

SALES_MONTH INT)

PARTITION BY RANGE (SALES_YEAR, SALES_MONTH)

(STARTING (2017 jue 1) ENDING (2017 jade 6)

ENDING (2017, 9)

ENDING (2017, 12)

ENDING (2018, 12))

Example 4.

CREATE TABLE parttabtest04

(ID INTEGER NOT NULL

SALES_PERSON VARCHAR (50)

REGION VARCHAR (50)

SALES_DATE DATE

SALES_MONTH GENERATED ALWAYS AS (month (SALES_DATE)

PARTITION BY RANGE (SALES_MONTH)

(STARTING FROM 1 ENDING AT 12 EVERY 1)

Table name

CREATE TABLE IDM_TRANSDTL (

COL1 VARCHAR (22) NOT NULL

COL2 VARCHAR (8) NOT NULL

COL3 VARCHAR (8) NOT NULL

COL4 VARCHAR (6) NOT NULL

COL5 VARCHAR (6)

COL6 VARCHAR (6)

COL7 VARCHAR (40)

COL8 VARCHAR (4)

COL9 VARCHAR (3)

COL10 VARCHAR (8)

COL11 VARCHAR (22)

COL12 VARCHAR (4)

COL13 VARCHAR (14)

COL14 VARCHAR (5)

COL15 VARCHAR (30)

COL16 VARCHAR (100)

COL17 VARCHAR (20)

COL18 VARCHAR (30)

COL19 VARCHAR (30)

COL20 VARCHAR (20)

COL21 VARCHAR (1)

COL22 VARCHAR (1)

COL23 VARCHAR (20)

COL24 VARCHAR (2)

COL25 VARCHAR (14)

COL26 VARCHAR (2)

COL27 VARCHAR (10)

COL28 VARCHAR (256)

COL29 VARCHAR (20)

COL30 VARCHAR (100)

COL31 VARCHAR (100)

COL32 VARCHAR (100)

COL33 VARCHAR (100)

COL34 VARCHAR (100)

COL35 DATE NOT NULL WITH DEFAULT CURRENT DATE)

INDEX IN IDMINDX PARTITION BY RANGE (COL35)

(PART PART0 STARTING ('2017-01-01') IN IDMDTL1

PART PART1 STARTING ('2017-04-01') IN IDMDTL2

PART PART2 STARTING ('2017-07-01') IN IDMDTL3

PART PART3 STARTING ('2017-10-01') ENDING ('2017-12-31') IN IDMDTL1

PART IDM_TRANSDTL_PART201801 STARTING ('2018-01-01') ENDING ('2018-02-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201802 STARTING ('2018-02-01') ENDING ('2018-03-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201803 STARTING ('2018-03-01') ENDING ('2018-04-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201804 STARTING ('2018-04-01') ENDING ('2018-05-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201805 STARTING ('2018-05-01') ENDING ('2018-06-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201806 STARTING ('2018-06-01') ENDING ('2018-07-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201807 STARTING ('2018-07-01') ENDING ('2018-08-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201808 STARTING ('2018-08-01') ENDING ('2018-09-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201809 STARTING ('2018-09-01') ENDING ('2018-10-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201810 STARTING ('2018-10-01') ENDING ('2018-11-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201811 STARTING ('2018-11-01') ENDING ('2018-12-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX

PART IDM_TRANSDTL_PART201812 STARTING ('2018-12-01') ENDING ('2019-01-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.