Teradata DBA: An Introduction to FastExport

FastExport STRUCTURE :

1. .ACCEPT < variable > FROM FILE < filename >;

7. .BEGIN EXPORT

SESSIONS < limit >

TENACITY < hours >

SLEEP < minutes >;

8. .LAYOUT < tablename >_Source;

.FIELD < attributename1 > * < datatype >;

.FIELD < attributename2 > * < datatype >;

.FILLER < attributename > * < datatype >;

9. .IMPORT INFILE < datafilename >

FORMAT VARTEXT '< delimiter >'

LAYOUT < layoutname >;

10. .EXPORT OUTFILE < filename >;

SELECT < fieldname1 >, < fieldname2 >

FROM < tablename >

WHERE < condition >;

11. .END EXPORT;

12. .IF < condition > THEN

< statement >;

.ENDIF;

13. .LOGOFF;

.EXPORT OUTFILE Field [OUTMOD ]

[MODE RECORD | INDICATOR ]

[FORMAT FASTLOAD|BINARY|TEXT|UNFORMAT]

[OUTLIMIT record_count]

[MLSCRIPT field ];

1. Script filename

- The suggested format for the script filename is <tablename>.fxp. Here, tablename indicates the name of the table that the script would be extracting data from and the extension .fxp indicates a FastExport script.

2. Logtable Name

- The FastExport utility uses the information in the restart log table to restart jobs that are halted because of a Teradata RDBMS or client system failure.

- The suggested format of the Logtable name is <logtablename>_lg. Here, <logtablename> is generally the name of the table that is referenced by the script. _lg indicates that the table is a logtable.

- The <databasename> in the statement indicates the database in which the logtable would be created or would be accessed from if required.

3. Run File command

.RUN FILE /load/admin/logon.fxp;

- This command is used to run a file called logon.fxp. It contains the .Logon command along with the username and password used to log into the database.

- An alternative to the above command is to directly include the .Logon command in the FastExport script file. However, this is not a suggested approach due to security reasons since by giving the username and password in the FastExport script, anyone can have access to the database to the extent to which the user has access rights.

- Using a logon.fxp file in the FastExport script allows the details of the username and password to be hidden from the user. Apart from that, in case of a need to change the password, the change has to be made only in the logon file and not in all the FastExport scripts.

4. Database command

DATABASE < databasename >;

- This command sets your default database to <databasename>. Henceforth, any objects referred to in SQL statements which are not preceded by a database name will refer to the default database. This command is optional.

5. .Set command

.SET < variable > TO < expression >;

- The SET command assigns a data type and a value to a FastExport utility variable. This command is optional.

6. .Accept command

.ACCEPT < variable > FROM FILE < filename >;

- The .ACCEPT command sets FastExport utility variables to the value of a specified External data source and valid character fields or to internal environmental variables.

- The .ACCEPT command accepts from a single data record from an external source. This command is optional.

7. Begin Export command

.BEGIN EXPORT

- This command indicates the beginning of the FastExport session.

SESSIONS < limit >

- Maximum, and optionally, minimum number of sessions the utility may use - defaults to 4 for UNIX FastExport.

- The utility will log on two additional SQL sessions: one for the Restart Log and one for the SELECT.

TENACITY < hours >

- Number of hours FastExport will try to establish a connection to the system. The default is 4 hours.

SLEEP < minutes >

- Number of minutes that FastExport will wait between logon attempts. The default is 6 minutes.

8. .Layout command

.LAYOUT < tablename >_Source;

.FIELD < attributename > * < datatype >;

.FILLER < attributename > * < datatype >;

- The LAYOUT command, used with an immediately following sequence of FIELD, FILLER, and TABLE commands, specifies the layout of the input data records.

- The FILLER command specifies a field that is not sent to the Teradata RDBMS as part of the input record that provides data values for the constraint parameters of the SELECT statement. This command is optional.

9. .Import command

.IMPORT INFILE < datafilename >

FORMAT VARTEXT '< delimiter >'

LAYOUT < layoutname >

- The IMPORT INFILE command specifies the import data filename along with its path.

- FORMAT VARTEXT '<delimiter>' statement specifies the delimiter that is used in the data files for separation of the attribute fields. The preferred delimiters are '~' or '|'. ',' can be used as a delimiter but is not suggested as a variable value may contain ','.

- When using the VARTEXT specification, VARCHAR, VARBYTE and LONG VARCHAR are the only valid data type specifications you can use in the FastExport layout FIELD and FILLER commands.

- This statement specifies the layout to be used while receiving parameters from the source file. This command is optional.

10. .Export command

.EXPORT OUTFILE < filename >;

- The EXPORT command provides the client system destination and file format specifications for the export data retrieved from the Teradata RDBMS and, optionally, generates a MultiLoad script file that you can use to reload the export data.

Other options available with .EXPORT command:

MODE RECORD | INDICATOR

- If RECORD, then indicator bytes for NULLs are not included in exported data.

- If INDICATOR, then indicator bytes for NULLs are included in exported data.

BLOCKSIZE < integer >

- Defines the maximum block size to be used in returning exported data. Default (and maximum) is 63.5 KB.

FORMAT FASTLOAD | BINARY | TEXT | VARTEXT | UNFORMAT

- Record format of the export file.

OUTLIMIT < record_count >

- Defines the maximum number of records to be written to the output host file.

MLSCRIPT < filename >

- FastExport generates a MultiLoad script that can be used later to load the exported data back into a Teradata system.

SELECT < fieldname1 >, < fieldname2 >

FROM < tablename >

WHERE < condition >;

- The SELECT statement specifies the columns and rows from which the data is to be exported from the respective tables.

11. .End Export command

.END EXPORT;

- Delimits a series of commands that define a single EXPORT action.

- Causes the utility to send the SELECT(s) to the Teradata Database.

12. Conditional Expressions

.IF < condition > THEN

< statement >;

.ENDIF;

- The IF, ELSE and ENDIF commands provide conditional control of execution processes.

- These conditional expressions can be written either before the .BEGIN EXPORT command or after the .END EXPORT command.

- The conditional expressions statements are optional. These expressions can be used anywhere within the script except between the BEGIN EXPORT and END EXPORT command.

13. .Logoff command

- This command is used to logout from the database and optionally with a specific return code.

- When a FastExport job terminates, and you have not specified an optional return code value, the utility returns a code indicating the way the job completed:

CODE Signifies:

00 the job completed normally.

04 a warning condition occurred. Warning conditions do not terminate the job.

08 a user error, such as a syntax error in the FastExport job script, terminated the job.

12 a fatal error terminated the job. A fatal error is any error other than a user error.

16 no message destination is available.

- You can specify the optional completion code value, return code, as a conditional or an arithmetic expression, evaluated to a single integer.

- The .LOGOFF command is processed when the highest return code reached prior to the .LOGOFF command is no more than 04 (warning). Any higher return code would have already terminated the FastExport job.

- If the .LOGOFF command is processed, FastExport returns the higher of:

- The return code value specified as a .LOGOFF command option.

- The highest return code reached prior to the .LOGOFF command.

- If a serious error terminates the program before the LOGOFF command is processed, the return code output is the value generated by the error condition rather than the return code value specified as a LOGOFF command option.

14. Executing FastExport script

The command given at the prompt to run an FastExport script is as follows:

fexp < < scriptname >

An Introduction to FastExport(Diff Between Bteq n Fastexport)

FastExport logs off all sessions with the Teradata Database and returns a status message

indicating:

• The total processor time that was used

• The job start and stop date/time

• The highest return code that was encountered:

• 0 if the job completed normally

• 4 if a warning condition occurred

• 8 if a user error occurred

• 12 if a fatal error occurred

• 16 if no message destination is available

• FastExport is when it comes to exporting vast amounts of data from Teradata and transferring the data from Tables into flat files on either a mainframe or network-attached computer.

• FastExport has the, which provides the user the capability to write, select, validate, and preprocess the exported ability to except OUTMOD routines data.

• FastExport can take internal as well as external parameters.

• if FastExport terminates abnormally, all the selected rows are in worktables and it can continue sending them where it left off.

• FastExport can also export from multiple tables during a single operation.

• FastExport utilizes the Support Environment, which provides a job restart capability from a checkpoint if an error occurs during the process of executing an export job.

• FastExport does not import data into Teradata. it understands only SELECT.

• FastExport supports multiple SELECT statements and multiple tables in a single run.

• FastExport supports conditional logic, conditional expressions, arithmetic calculations, and data conversions.

• FastExport does NOT support error files or error limits.

• Maximum 15 jobs are supported.

• it processes block by block data.

Differentiate FASTEXPORT and BTEQ

BTEQ FASTEXPORT

BTEQ does not have this load limitation Maximum 15 jobs are supported.

if BTEQ terminates abnormally, all of your rows FastExport terminates abnormally, all the selected continue rows are in worktables and it ca (which are in SPOOL) are discarded. sending them where it left off.

FastExport will work with less data, BTEQ is Much Faster than Fastexport.

but the speed may not be much faster than BTEQ. BTEQ does not have this load limitation.

How FastExport Works

When FastExport is invoked, the utility logs onto the Teradata database and retrieves the rows that are specified in the SELECT statement and puts them into SPOOL. From there, it must build blocks to send back to the client. In comparison, BTEQ starts sending rows immediatelfor storage into a file.

If the output data is sorted, FastExport may be required to redistribute the selected data two times across the AMP processors in order to build the blocks in the correct sequence. Remember, a lot of rows fit into a 64K block and both the rows and the blocks must be sequenced. While all of this redistribution is occurring, BTEQ continues to send rows. FastExport is getting behind in the processing. However, when FastExport starts sending the rows back a block at a time, it quickly overtakes and passes BTEQ’s row at time processing.

The other advantage is that You must rerun the BTEQ script from the beginning. However, if FastExport terminates abnormally, all the selected rows are in worktables and it can continue sending them where it left off. Pretty smart and very fast!

Also, if there is a requirement to manipulate the data before storing it on the computer’s hard drive, an OUTMOD routine can be written to modify the result set after it is sent back to the client on either the mainframe or LAN. Just like the BASF commercial states, “We don’t make the products you buy, we make the products you buy better”. FastExport is designed off the same premise, it does not make the SQL SELECT statement faster, but it does take the SQL SELECT statement and processes the request with lighting fast parallel processing!

FastExport Fundamentals

#1: FastExport EXPORTS data from Teradata. The reason they call it FastExport is because it takes data off of Teradata (Exports Data). FastExport does not import data into Teradata. Additionally, like BTEQ it can output multiple files in a single run.

#2: FastExport only supports the SELECT statement. The only DML statement that FastExport understands is SELECT. You SELECT the data you want exported and FastExport will take care of the rest.

#3: Choose FastExport over BTEQ when Exporting Data of more than half a million+ rows. When a large amount of data is being exported, FastExport is recommended over BTEQ Export. The only drawback is the total number of FastLoads, FastExports, and MultiLoads that can run at the same time, which is limited to 15. BTEQ Export does not have this restriction. Of course, FastExport will work with less data, but the speed may not be much faster than BTEQ.

#4: FastExport supports multiple SELECT statements and multiple tables in a single run. You can have multiple SELECT statements with FastExport and each SELECT can join information up to 64 tables.

#5: FastExport supports conditional logic, conditional expressions, arithmetic calculations, and data conversions. FastExport is flexible and supports the above conditions, calculations, and conversions.

#6: FastExport does NOT support error files or error limits. FastExport does not record particular error types in a table. The FastExport utility will terminate after a certain number of errors have been encountered.

#7: FastExport supports user-written routines INMODs and OUTMODs. FastExport allows you write INMOD and OUTMOD routines so you can select, validate and preprocess the exported data

FastExport Supported Operating Systems

The FastExport utility is supported on either the mainframe or on LAN. The information below illustrates which operating systems are supported for each environment:

The LAN environment supports the following Operating Systems:

· UNIX MP-RAS, Windows 2000,Windows 95, Windows NT, UNIX HP-UX , AIX

· Solaris SPARC,Solaris Intel

The Mainframe (Channel Attached) environment supports the following Operating Systems:

· MVS

· VM

Maximum of 15 Loads

The Teradata RDBMS will only support a maximum of 15 simultaneous FastLoad, MultiLoad, or FastExport utility jobs. This maximum value is determined and configured by the DBS Control record. This value can be set from 0 to 15. When Teradata is initially installed, this value is set at 5.

The reason for this limitation is that FastLoad, MultiLoad, and FastExport all use large blocks to transfer data. If more then 15 simultaneous jobs were supported, a saturation point could be reached on the availability of resources. In this case, Teradata does an excellent job of protecting system resources by queuing up additional FastLoad, MultiLoad, and FastExport jobs that are attempting to connect.

For example, if the maximum numbers of utilities on the Teradata system is reached and another job attempts to run that job does not start. This limitation should be viewed as a safety control feature. A tip for remembering how the load limit applies is this, “If the name of the load utility contains either the word “Fast” or the word “Load”, then there can be only a total of fifteen of them running at any one time”.

BTEQ does not have this load limitation. FastExport is clearly the better choice when exporting data. However, if two many load jobs are running. BTEQ is an alternate choice for exporting data.

FastExport Supported SQL Commands

FastExport accepts the following Teradata SQL statements. Each has been placed in alphabetic order for your convenience.

SQL Commands

ALTER TABLE	Change a column or table options of a table.
CHECKPOINT	Add a checkpoint entry in the journal table.
COLLECT STATISTICS	Collect statistics for one or more columns or indexes in a table.
COMMENT	Store or retrieve a comment string for a particular object.
CREATE DATABASE	Creates a new database.
CREATE TABLE	Creates a new table.
CREATE VIEW	Creates a new view.
CREATE MACRO	Creates a new macro.
DATABASE	Specify a default database for the session.
DELETE	Delete rows from a table.
DELETE DATABASE	Removes all tables, views, macros, and stored procedures from a database.
DROP DATABASE	Drops a database.
GIVE	Transfer ownership of a database or user to another user.
GRANT	Grant access privileges to an object.
MODIFY DATABASE	Change the options for a database.
RENAME	Change the name of a table, view, or macro.
REPLACE MACRO	Change a macro.
REPLACE VIEW	Change a view.
REVOKE	Revoke privileges to an object.
SET SESSION COLLATION	Override the collation specification during the current session.
UPDATE	Change a column value of an existing row or rows in a table.

FastExport Support and Task Commands

FastExport accepts both FastExport commands and a subset of SQL statements. The FastExport commands can be broken down into support and task activities. The table below highlights the key FastExport commands and their definitions. These commands provide flexibility and control during the export process.

Support Environment Commands

ACCEPT	Allows the value of utility variables to be accepted directly from a file or from environmental variables.
DATEFORM	Specifies the style of the DATE data types for FastExport.
DISPLAY	Writes messages to the specific location.
ELSE	Used in conjunction with the IF statement. ELSE commands and statements will execute when a proceeding IF condition is false.
ENDIF	Used in conjunction with the IF or ELSE statements. Delimits the commands that were subject to previous IF or ELSE conditions.
IF	Introduces a conditional expression. If true then execution of subsequent commands will happen.
LOGOFF	Disconnects all FastExport active sessions and terminates FastExport.
LOGON	LOGON command or string used to connect sessions established through the FastExport utility.
LOGTABLE	FastExport utilizes this to specify a restart log table. The purpose is for FastExport checkpoint information.
ROUTE MESSAGES	Will route FastExport messages to an alternate destination.
RUN FILE	Used to point to a file that FastExport is to use as standard input. This will Invoke the specified external file as the current source of utility and Teradata SQL commands.
SET	Assigns a data type and value to a variable.
SYSTEM	Suspends the FastExport utility temporarily and executes any valid local operating system command before returning.

Task Commands

BEGIN EXPORT	Begins the export task and sets the specifications for the number of sessions with Teradata.
END EXPORT	Ends the export task and initiates processing by Teradata.
EXPORT	Provides two things which are:. The client destination and file format specifications for the export data retrieved from Teradata. A generated MultiLoad script file that can be used later to reload the export data back into Teradata
FIELD	Constitutes a field in the input record section that provides data values for the SELECT statement.
FILLER	Specifies a field in the input record that will not be sent to Teradata for processing. It is part of the input record to provide data values for the SELECT statement.
IMPORT	Defines the file that provides the USING data values for the SELECT.
LAYOUT	Specifies the data layout for a file. It contains a sequence of FIELD and FILLER commands. This is used to describe the import file that can optionally provide data values for the SELECT.

Figure 3-2

A FastExport in its Simplest Form

The hobby of racecar driving can be extremely frustrating, challenging, and rewarding all at the same time. I always remember my driving instructor coaching me during a practice session in a new car around a road course racetrack. He said to me, “Before you can learn to run, you need to learn how to walk.” This same philosophy can be applied when working with FastExport. If FastExport is broken into steps, then several things that appear to be complicated are really very simple. With this being stated, FastExport can be broken into the following steps:

· Logging onto Teradata

· Retrieves the rows you specify in your SELECT statement

· Exports the data to the specified file or OUTMOD routine

· Logs off of Teradata

/* Created by CoffingDW */
/* Setup the Fast Export Parameters */
LOGTABLE sql01.SWA_Log;	Creates the logtable -Required
.LOGON CDW/sql01,whynot;	Logon to Teradata
BEGIN EXPORT SESSIONS 12;	Begin the Export and set the number of sessions on Teradata
.EXPORT OUTFILE Student.txt MODE RECORD FORMAT TEXT;	Defines the output file name. In addition, specifies the output mode and format (LAN – ONLY)
	The SELECT defines the column used to create the export file. NOTE: The selected columns for the export are being converted to character types. This will simplify the importing process into a different database.
/* Finish the Export Job and Write to File */ .END EXPORT; .LOGOFF;	End the Export and logoff Teradata.

Figure 3-4

Sample FastExport Script

Now that the first steps have been taken to understand FastExport, the next step is to journey forward and review another example that shows builds upon what we have learned. In the script below, Teradata comment lines have been placed inside the script [/*. . . . */]. In addition, FastExport and SQL commands are written in upper case in order to highlight them. Another note is that the column names are listed vertically. The recommendation is to place the comma separator in front of the following column. Coding this way makes reading or debugging the script easier to accomplish.

/* ---------------------------------------------------------------- / / @(#) FASTEXPORT SCRIPT / / @(#) Version 1.1 / / @(#) Created by CoffingDW / / --------------------------------------------------------------------*/	ALWAYS GOOD TO IDENTIFY THE SCRIPT AND AUTHOR IN COMMENTS
/* Setup the Fast Export Parameters */ .LOGTABLE SQL01.CDW_Log; .LOGON CDW/SQL01,whynot;	CREATE LOGTABLE AND LOGON;
.BEGIN EXPORT SESSIONS 12;	BEGIN EXPORT STATEMENT. SESSIONS 12;
.EXPORT OUTFILE Join_Export.txt MODE RECORD FORMAT TEXT;	DEFINES THE OUTPUT FILE NAME. IN ADDITION, SPECIFIES THE OUTPUT MODE AND FORMAT(LAN – ONLY) MODE RECORD FORMAT TEXT;
	THE SELECT PULLS DATA FROM TWO TABLES. IT IS GOOD TO QUALILY WHEN DOING A TWO-TABLE JOIN.
/* Finish the Export Job and Write to File */ .END EXPORT; .LOGOFF;	END THE JOB AND LOGOFF TERADATA;

FastExport Modes and Formats

FastExport Modes

FastExport has two modes: RECORD or INDICATOR. In the mainframe world, only use RECORD mode. In the UNIX or LAN environment, RECORD mode is the default, but you can use INDICATOR mode if desired. The difference between the two modes is INDICATOR mode will set the indicator bits to 1 for column values containing NULLS.

Both modes return data in a client internal format with variable-length records. Each individual record has a value for all of the columns specified by the SELECT statement. All variable-length columns are preceded by a two-byte control value indicating the length of the column data. NULL columns have a value that is appropriate for the column data type. Remember, INDICATOR mode will set bit flags that identify the columns that have a null value.

FastExport Formats

FastExport has many possible formats in the UNIX or LAN environment. The FORMAT statement specifies the format for each record being exported which are:

· FASTLOAD

· BINARY

· TEXT

· UNFORMAT

The default FORMAT is FASTLOAD in a UNIX or LAN environment.

FASTLOAD Format is a two-byte integer, followed by the data, followed by an end-of-record marker. It is called FASTLOAD because the data is exported in a format ready for FASTLOAD.

BINARY Format is a two-byte integer, followed by data.

TEXT is an arbitrary number of bytes followed by an end-of-record marker.

UNFORMAT is exported as it is received from CLIv2 without any client modifications.

A FastExport Script Using Binary Mode

/* --------------------------------------------------------------/ / @(#) FASTEXPORT SCRIPT / / @(#) Version 1.1 / / @(#) Created by CoffingDW / / --------------------------------------------------------------*/	COMMENTS
/* Setup the Fast Export Parameters */ .LOGTABLE SQL01.SWA_LOG; .LOGON CDW/Sql101,whynot;	CREATE LOGTABLE AND LOGON TO TERADATA
.BEGIN EXPORT SESSIONS 12;	BEGIN EXPORT STATEMENT;
.EXPORT OUTFILE CDW_Export.txt MODE RECORD FORMAT TEXT;	NAME THE OUTPUT FILE AND SET THE FORMAT TO BINARY;
	THE SELECT PULLS DATA FROM TWO TABLES. IT IS GOOD TO QUALILY WHEN DOING A TWO-TABLE JOIN.
/* Finish the Export Job and Write to File */ .END EXPORT; .LOGOFF;	END THE JOB;

Figure 3-6

CREATE SET TABLE MYUSER.STUDENT_T ,FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

stundent_id VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

last_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

first_name VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC,

class_code VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,

grade_pt VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( stundent_id );

Exporting data in variable text format [,deliminated]:

.LOGTABLE myuser.yogita_fexp4;

.LOGON 127.0.0.1/MYUSER,MYUSER1;

DATABASE MYUSER;

.BEGIN EXPORT SESSIONS 2;

.EXPORT OUTFILE C:\TEST\student4.txt

MODE RECORD format text;

SELECT

CAST (stundent_id AS VARCHAR(20)) ||','||

CAST (last_name as VARCHAR(20)) ||','||

CAST (first_name as VARCHAR(14)) AS output_data

FROM STUDENT_T;

.END EXPORT;

.LOGOFF;

Exporting FASTLOAD data :

.LOGTABLE myuser.yogita_fexp7;

.LOGON 127.0.0.1/MYUSER,MYUSER1;

DATABASE MYUSER;

.BEGIN EXPORT SESSIONS 2;

.EXPORT OUTFILE C:\TEST\student7.txt format FASTLOAD;

SELECT * FROM STUDENT_T;

.END EXPORT;

.LOGOFF;

EXPORTING INTO MULTIPLE FILE :

.logon 127.0.0.1/myuser,muyser1;

.BEGIN EXPORT < >

.EXPORT OUTFILE <FILENAME1>

.END EXPORT

.BEGIN EXPORT <>

.EXPORT OUTFILE <FILENAME2>

.END EXPORT

.logoff;

Passing Paramenters to Fastexports:

There are two ways we Can pass parameters

Internal Parameters :

.IF ’&SYSDAY’ = ’Fri’ THEN;

14:10:28 - FRI MAY 09, 1993

UTY2402 Previous statement modified to:

0004 .IF ’FRI’ = ’Fri’ THEN;

0005.RUN FILE UTNTS38;

0006 .ENDIF;

.IF ’&SYSDAY’ = ’Fri’ THEN;

14:10:28 - FRI MAY 09, 1993

UTY2402 Previous statement modified to:

0004 .IF ’FRI’ = ’Fri’ THEN;

0005.RUN FILE UTNTS38;

0006 .ENDIF;

External Parameters:

.Accept accepts single record, Import accepts multiple records.

.logtable yogita_fexplog;

.RUN FILE_logon;

.SET cityname TO 'los Angeless';

.SET ZIPCODE TO 80006;

.BEGIN EXPORT SESSION 4;

.EXPORT OUTFILE custacct_data;

SELECT a.account_number,

c.last_name,

c.first_name,

A.balance_current

FROM accounts A INNER JOIN

Accounts_custoer AC INNER JOIN

ON c.customer_number = AC.customer_number

ON A.account_number=AC.account_number

WHERE a.city = '&cityname'

and a.zip_code = &zipcode

ORDER BY 1;

.END EXPORT

.LOGOFF;

FastExport Example

The following FastExport job script example executes a single SELECT statement

and returns the results to a data set on the client system:

.LOGTABLE utillog ; /* define restart log */

.LOGON tdpz/user, pswd ; /* DBC logon string */

.BEGIN EXPORT /* specify export function */

SESSIONS 20; /* number of sessions to be used */

.LAYOUT UsingData ; /* define the input data */

.FIELD ProjId * Char(8) ; /* values for the SELECT */

.FIELD WkEnd * Date ; /* constraint clause. */

.IMPORT INFILE ddname1 /* identify the file that */

LAYOUT UsingData ; /* contains the input data */

.EXPORT OUTFILE ddname2 ; /* identify the destination */

/* file for exported data */

SELECT EmpNo, Hours

FROM CHARGES /* provide the SQL SELECT */

WHERE WkEnd = :WkEnd /* statement with values */

AND Proj_ID = :ProjId /* provided by the IMPORT */

ORDER BY EmpNo ; /* command */

.END EXPORT ; /* terminate the export */

.LOGOFF ; /* disconnect from the DBS */

TRY fastexport EXAMPLE.

Here is the Source table
--------------------------------------

show table Dealer_sale_service_station;

*** Text of DDL statement returned.

*** Total elapsed time was 1 second.

-------------------------------------------------------------------

CREATE SET TABLE CSS.Dealer_sale_service_station ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

SS_station VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

SS_stationid SMALLINT,

SS_Emailid VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

SS_Address VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,

SS_Phone VARBYTE(15))

UNIQUE PRIMARY INDEX ( SS_stationid )

UNIQUE INDEX ( SS_Phone );

Here is the Destination table
--------------------------------------

show table Dealer_sale_service_tmp;

*** Text of DDL statement returned.

*** Total elapsed time was 1 second.

--------------------------------------------------------------------

CREATE SET TABLE CSS.Dealer_sale_service_tmp ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

SS_station VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

SS_stationid SMALLINT,

SS_Emailid VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

SS_Address VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,

SS_Phone VARBYTE(15))

UNIQUE PRIMARY INDEX ( SS_stationid );

Here is the fastexport script.
--------------------------------------

.logtable css.css_logtbl;

.logon leo/dbc,dbc;

database css;

.begin export;

.export outfile Dealer_sale_service_station.fout format fastload;

sel * from Dealer_sale_service_station;

.end export;

Here is the fastload script:
--------------------------------------

.sessions 2;

.errlimit 2;

.logon leo/dbc,dbc

database css;

DEFINE FILE=./Dealer_sale_service_station.fout;

begin loading Dealer_sale_service_tmp errorfiles error_adsss,

error_bdsss;

insert into Dealer_sale_service_tmp.*;

end loading;

.logoff

.quit

Regards,

yogita

Teradata DBA

Monday, February 13, 2017

An Introduction to FastExport

1 comment: