Archives of the TeradataForum

Message Posted: Tue, 31 Jul 2012 @ 12:27:02 GMT

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

Hi,

This procedure may be help full to you,

     REPLACE PROCEDURE COLLECT_STATS_PROCEDURE (input_Db char(30), input_Tbl char(30))
     BEGIN
     DECLARE VAR1 VARCHAR(1000);
     DECLARE NEWCOUNT INTEGER;


     DECLARE CALCUR CURSOR FOR
     SELECT 'COLLECT STATISTICS ON      '||
     TRIM(DATABASENAME)||'.'||

     TRIM(TABLENAME)||' COLUMN  ('||
     case when Columnname in('Year','Day','Month','Time')
             then '"'||Columnname||'"'
             else Columnname
             end

     ||');'  FROM dbc.indices
     where  (TRIM(TABLENAME) like trim(:input_Tbl) or coalesce(input_Tbl,'*')='*')
     AND trim(DATABASENAME) LIKE trim(:input_Db ) ;

     OPEN CALCUR;

     SET NEWCOUNT = ACTIVITY_COUNT;

     REPEAT

     FETCH CALCUR INTO VAR1;
     CALL DBC.SYSEXECSQL(VAR1);

     SET NEWCOUNT = NEWCOUNT - 1;
     UNTIL NEWCOUNT = 0
     END REPEAT;

     CLOSE CALCUR;

     END;

Regards,

Vinay wani

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