Configuring Storage Space for the SQL Query Rewrite Facility

The SQL query rewrite facility uses intermediate results tables to expedite original SQL queries. Administrators must provide adequate space in order for the intermediate results tables to be generated and stored. The intermediate results tables are formatted as server tables. Optional indexes are permitted.

Intermediate results tables are stored in a common SPD Server domain. One dedicated domain is sufficient to provide adequate intermediate results table storage for many concurrent SPD Server users. The SQL query rewrite facility uses the sasspds engine’s TEMP=YES LIBNAME option when it accesses the SPD Server 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 the domain for SQL query rewrite facility intermediate results storage. Administrators can specify the domain by setting the TMPDOMAIN= parameter in the spdsserv.parm parameter file, as follows:

TMPDOMAIN=Domain-Name;

Domain-Name is the name of a domain that is defined in the libnames.parm parameter file that is associated with SPD Server.

Users can specify the location by submitting TMPDOMAIN= as a SQL pass-through RESET option. For example, if a user wanted to use the domain EMATMP for SQL Rewrite Facility intermediate results, he could submit the following statement in his SQL job stream:

execute(reset tmpdomain=ematmp) by sasspds;

The EMATMP domain takes precedence over the TMPDOMAIN= setting that was configured in the spdsserv.parm parameter file. Any domain that a user submits as an SQL Query Rewrite storage location must be defined in the libnames.parm parameter 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 TEMP=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.

SPDS_ERROR: Error materializing RWE context.