Archives of the TeradataForum

Message Posted: Wed, 27 Nov 2002 @ 13:10:10 GMT

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

Hi,

here is a sample sql-script, which creates a Sql to collect statistics .

.export report file = outfile.txt

/**************************  All Indexes *****************************/

sel
       cast ( ( Case
                      when col_num = 1 then  'Collect statistics on '|| trim (both from databasename)||'.'||trim(both from tablename)
                      else ' '
                   End) as Char(83) ) (Title ''),

       cast( (Case
                    when col_num = 1 then ' index ('
                    else   '       ,'
               End) as Char(8) )

|| trim(both from columnname) ||
    cast( (Case
                    when col_num = max_num then ');'
                    else '  '
               End)  as Char(2) ) (Title '')

from

(

sel p1.databasename, p1.tablename, p1.columnname, p1.indexnumber, p1.col_num, p2.max_num
from
(sel databasename, tablename, indexnumber, columnname,  csum(1,1) col_num
from dbc.indices  group by 1, 2, 3) p1
join
(sel databasename, tablename, indexnumber, count(*) max_num from
dbc.indices group by 1,2,3) p2
on p1.databasename = p2.databasename
and p1.tablename = p2.tablename
and p1.indexnumber = p2.indexnumber

) tab1

where databasename = ''
/********************** CHANGE DATABASENAME  **********************/

order by tab1.databasename, tab1.tablename, indexnumber, tab1.col_num;

/**************************  Columns   *****************************/

Select 'Collect statistics on '|| trim(both from tab1.databasename)
||'.'||trim(both from tab1.tablename)||' column '||trim(both from
tab1.columnname)||';' (Title '')

from
(

sel p1.databasename, p1.tablename, p1.columnname, p1.indexnumber,
p1.col_num, p2.max_num
from
(sel databasename, tablename, indexnumber, columnname,  csum(1,1)
col_num
from dbc.indices  group by 1, 2, 3) p1
join
(sel databasename, tablename, indexnumber, count(*) max_num from
dbc.indices group by 1,2,3) p2
on p1.databasename = p2.databasename
and p1.tablename = p2.tablename
and p1.indexnumber = p2.indexnumber

) tab1

where (databasename, tablename, columnname)
not in
(sel databasename, tablename, columnname
from
(sel p1.databasename, p1.tablename, p1.columnname, p1.indexnumber,
p1.col_num, p2.max_num
from
(sel databasename, tablename, indexnumber, columnname,  csum(1,1)
col_num
from dbc.indices  group by 1, 2, 3) p1
join
(sel databasename, tablename, indexnumber, count(*) max_num from
dbc.indices group by 1,2,3) p2
on p1.databasename = p2.databasename
and p1.tablename = p2.tablename
and p1.indexnumber = p2.indexnumber
where p1.col_num = 1
and    p2.max_num  = 1
) temp_inner
group by 1,2,3
)

and databasename = ''
/********************** CHANGE DATABASENAME  **********************/
group by 1 order by 1;

.export reset

.run file = outfile.txt

.quit

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