Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 28 Feb 2010 @ 10:49:46 GMT


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


Subj:   Regarding Dynamically collecting the Stats
 
From:   Dharmawat, Rahul

Hi

I am creating the SP which will dynamically collect the stats on the tables on which stats have not been collected.

Inside the For loop I am using following statement to collect the Stats.

     SET PCG_MSG4='COLLECT STATISTICS ON ' ||PCG_MSG1||'.'||PCG_MSG2|| '  INDEX (' ||
     PCG_MSG3|| ') ' ; --SET P_MSG=PCG_MSG4; CALL DBC.SYSEXECSQL (:PCG_MSG4 ) ; --CALL
     DBC.SysExecSQL(  'COLLECT STATISTICS ON ' ||PCG_MSG1||'.'||PCG_MSG2|| '  INDEX (' || PCG_MSG3|| ')' );
     SET        PCG_MSG4 = '  COMMIT   ';
     CALL DBC.SYSEXECSQL ('  COMMIT   ') ;
     SET P_MSG=PCG_MSG4;

It is successful complied but when I try to call it gives me an error of Invalid Statement Specified inside a For cursor Statement

When I remove the Syntax of stats collection process and just capture the message it is successfully showing the statement.


Acronyms:

PCG_MSG1=DatabaseName(getting from Select Column) PCG_MSG2=Tablename(getting from Select Column) PCG_MSG3=ColumnName(getting from Select Column)


Note: SP is written in ANSI mode.


Regards

Rahul Dharmawat
Morgan Stanley | Technology & Data



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016