Autopartitioning Scheme for SAP HANA

Overview

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

Autopartitioning Restrictions

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

• INTEGER, SMALLINT, and TINYINT columns are given preference.
• You can use DECIMAL, DOUBLE, FLOAT, NUMERIC, and REAL columns for partitioning. These types are converted to BIGINT by using the CAST function.

Nullable Columns

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.

Using WHERE Clauses

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

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

Using DBSLICEPARM=

SAS/ACCESS Interface to SAP HANA defaults to three threads when you use autopartitioning, but do not specify a maximum number of threads in DBSLICEPARM= to use for the threaded Read.

Using DBSLICE=

You might achieve the best possible performance when using threaded Reads by specifying the DBSLICE= option for SAP HANA in your SAS operation. This is especially true if your table uses multiple database partitions. You can also use the DBSLICE= clause to specify the WHERE clause for each partition, as shown in this example.

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

The methods and examples described in DBSLICE= work well in cases where the table that you want to read is not stored in multiple partitions in your DBMS. These methods also give 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 hanalib2.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;