expired table partition.

[Vince]

hello everyone and happy new year..
one of our tables has an expired partition.. what's the easiest method I can update the partition..

without creating a new table with new partition duration and copying data from older table...

PARTITION BY RANGE_N(PRGM_DT BETWEEN DATE '2014-12-01' AND '2017-12-31' EACH INTERVAL '1' DAY );

CREATE MULTISET TABLE GRP_CDM_ANALYTIC_TABLE.FIBE_VIEWING_DLY ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ACCT_NO CHAR(16) CHARACTER SET LATIN CASESPECIFIC,
CHANNEL_MAP VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC,
PRGM_DT DATE FORMAT 'YYYY-MM-DD',
FIRST_VIEWING_TM TIME(0),
PROGRAM_TITLE VARCHAR(75) CHARACTER SET LATIN CASESPECIFIC,
EPISODE_TITLE VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC,
CHANNEL_NUM INTEGER,
CHANNEL_TYPE VARCHAR(5) CHARACTER SET LATIN CASESPECIFIC,
CALL_SIGN VARCHAR(6) CHARACTER SET LATIN CASESPECIFIC,
NETWORK VARCHAR(60) CHARACTER SET LATIN CASESPECIFIC,
REPORT_NAME VARCHAR(55) CHARACTER SET LATIN CASESPECIFIC,
TOTAL_DURATION INTEGER,
VIEWING_TYPE_CD CHAR(1) CHARACTER SET LATIN CASESPECIFIC,
BELL_MEDIA_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,
BUS_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,
PROGRAM_NATIVE_NO INTEGER,
STATION_NATIVE_NO INTEGER,
RECORD_DT DATE FORMAT 'YYYY-MM-DD',
RECORD_TM TIME(0))
PRIMARY INDEX ( ACCT_NO )
PARTITION BY RANGE_N(PRGM_DT BETWEEN DATE '2014-12-01' AND '2017-12-31' EACH INTERVAL '1' DAY );

[dnoeth]

Use ALTER TABLE and extend the partitioning so far into the future, that you'll never have to do it again:

ALTER TABLE GRP_CDM_ANALYTIC_TABLE.FIBE_VIEWING_DLY
MODIFY PRIMARY INDEX
ADD RANGE BETWEEN DATE '2018-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' DAY

[Vince · Accepted solution]

thank you D.. I appreciate your quick response..  I have 3 tables I will apply the same rule on all 3...
have a great day..

Vince