Archives of the TeradataForum

Message Posted: Wed, 22 Mar 2006 @ 10:26:22 GMT

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

Actually, I think I was just trying to outsmart the whole process... consider this:

     call sysdba.collect_stats('dba_tool','dw_ops_stats_hist','testing');

     call sysdba.collect_stats('dba_tool','dw_ops_stats_hist','testing');
      *** Failure 5628 collect_stats:Column testing not found in
     dba_tool.dw_ops_stats_hist.
      *** Total elapsed time was 1 second.

      BTEQ -- Enter your DBC/SQL request or BTEQ command:

So it does return an error code appropriate, it seems.. this is from the following SP:

             SPLText
             REPLACE    PROCEDURE sysdba.collect_stats (
                   IN i_db_name varchar(32),
                   IN i_tbl_name varchar(32),
                   IN i_column_clause varchar(255)
                   )
             BEGIN
             DECLARE    db_tbl VARCHAR(65);
             DECLARE    column_clause VARCHAR(255);
             DECLARE    start_time TIMESTAMP(0);
             DECLARE    end_time TIMESTAMP(0);

                  COLLECTSTATS:
             BEGIN
                   -- Trim input clauses, set start time
                   SET db_tbl=TRIM(i_db_name)||'.'||TRIM(i_tbl_name);
                   SET column_clause=TRIM(i_column_clause);
                   SET start_time=current_timestamp(0);
                   -- Create record in history table
             INSERT      INTO dba_tool.dw_ops_stats_hist
     (databaseName,tableName,columnName,startTime,userName)
             VALUES
     (TRIM(:i_db_name),TRIM(:i_tbl_name),:column_clause,:start_time,user);

                   -- Collect statistics
             CALL DBC.SysExecSQL('COLLECT STATISTICS ON ' || :db_tbl || '
     COLUMN ' || :column_clause || ';');
                   SET end_time=current_timestamp(0);
             UPDATE     dba_tool.dw_ops_stats_hist SET endTime=:end_time
             WHERE
                   databaseName=TRIM(:i_db_name)
              AND     tableName=TRIM(:i_tbl_name)
              AND     columnName=:column_clause
              AND     startTime=:start_time
              AND     userName=user;
                 IF SQLSTATE<>0 THEN
             -- We must return some code back saying this request was invalid here
              LEAVE  COLLECTSTATS;
             -- ELSE IF SQLSTATE>0 THEN
             -- ELSE
             END IF;
             END COLLECTSTATS;
        END;

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