Archives of the TeradataForum

Message Posted: Tue, 16 Nov 2010 @ 23:27:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

My boss wants me to create a job to run monthly in order to automatically DROP the oldest RANGE from a group of tables. (BTW, Stored Procedures don't support DROP RANGE.)

The SQL statement for a single table is...

     ALTER TABLE EDW1_STS2_DATA.TOSA_ACTY_mj MODIFY PRIMARY INDEX
     DROP  RANGE BETWEEN DATE '2008-12-01'  AND DATE '2008-12-31'
       EACH INTERVAL '1' MONTH
       WITH DELETE;

PARTITIONING SCHEME on the table is...

     PRIMARY INDEX ( KEY_ACCT )
     PARTITION BY RANGE_N(DT2_MONTH_END  BETWEEN DATE '2008-12-01' AND DATE
     '2100-12-31' EACH INTERVAL '1' MONTH );

I can get the PARTITIONING Column from dbc.Indexconstraints, but the actual columnname it is not used in the ALTER TABLE stmt.

Looking for something like below ... (but how to get the oldest date ranges?) (sel min(parition) from tablename?) (sel min(DT2_MONTH_END) from tablename?)

     sel 'ALTER TABLE  ' || trim(databasename) ||'.'|| trim(tablename) || '
     MODIFY PRIMARY INDEX DROP RANGE BETWEEN DATE  ' || '     '  || 'AND DATE '
     || '     '  || 'EACH INTERVAL 1 MONTH ' || ';'
     from dbc.indexconstraints
     where databasename = 'EDW1_STS2_DATA'
     and constrainttext like '%DT2_MONTH_END%'
     order by constrainttext;

     ALTER TABLE  EDW1_STS2_DATA.TOSA_ACTY_mj2   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_ACCT_FOOTNOTE_TEMP   MODIFY PRIMARY INDEX
     DROP RANGE BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_ACCT_FOOTNOTE   MODIFY PRIMARY INDEX DROP
     RANGE BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_ELIGIBLE_ACCT   MODIFY PRIMARY INDEX DROP
     RANGE BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TOSA_TAX_LOT_30   MODIFY PRIMARY INDEX DROP
     RANGE BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_EQRT   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_AVIF   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_ACIP   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_CNAA   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_ACAA   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_AAAI   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;
     ALTER TABLE  EDW1_STS2_DATA.TIDM_ACAAB   MODIFY PRIMARY INDEX DROP RANGE
     BETWEEN DATE       AND DATE      EACH INTERVAL 1 MONTH ;

Regards,

Mark Jahnke
MSSB Teradata DBE COE Team

	<Prev	Next>		<<First	<Prev	Next>	Last>>