Understanding the Server’s SQL Pass-Through

An SQL explicit pass-through connection is a connection to the server using the CONNECT statement from PROC SQL or another SQL-aware procedure.

You specify server SQL statements in a PROC SQL EXECUTE statement or in a subsequent SELECT * FROM CONNECTION statement. When you use an explicit SQL pass-through connection, all tables that are referenced in the SQL statement must be server tables or an error occurs. The server SQL engine must be able to successfully parse the submitted SQL statement. If the server cannot successfully parse the statement, the request fails.

The SQL code that you submit with an explicit SQL connection is passed exactly as written to the server’s SQL processor. Use SQL explicit pass-through when you want to optimize the SQL yourself, or when you want to control exactly which commands are sent to the server’s SQL processor.

SQL explicit pass-through sends SQL code very efficiently, because there is no automatic translation of your SQL code. However, there is no optimization done to improve performance of a query. You must take advantage of the optimization features of the server to ensure that the code performs as efficiently as possible (for example, parallel GROUP BY processing or BY data grouping). For information about optimizing explicit SQL, see SQL Planner Options.

An SQL implicit pass-through connection is a connection to the server using a SASSPDS LIBNAME statement with the option IP=YES. IP=YES invokes the SQL implicit pass-through facility. With this connection, the client generates automatically optimized server SQL code when you call the SAS SQL procedure. The generated SQL is automatically optimized and then “passed through” to the server’s SQL processor. For an example of an implicit pass-through request, see Create a Table with PROC SQL.

When you use an SQL implicit pass-through connection, the SAS SQL Planner parses SQL statements to determine which, if any, portions can be passed to the server SQL engine. In order for a submitted SQL statement to take advantage of SQL implicit pass-through SQL, the tables that are referenced in the SQL statement must be server tables, and the server SQL engine must be able to successfully parse the submitted SQL statement. If the server cannot successfully parse the statement, SAS SQL retries the query on the client.

If the server cannot process the SQL implicit pass-through query that is submitted through SAS PROC SQL, PROC SQL simplifies the query and iteratively retries the simplified query until it succeeds.

By default, the server does not report in the SAS log implicit pass-through queries that could not be handled by the server's SQL processor. These are reported in the server log.

To turn on the server SQL implicit pass-through error reporting in the SAS log, set the SPDSIPDB macro variable (%let SPDSIPDB=YES;). An SQL pass-through failure is recorded in the SAS log as a Note, not as an Error.