For general information about this feature, see Autopartitioning Techniques in SAS/ACCESS. For platform-specific details and special considerations, see your Teradata documentation.
The FastExport Utility is the fastest available way to read large Teradata tables. FastExport is software that Teradata provides 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=.
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:
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, 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.
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 might be an issue when SAS generates SQL code that contains a WHERE clause with one or more character comparisons. It might also be an issue when you provide 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=myusr1 password=mypwd1 dbsliceparm=all;
proc print data=trlib.employees;
where lastname='lovell';
run;
/* Use explicit SQL with PROC SQL & provide the
SQL yourself, also starting FastExport. */
proc sql;
connect to teradata(user=myusr1 password=mypwd1 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.
Legacy or non-TPT FastExport requires passwords to be in clear text. Because this poses a security risk, users must specify the full path name so that the file path is in a protected directory:
There are three requirements for using non-TPT FastExport with SAS:
Assuming you have the non-TPT FastExport Utility in Teradata, perform this setup, which varies by system:
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.
To use non-TPT 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=myusr1 password=mypwd1;
data saslocal(keep=EMPID SALARY);
set trlib.employees;
run;Legacy or non-TPT 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=myusr1 password=mypwd1);
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.
With the legacy or non-TPT FastExport utility on Teradata and the SasAxsm module in place that SAS supplies, FastExport works automatically for all SAS steps where threaded Reads are enabled, except for one situation. Non-TPT 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 the FastExport utility and single AMP queries, see your Teradata documentation.
To determine whether non-TPT FastExport worked, turn on SAS tracing in advance of the step that attempts to use this utility. If you use non-TPT FastExport, you receive this (English only) message, which is written to your SAS log:
teradata/tryottrm(): SELECT was processed with FastExport.
To turn on SAS tracing, run this statement:
options sastrace=',,,d' sastraceloc=saslog;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:
A DECIMAL column is eligible only if the column definition restricts it to integer values. In other words, the DECIMAL column must be specified 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 specified as NOT NULL. Because this is an unsophisticated set of selection rules, you might want to provide your own partitioning using the DBSLICE= option.
To view your table's column definitions, use the Teradata SHOW TABLE statement.
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 WHERE clauses is not supported for explicit SQL.