Example Libname.parm File Configurations

The following SPD Server code examples illustrate the range of LIBNAME domains that can be created using the libnames.parm file. The code examples begin with the simplest forms of LIBNAME domain declaration and increase in complexity.

Example 1: Minimum Configuration for Domain Declaration

Example 2: Specify Domain Paths for Data, Index, and Workspace Tables

Example 3: Query-Rewrite Domain Configuration

Example 4: Multiple Domain Types and Paths Configuration

Example 1: Minimum Configuration for Domain Declaration

Example 1 demonstrates the syntax necessary for the simplest form of LIBNAME domain configuration:

LIBNAME=SKULIST PATHNAME=c:\data\skulist;

This statement creates the SPD Server LIBNAME domain SKULIST. All SPD Server tables that are associated with the SKULIST domain (table data, metadata, index data, and intermediate data) will reside in the single directory that is referenced in the path specification c:\data\skulist.

Example 2: Specify Domain Paths for Data, Index, and Workspace Tables

Example 2 demonstrates the syntax necessary to declare a LIBNAME domain with separate paths allocated for the domain data tables, index tables, and intermediate data files. The domain metadata will continue to be stored in the location specified by the PATHNAME= specification.

LIBNAME=SKULIST PATHNAME=/metadata/skulist
  options="
    DATAPATH=('/data01/skulist'
              '/data02/skulist'
              '/data03/skulist'
              '/data04/skulist'
              '/data05/skulist'
              '/data06/skulist')
    INDEXPATH=('/idx01/skulist'
               '/idx02/skulist'
               '/idx03/skulist'
               '/idx04/skulist')
    WORKPATH=('/work01/skulist'
              '/work02/skulist'
              '/work03/skulist'
              '/work04/skulist')";

Example 2 uses the domain path options DATAPATH=, INDEXPATH=, and WORKPATH=. Optimal performance can be achieved in this configuration when each domain path resides on a separate disk or on network components that can take advantage of parallelism opportunities.

The INDEXPATH= is designed to take advantage of multiple file systems. SPD Server 4.5, index components can take advantage of the SPD Server RANDOMPLACEDPF feature. The RANDOMPLACEDPF feature enables administrators to configure smaller disk partitions for index space, which benefits SPD Server backup and recovery operations.

The WORKPATH= specified for the Example 2 SKULIST domain allows domain users to override the default workpath, if any, specified in the spdsserv.parm file.

Example 3: Query-Rewrite Domain Configuration

Example 3 demonstrates how to use temporary tables to configure a LIBNAME domain for performance when using the SPD Server SQL query rewrite facility.

The SPD Server SQL query rewrite facility does 'behind the scenes' work to find the most processor-efficient method to evaluate submitted SQL statements. The SQL query rewrite facility uses numerous temporary tables that are distributed across a parallelized environment to rapidly evaluate and process the SQL statements.

At the end of the SPD Server session, temporary tables are automatically deleted. Some SPD Server users might use the QRW domain for its temporary table space, even if they are not submitting code for an SPD Server SQL query rewrite job.

Example 3 creates a query rewrite domain named 'QRW' that uses distributed temporary SPD Server tables. In order to use SPD Server QRW, the following must be done:

A specific domain for the query rewrite operations should be created in the libnames.parm file. This example names the query rewrite domain 'QRW'.
The spdsserv.parm file should contain a TMPDOMAIN=<QRW-domain-name> statement that references the QRW domain that was created in the libnames.parm file.

Libnames.parm file code (note the LIBNAME=QRW statement creates a specific domain for the query rewrite tables):

LIBNAME=QRW PATHNAME=/metadata/qrw
  options="
    DATAPATH=('/data01/qrw'
              '/data02/qrw'
              '/data03/qrw'
              '/data04/qrw'
              '/data05/qrw'
              '/data06/qrw'
              '/data07/qrw'
              '/data08/qrw'
              '/data09/qrw')
    INDEXPATH=('/idx01/qrw'
               '/idx02/qrw'
               '/idx03/qrw'
               '/idx04/qrw'
               '/idx05/qrw')";

Spdsserv.parm file code (note the TMPDOMAIN=QRW statement references the domain created for query rewrite tables):

SORTSIZE=128M;
INDEX_SORTSIZE=128M;
GRPBYROWCACHE=128M;
BINBUFSIZE=32K;
INDEX_MAXMEMORY=8M;
NOCOREFILE;
SEQIOBUFMIN=64K;
RANIOBUFMIN=4K;
MAXWHTHREADS=8;
WHERECOSTING;
RANDOMPLACEDPF;
MINPARTSIZE=128M;
TMPDOMAIN=QRW;
WORKPATH=('c:\temp\work1');

Example 4: Multiple Domain Types and Paths Configuration

Example 4 uses a combination of libnames.parm, spdsserv.parm, and user-issued SAS code submitted to SPD Server to create multiple domains that house the following:

permanent production tables
permanent to semi-permanent user tables
temporary tables for intermediate calculations

In the Example 4 environment, users can access information from permanent production-type tables, manipulate the information and save, and delete the results in a semi-permanent user space, and at the same time, use temporary tables with sufficient disk space to perform large or optimized intermediate table calculations. Multiple data and index paths are specified to take advantage of RAID-configured disk arrays.

Libnames.parm file code defines the domain named PROD, which contains permanent production and historical data tables:

LIBNAME=PROD PATHNAME=/metadata/prod
  options="
    DATAPATH=('/data01/prod'
              '/data02/prod'
              '/data03/prod'
              '/data04/prod'
              '/data05/prod'
              '/data06/prod'
              '/data07/prod'
              '/data08/prod'
              '/data09/prod')
    INDEXPATH=('/idx01/prod'
               '/idx02/prod'
               '/idx03/prod'
               '/idx04/prod'
               '/idx05/prod')";

Additional libnames.parm file code defines the domain named USERTBLS, which contains semi-permanent tables for user projects. Content in USERTBLS can be saved and deleted by SPD Server users.

LIBNAME=USERTBLS PATHNAME=/metadata/usertbls
  options="
    DATAPATH=('/data01/usertbls'
              '/data02/usertbls'
              '/data03/usertbls'
              '/data04/usertbls'
              '/data05/usertbls'
              '/data06/usertbls'
              '/data07/usertbls'
              '/data08/usertbls'
              '/data09/usertbls')
    INDEXPATH=('/idx01/usertbls'
               '/idx02/usertbls'
               '/idx03/usertbls'
               '/idx04/usertbls'
               '/idx05/usertbls')";

Finally, more libnames.parm file code defines the domain named SPDTEMP, which contains temporary table space that is automatically deleted at the end of the SPD Server session.

LIBNAME=SPDTEMP PATHNAME=/metadata/spdtemp
  options="
    DATAPATH=('/data01/spdtemp'
              '/data02/spdtemp'
              '/data03/spdtemp'
              '/data04/spdtemp'
              '/data05/spdtemp'
              '/data06/spdtemp')
    INDEXPATH=('/idx01/spdtemp'
               '/idx02/spdtemp'
               '/idx03/spdtemp'
               '/idx04/spdtemp')";

Spdsserv.parm file code uses the TMPDOMAIN=SPDTEMP statement to reference the domain that was created for temporary tables, and uses the WORKPATH= statement to identify an array of RAID-enable disk paths for temporary SPD Server work tables and temporary SPD Server intermediate files.

SORTSIZE=128M;
INDEX_SORTSIZE=128M;
GRPBYROWCACHE=128M;
BINBUFSIZE=32K;
INDEX_MAXMEMORY=8M;
NOCOREFILE;
SEQIOBUFMIN=64K;
RANIOBUFMIN=4K;
MAXWHTHREADS=8;
WHERECOSTING;
RANDOMPLACEDPF;
MINPARTSIZE=128M;
TMPDOMAIN=SPDTEMP;
WORKPATH="('/work1/spdswork'
           '/work2/spdswork'
           '/work3/spdswork'
           '/work4/spdswork'
           '/work5/spdswork')";

SAS code submitted to SPD Server by the user connects to the PROD, USERTBLS, and SPDTEMP domains, and configures SPDTEMP as a temporary domain space. Tables in the SPDTEMP domain will be automatically deleted at the end of the SPD Server session.

LIBNAME PROD sasspds "PROD"
  server=hostname.hostport
  user="user-id"
  password="password"
  IP=YES;

LIBNAME USERTBLS sasspds "USERTBLS"
  server=hostname.hostport
  user="user-id"
  password="password"
  IP=YES;

LIBNAME SPDTEMP sasspds "SPDTEMP"
  server=hostname.hostport
  user="user-id"
  password="password"
  IP=YES
  TEMP=YES;