Usage Notes - Advanced SQL Engine - Teradata Database
SQL Operators and User-Defined Functions
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
xwv1596137968859.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™
Introduction to SQL Operators and User-Defined Functions
Changes and Additions
Compression/Decompression Functions
CAMSET
Argument Type and Rules
Result Type
Usage Notes
Restrictions
Uncompressing Data Compressed with CAMSET
Examples
Example: Compressing Unicode Values
Example: Querying for Compressed Values
Related Topics
CAMSET_L
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with CAMSET_L
Examples
Example: Compressing Latin Values
Example: Querying for Compressed Latin Values
Related Topics
DECAMSET
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Unicode Values
DECAMSET functions Related Topics
DECAMSET_L
Argument Type and Rules
Result Type
Usage Notes
Example: DECAMSET_L Function
Related Topics
JSON_COMPRESS
Argument Type and Rules
Result Type
Example: Compressing JSON Data Types
Related Topics
JSON_DECOMPRESS
Argument Type and Rules
Result Type
Example: Decompressing JSON Data Types
Related Topics
LZCOMP
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with LZCOMP
Examples
Example: Compressing Unicode Values
Example: Querying for the Compressed Unicode Values of the Description column
Related Topics
LZCOMP_L
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with LZCOMP_L
Examples
Example: Compressing Latin Values
Example: Querying for the Compressed Latin Values of the Description column
Related Topics
LZDECOMP
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Unicode Values with LZCOMP
Related Topics
LZDECOMP_L
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Latin Values with LZCOMP_L
Related Topics
TD_LZ_COMPRESS
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with TD_LZ_COMPRESS
Related Topics
TD_LZ_DECOMPRESS
Argument Type and Rules
Result Type
Usage Notes
Related Topics
TS_COMPRESS
Argument Type and Rules
Result Type
Usage Notes
Examples
Example: Compressing the TIME Data Type
Example: Compressing the TIMESTAMP Data Type
Related Topics
TS_DECOMPRESS
Argument Type and Rules
Result Type
Example: Creating a Compressed TIME Column
Related Topics
TransUnicodeToUTF8
Argument Type and Rules
Result Type
Usage Notes
Restrictions
Example: Uncompressing Data Compressed with TransUnicodeToUTF8
Related Topics
TransUTF8ToUnicode
Argument Type and Rules
Result Type
Usage Notes
Example: Uncompressing Unicode Values with TransUTF8ToUnicode
Related Topics
Export Width Procedures
Using Export Width
ReplaceExportDefinition
Argument Types
Usage Notes
Export Width Rules
Example: ReplaceExportDefinition
RemoveExportDefinition
Argument Types
Usage Notes
Example: RemoveExportDefinition
File System Information Macros and Functions
CreateFsysInfoTable/CreateFsysInfoTable_ANSI
Examples
Example: Create a File System Information Table for Short Display for SHOWBLOCKS
Example: Create a File System Information Table for Short Display for SHOWCOMPRESS
Example: Create a File System Information Table for Short Display for SHOWWHERE
PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI
Examples
Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'S'
Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'M'
Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'L'
Example: Populate a File System Information Table for SHOWCOMPRESS Output, Option 'S'
Example: Populate a File System Information Table for SHOWCOMPRESS Output, Option 'L'
Example: Populate a File System Information Table for SHOWWHERE Output, Option 'S'
Example: Populate a File System Information Table for SHOWWHERE Output, Option 'M'
Example: Populate a File System Information Table for SHOWWHERE Output, Option 'L'
AlterFsysInfoTable_TD16/AlterFsysInfoTable_ANSI_TD16
Examples
Example: Using the PopulateFsysInfoTable Macro to Populate a Pre-Existing Table
Example: Migrating Tables to a Prior Release
Heatmap Table Function and Macro (tdheatmap and tdheatmap_m)
Differences Between the tdheatmap Function, Macro and View
Returned Columns
Usage Notes
Examples
Example: Using the tdheatmap Function
Example: Using the tdheatmap_m Macro
Example: Using tdheatmap to Create a View
Example: Data Temperature Report Showing a Specific Table
Example: Showing Cylinders in TIM Cache
Example: Heatmap Report Showing Tables with Different Subtable Types
Related Topics
Map Functions, Macros, and Procedures
Map Functions
SYSLIB.ContiguousMapAMPs
Usage Notes
Example: Identifying Which AMPs are in a Contiguous Map
SYSLIB.SparseMapAMPs
Usage Notes
Example: Identifying an AMP with a Specific Table
SYSLIB.SparseTableAMPs
Usage Notes
Example: Identifying AMPs for a Table with a Sparse Map
Map Macros
Map Procedures
AdjustSpace
Argument Types
Result Type
Examples
Example: Adjusting the Space Settings After a System Expansion
Example: Using AdjustSpace
Usage Notes
Advisor Procedures
AddExclusionListEntrySP(X)
Argument Type
Usage Notes
Examples
Example: Creating an Exclusion List Named ‘MyExclusions’
Example: Analyzing Exclusions Defined in 'MyExclusions'
AddMapListEntrySP(X)
Argument Types
Usage Notes
Example: Calling AddMapListEntrySP(X)
AddObjectListEntrySP(X)
Argument Types
Usage Notes
Examples
Example: Creating an Object List
Example: Analyzing Objects Defined in 'MyObjects'
Example: Creating Object List ‘ObjNotInMap2’
AnalyzeSP
Argument Types
Result Type
Usage Notes
Example: Using AnalyzeSP
CleanUpAnalyzerSP
Argument Types
Example: Calling CleanUpAnalyzerSP
CreateExclusionListSP
Argument Types
Result Type
Usage Notes
Example: Calling CreateExclusionListSP
CreateExpansionMaps
Example: Using CreateExpansionMaps
CreateMapListSP
Argument Types
Result Type
Usage Notes
Example: Calling CreateMapListSP
CreateObjectListSP
Argument Types
Result Type
Usage Notes
Example: Calling CreateObjectListSP
MonitorAnalyzeSP
Argument Types
Result Types
Example: Calling MonitorAnalyzeSP
PostExpansionAction
Example: Using PostExpansionAction
Mover Procedures
ManageMoveTablesSP
Argument Types
Result Type
Usage Notes
Example: Executing the Actions in the 'MyNewMapActions' List
MoveTablesSP
Argument Types
Usage Notes
Examples
Example: Using BTEQ to Execute Queued Actions
Example: Move a Table to a Map
MonitorMoveTablesSP
Argument Types
Result Type
Example: Using MonitorMoveTablesSP
StopMoveTablesSP
Argument Types
Result Type
Usage Notes
Example: Using StopMoveTablesSP
StopMoverSP
Argument Types
Example: Using StopMoverSP
StopSerialWorkerSP
Example: Using StopSerialWorkerSP
CleanUpMoveTablesSP
Usage Notes
Example: Calling CleanUpMoveTablesSP
MoveTDMapsTablesSP
Argument Types
Result Type
Example: Calling MoveTDMapsTablesSP
TruncateHistorySP
Argument Types
Result Type
Usage Notes
Example: Calling TruncateHistorySP
Table Operators
CALCMATRIX
Data Type
Usage Notes
Examples
Example: One Input Matrix, Single SQL Statement
Example: One Input Matrix, Multiple SQL Statements
Example: Multiple Input Matrixes, One SQL Statement
Related Topics
Cogroups
Example: Cogroup Used for a Table Operator with Multiple Inputs
Related Topics
FeatureNames_TBF
Columns
Example: Generating a Report on Feature Usage
READ_NOS
READ_NOS Syntax
READ_NOS Examples
Setting Up an Object Store for River Flow Data
Define a Function Mapping for READ_NOS
Example: Creating an Authorization Object
Example: Function Mapping Definition Using EXTERNAL SECURITY Clause for JSON Data Files
Example: Function Mapping Definition Using ANY IN TABLE for JSON Data Files
Example: Using READ_NOS with NOSREAD_KEYS Return Type
Example: Using READ_NOS with NOSREAD_RECORD Return Type
Example: Using READ_NOS with NOSREAD_RAW Return Type
Example: Using READ_NOS to List Files in a Location
Example: Using READ_NOS to Display Keys in a JSON File without an Extension
Example: Using READ_NOS to Query an External JSON File
Example: Using READ_NOS to Query an External JSON File Without an Extension
Example: Displaying Keys in a CSV File Using READ_NOS
Example: Using READ_NOS to Query an External CSV File
Example: Using READ_NOS to Query an External CSV File Without an Extension
WRITE_NOS
WRITE_NOS Syntax
WRITE_NOS Examples
R Table Operator
Installation of R Components and Packages
Using ExecR to Execute R Scripts and Table Operators
Related Topics
SCRIPT
Input and Output to the Script File
Data Types
Setting the Database Search Path
Executing LINUX Commands
Executing Scripts
Script Files and Security
Setting Memory Limits
Troubleshooting
Examples
Example: SCRIPT Table Operator Function
Example: Use SYSUIF.INSTALL_FILE to Install Files and then Run the SCRIPT Table Operator
Example: Use the SCRIPT Table Operator to Invoke a Python Script
Example: Creating JSON Output by Using the SCRIPT Table Operator to Invoke Python Modules
Example: SCRIPT Calls Python for Data Type Conversion
Related Information
TD_DBQLParam
Invocation
Argument Types
Result Type
Usage Notes
Example: TD_DBQLParam
TD_DBQLFUL
Invocation
Argument Types
Result Types
Usage Notes
Examples
TD_UNPIVOT
Data Type
Examples
Example: Twelve Columns Unpivoted to One
Example: More Than One Value Column
Usage Notes for Examples
User-Defined Functions
Scalar UDF
Restrictions
UDF Arguments
Result Data Type
Default Title
Examples
Example 1
Example 2
Related Topics
Aggregate UDF
Restrictions
Usage Notes
Example: Aggregate UDF
Related Topics
Window Aggregate UDF
Arguments to Window Aggregate UDFs
Supported Window Types for Aggregate UDFs
Unsupported Window Types for Aggregate UDFs
Partitioning
Result Type and Format
Usage Notes
Problems With Missing Data
Restrictions
Example: Window Aggregate
Related Topics
User-Defined Functions
Restrictions
Usage Notes
Related Topics
UDF Invocation
User-Defined Type Expressions/Methods
UDT Expression
Usage Notes
Example: Methods and Functions
Examples
Example: Column Name
Example: CAST
Example: NEW
Related Topics
NEW
Usage Notes
Default Constructor
Determining Which Constructor is Invoked
Examples
Example 1
Example 2
Related Information
NEW JSON
Related Topics
NEW VARIANT_TYPE
Usage Notes
Restrictions
Examples
Example 1
Example 2
Related Topics
NEW XML
Related Topics
Method Invocation
Observer and Mutator Methods
Usage Notes
Restrictions
Example: UDT Expression Method
Related Topics
Script Installation Procedures
Execution Rules
Installing and Registering External Language Scripts
SYSUIF.INSTALL_FILE
Example: Calling SYSUIF.INSTALL_FILE to Install a Python Script
Related Topics
Replacing External Language Scripts
Before You Begin
SYSUIF.REPLACE_FILE
Example: Replacing an Existing File Mapper
Redistributing External Language Scripts
Before You Begin
SYSUIF.REDISTRIBUTE_FILE
Example: Redistributing External Language Scripts Argument
Displaying the User-Installed File
Related Topics
Removing External Language Scripts
Before You Begin
SYSUIF.REMOVE_FILE
Example: Removing External Language Script
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
Additional Information
Teradata Links
You can reduce the analysis time for a query log by limiting it to queries that fall within a specified LogStartTime and/or LogEndTime. If the specified LogStartTime is NULL, the analysis reads from the start of the query log. If the specified LogEndTime is NULL, the analysis reads to the end of the query log.
If LogDatabase is NULL, tables are grouped by database.
For LogDatabases other than DBC and PDCRDATA, user TDMaps must be granted the SELECT privilege on its query log tables prior to calling AnalyzeSP.
All generated actions from a call to AnalyzeSP are associated with the common identifier OutputActionList, which cannot have a NULL value. The recommended actions are written as rows to table TDMaps.ActionsTbl. Action types are limited to ALTER and EXCLUDE.
Analysis recommendations identified by OutputActionList can be passed as an input parameter to ManageMoveTablesSP, which executes the recommended actions.
If the OutputActionList value corresponds to an action list that already exists in the actions table, an error occurs.
If there is no query log data, tables are grouped by database. If a log database is specified but there is no log data, then each table belongs to its own group, essentially not grouping anything.
If the output parameter NumLogEntries returns a value of 0, the query log suggests actions to take.
Error tables (created by CREATE ERROR TABLE) are added to ActionsTbl following the base table.