Archives of the TeradataForum

Message Posted: Thu, 16 Mar 2006 @ 20:32:29 GMT

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

<-- Anonymously Posted: Thursday, March 16, 2006 15:22 -->

You can either put your SQL in stored procedure and call stored procedure from BTEQ. Or your can capture the output then grep for the row count from the output and insert to the process table at the completion of the job.

Here is an example of what you want in store procedure.

In your store procedure you place the attached after each statement you wish to log row count as example below:

     BEGIN
       insert into SYSDBA.TMP_STMT_TBL
     select
     a.stmt_seq_nbr+rank(tablename asc)
     ,'CREATE TABLE '||TRIM(:DB_NM)||'.'||TRIM(TABLENAME)
     ,'AS '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)
     ,'WITH NO DATA'
     ,';'
     ,''
     ,''
     ,''
     FROM TEST1.IDW_BAR_REQTS
     cross join
     (select zeroifnull(max(stmt_seq_nbr)) stmt_seq_nbr from
     SYSDBA.TMP_STMT_TBL) a
     where
     databasename=:database_nm
     and tablename=:tb_nm
     ;
     END;
     CALL SYSDBA.SP_LOG_EVENT('mv_bkup_data',:db_nm,:tb_nm,
     'Create create table statement.',:activity_count,:sqlstate,:sqlcode);

The SQL for the SYSDBA.SP_LOG_EVENT follows:

     replace procedure SYSDBA.SP_LOG_EVENT
     (PRCS_NM CHAR(30),
     DB_NM CHAR(30),
     TB_NM CHAR(30),
     PRCS_STEP_DESC CHAR(100),
     PRCS_ACTIVITY_COUNT DECIMAL(15,0),
     PRCS_SQLSTATE CHAR(5),
     PRCS_SQLCODE SMALLINT )

     BEGIN

        /** Exit in case of any exception.  **/
        DECLARE CONTINUE HANDLER
        FOR SQLEXCEPTION
           BEGIN
              INSERT INTO SYSDBA.SP_ERROR_LOG VALUES
              (CURRENT_TIMESTAMP, :PRCS_STEP_DESC, :SQLSTATE, :SQLCODE);
           END;

        /** Log the text in the process log table. **/
        insert into SYSDBA.SP_PRCS_LOG
        values (CURRENT_TIMESTAMP, :PRCS_NM,:DB_NM,:TB_NM,
        :PRCS_STEP_DESC,:PRCS_ACTIVITY_COUNT,
        :PRCS_SQLSTATE,:PRCS_SQLCODE);

     END;

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