How to write UDF to handle operating System I/O operations

Connectivity
Enthusiast

How to write UDF to handle operating System I/O operations

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
13 REPLIES
Enthusiast

Re: How to write UDF to handle operating System I/O operations

In Teradata UDFs you cannot perform any I/O task, you have to think of an alternate strategy.
rgs
Enthusiast

Re: How to write UDF to handle operating System I/O operations

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.
Enthusiast

Re: How to write UDF to handle operating System I/O operations

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
Enthusiast

Re: How to write UDF to handle operating System I/O operations

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.
Enthusiast

Re: How to write UDF to handle operating System I/O operations

Bear in mind that, even with an external procedure, you normally don't have control over which node the procedure executes on.
rgs
Enthusiast

Re: How to write UDF to handle operating System I/O operations

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.
Enthusiast

Re: How to write UDF to handle operating System I/O operations

ur right.it doesn't work on 2 node system.The error is like 'it failed to create authorization on the two nodes if i give authorization clause as it is a OS I/o.With out this clause in the create function it gives an error as 'a system trap was caused.'the c program was a simple c function for opeing and writing to a file.
rgs
Enthusiast

Re: How to write UDF to handle operating System I/O operations

You are probably not using the AUTHORIZATION statement correctly. The only thing that statement does is to allow a UDF or external procedure to be associated with a particular OS user account. That OS user account has to exist on all nodes of the database system. So when it comes back an tells you that it failed to create the authorization it means that you either entered a user ID that did not exist on both nodes (in your case) or you entered a bad password or both.

You said you tired to make it work without that authorization, which means it uses the default database user ID called “tdatuser”. On MPRAS or Linux it has a “home” directory on each node. So it should be able to write into that. On Windows there is no directory and no access control list set up for anything for “tdatuser” to use. You might be able to write to the tmp directory on a node but only if the system administrator allows it. You did not say what the system trap was for. But my guess is that your program was probably faulty. You should always check out your software to make sure it works outside the database first. Did you verify that you could write to the log file? Are you creating it in a directory accessible to “tdatuser”, probably not! Keep in mind when you write a UDF you are writing an extension to the database and when it runs it becomes an integral part of the database. It is not like writing an application. It takes some expertise in knowing the OS (UNIX or Windows), so unless you have some pretty good system development skills you are probably venturing into dangerous territory.

Rolf
Enthusiast

Re: How to write UDF to handle operating System I/O operations

Thanks for the information.Can you post a sample code which opens a file and writes anything to it.And the create function statement also.