Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Capturing Activity Count insite bteqs
 
From:   Anomy Anom

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

  How to capture the activity count of the first insert statement > in the same bteq?  


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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023