Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.

Expand All Subscribe to Teradata Developer Exchange - Extensibility content The Latest
Developing Database Extensions (UDFs etc)

From time to time I’m asked how one might go about writing an UDF or an INMod or some other procedural extension to the database. The question isn’t a “where do I learn C or C++” question; rather it is a how do I go about debugging and testing my extension without the overheads and constraints of running within the database (or utility).

My response is you can use “my Teradata” for which I have the source code (attached). This will allow you to run, test and debug your function outside of Teradata. In short with “my Teradata” all of the features of your IDE will be available to you, including your debugger.

Putting Geospatial on the Map Part I

In my previous article we explored Teradata's spatial features using geometric shapes on a 10x10 grid.  I hope this was a useful introduction into the new ST_Geometry data type and its powerful set of functions.  And now that we've covered the basics, let's take a step into the world of geospatial.  By geospatial, we're now talking about locations based on geographic reference points on Earth; for example longitude and latitude.

Fun with Teradata and Geometry

Now that you’ve installed Teradata’s geospatial features with Teradata Express let’s roll up our sleeves and see what we can do with this. (If you haven’t setup your TD Express with Geospatial yet, see my article. I think the best way to begin exploring these features is to start is with some basic geometric shapes. Even though we call this Teradata Geospatial, we can ignore the “geo’ part for now and just play around with simple spatial objects like Points, Lines and Polygons.

XML Document Loading

Teradata XML Services (TXS) extends the database’s XML capabilities focusing on transforming data between its XML and relational representations. Two key components of XML Services are:

Java External Stored Procedure and UDF runtime library

This library is used when developing Java functions for execution in the Teradata database using an IDE like eclipse. It provides the interface descriptions for the database methods available to the functions. 

Teradata UDFs for popular Oracle functions

A collection of UDFs that emulate the most-commonly used Oracle built-in SQL functions.

Month-Year Difference Determination UDF

These UDFs implement difference determination in months/years using smaller units of granularity, days/months, to accurately calculate the difference. According to the ANSI SQL standard, units smaller than the unit of the result are to be ignored in an SQL calculation, but this can lead to results not consistent with business practices (Refer to NTA 1553). This distribution contains full source code, REPLACE FUNCTION SQL, a test suite and documentation.

MD5 Message Digest UDF

This UDF implements the MD5 message-digest algorithm. The algorithm takes as input a message of arbitrary length and produces as output a 128-bit "fingerprint" or "message digest" of the input (RFC1321). This distribution contains full source code, a test suite, scripts, and documentation.

Great Circle Distance Calculation UDF

This UDF implements the great circle distance algorithm to determine the distance in miles between two points expressed in longitude and latitude. This distribution contains full source code, REPLACE FUNCTION SQL, a test suite and documentation.

SHA-1 Message Digest UDF

This UDF implements the Secure Hash Algorithm, SHA-1, for computing a condensed representation of a message. When a message of any length < 264 bits is input, the SHA-1 produces a 160-bit output called a message digest (FIPS 180-1 / RFC 3174). This distribution contains full source code, a test suite, scripts, and documentation.