All Forums Database
UpaMishra 35 posts Joined 01/08
24 Jan 2008
Oracle to Teradata Migration

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;Can anyone guide me about this

joedsilva 505 posts Joined 07/05
24 Jan 2008

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.... :) )

Jim Chapman 449 posts Joined 09/04
24 Jan 2008

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?

UpaMishra 35 posts Joined 01/08
25 Jan 2008

log table is obviously an option,and it can be exported by bteq export to a data file.But I am trying to use the external procedure feature to write the same UTIL_FILE pack in Teradata .

Luckyhansh 30 posts Joined 08/06
25 Jan 2008

It seems that you have to do it yourselfwe can only get it from bteq log.or you can use " case when "but i thought it will need extra spaces

UpaMishra 35 posts Joined 01/08
28 Jan 2008

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.

You must sign in to leave a comment.