16.20 - Implementation Guidelines - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
April 2020
Content Type
Programming Reference
Publication ID
English (United States)

Here are the basic steps you take to write a Java method for a table UDF that is invoked with constant expression input arguments:

  1. Define the parameter list in the order that the CREATE FUNCTION statement specifies the parameters.

    For more information, see Method Signature for Table UDFs.

  2. If the table UDF is defined with dynamic result row specification, call Tbl.getColDef() to get the actual number and data types of the result row arguments that the method must return.
  3. Call Tbl.getPhase() and verify that the mode (the return value) is Tbl.TBL_MODE_CONST, indicating that the table function was invoked with constant expression input arguments.
  4. Use the value that Tbl.getPhase() returns in the phase argument to determine the phase in which Teradata Database invoked the method and what action to take.
    IF the value is... THEN the method...
    Tbl.TBL_PRE_INIT must decide whether it should be the controlling copy for the table UDF (other copies of the method are executed on other AMP vprocs).

    If the method wants to provide control context to all other copies of the method, the method must call Tbl.control().

    If the method does not want to be the controlling copy for the table UDF, or if the method is designed without the need for a controlling method, the method can simply return and do nothing during this phase.

    All copies of the method must complete this phase before any copy continues to the Tbl.TBL_INIT phase.

    Tbl.TBL_INIT can start to access system resources, such as local files, if there is a need to do so.

    Any copy of the method that does not want to participate further must call Tbl.optOut(). Teradata Database will not call the method again.

    All copies of the method must complete this phase before any copy continues to the Tbl.TBL_BUILD phase.

    Tbl.TBL_BUILD should take one of the following actions:

    If the method has...

    • a row to build, then build an output row by filling out each output argument that has a non-null input value.

      The method remains in the Tbl.TBL_BUILD phase.

    • no row to build, then throw an SQLException, setting the SQLState field to "02000" to indicate no data.

      The method continues to the Tbl.TBL_END phase.

    Tbl.TBL_END (if it is the the controlling copy of the table UDF) is called with this phase after all other copies of the table UDF have completed this phase, allowing the controlling function to do any notification to the external world.

    Teradata Database does not invoke the method again after it returns from this phase.

    Tbl.TBL_ABORT is being aborted. A method can be called at any time with this phase, which is only entered when a copy of the table functions calls Tbl.abort(). This phase is not entered when the method aborts for an external reason, such as a user abort.
  5. If the method detects an error, throw an SQLException. For more information, see Exception Handling .