spdsserv.parm Options

BINBUFSIZE=

BINBUFSIZE= specifies the amount of memory to allocate for each bin buffer during a sort operation. When sorting, SPD Server writes blocks of sorted rows (called spill bins) to disk. The final step of the sorting process reads the contents of the spill bins to perform final row ordering. BINBUFSIZE= specifies the amount of memory that is allocated to each spill bin during final row ordering. The spill bins use the memory buffer to read rows back into memory during interleaving.

The number of spill bins depends on the size of the table, the amount of memory specified in SORTSIZE=, and the number of threads that SPD Server uses to perform sorting. For example, if you sort a 10 GB table using two concurrent threads, and your SORTSIZE= is configured as 2 GB, the SORTSIZE= memory is divided between the two concurrent threads. Each thread reads 1 GB of row data from the table into memory. In this case, each 1 GB block of row data comprises a spill bin. The rows in the spill bin are sorted, and then written to disk. After all of the rows in the table have been sorted and written to disk, the sorting process reads the spill bins back into memory for final processing. In the example, a total of 10 spill bins and 10 buffer areas are used to interleave the sorted rows.

Usage

BINBUFSIZE= <bin-buffer-size> ;

Note: If you specify a value that is smaller than the record length of the spill bin, a bin buffer large enough to hold one record is created automatically.

FMTDOMAIN=

specifies a user defined format. The FMTDOMAIN= option specifies the domain where the user defined format is stored. To use user defined formats, you must create a domain that is named FORMATS. FMTDOMAIN= is used with FMTNAMENODE= and FMTNAMEPORT=.

Usage

FMTDOMAIN=FORMATS ;

FMTNAMENODE=

specifies a user defined format. The FMTNAMENODE= option specifies the server on which the user defined formats are stored. FMTNAMENODE= is used with FMTDOMAIN= and FMTNAMEPORT=.

Usage

FMTNAMENODE=d8488 ;

FMTNAMEPORT=

specifies a user defined format. The FMTNAMEPORT= option specifies the port number of the server on which the user defined formats are stored. FMTNAMEPORT= is used with FMTDOMAIN= and FMTNAMENODE=.

Usage

FMTNAMEPORT=5400 ;

GRPBYROWCACHE=

specifies the maximum amount of memory threads that are used during parallel group aggregations. The parallel group SELECT statement uses multiple threads up to the MAXWHTHREADS= limit to perform parallel group aggregations. The threads equally share the memory that is specified using GRPBYROWCACHE to cache groups in memory; each thread receives 1/MAXWHTHREADS= of the cache.

Once a thread accumulates enough distinct groups to fill its cache, the groups are moved to secondary bins. At the completion of the parallel GROUP BY, the parallel group aggregations in memory and secondary bins are merged to produce the final sorted results. If the GRPBYROWCACHE option is not specified, the default value is a 2-megabyte cache per thread. Increasing the default value can result in improved aggregation performance with large numbers of groups. The trade-off is potentially allocating more memory than is needed for caching, which diminishes the available resources for processing by the excess amount of assigned memory.

Usage

GRPBYROWCACHE= <memory-cache-size> ;

IDLE_TIMEOUT=

specifies the interval of idle time that is permitted before the SPD Server client process automatically terminates the client connection. When IDLE_TIMEOUT= is specified as a value that is greater than 0, the option is enabled. If the value is less than or equal to 0, SPD Server does not enable idle timeouts. The default value is 0.

Usage

IDLE_TIMEOUT= <timeout_seconds> ;

INDEX_MAXMEMORY=

affects Read operations on SPD Server tables. The specified value restricts the amount of memory to allocate for each open index.

Usage

INDEX_MAXMEMORY= <maximum-allocated-index-memory> ;

INDEX_SORTSIZE=

controls the amount of memory to allocate for asynchronous (parallel) sort index creation or appends. The specified value is divided by the number (n) of indexes to be created or appended in parallel; each receives 1/nth of the memory allocation.

Usage

INDEX_SORTSIZE= <allocated-async-sort-index-memory> ;

LDAPSERVER=

specifies the network IP address or the host machine for the LDAP server. This is usually the same value as the IP address of the SPD Server host. The default value for LDAPSERVER= is the IP address of the SPD Server host.

Usage

LDAPSERVER=<ldap_server_host_ip> <LDAP-Server-IP-address-or-LDAP-Server-name>;

LDAPPORT=

specifies the TCP/IP port that is used to communicate with the LDAP server. The default value is LOCAL_HOST or port 389.

Usage

LDAPPORT=<ldap_server_tcpip_port_number> <port-number-or-port-name> ;

LDAPBINDMETH=

indicates the LDAP authentication security level. The default value for LDAPBINDMETH= is ANONYMOUS. The ANONYMOUS value is not recommended for use with secure environments. The following are valid LDAPBINDMETH= values:

LDAP_AUTH_SIMPLE - The SPD Server user password is sent to the LDAP server in clear-text.
LDAP_AUTH_SASL (UNIX platforms only) - SPD Server user authentication is performed via the SASL using the Digest-MD5 mechanism.
LDAP_AUTH_NEGOTIATE (Windows platforms only) - The most appropriate authentication method is chosen from a list of available methods on both the SPD Server and LDAP server machines. Note that LDAP_AUTH_NEGOTIATE is not guaranteed to be secure.

Usage

LDAPBINDMETH=<LDAP_SERVER_BINDMETH_STRING> <LDAP-bind-method-string> ;

LDAPBINDDN=

specifies the Relative Distinguished Name (RDN) or the location in the LDAP server database where the information for the connecting client is stored. The SPD Server administrator can obtain RDN strings from the LDAP server administrator when the SPD Server is being configured to use LDAP authentication.

Usage

LDAPBINDDN=<ldap_server_binddn_string> <RDN-string> ;

MAXGENNUM=

specifies the maximum number of member tables that can be created within an SPD Server cluster table.

Usage

MAXGENNUM= <maximum-number-of-member-tables> ;

MAXSEGRATIO=

controls segment candidate pre-evaluation for WHERE clause predicates with a hybrid index. The WHERE clause planner pre-evaluates the segment candidates for the predicate. Only the segment candidates are searched to resolve the WHERE clause. Some queries can benefit from no pre-evaluation, based on the ratio of the number of segments containing candidates, to the total number of segments in the file. If the percentage of possible segments exceeds the specified value, pre-evaluation is not performed and all of the segments are searched to resolve the WHERE clause. If a value is not specified, the default value is 75%.

Usage

MAXSEGRATIO= <maximum-ratio-of-segment-
candidates-to-segments> ;

MAXSORTTHREADS=

specifies the number of parallel threads to create for a parallel sort operation.

Threading for sorting data in parallel is a resource-intensive process that behaves differently from threaded processing. Use MAXSORTTHREADS= to specify the number of parallel threads that you want to create for each parallel sort. Use caution when assigning values for MAXSORTTHREADS=. If a parallel sort uses one thread for every CPU on the server, the sort job might starve other jobs of resources.

For better performance during parallel sort operations, SPD Server recommends that you configure values for SORTSIZE= (in MB) and MAXSORTTHREADS= (in number of threads) so that the ratio of SORTSIZE= to MAXSORTTHREADS= is between 256 MB per thread and 1 GB per thread.

Use MAXSORTTHREADS= with MAXWHTHREADS= to balance your system load. Parallel sorting tends to be a resource-intensive process, while parallel WHERE processing tends to be more I/O intensive. In most cases, parallel WHERE processing tasks require more threads than parallel sorting tasks.

If no value is specified for MAXSORTTHREADS=, SPD Server by default assigns MAXSORTTHREADS= the value that MAXWHTHREADS= is set to.

Usage

MAXSORTTHREADS= <maximum-number-of-parallel-sort-threads> ;

MAXWHTHREADS=

specifies the number of parallel threads to launch for a WHERE clause evaluation.

Usage

MAXWHRTHREADS= <maximum-number-of-parallel-threads> ;

MINPARTSIZE=

ensures that large SPD Server tables cannot be created with arbitrarily small partition size. Large SPD Server tables with small partition sizes create an excessive number of physical files, which increases clutter and degrades I/O performance. The default value for MINPARTSIZE= is 16 MB. The most common values for the MINPARTSIZE parameter range from 128 MB to 256 MB.

Usage

MINPARTSIZE= <minimum-partition-size> ;

MINPORTNO=/MAXPORTNO=

specifies a range of port numbers that can be used by the SPD Server user proxy processes to communicate with the client. You must set both the MINPORTNO= and the MAXPORTNO= option. This option is provided to support the use of SPD Server ports through an Internet firewall, in order to limit the range of ports that will be used by the server. If MINPORTNO= and MAXPORTNO= are not specified, then the SPD Server user proxy processes use any port that is available to communicate with the client.

Usage

MINPORTNO=<lower-port-range-number> ; 
MAXPORTNO= <upper-port-range-number> ;

[NO]BYINDEX

Controls whether to use an index for a BY-sort. The default is NOBYINDEX, which does not allow use of the index. The [NO]BYINDEX server parameter is used only when the SPDSNBIX= macro is set to NO (default value).

Usage

BYINDEX ; NOBYINDEX ;

[NO]COREFILE

controls whether the LIBNAME proxy creates a core file in the event of an unexpected process trap. The default value is NOCOREFILE.

Usage

COREFILE ; NOCOREFILE ;

[NO]LDAP

turns SPD Server LDAP user authentication on or off. If the LDAP option is found or set during SPD Server start-up, then the SPD Server host creates a context for LDAP user authentication.

Usage

LDAP ;

NOLDAP;

[NO]NLSTRANSCODE

enables or suppresses the server-side SPD Server NLS processing. The default value for NLSTRANSCODE is NONLSTRANSCODE if the option is not found in the spdsserv.parm file. The default spdsserv.parm file for SPD Server does not contain the NLSTRANSCODE option. Users must explicitly activate server-side transcoding in SPD Server 4.5.

Usage

NLSTRANSCODE ;

NONLSTRANSCODE ;

When NONLSTRANSCODE is specified, SPD Server treats all character column data as 8-bit raw bytes internally, regardless of the table's specified character set encoding (CEI). SPD Server 4.5 (with SAS 9) performs normal server-side processing of tables, ignoring the CEI of the table. SAS 9.2, however, will read the CEI value of the table and performs transcoding for any pertinent character data in the rows returned from SPD Server.

When NLSTRANSCODE is specified, SPD Server reads the table's CEI value and the CEI value of the associated SAS 9.2 session. SPD Server does not perform transcoding if these values are the same. If the CEI values are different, SPD Server restricts the types of WHERE clause predicates that are permitted in indexed lookups, and SPD Server ensures that data is returned to SAS 9.2 using the same encoding that the SAS 9.2 session uses.

[NO]WHERECOSTING

controls whether to use dynamic WHERE costing. The default value is NOWHERECOSTING. When dynamic WHERE costing is not enabled, SPD Server uses the rules-based heuristic WHINIT.

Usage

WHERECOSTING ;
NOWHERECOSTING ;

RANDOMPLACEDPF

invokes random placement of the initial data partition for all tables in a domain. The random placement strategy manages large tables efficiently and balances data loads without losing disk space. RANDOMPLACEDPF is enabled by default. To disable RANDOMPLACEDPF in SPD Server 4.5, include a NORANDOMPLACEDPF statement in your spdsserv.parm file.

Usage

RANDOMPLACEDPF;

RANIOBUFMIN=

specifies the minimum random I/O buffer size. The specified value becomes the minimum I/O buffer size that is used by the proxy when it performs random I/O and table requests.

Usage

RANIOBUFMIN= <minimum-random-i/o-buffer-size> ;

SEQIOBUFMIN=

specifies the minimum sequential I/O buffer size. The specified value specified becomes the minimum I/O buffer size that is used by the proxy when it performs sequential I/O and table requests.

Usage

SEQIOBUFMIN= <minimum-sequential-i/o-buffer-size> ;

SORTSIZE

controls the amount of memory to allocate for sort operations. During parallel sort operations, the memory allocated by SORTSIZE= is divided evenly among the sort threads. For best results, SPD Server recommends that you specify SORTSIZE= values that are between 256 MB and 1 GB per parallel sort thread, or between 256 * MAXSORTTHREADS= and 1 GB * MAXSORTTHREADS=.

Usage

SORTSIZE= <memory-allocated-for-sort-operations> ;

SQLOPTS=

overrides SQL default options for each SQL connect when it is specified on an SQL RESET command. If SQLOPTS= is not specified, SQL default options apply. See the SQL RESET command for possible RESET options you can set.

Usage

SQLOPTS= "RESET <SQL-option> [ <SQL-option>]" ;

TMPDOMAIN=

specifies an SPD Server domain that is defined in the libnames.parm file, which the query rewrite facility uses to store intermediate tables.

Usage

...
LIBNAME=qrw pathname=/IDX1/spdsmgr/spds45_sasdqh/qrw ;
...
TMPDOMAIN=qrw ;

WORKPATH=

specifies the LIBNAME proxy path for work files. If you think that the work files might overflow a single file system, you can specify multiple paths. When specifying multiple paths, enclose the complete path statement in double quotation marks.

Usage

WORKPATH= "('DirPath1' 'DirPath2' ...)";