BEGIN EXPORT - FastExport

The BEGIN EXPORT command signifies the beginning of an export task and sets the specifications for the task sessions with Teradata Database.

where:

Syntax Element	Description
SESSIONS…	Maximum number of FastExport sessions that is logged on when a LOGON command is entered and, optionally, the minimum number of sessions required to run the job The max value specifies the maximum number of sessions to log on. The max specification must be greater than zero. If a SESSIONS max value is specified that is larger than the number of available AMPs, FastExport limits the sessions to one per working AMP. The default maximum, if the SESSIONS option is not used, is 4.
	The min value specifies the minimum number of sessions required for the job to continue. The min specification must be greater than zero. The default minimum, if the SESSIONS option is not used or a min value is specified, is 1. If the min value is specified as 0 which is invalid, FastExport will display an error message and terminate with the user error, RC=8" The * value specifies the maximum and minimum number of sessions. Using the asterisk character as the max specification logs on for the maximum number of sessions, one for each AMP. Using the asterisk character as the min specification logs on for at least one session, but always less than or equal to the max specification. For more information about setting number of sessions, see Table 26 on page 67.
TENACITY hours	Number of hours that FastExport tries to log on to Teradata Database When FastExport tries to log on for a new task, and Teradata Database indicates that the maximum number of utility import/export sessions are already running, FastExport: 1 Waits for six minutes, by default, or for the amount of time specified by the SLEEP option. 2 Then it tries to log on to Teradata Database again. FastExport repeats this process until it has either logged on for the required number of sessions or exceeded the TENACITY hours time period. The default value is 4. For more information about the maximum number of load utility tasks that can run, see “Concurrent Load Utility Tasks” on page 38. For information on how the TENACITY command interacts with the SLEEP command, see the SLEEP minutes entry in this table.
SLEEP minutes	Number of minutes that FastExport waits between logon attempts Default value is 6. FastExport uses the SLEEP specification in conjunction with the TENACITY specification. If the amount of time specified with the SLEEP command exceeds that of the TENACTIY command, then the sleep interval is reset and equated to the amount of time specified by the TENACITY command. For example, if the time specified with SLEEP command is 65 minutes and the time specified with TENACITY command is 1 hour, then the SLEEP time is reset to 60 minutes so that the SLEEP time does not exceed the TENACITY time. The sleep interval specified by the SLEEP command is dynamically adjusted so that the total sleep time does not exceed the amount of time specified by the TENACITY command. For example, if the time specified with the SLEEP command is 35 minutes and the time specified with the TENACTY command is 1 hour then: FastExport sleeps for 35 minutes and then attempts to log onto the Teradata Database. If the first attempt fails, then the SLEEP time is dynamically adjusted to 25 minutes (SLEEP time subtracted from TENACTITY time), so that the total SLEEP time does not exceed the TENACTIY time (60 minutes).
NOTIFY…	FastExport implementation of the notify user exit option: NOTIFY OFF suppresses the notify user exit option. NOTIFY LOW enables the notify user exit option for those events signified by “Yes” in the Low Notification Level column of Table 25. NOTIFY MEDIUM enables the notify user exit option for those events signified by “Yes” in the Medium Notification Level column of Table 25. NOTIFY HIGH enables the notify user exit option for those events signified by “Yes” in the High Notification Level column of Table 25.
EXIT name	User‑defined exit where name is the name of a user‑supplied library with a member name of _dynamn The exit must be written in C, or in a programming language with a runtime environment that is compatible with C. For an example, see “Sample Notify Exit Routine” on page 171. Note: On some versions of UNIX operating systems, ./ prefix characters may have to be added to the EXIT name specification if the module is in the current directory. A new EXIT64 keyword is added if the user intends to see large count values more than 4000, like 4294967300. It works the same way as EXIT keyword, and is used when the Notify value is higher than LOW. EXITEON keyword is added if the user intends to view Extended Object Names of 128 characters. It works the same as EXIT keyword when Extended Object names are used in script.
TEXT 'string'	A user‑supplied string of up to 80 characters that FastExport passes to the named user exit routine The string specification must be enclosed in single quote characters (').
MSG 'string'	A user‑supplied string of up to 16 characters that FastExport logs on to: The operator’s console (mainframe‑attached z/OS client systems) The system log (network‑attached UNIX and Windows client systems) The string specification must be enclosed in single quote characters (').
QUEUE option	Queue management option on mainframe‑attached z/OS client systems Note: This option is available only on z/OS, and only for tasks with a low notification specification. This option invokes an ENQ when the BEGIN EXPORT command is processed, followed by a DEQ when the significant event occurs. The option specification is one of the following: RNAME A parameter containing a quoted string of up to 255 characters. The default is TDUSER. SCOPE A parameter that is one of the following: JOB – Specifies that the QUEUE is local to the job, including all job steps. SYSTEM – Specifies that the QUEUE is global to the computer running it. SYSTEMS – Specifies that the QUEUE is global to all computers in the complex. The default is SYSTEMS. NOBLOCK A parameter specifying that if the ENQ blocks for any reason, it must return an error instead. This is a fatal error for the job. The default, an implied BLOCK (there is no BLOCK keyword), means that the ENQ will wait for the QUEUE.
DATAENCRYPTION	Keyword that enables data encryption for the FastExport job Valid options are: ON = All the requests between BEGIN EXPORT and END EXPORT commands will be encrypted. OFF = The requests between BEGIN EXPORT and END EXPORT commands will not be encrypted. This is the default. This option will apply only to the requests between BEGIN EXPORT and END EXPORT commands. Using this option overwrites the data encryption settings specified by both the runtime parameters and in the fexpcfg.dat configuration file.
DECIMALDIGITS	A user‑supplied maximum number of digits in the DECIMAL data type that can be exported. Starting from V2R6.2, the maximum number of digits in the DECIMAL data type increased from 18 to 38. Note that if a user doesn’t set the limit, the default maximum number of digits is 18. When the client is a mainframe, the user can set the limit to 31 to request automatic CAST to avoid n>31 results. Using this option overwrites the max_decimal_returned value specified in the clispb.dat file for network‑attached systems or the HSHSPB parameter for mainframe‑attached systems.
NOSTOP	If the NOSTOP option is specified and Teradata or CLIv2 does not support Large Decimal, if the user specifies a valid value for the decimaldigits parameter, FastExport does the following: Displays a message that Teradata Database or Teradata CLIv2 does not support Large Decimal Displays a warning that the decimaldigits setting is ignored Continues with the Teradata FastExport job Exits with an exit code of 4, unless there is another error with a higher exit code
	If the NOSTOP option is not specified and Teradata or CLIv2 does not support Large Decimal, if the user specifies a valid value for the decimaldigits parameter, FastExport maintains the current behavior and does the following: Displays a message that Teradata Database or Teradata CLIv2 does not support Large Decimal Terminates the FastExport job Exits with an exit code of 8 Note: If the user specifies a valid value for the max_decimal_returned parameter in clispb.dat, FastExport maintains the current behavior, regardless of NOSTOP option.
SPOOL	Tells FastExport to spool the answer set. This is the default.
NOSPOOL	Tells FastExport to try to use the NoSpool method. If the NoSpool method is not supported, FastExport issues a warning and then uses the Spool method
NOSPOOLONLY	Tells FastExport to use the NoSpool method only. If the NoSpool method is not supported, then terminate the job with an error.

Table 25 lists the events which create notifications.

Table 25: Events That Create Notifications
Event	Notification Level			Signifies
Event	Low	Medium	High	Signifies
Initialize	Yes	Yes	Yes	Successful processing of the BEGIN EXPORT command
File or INMOD open	No	No	Yes	Successful processing of the IMPORT command
Teradata Database Restart	No	Yes	Yes	A crash error from Teradata Database or CLIv2
CLIv2 error	Yes	Yes	Yes	A CLIv2 error
Teradata Database error	Yes	Yes	Yes	A Teradata Database error that terminates FastLoad
Exit	Yes	Yes	Yes	FastExport is terminating
Export begin	No	Yes	Yes	Opening the export file
Request submit begin	No	Yes	Yes	Submitting the SELECT request
Request submit end	No	Yes	Yes	Received SELECT request response
Request fetch begin	No	Yes	Yes	Fetching SELECT request results
File or OUTMOD open	No	No	Yes	Opening output file or OUTMOD
Statement fetch begin	No	No	Yes	Fetching current statement
Statement fetch end	No	No	Yes	Last record fetched for current statement
Request fetch end	No	Yes	Yes	Last record fetched for current request
Export end	No	Yes	Yes	Export task completed
Block Count	No	Yes	Yes	The total number of blocks when the no spool is in effect

Table 26 describes the things to consider when using the BEGIN EXPORT command.

Table 26: 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” on page 25) or a standard runtime parameter (see “Runtime Parameters” on page 15). 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.