For the maximum visibility for operators, troubleshooters etc, logging should be the domain of flat log files. It is the only way to preserve the chain of events with complete context.

Also avoid having too many disparate or unrelated SQL operations in the same proc. A proc's reason for existence is to solve a difficult feature issue, not be the "place where all ELT runs". That's why it's a procedure, rather than a program.

I need to see log records while the stored proc( it's batch process whcih takes half an hour) to check which step I am in. also to log when the error occured. I prefer log table over  raise notice or external table.Please let me know the best way to achieve this(UDX or no UDX).

Is it possible to insert rows from UDX in netezza or any other database . If yes any thing i have to take care when I start writing the UDX.

If you must use a log table, you can still access the rows after a rollback...

set show_deleted_records = true;

select * from mylogtable;

This will show all rows in the table, including those that were deleted.