Archives of the TeradataForum

Message Posted: Fri, 09 Feb 2001 @ 14:41:56 GMT

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

We have been updating a load stats table since version 3. We set up the commands in the multiload stream and update the stats at the beginning and end of the load job.

We insert the record before the load and then do an update after the load:

.SET DATABASENAME TO '''BIS_SUM'''; .SET TABLENAME TO '''USER_PRDCT_MTHLY_SUM'''; .SET JOBNAME TO '''MWTD205'''; .SET START_DT TO '''&SYSDATE4'''; .SET START_TIME TO '''&SYSTIME'''; .SET END_DT TO '''&SYSDATE4'''; .SET END_TIME TO '''&SYSTIME''';

INSERT INTO LDWMETRICS.LOAD_STATS
VALUES
(&DATABASENAME
,&TABLENAME
,&JOBNAME
,&START_DT (DATE, FORMAT 'YYYY-MM-DD')
,&START_TIME
,CURRENT_TIMESTAMP(2)
,&END_DT (DATE, FORMAT 'YYYY-MM-DD')
,&END_TIME
,CURRENT_TIMESTAMP(2)
,0
,0
,0
,0
,0
,'C'
)
;

.SET INSRT_CNT TO &SYSINSCNT;
.SET UPDT_CNT TO &SYSUPDCNT;
.SET DEL_CNT TO &SYSDELCNT;
.SET ET_CNT TO &SYSETCNT;
.SET UV_CNT TO &SYSUVCNT;
.SET END_DT TO '''&SYSDATE4''';
.SET END_TIME TO '''&SYSTIME''';

UPDATE LDWMETRICS.LOAD_STATS
SET END_DT = &END_DT (DATE, FORMAT 'YYYY-MM-DD')
, END_TIME = &END_TIME
, END_DTS = CURRENT_TIMESTAMP(2)
, INSRT_CNT = &INSRT_CNT
, UPDT_CNT = &UPDT_CNT
, DEL_CNT = &DEL_CNT
, ET_CNT = &ET_CNT
, UV_CNT = &UV_CNT
, CURR_IND = 'Y'
WHERE DATABASENAME = &DATABASENAME
AND TABLENAME = &TABLENAME
AND JOBNAME = &JOBNAME
AND CURR_IND = 'C';

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