Archives of the TeradataForum

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

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

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>>