Configuring Storage Space for the SQL Query Rewrite Facility

The SQL Query Rewrite Facility uses intermediate results tables to expedite original SQL queries. SPD Server administrators must provide adequate space for the generation and storage of the intermediate results tables. 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 the SPD Server administrator specifies. 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 accessing the LIBNAME domain for intermediate result tables. The TEMP=YES option creates a processing environment where multiple SPD Server users can concurrently create tables with no 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 both specify LIBNAME domains for SQL Query Rewrite Facility intermediate results storage. SPD Server administrators can configure use the TMPDOMAIN= parameter in the spdsserv.parm file to specify the SQL Query Rewrite Facility intermediate results storage domain:

TMPDOMAIN=<DomainName>;

where <DomainName> is the name of a LIBNAME domain that is defined in the SPD Servers associated libnames.parm file.

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 wanted to use the EMATMP LIBNAME domain for SQL Rewrite Facility intermediate results, the user would submit the following RESET command in his or her SQL job stream:

execute(reset tmpdomain=ematmp) by sasspds;

Setting 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 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 SQL Query Rewrite Facility intermediate results storage location does not affect TMP=YES environment setting that permits concurrent access to tables in the domain by multiple SPD Server users. This means that multiple SPD Server users can specify and share remapped TMPDOMAIN= locations without table handling or contention issues.

SPDS_ERROR: Error materializing RWE context.