Autopartitioning Scheme for DB2 under z/OS

Overview

Autopartitioning for SAS/ACCESS Interface to DB2 under z/OS is a modulo (MOD) method. Threaded Reads for DB2 under z/OS involve a trade-off. A threaded Read with even distribution of rows across the threads substantially reduces elapsed time for your SAS step. So your job completes in less time. This is positive for job turnaround time, particularly if your job needs to complete within a constrained period of time. However, threaded Reads always increase the CPU time of your SAS job and the workload on DB2. If increasing CPU consumption or increasing DB2 workload for your job are unacceptable, you can turn threaded Reads off by specifying DBSLICEPARM=NONE. To turn off threaded Reads for all SAS jobs, specify DBSLICEPARM=NONE in the SAS restricted options table.

For general information about this feature, see Autopartitioning Techniques in SAS/ACCESS.

Autopartitioning Restrictions

SAS/ACCESS Interface to DB2 under z/OS places additional restrictions on the columns that you can use for the partitioning column during the autopartitioning phase. Here are the column types that you can partition.

You must confine eligible DECIMAL columns to an integer range—specifically, DECIMAL columns with precision that is less than 10. For example, DECIMAL(5,0) and DECIMAL(9,2) are eligible.

Column Selection for MOD Partitioning

If multiple columns are eligible for partitioning, the engine queries the DB2 system tables for information about identity columns and simple indexes. Based on the information about the identity columns, simple indexes, column types, and column nullability, the partitioning column is selected in order by priority.

  1. Identity column
  2. Unique simple index: SHORT or INT, integral DECIMAL, and then nonintegral DECIMAL
  3. Nonunique simple index: SHORT or INT (NOT NULL), integral DECIMAL (NOT NULL), and then nonintegral DECIMAL (NOT NULL)
  4. Nonunique simple index: SHORT or INT (nullable), integral DECIMAL (nullable), and then nonintegral DECIMAL (nullable)
  5. SHORT or INT (NOT NULL), integral DECIMAL (NOT NULL), and then nonintegral DECIMAL (NOT NULL)
  6. SHORT or INT (nullable), integral DECIMAL (nullable), and then nonintegral DECIMAL (nullable)

If a nullable column is selected for autopartitioning, the SQL statement OR<column-name>IS NULL is appended at the end of the SQL code that is generated for one read thread. This ensures that any possible NULL values are returned in the result set.

How WHERE Clauses Restrict Autopartitioning

Autopartitioning does not select a column to be the partitioning column if it appears in a SAS WHERE clause. For example, the following DATA step cannot use a threaded Read to retrieve the data because all numeric columns in the table (see the table definition in Using DBSLICE=) are in the WHERE clause.

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

READBUFF= Restriction

If Autopartitioning is specified or being done by default and if READBUFF= is set to a value higher than 1, then a program fails if there are no columns eligible for partitioning. Only numeric columns are used for partitioning.

If no eligible columns are available for partitioning, then the following error message is sent to the SAS log, and the program ends abnormally. 

ERROR: (ACCDB2M240E) Couldn't fallback to non-sliced with READBUFF>1.

To fix this, ensure that an eligible column is available for partitioning. Alternatively, specify READBUFF=1 or set DBSLICEPARM=NONE.

Using DBSLICEPARM=

SAS/ACCESS Interface to DB2 under z/OS defaults to two threads when you use autopartitioning.

Using DBSLICE=

To achieve the best possible performance when using threaded Reads, specify the DBSLICE= data set option for DB2 in your SAS operation.

Last updated: February 3, 2026