ExecuteForeignSQL Stored Procedure | Teradata Connector | QueryGrid - ExecuteForeignSQL - Teradata QueryGrid

QueryGridâ„¢ Installation and User Guide - 3.06

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Teradata QueryGrid
Release Number
3.06
Published
December 2024
ft:locale
en-US
ft:lastEdition
2024-12-07
dita:mapPath
ndp1726122159943.ditamap
dita:ditavalPath
ft:empty
dita:id
lxg1591800469257
lifecycle
latest
Product Category
Analytical Ecosystem

Purpose

The ExecuteForeignSQL stored procedure provides an interface for executing basic SQL queries on the foreign server. For example, you can use ExecuteForeignSQL to create or drop a table in a database on the foreign server.

ExecuteForeignSQL passes all SQL queries through, but it does not return results sets, so if you use it to execute a SELECT or HELP statement, you do not see any results.

ExecuteForeignSQL and an install script are provided as part of the QueryGrid connector package. The DBA manually executes the script to install ExecuteForeignSQL and then grants the appropriate privileges. Before you use ExecuteForeignSQL you create a foreign server using the QGExecuteForeignQuery table operator in the DO IMPORT WITH clause.

Syntax

CALL [ SYSLIB. ] ExecuteForeignSQL ( 'query_expression', 'server_name' ) [;]

Syntax Elements

SYSLIB.
The name of the database where the stored procedure is located.
query_expression
A valid Teradata SQL expression.

The query is passed through unparsed to the foreign server.

Maximum character limit is 8000.

server_name
The name of the foreign server.

Usage Notes

ExecuteForeignSQL provides secure execution by using an embedded table operator along with Trusted Sessions to handle logon credential verification.

A DBA can selectively GRANT the privilege to use ExecuteForeignSQL.

To call ExecuteForeignSQL, you must first create a foreign server object that specifies the use of the TD_SYSFNLIB.QGExecuteForeignQuery table operator:

DO IMPORT WITH TD_SYSFNLIB.QGExecuteForeignQuery;

You can only associate one import and one export operator at a time with a foreign server, so to run ExecuteForeignSQL on an existing foreign server that is being used for queries, you can create a separate foreign server object with a different name to use for running ExecuteForeignSQL. You must have EXECUTE FUNCTION and SELECT privileges on this operator.

If you reference a table name, you must prepend it with the schema name.

SQL queries that return result sets, such as SELECT or HELP, are run on the remote system, but the resultant rows are not displayed. The query may continue to use CPU, memory and I/O resources on the remote system.

ExecuteForeignSQL can be used with Kerberized clusters.

Example: Using ExecuteForeignSQL

Before you call ExecuteForeignSQL, you must create the foreign server object and specify DO IMPORT WITH TD_SYSFNLIB.QGExecuteForeignQuery, as shown in the following example.

CREATE FOREIGN SERVER <fs_name> 
EXTERNAL SECURITY DEFINER TRUSTED TD_SERVER_DB.<auth_name>
USING
 LINK('<linkName>')
 version('active')
DO IMPORT WITH TD_SYSFNLIB.QGExecuteForeignQuery;

The example creates a table on the foreign server:

CALL SYSLIB.ExecuteForeignSQL(
              'create table tab1(c1 int, c2 string)', 
              'QG_Presto1');

Where QG_Presto1 is the name of the foreign server.

This example works similarly for a remote Teradata, Presto, or Hive system. Simply create a foreign server object (for example, TD_SERVER_DB.HIVE_EFSSP) and call SYSLIB.ExecuteForeignSQL.