Specifying SPD Server SQL Planner Options

The SPD Server SQL language provides reset options that you can use to configure the behavior of the SQL language. You can also use these options to configure the SPD Server facilities that function through the SQL Planner, such as the SPD Server Parallel Group-By facility, the SPD Server Parallel Join facility, and the SPD Server STARJOIN facility. You can specify SPD Server SQL reset options by using either explicit pass-through or implicit pass-through code.

Specify SQL Options By Using Explicit Pass-Through Code

Specify SQL Options By Using Implicit Pass-Through Code

Logging or Suppressing Errors when Submitting Implicit Pass-Through SQL Code

Specify SQL Options By Using Explicit Pass-Through Code

The following example shows how to use an execute(reset <reset-options>) statement in explicit SPD Server pass-through SQL code to invoke an SQL Planner, Parallel Group-By facility, Parallel Join facility, or STARJOIN facility reset option.

Most usage examples of the SQL Planner reset option in this document use explicit pass-through code. For an example of how you can declare SQL reset options by using an implicit %let spdssqlr= statement instead of an explicit execute(reset <reset-options>) statement, see Specify SQL Options By Using Implicit Pass-Through Code.

/* Explicit Pass-Through SQL Example */
/* to invoke an SQL Reset Option */

PROC SQL ;

connect to sasspds (
  dbq='MyDomainName'
  server='NameServerID'.'NameServerPortNumber'
  user='wnelson') ;

execute(reset PRINTLOG)
  by sasspds ;

execute(SQL statements)
  by sasspds ;

disconnect from sasspds ;
 quit ;

Specify SQL Options By Using Implicit Pass-Through Code

The following example shows how to use a %let spdssqlr=<reset-options> statement in implicit SPD Server pass-through SQL code to invoke an SQL Planner, Parallel Group-By facility, Parallel Join facility, or STARJOIN facility reset option.

Most usage examples of the SQL Planner reset option in this document use explicit pass-through code. The following implicit pass-through code example shows how you can declare SQL reset options by using an implicit %let spdssqlr= statement instead of an explicit execute(reset <reset-options>) statement.

/* Implicit Pass-Through SQL Example */
/* to invoke an SQL Reset Option */

%let spdssqlr= INOBS=1000 ;

PROC SQL ;
SQL statements ;

quit ;

Logging or Suppressing Errors when Submitting Implicit Pass-Through SQL Code

If SPD Server cannot process the implicit SQL pass-through query submitted to SAS PROC SQL, PROC SQL simplifies the query and iteratively retries the simplified query until it succeeds.

By default, SPD Server does not report on implicit PROC SQL pass-through queries that fail in the SAS log.

To turn on SPD Server SQL implicit pass-through error reporting in the SAS log, set SPDSIPB to YES via the SPDSIPB macro

%let
                     SPDSIPB=yes;

. An SQL pass-through failure is recorded in the SAS log as a Note, not as an Error.