Archives of the TeradataForum

Message Posted: Fri, 12 Sep 2003 @ 13:34:18 GMT

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

Hello!

This is an example procedure developed by Muhammad Amjad, who's been working for us (hey, he gets credit, not me). It's pretty simple and pretty nice and shows how to do what you want to do.

REPLACE PROCEDURE DBA.RFRSH_TBL_STATS(IN PARM_DB_NM VARCHAR(30), IN
PARM_TBL_NM VARCHAR(30))

--Declare Procedure level Label B - BATCH
B:
BEGIN
DECLARE   DB_NM                          VARCHAR(30) ;
DECLARE  TBL_NM                         VARCHAR(30);
DECLARE  CNT                                INTEGER  DEFAULT 0;
DECLARE  ROWS_EXIST_FLG       VARCHAR(1)  DEFAULT 'N';

--set the TBL_NM variable to PARM_TBL_NM to check which part procedure
to
execute.
SET TBL_NM=PARM_TBL_NM;

IF TBL_NM = 'ALL' THEN
--Declare Cursor to bring all tables in one database name - Label A- all tables
A:
FOR GETCOLS AS GETINDEX CURSOR FOR
SELECT
   B.DATABASENAME AS D1
  ,B.TABLENAME AS T1
FROM DBC.DATABASES AS A, DBC.TABLES  AS B
WHERE
           A.DATABASENAME= B.DATABASENAME
AND   B.TABLEKIND          =  'T'
AND   A.DATABASENAME= :PARM_DB_NM
AND   (B.DATABASENAME,B.TABLENAME) IN
(
SELECT Y.DATABASENAME, Y.TABLENAME
FROM  DBC.COLUMNSTATS AS Z, DBC.TABLES AS Y
WHERE Z.DATABASENAME = Y.DATABASENAME
AND       Z.TABLENAME         = Y.TABLENAME
AND       Y.TABLEKIND          =  'T'
AND      FieldStatistics IS NOT NULL
UNION
SELECT W.DATABASENAME,W.TABLENAME
FROM  DBC.INDEXSTATS AS X, DBC.TABLES AS W
WHERE X.DATABASENAME = W.DATABASENAME
AND       X.TABLENAME         = W.TABLENAME
AND       W.TABLEKIND          =  'T'
AND      indexStatistics IS NOT NULL
)

DO
SET ROWS_EXIST_FLG ='Y';
SET TBL_NM                            =  GETCOLS.T1;
SET DB_NM                             =   GETCOLS.D1;
CALL DBC.SYSEXECSQL('COLLECT STATISTICS  ON ' || trim(both From
:PARM_DB_NM) || '.' ||  TRIM(BOTH FROM :TBL_NM) || ';'  );
END FOR A;

IF ROWS_EXIST_FLG = 'N' THEN
    CALL DBC.SYSEXECSQL('COLLECT STATISTICS  ON  A.A;'  );
END IF;

ELSE
      CALL DBC.SYSEXECSQL('COLLECT STATISTICS  ON ' || trim(both From
:PARM_DB_NM) || '.' ||  TRIM(BOTH FROM :PARM_TBL_NM) || ';'  );
END IF;

END;

iv

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