This example partitions an insurance company customer table into history and current partitions, where the history partition contains expired policies and the current partition contains current policies.
CREATE TABLE customer ( cust_name CHARACTER(8), policy_number INTEGER, policy_expiration_date DATE FORMAT 'YYYY/MM/DD') PRIMARY INDEX (cust_name, policy_number) PARTITION BY CASE_N(policy_expiration_date>=CURRENT_DATE, NO CASE);
Suppose that customer was created on April 17, 2010.
The output of a SHOW TABLE request displays the user-specified partitioning expression as a DATE in ANSI format.
SHOW TABLE customer; CREATE SET TABLE MOVEDATE.customer, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM=DEFAULT ( cust_name CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC, policy_number INTEGER, policy_expiration_date DATE FORMAT 'YYYY/MM/DD') PRIMARY INDEX ( cust_name ,policy_number ) PARTITION BY CASE_N( policy_expiration_date >= DATE, NO CASE);
The output of a SHOW DML request on customer also displays the user-specified partitioning expression as a DATE in ANSI format.
SHOW SELECT * FROM customer; CREATE SET TABLE MOVEDATE.customer, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( cust_name CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC, policy_number INTEGER, policy_expiration_date DATE FORMAT 'YYYY/MM/DD') PRIMARY INDEX ( cust_name ,policy_number ) PARTITION BY CASE_N(policy_expiration_date >= DATE, NO CASE);
The output of a SHOW QUALIFIED DML request on customer displays the partitioning expression with CURRENT_DATE replaced by the resolved date, which is 2010-04-17 in ANSI format.
SHOW QUALIFIED SELECT * FROM customer; CREATE SET TABLE movedate.customer ,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( cust_name CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC, policy_number INTEGER, policy_expiration_date DATE FORMAT 'YYYY/MM/DD') PRIMARY INDEX ( cust_name ,policy_number ) PARTITION BY CASE_N( policy_expiration_date >= DATE '2010-04-17', NO CASE);