DB2分区表相关语句

创建分区表

CREATE TABLE
    A_TEST_FENQU_1
    (
        ID VARCHAR(8) NOT NULL,
        DATA_DT VARCHAR(16),
        CUST_TYPE VARCHAR(8),
        CS_NO VARCHAR(12),
        PRIMARY KEY (ID)
    )
    PARTITION BY RANGE(DATA_DT)
    (  
     STARTING '20230601000000' INCLUSIVE ENDING '20230630235959' INCLUSIVE, 
     STARTING '20230701000000' INCLUSIVE ENDING '20230731235959' INCLUSIVE,
     STARTING '20230801000000' INCLUSIVE ENDING '20230831235959' INCLUSIVE
    ) 

查看分区情况

SELECT
    SUBSTR(TABNAME ,1,10)           t_name,
    SUBSTR(DATAPARTITIONNAME ,1,10) part_name,
    DATAPARTITIONID ,
    LOWVALUE  low_value,
    HIGHVALUE  high_value
FROM
    syscat.datapartitions
WHERE
    tabname='A_TEST_FENQU_1'
ORDER BY
    datapartitionid;

SELECT
    *
FROM
    SYSCAT.datapartitions
WHERE
    TABNAME='A_TEST_FENQU_1';

新增分区

ALTER TABLE A_TEST_FENQU_1 add PARTITION  
STARTING '20230901000000' inclusive 
ENDING '20230931235959' inclusive

删除分区

删除分区无法直接删除，需要将分区转到临时表，然后删除临时表（临时表表名自定义，需要是目前不存在的表，否则会报错）

ALTER TABLE
    A_TEST_FENQU_1 detach partition PART_202309
INTO
    A_TEST_FENQU111