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 Teradata 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.
- The name of the database where the stored procedure is located.
- A valid Teradata SQL expression.
- The query is passed through unparsed to the foreign server.
- The name of the foreign server.
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 executed 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 uses a proxyuser, so the execution of commands on behalf of DBC is not supported.
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 example below.
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.