Internal and External Data | SQL External Routine Programming | Teradata Vantage - Internal and External Data - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantage™

The following rules apply to internal and external data.

IF the UDF is … THEN the function …
a scalar function cannot retain global data from function call to function call unless it uses the Global and Persistent (GLOP) data feature. For more information, see Global and Persistent Data.
an aggregate function can use an intermediate storage area to keep partially aggregated data.
a table function can use scratch pad memory keep track of its progress.

A function cannot contain any static variables. A function can contain static constants.

You can specify the External Data Access clause in the CREATE FUNCTION/REPLACE FUNCTION statement to define the relationship between a function or external stored procedure and data that is external to Vantage.

The option you specify determines:
  • Whether or not the external routine can read or modify external data
  • Whether or not the database will redrive the request involving the function or procedure after a database restart
If Redrive protection is enabled, the system preserves responses for completed SQL requests and resubmits uncompleted requests when there is a database restart. However, if the External Data Access clause of an external routine is defined with the MODIFIES EXTERNAL DATA option, then the database will not redrive the request involving that function or procedure. For details about Redrive functionality, see:
  • Teradata Vantage™ - Database Administration, B035-1093
  • The RedriveProtection and RedriveDefaultParticipation DBS Control fields in Teradata Vantage™ - Database Utilities, B035-1102.

If you do not specify an External Data Access clause, the default is NO EXTERNAL DATA.

The following table explains the options for the External Data Access clause and how the database uses them for external routines.

Option Description
MODIFIES EXTERNAL DATA The routine modifies data that is external to the database. In this case, the word modify includes delete, insert, and update operations.
Following a database failure, the database does not redrive requests involving a function or external stored procedure defined with this option.
NO EXTERNAL DATA The routine does not access data that is external to the database. This is the default.
READS EXTERNAL DATA The routine reads, but does not modify data that is external to the database.

For more information, see the information about CREATE FUNCTION (External Form) in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.