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)

Levels of Integration with 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. There are four levels of integration you can do, which we'll discuss here.

Regular UDRs

The least integrated option is of course to write your own UDFs and manage their source code and build process in your own environment. Those are not part of Trafodion at all. If you would like to share generic UDFs with the Trafodion community, consider posting a link to your github repository on the Trafodion Code Examples web page.

UDRs shipped with Trafodion that are created during INITIALIZE TRAFODION

Some UDRs come with Trafodion. Most of them are UDRs that are needed for manageability. If you would like to add such a UDR, take a look at file core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp and at method CmpSeabaseDDL::createLibmgrProcs() to see an example how such procedures are created.

Predefined UDRs

If you have a TMUDF that just transforms data and has no need for grant/revoke security, then you can go one step further and make it a "predefined" TMUDF. This is a hybrid between a built-in and a user-defined function. Here is a comparison:

 Built-in functionPredefined UDFTMUDF (not predefined)
LanguageC++C++, JavaC++, Java
InterfaceComplex internal interfaceTMUDF interfaceTMUDF interface
PrivilegesAnyone can execute built-in functionsAnyone can execute predefined UDFsGrant/revoke
NameOne-part nameOne-part name, can be eclipsed by a UDF of the same name in the default schema3-part name
MetadataHard-wired in source codeNo metadata in metadata tables, uses TMUDF compiler interfaceMetadata tables and optional TMUDF compiler interface
SyntaxSpecial syntax can be defined in Trafodion parserUDF syntax or special syntax, defined in Trafodion parserUDF syntax only
ExecutionIn the Trafodion engine (master executor, ESP)In a separate tdm_udrserv processIn a separate tdm_udrserv process

If you want to add a predefined TMUDF, you can follow the example of an existing one: https://github.com/apache/trafodion/blob/master/core/sql/sqludr/SqlUdrPredefTimeSeries.cpp. There are other predefined UDFs like event_log_reader and jdbc, but those should have been made regular UDRs (mea culpa), since not all system administrators may want to give access to those UDFs.

Built-in Functions

The tightest integration can of course be achieved by adding to one of the class hierarchies in Trafodion. Here are the main ones used to extend the code:

ClassDescriptionExample
ItemExprScalar expression, taking zero or more scalar inputs and producing one scalar result. Dave Birdsall put together a wiki page on how to add a scalar operator.UPPER()
RelExprRelational expression, taking zero or more table-valued inputs and producing a table-valued result.UNION
RuleOptimizer rule. These could be transformation rules like the join commutativity rule (A join B => B join A) or implementation rules that specify runtime operators (see example).A join B ==> A hash_join B
PartitioningFunctionPartitioning function. This describes how rows can be partitioned to multiple nodes in a cluster, based on partitioning key columns.Hash partitioning function.

 

Content by label

There is no content with the specified labels