The SQL Query Rewrite
Facility uses intermediate results tables to expedite original SQL
queries. You must provide adequate space for the intermediate results
tables to be generated and stored. The intermediate results tables
are formatted as SPD Server tables. Optional indexes are permitted.
Intermediate results
tables are stored in a common SPD Server LIBNAME domain that you specify.
One dedicated SQL Rewrite Facility LIBNAME domain is sufficient to
provide adequate intermediate results table storage for many concurrent
SPD Server users. Why is only one domain enough? The SQL Query Rewrite
Facility uses the SPD Server TEMP=YES option setting when it accesses
the LIBNAME domain for intermediate result tables. The TEMP=YES option
creates a processing environment in which multiple users can concurrently
create tables without name or resource contention issues. The TEMP=YES
option also automatically cleans up the contents of the working storage
area when users close their SPD Server session in a normal fashion.
SPD Server administrators
and users can specify LIBNAME domains for SQL Query Rewrite Facility
intermediate results storage. Administrators can use the TMPDOMAIN=
parameter in the spdsserv.parm file to specify the SQL Query Rewrite
Facility intermediate results storage domain:
TMPDOMAIN=<DomainName>;
The <DomainName>
value is the name of a LIBNAME domain that is defined in the libnames.parm
file that is associated with SPD Server.
SPD Server users can
override the primary TMPDOMAIN= location by specifying their own LIBNAME
domain for SQL Query Rewrite Facility intermediate results storage.
Users specify their own LIBNAME domain by using the pass-through SQL
RESET command with the TMPDOMAIN= option. For example, if an individual
SPD Server user wants to use the EMATMP LIBNAME domain for SQL Rewrite
Facility intermediate results, that user submits the following RESET
command in his or her SQL job stream:
execute(reset tmpdomain=ematmp) by sasspds;
TMPDOMAIN=EMATMP causes
the EMATMP domain to take precedence over the TMPDOMAIN= setting that
was specified in the spdsserv.parm file. Any LIBNAME domain that an
individual user submits as an SQL Query Rewrite storage location must
be defined in the libnames.parm file of the SPD server that runs the
pass-through SQL code.
Reassigning the location
of the SQL Query Rewrite Facility intermediate results storage does
not affect the TMP=YES environment setting that permits concurrent
access to tables in the domain by multiple SPD Server users. Multiple
SPD Server users can specify and share remapped TMPDOMAIN= locations
without experiencing table handling or contention issues.
Note: If the SPD Server parameter
TMPDOMAIN is not configured and the SQL query rewrite is enabled,
the query rewrite will fail with the following error:
SPDS_ERROR: Error materializing RWE context.