Archives of the TeradataForum

Message Posted: Thu, 08 Sep 2011 @ 13:23:16 GMT

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

Okay....I thought about it and had a few minutes so I wrote the following....it would be cleaner if the UPDATE statement was wrapped in a macro, but since this a one-off, I'm not going through change control to create macro for the sake of 'elegance' ;-)

The following will generate collect stats statements for all NUSI indices.....modify as you see fit.

Determine the largest secondary index on your system (you may be in for a surprise like I was, when you find some purchased product has 10 columns in a secondary index!)

     SEL MAX(columnposition)
     FROM dbc.indices WHERE indextype = 's'
     AND uniqueflag = 'n';

     CREATE VOLATILE TABLE  nusi_col ,NO FALLBACK ,
             NO BEFORE JOURNAL,
             NO AFTER JOURNAL,
             CHECKSUM = DEFAULT
             (
              databasename CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
              tablename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
              indexnumber SMALLINT,
              NameText VARCHAR(512) CHARACTER SET LATIN NOT CASESPECIFIC
     )
        UNIQUE PRIMARY INDEX ( databasename, tablename, indexnumber ) on
     commit preserve rows;

     INSERT INTO nusi_col
     SEL databasename, tablename, indexnumber, columnname
     FROM dbc.indices
     WHERE indextype = 's'
     AND uniqueflag = 'n'
     AND columnposition = 1;


     --run the following repeating SQL until you reach your max
     columnposition.
      UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 2;


       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 3;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 4;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 5;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 6;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 7;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 8;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 9;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 10;


     SEL 'COLLECT STATS ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) ||
     ' INDEX ( ' || TRIM(NAMETEXT) || ' ) ;'
       FROM NUSI_COL
       ORDER BY 1;

Ruth Fenwick | Teradata Technical Lead, Enterprise Data Management,
Enterprise Information Management | Royal Bank of Canada |

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