SPD Server Macro Variables

Overview of Macro Variables

You can use global macro variables in SPD Server to simplify your work. Global macro variables use default values set by SPD Server and operate in the background. You can make global changes to the values of macro variables in your code by specifying a new default setting for the specified variable. The new default setting is applied to all macro variables in the code that you submit to SPD Server. You can override the setting for a single macro variable by using a table option to change the setting for only the specified table.
The default macro variable values automate sophisticated processing decisions. The default settings provide good performance. However, optimal performance requires intelligent changes to some macro variable default settings. When you make changes to the macro variable default settings, you should find the best processing opportunity for the type of data that you have.
Learning the best way to set SPD Server macro variables and options takes time. Sometimes, performance testing is the only way to determine whether changing a setting improves processing performance. Performance testing is time well spent. After you quantify performance parameters under various macro variable settings, you can customize SPD Server so that it solves your real business or data problems with maximum efficiency.
Each SPD Server installation is different. You might want to change many values, or just a few. When you make changes, you will learn that macro variables are flexible and easy to manipulate.
Use a %LET statement to change macro variable values. You can place the macro variable assignment anywhere in the open code of a SAS program except in the data lines. The most convenient location for your %LET statements to initialize macro variables is in your autoexec.sas file or at the beginning of a program. The macro variable assignment is valid for the duration of your session or the executing program. Macro variable values remain in effect until they are changed by a subsequent assignment.
Assignments for macro variables with YES or NO arguments must be uppercase.
Because SPD Server macro variables operate behind the scenes, you cannot query SPD Server to determine the status of a macro variable. SAS does not know about the status of macro variables. If you want to know which SPD Server macro variables are in effect, or what their default values are, you can use PROC SPDO.

Macro Variables and Corresponding Table Options

When you need to apply the action to a single table, and the macro variable applies the action globally to all tables, you should use a table option instead of the macro variable setting. A table option is more selective because you can turn the macro variable function on or off for a single table.

Summary of SPD Server Macro Variables

This section summarizes the SPD Server macro variables, and groups them by function.

Variable for a Client and Server Running on the Same UNIX Machine

SPDSCOMP=
specifies to compress the data when sending a data packet through the network.

Variable for Compatibility with the Base SAS Engine

SPDSBNEQ=
specifies the output order of table rows with identical values in the BY column.

Variables for Miscellaneous Functions

SPDSEOBS=
specifies the end row (observation) number in a user-defined range when processing a table.
SPDSSOBS=
specifies the start row (observation) number in a user-defined range when processing a table.
SPDSUSAV=
specifies to save rows with nonunique (rejected) keys to a separate SAS table when appending to tables with unique indexes.
SPDSUSDS=
returns the name of a hidden SAS table generated by the SPD Server, which stores rows with identical (nonunique) table values.
SPDSVERB=
specifies when executing a PROC CONTENTS statement to provide more information specific to SPD Server indexes that are associated with the table. Examples of information include ACL information, index information, PARTSIZE= value, and so on.
SPDSFSAV=
specifies to retain the table if an abnormal condition is encountered when the table is being created. (Usually SAS closes and deletes these tables.)
SPDSEINT=
specifies disconnect behavior for the SQL pass-through EXECUTE() statement.

Variables for Sorts

SPDSBSRT=
specifies for the SPD Server to perform a sort whenever it encounters a BY clause and there is no index available.
SPDSNBIX=
specifies whether to turn BY sorts with an index on or off.
SPDSSTAG=
specifies whether to use tagged sorting for PROC SORT or BY processing.

Variables for WHERE Clause Evaluations

SPDSTCNT=
specifies the number of threads to be used for WHERE clause evaluations.
SPDSEV1T=
specifies whether the data returned from WHERE clause evaluations that use an index should be in strict row (observation) order.
SPDSEV2T=
specifies whether the data returned from WHERE clause evaluations that do not use an index should be in strict row (observation) order.
SPDSWDEB=
specifies when evaluating a WHERE expression whether WHINIT—the WHERE clause planner—should display a summary of the execution plan.
SPDSIRAT=
controls whether to perform segment candidate pre-evaluation when WHERE clause processing with enhanced bitmap indexes.

Variables That Affect Disk Space

SPDSCMPF=
specifies to add a number of bytes to a compressed block as growth space.
SPDSDCMP=
specifies to compress SPD Server tables on the disk.
SPDSIASY=
specifies whether to create the indexes in parallel when creating multiple indexes on an SPD Server table.
SPDSSIZE=
specifies the size of an SPD Server table partition.

Variables to Enhance Performance

SPDSNETP=
sizes a buffer in server memory for the network data packet.
SPDSSADD=
specifies whether to apply a single row or multiple rows when appending to a table.
SPDSSYRD=
specifies whether to perform data streaming when reading a table.
SPDSAUNQ=
specifies whether to cancel an append operation if uniqueness is not maintained.