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

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 Vantage 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 Vantage.