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

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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 
     FROM 
   (SELECT write_mq
      ('queue.manager.1','QUEUE1','CHANNEL1/TCP/153.64.119.177',
         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.