16.20 - Example: Retrieving Rows from the Data Dictionary and Writing Them to an External Queue on a Client System - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

Now consider a somewhat broader use of the UDF defined in Example: Using a UDF to Write a Message to an External Queue. In this example, you retrieve dictionary information from within Teradata Database and write it to an external MQ queue on a client system.

To do this, you invoke the write_mq UDF for each row found in DBC.Tables that meets the requirements specified in the SELECT request WHERE clause. The SELECT request concatenates the database and table names, creating a VARCHAR string that becomes the input argument to the UDF, which then writes the concatenated databasename.tablename text as a message to the MQ queue.

     SELECT COUNT(*) AS sent_msgs 
   (SELECT write_mq
         Trim(databasename)||'.'||trim(TableName)) AS c1
       FROM DBC.Tables 
        WHERE TableKind = 'T')T;

What this example illustrates is the ease of sending an entire result set of an arbitrary SQL request to a queue that is completely outside Teradata Database.