This bit of code is part of a procedure Which is written in Oracle environment.. I haven't tested yet it in Teradata as it is pre-IT stage.But I think It will work in teradata environment also because it supports ANSI merge statement.Any suggestion regarding this is welcome. MERGE INTO Upendra_best_customer_mail pl USING (SELECT b.prs_cd_id, b.upendra_id, b.effective_update_date, b.date_create, b.date_update, b.email_address, a.prs_id FROM exi_acd_best_email b , exi_acd_person a WHERE b.prs_cd_id = a.prs_cd_id(+) AND b.upd_ts >= ld_last_insert AND b.upd_ts <= ld_max_dt) sl ON (pl.prs_cd_id = sl.prs_cd_id) WHEN MATCHED THEN UPDATE SET pl.prs_id = sl.prs_id, pl.upendra_id = sl.upendra_id, pl.prs_eff_upd_ts = sl.effective_update_date, pl.prs_email_addr = sl.email_address, pl.acd_cre_ts = sl.date_create, pl.acd_upd_ts = sl.date_update, pl.upd_ts = ld_max_dt WHEN NOT MATCHED THEN INSERT ( pl.prs_cd_id, pl.prs_id, pl.upendra_id, pl.acd_cre_ts, pl.acd_upd_ts, pl.prs_eff_upd_ts, pl.prs_email_addr, pl.cre_ts, pl.upd_ts ) VALUES ( sl.prs_cd_id, sl.prs_id, sl.upendra_id, sl.date_create, sl.date_update, sl.effective_update_date, sl.email_address, ld_max_dt, ld_max_dt );
The real is Problem is here....... I used UTIL_file package to maintain logs. But here in teradata ,no such ready made packages are there.
Sample code That I have used .......
IF uuv_status = 'SUCCESS' THEN uuv_my_mesg := 'Procedure started successfully ' ; UTL_FILE.PUT_LINE(uuv_log_handle,uuv_my_mesg,TRUE); ELSE uuv_my_mesg := 'Error in Opening log file'; END IF;
Teradata V2r6.x doesn't support ANSI merge, it's supported only from TD12.0......
As far as logs as concerned, most of the shops I have worked, uses a stored proc to insert into some "log tables" (some how I admit that I like a log table compared to log file, because you can do all kinds of "search" on a log table.... :) )
Teradata's MERGE statement (as of 12.0) has some extended syntax that allows an "error table" to be specified, and then errors that are encountered during the merge (e.g. constraint violations) are automatically logged to the error table.
If that isn't what you want to log, perhaps a trigger would serve?
There is a print statement which gives the output to stdout. In Unix environment ,stdout is having a file descriptor 1 as unix treats every device as a file. We can redirect it to any file using unix '>' operator. It can be done using a shell script. I think this may be a way to maintain log files.The shell script will accept the input which ever come as the input to the stdout and move the contents to a specified file. like stdout>mylog. Can anyone give me any remarks regarding this.