You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

This is a brief overview of the types of user-defined routines offered by Trafodion and a description on how to create them. The article also talks about operators and routines that are built into Trafodion.

Routines, Procedures and Functions

The most generic term used here is a Routine, which includes all of the following:

TypeSubtypeReturn Value(s)Where in SQL syntaxLanguagesSecurityDescription
Stored Procedure
in Java (SPJ)		 

Output parameters

Result sets

CALL statementJavatrusted
  • Can be called with a CALL statement only.
  • Can have input, inout and output scalar parameters.
  • Can produce result sets.
  • Can use SQL via a special default JDBC connection (trusted, DB_ROOT).
  • SQL uses the same transaction as caller.
  • Recursive calls are possible, all result set of recursive calls are returned to top-level caller.

Function (UDF)

Scalar UDFScalar valueIn any scalar expressionCtrusted
  • C interface
Table-mapping UDF
(TMUDF)		TableFROM clauseJava / C++trusted
  • C++ or Java interface
  • Optional compiler interface
  • Two subtypes:
    • Table-valued UDF (TVUDF): Has no input tables, produces a table-valued result
    • Real Table-mapping UDF (TMUDF): Has one table-valued input and produces a table-valued result
  • Multiple input tables may be supported sometimes in the future

Click the links in the table above for more information.

 

Known Issues

Unfortunately, the security features of Trafodion UDRs are not completely implemented at this time. Here is an outline of what we have today and of the design we are trying to follow:

Security attribute of the UDRTRUSTEDISOLATEDCurrent Implementation
UDR code runs under Trafodion id, has full control over the database, can read and write any data, can corrupt system data structures.yesnoyes
UDR code runs in a separate process (tdm_udrserver)noyesyes (except for TMUDF compiler interface)
UDR code runs under a special user id with low privileges (similar to a guest id)noyesno
Scalar UDFs are converted into a relational operatornoyesyes
Ability to run SQL statements on a special connection that implements definer's rights or invoker's rights (the user id that created the UDR or that invoked the UDR, respectively)yesyesno (SQL in SPJs runs as DB_ROOT)

Predefined UDRs and UDRs created during INITIALIZE TRAFODION

In some cases, we may want to include a new function in Trafodion, if it is generic and potentially useful to most Trafodion users. 

 

Built-in Functions

x

 

Content by label

There is no content with the specified labels

  • No labels