Archives of the TeradataForum

Message Posted: Tue, 08 Jun 2004 @ 12:48:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

hello Sridhar,

this is how standard fastexport script looks:

Standard FastExport Script

A standard FastExport script is as follows and the details of each statement are given later on:

     1. #Script File Name < tablename >.fxp
     2. .LOGTABLE < databasename >.< logtablename >_lg;
     3. .RUN FILE /load/admin/logon.fxp;
     4. DATABASE < databasename >;
     5. .SET < variable > TO < expression >;
     6. .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;

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:

     CODESignifies:

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 >

Regards,

Shilpa Ramlal Bateja

	<Prev	Next>		<<First	<Prev	Next>	Last>>