Archives of the TeradataForum

Message Posted: Wed, 03 Dec 2003 @ 10:40:44 GMT

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

Hi Prabhjot,

Remember me, the below code written and tested by me, would help you perfectly (for both Show table and Collect stats job) as I too am a man who does not believe in Bull work rather smart work (no choice as number of tables/ number of databases if huge then becomes a laborious job)

Code to be customised by putting in the said database:

----------------------------------------------------------------
.set session transaction btet;
.logon nag2n1/xxx,zzz
.set defaults
.set echoreq on
.set errorout stdout
.width 250;
Database clips;
REPLACE MACRO ShowBarcCSDDL(intbl CHAR(30),coln CHAR(30))
AS
(
/*
ECHO '.TITLEDASHES OFF;';
select 'SHOW TABLE '|| trim(:intbl) || ';' (TITLE '')
FROM DBC.Tables
WHERE DATABASENAME = 'CLIPS'
AND TABLENAME = :intbl
AND TableKind='T'
;
*/

select 'COLLECT STATISTICS ' || trim(:intbl) || ' INDEX ( ' ||
trim(:coln) || ');' (TITLE '')
FROM DBC.Tables
WHERE DATABASENAME = 'CLIPS'
AND TABLENAME = :intbl
order by :intbl
;

);
/* Coded for Show DDL statement generation */
/*
.os rm ExpTblN.dat
.Export File = ExpTblN.dat
SELECT distinct cast(TABLENAME as char(30)) (title '')
FROM dbc.tables WHERE DataBaseName='CLIPS'
order by TABLENAME asc;
.EXPORT RESET
.os rm ShowDDLrpt.txt
.os rm BCout_sql.txt
.export file = BCout_sql.txt
.IMPORT VARTEXT FILE= ExpTblN.dat
.REPEAT *
USING (TblName Varchar(30))
exec ShowBarcDDL(:TblName,' ');
.export reset;
.export file = ShowDDLrpt.txt
.set heading 'This script lists the DDL statements for the objects in
the database specified.';
.run file = BCout_sql.txt
*/
/* Coded for Collect statistics on all the above tables generated */
.os rm tblfil.dat
.EXPORT DATA FILE=tblfil.dat
SELECT cast(TABLENAME as char(30)) (title ''),cast(ColumnName as
char(30)) (title '')
FROM dbc.Indices WHERE DataBaseName='CLIPS'
AND IndexType = 'P' order by TABLENAME;
.EXPORT RESET
.os rm ShowDDL_rptCS.rpt
.os rm BCoutsqlCS.txt
.export file = BCoutsqlCS.txt
.IMPORT DATA FILE=tblfil.dat;
.QUIET ON
.REPEAT *
USING tblNames(CHAR(30)),colname (CHAR(30))
exec ShowBarcCSDDL(:tblNames,:colname);
.export reset;
.set width 250
.export file = ShowDDL_rptCS.rpt
.set heading 'This script lists the Collect stats stmts for objects in
the database specified.';
.run file = BCoutsqlCS.txt
.export reset;
.os sed -e s/'CLIPS.'/''/g ShowDDLrpt.txt > ShowDDLrptCS_1.txt
/*
.os sed -e s/'NO AFTER JOURNAL,'/'NO AFTER JOURNAL'/g ShowDDLrptCS.txt >
ShowDDLrpt_CS.txt
.os sed -e s/'CHECKSUM = DEFAULT'/' '/g ShowDDLrpt_CS.txt >
ShowDDL_rpt_CS.txt
.os rm ShowDDLrpt.txt ShowDDLrptCS.txt ShowDDLrpt_CS.txt */
.os rm ShowDDLrpt.txt
.logoff;
----------------------------------------------------------------

Let me know, whether it works perfectly ?

What more from your side (Aus) ?

Regards,

Vishal Gangakhedkar
Satyam Computer Services Ltd.
Hyderabad, India
NCR-TGSC(DBS division)

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