Example libname.parm File Configurations

The following SPD Server code examples show the range of LIBNAME domains that you can create using the libnames.parm file. The code examples begin with the simplest form 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

This example contains the syntax that is required 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) 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

This example contains the syntax that is required to declare a LIBNAME domain with separate paths allocated for the domain data tables, index tables, and intermediate data files. The domain metadata continues 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')";

This example uses the domain path options DATAPATH=, INDEXPATH=, and WORKPATH=. You can achieve optimal performance with this configuration when each domain path resides on a separate disk or on network components that can take advantage of parallelism.

The INDEXPATH= option takes advantage of multiple file systems. In SPD Server 4.5, index components can take advantage of the SPD Server RANDOMPLACEDPF feature. You can configure smaller disk partitions for index space with the RANDOMPLACEDPF feature, which benefits SPD Server backup and recovery operations.

The WORKPATH= specified for the SKULIST domain enables domain users to override a default workpath that is specified in the spdsserv.parm file.

Example 3: Query-Rewrite Domain Configuration

This example shows how to use temporary tables to configure a LIBNAME domain for optimal performance when you are using the SPD Server SQL query rewrite facility.

The SPD Server SQL query rewrite facility finds the most processor-efficient method to evaluate 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.

This example creates a query rewrite domain named QRW that uses distributed temporary SPD Server tables. To use SPD Server QRW:

Create a specific domain for the query rewrite operations in the libnames.parm file. This example names the query rewrite domain QRW.
In the spdsserv.parm file, include a TMPDOMAIN=<QRW-domain-name> statement that references the QRW domain that you created in the libnames.parm file.

The libnames.parm file code (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')";

The spdsserv.parm file code (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

This example uses a combination of libnames.parm, spdsserv.parm, and user-issued SAS code that is submitted to SPD Server to create multiple domains that store the following items:

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

In this 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. At the same time, they can use temporary tables with sufficient disk space to perform large or optimized intermediate table calculations. The code specifies data and index paths to take advantage of RAID-configured disk arrays.

The 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. SPD Server users can save and delete content in USERTBLS.

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')";

The spdsserv.parm file code uses the TMPDOMAIN=SPDTEMP statement to reference the domain that was created for temporary tables. This code also uses the WORKPATH= statement to identify an array of RAID-enabled 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')";

The following SAS code is submitted to SPD Server by the user. The code connects to the PROD, USERTBLS, and SPDTEMP domains, and configures SPDTEMP as a temporary domain space. Tables in the SPDTEMP domain are 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;