Autopartitioning Scheme for Teradata

Overview

For general information about this feature, see Autopartitioning Techniques in SAS/ACCESS.
The FastExport Utility is the fastest available way to read large Teradata tables. FastExport is NCR-provided software that delivers data over multiple Teradata connections or sessions. If FastExport is available, SAS threaded Reads use it. If FastExport is not available, SAS threaded Reads generate partitioning WHERE clauses. Using the DBSLICE= option overrides FastExport. So if you have FastExport available and want to use it, do not use DBSLICE=. To use FastExport everywhere possible, use DBSLICEPARM=ALL.
Note: FastExport is supported only on z/OS and UNIX. Whether automatically generated or created by using DBSLICE=, partitioning WHERE clauses is not supported.

FastExport and Case Sensitivity

In certain situations Teradata returns different row results to SAS when using FastExport, compared to reading normally without FastExport. The difference arises only when all of these conditions are met:
  • A WHERE clause is asserted that compares a character column with a character literal.
  • The column definition is NOT CASESPECIFIC.
    Unless you specify otherwise, most Teradata native utilities create NOT CASESPECIFIC character columns. On the other hand, SAS/ACCESS Interface to Teradata creates CASESPECIFIC columns. In general, this means that you do not see result differences with tables that SAS creates. However, but you might see result differences with tables that Teradata utilities create, which are frequently many of your tables. To determine how Teradata creates a table, look at your column declarations with the Teradata SHOW TABLE statement.
  • A character literal matches to a column value that differs only in case.
    You can see differences in the rows returned if your character column has mixed-case data that is otherwise identical. For example, 'Top' and 'top' are identical except for case.
Case sensitivity is an issue when SAS generates SQL code that contains a WHERE clause with one or more character comparisons. It is also an issue when you supply the Teradata SQL yourself with the explicit SQL feature of PROC SQL. The following examples illustrate each scenario, using DBSLICEPARM=ALL to start FastExport instead of the normal SAS read: 
/* SAS generates the SQL for you. */
libname trlib teradata user=username password=userpwd dbsliceparm=all;
proc print data=trlib.employees;
where lastname='lovell';
run;

/* Use explicit SQL with PROC SQL & supply the
SQL yourself, also starting FastExport. */
proc sql;
   connect to teradata(user=username password=userpwd dbsliceparm=all);
select * from connection to teradata
   (select * from sales where gender='f' and salesamt>1000);
quit;
For more information about case sensitivity, see your Teradata documentation.

FastExport Password Security

FastExport requires passwords to be in clear text. Because this poses a security risk, users must specify the full pathname so that the file path is in a protected directory:
  • Windows users should specify BL_CONTROL="PROTECTED-DIR/myscr.ctl". SAS/ACCESS creates the myscr.ctl script file in the protected directory with PROTECTED-DIR as the path.
  • UNIX users can specify a similar pathname.
  • MVS users must specify a middle-level qualifier such as BL_CONTROL="MYSCR.TEST1" so that the system generates the USERID.MYSCR.TEST1.CTL script file.
  • Users can also use RACF to protect the USERID.MYSCR* profile.

FastExport Setup

There are three requirements for using FastExport with SAS:
  • You must have the Teradata FastExport Utility present on your system. If you do not have FastExport and want to use it with SAS, contact NCR to obtain the Utility.
  • SAS must be able to locate the FastExport Utility on your system.
  • The FastExport Utility must be able to locate the SasAxsm access module, which is supplied with your SAS/ACCESS Interface to Teradata product. SasAxsm is in the SAS directory tree, in the same location as the sasiotra component.
Assuming you have the Teradata FastExport Utility, perform this setup, which varies by system:
  • Windows: As needed, modify your Path environment variable to include both the directories containing Fexp.exe (FastExport) and SasAxsm. Place these directory specifications last in your path.
  • UNIX: As needed, modify your library path environment variable to include the directory containing sasaxsm.sl (HP) or sasaxsm.so (Solaris and AIX). These shared objects are delivered in the $SASROOT/sasexe directory. You can copy these modules where you want, but make sure that the directory into which you copy them is in the appropriate shared library path environment variable. On Solaris, the library path variable is LD_LIBRARY_PATH. On HP-UX, it is SHLIB_PATH. On AIX, it is LIBPATH. Also, make sure that the directory containing the Teradata FastExport Utility (fexp), is included in the PATH environment variable. FastExport is usually installed in the /usr/bin directory.
  • z/OS: No action is needed when starting FastExport under TSO. When starting FastExport with a batch JCL, the SAS source statements must be assigned to a DD name other than SYSIN. This can be done by passing a parameter such as SYSIN=SASIN in the JCL where all SAS source statements are assigned to the DD name SASIN.
Keep in mind that future releases of SAS might require an updated version of SasAxsm. Therefore, when upgrading to a new SAS version, you should update the path for SAS on Windows and the library path for SAS on UNIX.

Using FastExport

To use FastExport, SAS writes a specialized script to a disk that the FastExport Utility reads. SAS might also log FastExport log lines to another disk file. SAS creates and deletes these files on your behalf, so no intervention is required. Sockets deliver the data from FastExport to SAS, so you do not need to do anything except install the SasAxsm access module that enables data transfer.
On Windows, when the FastExport Utility is active, a DOS window appears minimized as an icon on your toolbar. You can maximize the DOS window, but do not close it. After a FastExport operation is complete, SAS closes the window for you.
This example shows how to create a SAS data set that is a subset of a Teradata table that uses FastExport to transfer the data: 
libname trlib teradata user=username password=userpwd;
data saslocal(keep=EMPID SALARY);
set trlib.employees(dbsliceparm=all);
run;

FastExport and Explicit SQL

FastExport is also supported for the explicit SQL feature of PROC SQL.
The following example shows how to create a SAS data set that is a subset of a Teradata table by using explicit SQL and FastExport to transfer the data: 
proc sql;
connect to teradata as pro1 (user=username password=userpwd dbsliceparm=all);
create table saslocal as select * from connection to pro1
   (select EMPID, SALARY from employees);
quit;
FastExport for explicit SQL is a Teradata extension only, for optimizing Read operations, and is not covered in the threaded Read documentation.

Exceptions to Using FastExport

With the Teradata FastExport Utility and the SasAxsm module in place that SAS supplies, FastExport works automatically for all SAS steps that have threaded Reads enabled, except for one situation. FastExport does not handle single Access Module Processor (AMP) queries. In this case, SAS/ACCESS simply reverts to a normal single connection read. For information about FastExport and single AMP queries, see your Teradata documentation.
To determine whether FastExport worked, turn on SAS tracing in advance of the step that attempts to use FastExport. If you use FastExport, you receive this (English only) message, which is written to your SAS log: 
sasiotra/tryottrm(): SELECT was processed with FastExport.
To turn on SAS tracing, run this statement: 
options sastrace=',,,d' sastraceloc=saslog;

Threaded Reads with Partitioning WHERE Clauses

If FastExport is unavailable, threaded Reads use partitioning WHERE clauses. You can create your own partitioning WHERE clauses using the DBSLICE= option. Otherwise, SAS/ACCESS to Teradata attempts to generate them on your behalf. Like other SAS/ACCESS interfaces, this partitioning is based on the MOD function. To generate partitioning WHERE clauses, SAS/ACCESS to Teradata must locate a table column suitable for applying MOD. These types are eligible:
  • BYTEINT
  • SMALLINT
  • INTEGER
  • DATE
  • DECIMAL (integral DECIMAL columns only)
A DECIMAL column is eligible only if the column definition restricts it to integer values. In other words, the DECIMAL column must be defined with a scale of zero.
If the table that you are reading contains more than one column of the above mentioned types, SAS/ACCESS to Teradata applies some nominal intelligence to select a best choice. Top priority is given to the primary index, if it is MOD-eligible. Otherwise, preference is given to any column that is defined as NOT NULL. Since this is an unsophisticated set of selection rules, you might want to supply your own partitioning using the DBSLICE= option.
To view your table's column definitions, use the Teradata SHOW TABLE statement.
Note: Partitioning WHERE clauses, either automatically generated or created by using DBSLICE=, are not supported on z/OS. Whether automatically generated or created by using DBSLICE=, partitioning WHERE clauses is not supported on z/OS and UNIX.

FastExport versus Partitioning WHERE Clauses

Partitioning WHERE clauses are innately less efficient than FastExport. The Teradata DBMS must process separate SQL statements that vary in the WHERE clause. In contrast, FastExport is optimal because only one SQL statement is transmitted to the Teradata DBMS. However, older editions of the Teradata DBMS place severe restrictions on the system-wide number of simultaneous FastExport operations that are allowed. Even with newer versions of Teradata, your database administrator might be concerned about large numbers of FastExport operations.
Threaded Reads with partitioning WHERE clauses also place higher workload on Teradata and might not be appropriate on a widespread basis. Both technologies expedite throughput between SAS and the Teradata DBMS, but should be used judiciously. For this reason, only SAS threaded applications are eligible for threaded Read by default. To enable more threaded Reads or to turn them off entirely, use the DBSLICEPARM= option.
Even when FastExport is available, you can force SAS/ACCESS to Teradata to generate partitioning WHERE clauses on your behalf. This is accomplished with the DBI argument to the DBSLICEPARM= option (DBSLICEPARM=DBI). This feature is available primarily to enable comparisons of these techniques. In general, you should use FastExport if it is available.
The explicit SQL feature of PROC SQL supports FastExport. Partitioning of WHERE clauses is not supported for explicit SQL.
Copyright © SAS Institute Inc. All rights reserved.