Autopartitioning Scheme for Sybase IQ

Autopartitioning for SAS/ACCESS Interface to Sybase IQ is a modulo (MOD) function method. For general information about this feature, see Autopartitioning Techniques in SAS/ACCESS.

SAS/ACCESS Interface to Sybase IQ places additional restrictions on the columns that you can use for the partitioning column during the autopartitioning phase. Here is how columns are partitioned.

If you select a nullable column for autopartitioning, the OR<column-name>IS NULL SQL statement is appended at the end of the SQL code that is generated for the threaded Read. This ensures that any possible NULL values are returned in the result set. Also, if the column to be used for partitioning is defined as BIT, the number of threads are automatically changed to two, regardless how DBSLICEPARM= is set.

Autopartitioning does not select a column to be the partitioning column if it appears in a SAS WHERE clause. For example, this DATA step cannot use a threaded Read to retrieve the data because all numeric columns in the table are in the WHERE clause:

data work.locemp;
set iqlib.MYEMPS;
where EMPNUM<=30 and ISTENURE=0 and
 SALARY<=35000 and NUMCLASS>2;
run;

Although SAS/ACCESS Interface to Sybase IQ defaults to three threads when you use autopartitioning, do not specify a maximum number of threads for the threaded Read in the DBSLICEPARM= LIBNAME option.

You might achieve the best possible performance when using threaded Reads by specifying the DBSLICE= data set option for Sybase IQ in your SAS operation. This is especially true if you defined an index on one of the columns in the table. SAS/ACCESS Interface to Sybase IQ selects only the first integer-type column in the table. This column might not be the same column where the index is defined. If so, you can specify the indexed column using DBSLICE=, as shown in this example.

proc print data=iqlib.MYEMPS(DBSLICE=("EMPNUM BETWEEN 1 AND 33"
"EMPNUM BETWEEN 34 AND 66" "EMPNUM BETWEEN 67 AND 100"));
run;

Using DBSLICE= also gives you flexibility in column selection. For example, if you know that the STATE column in your employee table contains only a few distinct values, you can customize your DBSLICE= clause accordingly.

datawork.locemp;
set iqlib2.MYEMP(DBSLICE=("STATE='FL'" "STATE='GA'"
  "STATE='SC'" "STATE='VA'" "STATE='NC'"));
where EMPNUM<=30 and ISTENURE=0 and SALARY<=35000 and NUMCLASS>2;
run;