Usage Notes - FastExport

The following table describes the things to consider when using the BEGIN EXPORT command.

BEGIN EXPORT Usage Notes
Topic	Usage Notes
Command Placement and Frequency	The BEGIN EXPORT command must be the first command in a group of FastExport utility commands that specify an export task. Multiple BEGIN EXPORT commands can be used in a FastExport job script, but each export task specification must begin with a BEGIN EXPORT command and end with an END EXPORT command.
Sessions Limit Specification	The number of sessions that you should specify depends on the connections to Teradata Database and the amount of data to be returned. In addition to the sessions that are used for the export task, FastExport uses two additional sessions to: Maintain the restart log table Submit Teradata SQL statements There is no general method to determine the optimal number of sessions, because it is dependent on several factors, including, but not limited, to: Teradata Database performance and workload Client platform type, performance, and workload Channel performance, for mainframe-attached systems Network topology and performance, for network-attached systems Volume of data to be processed by the application When specifying the session limit, always consider the load that the export task is placing on the channel or network connection. For example, four sessions on a mainframe-attached system, each on a different interface processor (IFP) on a channel, and all concurrently returning data can saturate a single channel. In such a case, define the maximum number of sessions as four times the number of channels that are controlled by the Teradata Director Program (TDP) that connects the sessions. Using too few sessions is likely to unnecessarily limit throughput. On the other hand, using too many sessions can increase session management overhead (and also reduce the number of sessions available to any other applications) and may, in some circumstances, degrade throughput. If the minimum number of FastExport sessions are not logged, FastExport will terminate. Regardless of the size of the Teradata Database configuration, for large repetitive production applications, it will usually be appropriate to experiment with several different session configurations to determine the best trade-off between resource utilization and throughput performance. For larger Teradata Database configurations, it is appropriate to establish an installation default for the maximum number of sessions that is greater than four sessions, but less than one session per AMP. This can be done using an installation configuration file (see FastExport Configuration File) or a standard runtime parameter (see Runtime Parameters). An installation default for number of sessions, if specified in the configuration file, can be overridden in individual FastExport job scripts, when necessary. On large to very large Teradata Database configurations, the limit of one session per AMP when * is specified may be inappropriately large.
Spool/NoSpool Modes	The NoSpool mode exports the contents of a table as fast as possible without reading the table into a spool file or distributing the file to all AMPs before extracting it. Three options for spooling are: (Default) SPOOL the data. Use the NOSPOOLONLY mode, but return an error if NOSPOOL is not supported. Use the NOSPOOL mode when possible; otherwise spool the data in Teradata Database. Possible scenarios for taking advantage of the NOSPOOL mode are as follows: Copy a table to another system in preparation for replication or to recover from a synchronization fault in a dual system environment. Copy a table to another system during a system migration (for example, replace NPARC, efficiency approaching ARC). Copy a table or a subset of a table to a test/development system where a data sample is most useful. Copy (possibly a subset of) a table to populate a Data Mart or other data store (for example, out to SAS, MOLAP tools, etc.). Limitations and functionality: NOSPOOL mode applies only to simple SELECT statements. The following are not supported: Access to nondata tables, such as SELECT DATE or SELECT USER USING modifier; instead, define restraint parameters by using a FastExport IMPORT command with supporting FIELD and FILLER commands Contains a SORT (ORDER BY), HAVING, or WITH clauses Joins Aggregations (Explain shows SUM step) TABLE functions Ordered-analytic (OLAP) functions Multiple SELECT statements or multistatement requests Statements with zero or more than one, retrieve or sampling step NOSPOOL mode only retrieves data from a single table, but the SELECT statement can be selective about which columns are exported and can constrain the job to a subset of rows. Scalar expressions/functions are allowed. The Sample and partition eliminating constraints are supported. The Activity Count returned for a regular spooled job indicates the number of affected blocks; however, for non-spooled jobs, the number of blocks is unknown, so the response message contains ActivityType (instead of Activity Count) to indicate the NOSPOOL process. Disadvantages of the NOSPOOL mode: Locks are maintained during the entire export process. Data conversion errors previously detected during the spooling phase will not be detected until the block is read, which could occur during any time during the export. Row order (because of the absence of the ORDER BY clause) may or may not be consistent between runs; therefore, NOSPOOL mode offers no guarantee of consistency.