SPD Server Parameter File Options :: SAS(R) Scalable Performance Data Server 5.1: Administrator's Guide, Second Edition

BINBUFSIZE=

BINBUFSIZE= specifies the amount of memory to allocate for each bin buffer during a sort operation. During the sorting process, SPD Server writes blocks of sorted rows (called spill bins) to disk. The final step of the 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 on 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 SORTSIZE=2 GB, the SORTSIZE= value 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 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.

FEDSQLSIZE=

specifies the number of bytes in virtual address space to allocate to progressive threaded kernel memory calls in SPD Server. The default amount is 4 GB.

Usage:

FEDSQLSIZE=4GB ;

FMTNAMENODE=

specifies the server on which the user-defined formats are stored. Use FMTNAMENODE= with FMTDOMAIN= and FMTNAMEPORT=.

Usage:

FMTNAMENODE=d8488 ;

FMTNAMEPORT=

specifies the port number of the server on which the user-defined formats are stored. Use FMTNAMEPORT= with FMTDOMAIN= and FMTNAMENODE=.

Usage:

FMTNAMEPORT=5400 ;

GRPBYROWCACHE=

specifies the maximum number 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 on GRPBYROWCACHE to cache groups in memory; each thread receives 1/MAXWHTHREADS= of the cache.

When a thread accumulates enough distinct groups to fill its cache, the groups are moved to secondary bins. At the completion of the parallel BY-group processing, the parallel group aggregations in memory and in secondary bins are merged to produce the final sorted results. If you omit the GRPBYROWCACHE option, the default value is a 2-megabyte cache per thread. You can improve aggregation performance with large numbers of groups by increasing the default value. However, you can potentially allocate more memory than is needed for caching, which diminishes the resources that are available for processing by the excess amount of assigned memory.

Usage:

GRPBYROWCACHE= <memory-cache-size> ;

IDLE_TIMEOUT=

specifies the interval of idle time that lapses before the SPD Server client process automatically terminates the client connection. When IDLE_TIMEOUT= 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=

restricts the amount of memory that is allocated for each open index. This option affects Read operations on SPD Server tables.

Usage:

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

INDEX_SORTSIZE=

controls the amount of memory that is allocated for creating asynchronous (parallel) sort indexes or appends. This value is divided by the number (n) of indexes that are to be created or appended in parallel; each index receives 1/nth of the allocated memory.

Usage:

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

LDAPSERVER=

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

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 is LDAP_AUTH_SASL. The Simple Authentication and Security Layer (SASL) performs LDAP_AUTH_SASL and SPD Server user authentication using Digest-MD5.

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. You can obtain RDN strings from the LDAP server administrator when you are configuring the SPD Server to use LDAP authentication.

Usage:

LDAPBINDDN=<ldap_server_binddn_string> <RDN-string> ;

MAXGENNUM=

specifies the maximum number of member tables that can be created in 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 not performing pre-evaluation, based on the ratio of the number of segments that contain candidates to the total number of segments in the file. If the percentage of possible segments exceeds MAXSEGRATIO=, pre-evaluation is not performed and all of the segments are searched to resolve the WHERE clause. If you omit this value, 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 caution when you assign a value 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, 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 can be a resource-intensive process, and 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 you omit this value, SPD Server assigns the value of MAXWHTHREADS= to MAXSORTTHREADS=.

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 an 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 is 16 MB. The most common values for the MINPARTSIZE parameter are in the range 128 MB–256 MB.

Usage:

MINPARTSIZE= <minimum-partition-size> ;

MINPORTNO= and MAXPORTNO=

specifies a range of port numbers that can the SPD Server user proxy processes can use to communicate with the client. You must set both the MINPORTNO= and the MAXPORTNO= option. This option supports the use of SPD Server ports through an Internet firewall, in order to limit the range of ports that are used by the server. If you omit MINPORTNO= and MAXPORTNO=, 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 value is NOBYINDEX, which indicates that an index is not used. The [NO]BYINDEX server parameter is used only when the SPDSNBIX= macro is set to NO (the default value).

Usage:

BYINDEX ; NOBYINDEX ;

[NO]COREFILE

controls whether the LIBNAME proxy creates a core file when an unexpected process trap occurs. 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 5.1.

When you specify NONLSTRANSCODE, 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 5.1 (with SAS 9.4) performs normal server-side processing of tables and ignores the CEI of the table. SAS 9.2, however, reads the CEI value of the table and performs transcoding for any pertinent character data in the rows that are returned from SPD Server.

When you specify NLSTRANSCODE, 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. SPD Server also ensures that data is returned to SAS 9.2 using the same encoding that the SAS 9.2 session uses.

Usage:

NLSTRANSCODE ;

NONLSTRANSCODE ;

[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 5.1, include a NORANDOMPLACEDPF statement in your spdsserv.parm file.

Usage:

RANDOMPLACEDPF;

RANIOBUFMIN=

specifies the minimum random I/O buffer size. This 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. This value 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 that SORTSIZE= aallocates is divided evenly among the sort threads. For best results, specify SORTSIZE= values in the range 256 MB–1 GB per parallel sort thread, or between 256 * MAXSORTTHREADS= and 1 GB * MAXSORTTHREADS=.

Usage:

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

STARSIZE=

controls the amount of memory to allocate for STARJOIN operations. During STARJOIN operations, the temporary results of Phase 1 of the IN-SET STARJOIN strategy are cached in memory for use by Phase 2 if there is sufficient STARSIZE= memory. Caching Phase 1 temporary results can result in significant performance improvements for STARJOIN. If you omit STARSIZE=, STARJOIN uses the SORTSIZE= option to determine the memory to use for Phase 1 caching.

Usage:

STARSIZE= <memory-allocated-for-STARJOIN-operations> ;

SQLOPTS=

overrides SQL default options for each SQL connect when you use the SQLOPTS= statement with an SQL RESET command. If you omit SQLOPTS=, SQL default options apply. See the For more information about SPD Server SQL RESET options, see Specifying SPD Server SQL Planner Options in SAS Scalable Performance Data Server: User's Guide.

Usage:

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

TMPDOMAIN=

specifies an SPD Server domain that is defined in the libnames.parm file. The SQL query rewrite facility uses this domain 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 you specify multiple paths, enclose the complete path statement in double quotation marks.

Usage:

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