Options That Affect Disk Space

ASYNCINDEX=

Specifies when creating multiple indexes on an SPD Server table whether to create the indexes in parallel.

Syntax

ASYNCINDEX=YES|NO

Default: NO

Corresponding Macro Variable

SPDSIASY

Arguments

YES

creates the indexes in parallel.

creates a single index at a time.

Description

SPD Server can create multiple indexes for a table at the same time. To do this, it launches a single thread for each index created, and then processes the threads simultaneously. Although creating indexes in parallel is much faster, the default for this option is NO. The reason is because parallel creation requires additional sort work space that might not be available.

For a complete description of the benefits and tradeoffs of creating multiple indexes in parallel, see SPDSIASY=.

Example

Because the disk workspace required for parallel index creation is available, specify for SPD Server to create, in parallel, the X, Y, and COMP indexes for table A.

PROC DATASETS lib=mydatalib;
   modify a(asyncindex=yes);
   index create x;
   index create y;
   index create comp=(x y);
   quit;

COMPRESS=

Compresses SPD Server tables on disk.

Syntax

COMPRESS=YES | NO | BINARY

Default: NO

Use in Conjunction with Table Option

IOBLOCKSIZE=

Corresponding Macro Variable

SPDSDCMP

Arguments

YES

performs the run-length compression algorithm SPDSRLLC.

performs no table compression.

BINARY

performs both character and numeric data compression.

Description

When COMPRESS= is assigned YES, SPD Server compresses newly created tables by 'blocks' based on the algorithm specified. To control the amount of compression, use the table option IOBLOCKSIZE=. This option specifies the number of rows that you want to store in the block.

When COMPRESS=BINARY is specified, both numeric data and character data are compressed.

Note: Once a compressed table is created, you cannot change its block size. To resize the block, you must PROC COPY the table to a new table, setting IOBLOCKSIZE= to the block size desired for the output table.

PARTSIZE=

Specifies the size of an SPD Server table partition.

Syntax

PARTSIZE=n

Default: 16 Megabytes

Corresponding Macro Variable

SPDSSIZE=

Arguments

is the size of the partition in megabytes.

Description

Specifying PARTSIZE= forces the software to partition (split) SPD Server tables at the given size. The actual size is computed to accommodate the largest number of rows that will fit in the specified size of n Mbytes.

Use this option to improve performance of WHERE clause evaluation on non-indexed table columns and on SQL GROUP_BY processing. By splitting the data portion of a Scalable Platform Data Server table at fixed-sized intervals, the software can introduce a high degree of scalability for these operations. The reason: it can launch threads in parallel to perform the evaluation on different partitions of the table, without the threat of file access contention between the threads. There is, however, a price for the table splits: an increased number of files, which are required to store the rows of the table.

The PARTSIZE= specification is limited by MINPARTSIZE=, an SPD Server parameter maintained by the SPD Server administrator. MINPARTSIZE= ensures that an over-zealous SAS user does not create arbitrarily small partitions, thereby generating a large number of files. The default for MINPARTSIZE= is 16 Mbytes and probably should not be lowered much beyond this value.

Note: The PARTSIZE value for a table cannot be changed after a table is created. To change the PARTSIZE, you must PROC COPY the table and use a different PARTSIZE option setting on the new (output) table.

Example

Using PROC SQL, extract a set of rows from an existing table to create a non-indexed table with a partition size of 32 Mbytes in a SAS job:

PROC SQL;
create table SPDSCEN.HR80SPDS(partsize=32)
  as select
    state,
    age,
    sex,
    hour89,
    industry,
    occup
   from SPDSCEN.PRECS
   where hour89 > 40;
quit;