Archives of the TeradataForum

Message Posted: Fri, 30 Jan 2015 @ 15:30:46 GMT

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

<-- Anonymously Posted: Friday, January 30, 2015 10:20 -->

Hi Martin,

I wrote an SP to recollect stats and take advantage of the new "optimized" syntax when we upgraded to Teradata 14.0. Since we were upgrading we did not have to be concerned about the MAXVALUELENGTH that Mr. Briggs referred to.

Below is the logic I think you are looking for. It is not the entire SP. Alterations may be necessary. Cheers!

Notes:

We recollected stats on DBC separately, so it was excluded.

The sys_upgrade.statsinfo table drives the process. This table was populated from the query that Dieter created. You could use DBC.StatsV if you are already up on TD14. In this case, I would exclude any stats that utilize the MAXVALUELENGTH.

The SP utilizes a "StartDatabaseName" and "EndDatabaseName"- This is so we could execute the recollect process for high priority databases and run multiple recollect processes simultaneously.

The oreplace2 function is used. If you are not on TD14 then that will need to be installed, otherwise it should be in td_sysfnlib. (downloads.teradata.com/...)

     CREATE SET TABLE sys_upgrade.statsinfo ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           DatabaseName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
           TableName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
           ColumnName VARCHAR(3000) CHARACTER SET UNICODE NOT CASESPECIFIC,
           SampleSize INTEGER,
           ProcessedFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( DatabaseName ,TableName );

     set v_columnid = 9999;
     set v_SQLStmt = '';
                     FOR cur1 as local_cursor1
                     CURSOR FOR
                       SELECT rank() over (partition by databasename, tablename, samplesize order by columnname) as columnid
                       , databasename
                       , tablename
                       , columnname
                       , samplesize
                       from sys_upgrade.StatsInfo
                       where databasename between :v_dbnameStart and :v_dbnameEnd
                       and databasename <> 'DBC'
                       and coalesce(ProcessedFlag, 'N') = 'N'
                       order by samplesize desc, databasename, tablename, columnid
                     DO
           if cur1.columnid <= v_columnid then
                          if v_columnid < 9999 then
                                       set v_SQLStmt = v_SQLStmt||' ON "'||trim(v_databasename)||'"."'||trim(v_tablename)||'" ;';
                                       call DBC.SysExecSql (:v_SQLStmt);
                                       update sys_upgrade.statsinfo
                                       set ProcessedFlag = 'Y'
                                       where databasename = v_databasename
                                       and   tablename = v_tablename;
                     end if;
                                       set v_SQLStmt = 'Collect Statistics';
                                       if cur1.Samplesize > 0 then
                                          set v_SQLStmt = v_SQLStmt||' Using Sample '||trim(cur1.Samplesize)||' Percent';
                                       end if;
                                       set v_SQLStmt = v_SQLStmt||' column ("'||trim(oreplace2(cur1.columnname,',','","'))||'")';
                                       set v_databasename = cur1.databasename;
                                       set v_tablename = cur1.tablename;
                                       set v_columnid = cur1.columnid;
            else
                           set v_SQLStmt = v_SQLStmt||' , column ("'||trim(oreplace2(cur1.columnname,',','","'))||'")';
                                       set v_columnid = cur1.columnid ;
                        end if;
         END FOR;
        IF v_SQLStmt <>'' THEN
                                         set v_SQLStmt = v_SQLStmt||' ON "'||trim(v_databasename)||'"."'||trim(v_tablename)||'" ;';
                                         call DBC.SysExecSql (:v_SQLStmt);
                                                     update sys_upgrade.statsinfo
                                                     set ProcessedFlag = 'Y'
                                                     where databasename = v_databasename
                                                     and   tablename = v_tablename;
       END IF;

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