|
|
|
Junior Member
 
Group: Forum Members
Last Login: 3/27/2008 2:37:06 AM
Posts: 22,
Visits: 60
|
|
Hi
Can anyone please guide me writing fopen(),put_line() and fclose() UDFs in Teradata.
These functions are in ORACLE UTL_FILE package.
As database is migrating from oracle to teradata,I need to
replace those functionality with teradata functions.
OR Is there any similar UDF available with anyone who can share the code .
Any type help what you can give in this respect is welcome.
Thanks In Advance
*
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/18/2008 2:51:53 AM
Posts: 7,
Visits: 324
|
|
In Teradata UDFs you cannot perform any I/O task, you have to think of an alternate strategy.
Regards,
Fawad Asrar Qureshi
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 9/30/2008 4:58:28 PM
Posts: 69,
Visits: 83
|
|
Probably an Oracle UDF is a lot different than a Teradata UDF. A Teradata UDF is invoked from an SQL expression and returns a scalar value. If the UDF is referenced in the SELECT statement and this statement is sent to all AMP vprocs, in essence that UDF instance is duplicated on each of those vprocs and they all execute at the same time. So how are you going to open a file and read it or write the file if each instance of the UDF is on different nodes? And what file are you going to open, the one on the system disk, on which node? You might be able to do it by mapping a network drive I suppose on all server nodes, which all point to the directory and same file. It’s fairly complicated to set up.
The other complication is that all of the processing for the UDF has to be done for that invocation; open, read/write and close. Once each instance of the UDF executes there is no context. If the UDF forgets to close the file, the file remains open. Next time it runs the file is opened now twice, and so on.
In general doing I/O with scalar, or aggregate UDFs is not recommended. If you do it they must be running in protected or secure mode. It was not recommended in release 5.1. It can be done in 6.1 and above, because the support of secure UDF servers. They allow a UDF to run in the context of a particular server OS user, were one can control (via that OS user) what the UDF has access to. The generic (protected mode) “tdatuser” has no access to anything other than what is public readable and writable for any logged on OS user.
Table functions which are a form of UDF are somewhat different in that they have context that is set up and retained until the table function exits its end phase. So opening external files is more relevant in this context, but again they run on all vprocs on all nodes, but there is a way to control what they do and which instance does what.
One other option is to use an external stored procedure to access external files. Only one instance of such a procedure runs at a time in a session. It is invoked via a procedure CALL statement. In Teradata 12.0 you can create Java or CLI based external stored procedures that allow you to submit SQL statements to the database.
In general you probably want to redesign your business applications to work on Teradata to take advantage of its capabilities. Just porting UDFs straight from Oracle is not going to work out to well.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 3/27/2008 2:37:06 AM
Posts: 22,
Visits: 60
|
|
As of version 7.x we can write external procedures and UDFs in C or C++.
Anyone having prior experience in writing these types of external procedures or UDFs.please share
some knowledge
thanks in advance
*
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 3/27/2008 2:37:06 AM
Posts: 22,
Visits: 60
|
|
Redesigning our business applications to work on Teradata is an option.
So that we can the take the advantage of its capabilities.But we are experimenting on all the probabilities to retain our model.As of now our only problem is to find a replacement of Oracle UTIL_FILE package.If anyone gets any valuable information this,please share the knowledge.
*
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 12:52:50 PM
Posts: 217,
Visits: 454
|
|
|
Bear in mind that, even with an external procedure, you normally don't have control over which node the procedure executes on.
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 9/30/2008 4:58:28 PM
Posts: 69,
Visits: 83
|
|
|
I think you are trying to put a square peg into a round hole. I think you will find that what you are trying to do will not work too well on Teradata. That is as it may be, but there is no equivalent UTIL_FILE package. You can write your own and call fopen, putc or fwrite, and fclose, since that is part of the C standard and is supported on Unix and Windows platforms and can be called in a UDF. But it will probably not work the way you think, unless you have only a one node system. I think from your other threads it seems like your log would be on the client side rather than on the database. If that is the case then use the tools available on the client system and put the log calls in a script.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 3/27/2008 2:37:06 AM
| | | |