Example | Migrate UDF Scripts | VantageCloud Lake - Example: Migrating Existing UDF SQL Scripts to VantageCloud Lake

This example shows how to convert a BTEQ script that creates the udfSubStr function into a script that can be used in VantageCloud Lake. The example uses these directories:

Input directory: UDF_input_dir
Output directory: UDF_output_dir
Source directory: UDF_source_dir

The following BTEQ file and config.yml file are stored in UDF_input_dir .

BTEQ file contents:

.logon user,pass;
 
  CREATE TABLE Test (
   a INTEGER NOT NULL,     
  ) primary index(a);
 
  CREATE FUNCTION udfSubStr (inputString VARCHAR(512), start INTEGER)
  RETURNS VARCHAR(512)
  LANGUAGE C
  NO SQL
  EXTERNAL NAME 'CS!substr!substr.c!F!udf_substr'
  PARAMETER STYLE TD_GENERAL
  EXECUTE ON ALL
  EXECUTE MAP = map_name
  SPECIFIC udfSubStr1;  
 
INSERT INTO Test VALUES (1);
 
SEL  udfSubStr(string,2); --EOF

config.yml file contents:

Systemcall: .os
tdext-path: tdextroutine.exe
verbose: y

The UDF source file, substr.c, is stored in UDF_source_dir.

XRMigrationTool was run as follows:

XRMigrationTool -d UDF_input_dir -o UDF_output_dir -s UDF_source_dir

XRMigrationTool updates the BTEQ file in UDF_input_dir, creates a backup of the bteq and mvsjcl files in UDF_input_dir, and replaces the CREATE FUNCTION syntax with a call to tdextroutine.

XRMigrationTool supports single line comments starting with * or -- and multiline comments within /* */. Comments are supported in between the DDL statements as well. For example,

* UDF creation

CREATE FUNCTION udfSubStr (inputString VARCHAR(512), start INTEGER)
-- Takes in two input parameters
RETURNS VARCHAR(512)
/*=================================================================
UDF Name – udfSubStr
Input Parameters – inputString VARCHAR(512), start INTEGER
===================================================================*/
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!substr!substr.c!F!udf_substr'
PARAMETER STYLE TD_GENERAL
EXECUTE ON ALL
EXECUTE MAP = map_name
SPECIFIC udfSubStr1;

Updated BTEQ file contents:

.logon user,pass;
 
CREATE TABLE Test
(
 a INTEGER NOT NULL,     
)primary index(a);
 
.os tdextroutine <necessary arguments to call tdextroutine to complete the UDF create/replace request>
 
INSERT INTO Test VALUES (1);
 
SEL  udfSubStr(string,2); --EOF

XRMigrationTool creates a subdirectory in UDF_output_dir named SQLToPayload.

XRMigrationTool creates the UDF manifest file, manifest.json, in the SQLToPayload directory.

manifest.json file contents:

{
    "manifest_version": "v1",
    "parameter_spec": " inputString VARCHAR(512), start integer",
    "return_spec": "VARCHAR(512)",
    "parameter_style": "TD_GENERAL",
    "execute_on": "all"
    "execute_map": "map name"
    "interim_size": 0,
    "source_language": "C",
    "files": [
        {
            "location": "client",
            "type": "source",
            "name_on_server": "substr",
            "filepath": "substr.c"
        }
    ]
}

When XRMigrationTool completes, you can run the updated BTEQ script to create the udfSubStr UDF in VantageCloud Lake.

Example | Migrate UDF Scripts | VantageCloud Lake - Example: Migrating Existing UDF SQL Scripts to VantageCloud Lake - Teradata VantageCloud Lake

Lake - Using Queries, UDFs, and External Stored Procedures