Archives of the TeradataForum

Message Posted: Thu, 24 May 2012 @ 15:36:02 GMT

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

Thanks for the suggestions on how to get the ActivityCount from Bteq, which appear to be either use a Stored Procedure to run the DML, or use a scripting language to 'grep' it from the log file.

Well, I've decided to do the latter approach and, if I say it myself, I'm pretty pleased with the end result.

So, for anyone who's thinking of doing something similar, this is pretty much how I've done it (leaving out some technical specifics, which don't help in the explanation):

     ------------------------------------------------------------------
     bteq << EOI
     .
     .
     lots of bteq stuff
     .
     .
     /* Update existing rows */
     UPDATE $ENV$TARGET_DB.CORCOV01 TGT1
     SET
     EDW_LOAD_TD=CORCOW02.EDW_LOAD_TD,
     CON_DELETE_IN=CORCOW02.CON_DELETE_IN,
     CON_ADD_TD=CORCOW02.CON_ADD_TD,
     CON_ROW_UPDT_TD=CORCOW02.CON_ROW_UPDT_TD
     and other Columns
     WHERE
     TGT1.CON_ID=CORCOW02.CON_ID

     /* Insert new rows */
             INSERT INTO $ENV$TARGET_DB.CORCOV01 (CON_ID ,EDW_LOAD_TD ,CON_DELETE_IN ,CON_SHIPPER_TYP_CD ,CON_ROW_UPDT_TD and other Columns
      FROM CORCOW02 WRK2
     WHERE NOT EXISTS
             (SELECT 1 FROM $ENV$TARGET_DB.CORCOV01 TGT1
                     WHERE TGT1.CON_ID= WRK2.CON_ID
     ) ;

     .If ErrorCode <> 0 Then .Exit 12;

     .OS python $py_path/processLogfile.py ${table_name}.log ${out_file} >> ${log_file}

     .IMPORT VARTEXT '|' FILE =
     /edw/dataloads/service/coadmin/consignment/data/corcot01_${C3}_log.txt,
     SKIP=1
     USING (DB_NM VARCHAR(30)
           ,TB_NM VARCHAR(30)
               ,INS_CT VARCHAR(5)
               ,UPD_CT VARCHAR(5)
               ,DEL_CT VARCHAR(5))
     EXEC DEVDBA_X.LOAD_LOG_M (:DB_NM
                              ,:TB_NM
                      ,:INS_CT
                      ,:UPD_CT
                      ,:DEL_CT);
     .
     .

     .Exit 0;

     EOI
     ------------------------------------------------------------------

So, the Operating System call runs my script (processLogfile.py), which is passed the name of the log file (containing the DML log entries for the previous DML statements), then after finding the ones it needs, writes them to an output file.

From there, an Import will use that written data to write (via a simple Macro) to an Audit Table.

Simple, but I'd say fit for purpose.

Regards

David Clough
Senior Database Designer
Database Design Group

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