Archives of the TeradataForum

Message Posted: Fri, 24 Mar 2006 @ 20:31:06 GMT

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

<-- Anonymously Posted: Friday, March 24, 2006 15:32 -->

Here is an example of script set up to run monthly at the first day of the month to extend one month.

     .os cat /dev/null > $OUT/extend_ppi.btq
     .export file=$OUT/extend_ppi.btq
     sel 'alter table '||trim(databasename)||'.'||trim(tablename)||
     ' modify primary index add range between '||''''||
     ((date - extract(day from date)+1) (format 'yyyy-mm-dd'))||''''||
     ' and '||''''||((add_months(date,1)-
     extract(day from add_months(date,1))) (format 'yyyy-mm-dd'))||
     ''''||' each interval ''1'' day ; ' (title '')
     from dbc.indices i
     where indextype='q'
     and exists
     (select 1
     from test1.idw_production_tables
     where databasename=i.databasename
     and tablename=i.tablename)
     and tablename not like '%mthly%'
     and trim(tablename) not like 'pas%'
     group by databasename, tablename
     union all
     sel 'alter table '||trim(databasename)||'.'||trim(tablename)||
     ' modify primary index add range between '||''''||
     ((date - extract(day from date)+1) (format 'yyyy-mm-dd'))||''''||
     ' and '||''''||((add_months(date,1)-
     extract(day from add_months(date,1))) (format 'yyyy-mm-dd'))||
     ''''||' each interval ''1'' month ; ' (title '')
     from dbc.indices i
     where indextype='q'
     and exists
     (select 1
     from test1.idw_production_tables
     where databasename=i.databasename
     and tablename=i.tablename)
     and not exists
     (select 1
     from dbc.columns
     where columnname='mnth_end_yyyymm'
     and databasename=i.databasename
     and tablename=i.tablename)
     group by databasename, tablename

     union all

     sel 'alter table '||trim(databasename)||'.'||trim(tablename)||

     ' modify primary index add range between '||''''||

     cast(cast(trim(extract(year from date))
     as date format 'yyyy') as format 'yyyymm')||''''||
     ' and '||''''||cast((cast(trim(extract(year from add_months(date,12)))
     as date format 'yyyy')-1) as format 'yyyymm')||
     ''''||' each 1 ; ' (title '')
     from dbc.indices i

     where indextype='q'

     and exists
     (select 1
     from dbc.columns
     where columnname='mnth_end_yyyymm'
     and databasename=i.databasename
     and tablename=i.tablename)
     group by databasename, tablename
     order by 1
     ;
     .if errorcode <> 0 then .exit errorcode
     .export reset
     .run file=$OUT/extend_ppi.btq

Some people do since it is safer than deleting data from table sometimes. Alter table allow you to drop or add specified range of partition with delete so you don't need to drop or add using new table. You can only perform such task for both end of defined PPI partition range currently.

We have script to perform monthly since that would satisfy our need. Your need should dictate your frequency and whether manually perform would be more feasible.

I would imaging that it's probably a good idea to collect stats on the PPI index again after maintaining the PPI.

It is recommended that you keep your stats fresh on whatever column you need for joining so it might be to your benefit to refresh stats whenever data change more than 10% (from prior to V2R4 experience).

The PPI is truely small table performance for large table if used correctly. UPI is only possible if PPI is part of the columns that make up for UPI. Most of the do and don't are well documented in SQL reference manual. NCR/TERADATA had white paper on this topic in TERADATA@yourservice so look it up. I am sure your support person can provide more details if you asked them or participate in user group sponsor by NCR/TERADAT should give you the latest and greatest.

Hope this is what you are looking for and best wish.

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