How to check table distribution.

Question:

            

How can you tell if a table is well distributed?

Answer:

We hope (by making good PI choices and hashing on the PI columns) to get data distributed across the amps as evenly possible. Ideally, each amp would have the same number of rows. Rarely do we achieve perfection, but we often get close.

The following SQL will count the number of rows on each amp and report the (positive or negative) deviation from the average.

Select  dt1.a (title'AMP') ,dt1.b (title'Rows') ,((dt1.b/dt2.x (float)) - 1.0) * 100 (format'+++9%',title'Deviation') from (select hashamp(hashbucket(hashrow(<primary index>))) ,count(*) from <databasename>.<tablename> group by 1 )dt1 (a,b)  ,(sel (count(*) / (hashamp()+1)(float)) FROM <databasename>.<tablename> )dt2(x) order by 2 desc,1;

Ex :

Select  dt1.a (title'AMP') ,dt1.b (title'Rows') ,((dt1.b/dt2.x (float)) - 1.0) * 100 (format'+++9%',title'Deviation') from (select hashamp(hashbucket(hashrow(RetailstoreID))) ,count(*) from teraqa.copy_src  group by 1 )dt1 (a,b)  ,(sel (count(*) / (hashamp()+1)(float)) FROM  teraqa.copy_src  )dt2(x) order by 2 desc,1;

Running this will show:

      

 AMP         Rows  Deviation

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

         9           63       +15%

         2           62       +13%

         4           61       +11%

         5           60        +9%

        11           59        +7%

        12           59        +7%

        13           58        +5%

        15           57        +4%

        14           55        +0%

         0           53        -4%

         8           52        -5%

        10           52        -5%

         3           51        -7%

         6           51        -7%

         7           51        -7%

         1           49       -11%

You need not worry until the max or min vary from the average by at least 15%.

Hash synonyms (two different values hashing to the same rowhash) are sometimes an issue with distribution questions. Here is a query to check:

.retlimit 10

Select  h (title'Hash') ,c (title'Frequency') from (select  hashrow(<primary index>) ,count(*) from <database>.<tablename> group by 1 )dt (h,c) qualify rank(c desc) <= 10 order by 2 desc, 1;

Ex :

Select  h (title'Hash') ,c (title'Frequency') from (select  hashrow(RetailstoreID) ,count(*) from teraqa.copy_src  group by 1 )dt (h,c) qualify rank(c desc) <= 10 order by 2 desc, 1;

For example:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

show table temp.table1;

show table temp.table1;

 *** Text of DDL statement returned.

*** Total elapsed time was 1 second.

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

CREATE SET TABLE temp.table1 ,NO FALLBACK ,

    NO BEFORE JOURNAL,

    NO AFTER JOURNAL

    (

     c1 CHAR(44) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '

                                    ',

     c2 INTEGER NOT NULL,

     c3 SMALLINT NOT NULL,

     c4 CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '

                      ')

PRIMARY INDEX I1 ( c1 )

UNIQUE INDEX I2 ( c1 ,c2 )

INDEX I3 ( c3 );

References to Procedures:

           

Additional Info/Comments:

            A lump is a bunch of rows where some column has the same value. Lumpiness is the opposite of uniqueness. The ultimate lump would be all rows in a table having the same value in some column.

Current Attachments:

           

Fastload Dummy Script

Error is :

Our teradata loader sessions failed with following error:

**** 11:57:43 RDBMS error 2652: Operation not allowed:

  Qasrvr85.ldr_enh_fl_pipe_tgt is being Loaded.

Please check whether fast load is locked for this user.

Here Fastload Dummy script :

.set record unformatted;

.logon ipaddress/user_name, pswd;

database Database_Name;

begin loading target_table

erorfiles errortable_name, uv_tablename

checkpoint checkpoint_value;

end loading;

.logoff;

The above one will writes something like below ..

.set record unformatted;

.logon 127.0.0.1/dbc, dbc;

database dbc;

begin loading party3

erorfiles erparty3, uvparty3

checkpoint 10;

end loading;

.logoff;

Fastload uses 2 error tables :

ET TABLE 1: where format of data is not correct.
ET TABLE 2: violations of UPI
it maintains only error field name, error code and data-parcel only.

Mload also uses 2 error tables (ET and UV), 1 work table and 1 log table :

1. ET TABLE - Data error
MultiLoad uses the ET table, also called the Acquisition Phase error table, to store data errors found during the acquisition phase of a MultiLoad import task.

2. UV TABLE - UPI violations
MultiLoad uses the UV table, also called the Application Phase error table, to store data errors found during the application phase of a MultiLoad import or delete task

Apart from error tables, it also has work and log tables
3. WORK TABLE - WT
Mload loads the selected records in the work table

4. LOG TABLE
A log table maintains record of all checkpoints related to the load job, it is essential l/ mandatory to specify a log table in mload job. This table will be useful in case you have a job abort or restart due to any reason.

Notes :

drop table t1_er1;   // Here t1_er1 is error voilation

drop table t1_er2;   //Here t1_er2 menas uniqueness voilation

Notes :

The two Error Tables are populated with rows for different reasons and those error tables

are controlled and populated by the Teradata DBS. Not by the client.

Error Table 1 contains rows that had errors during the Acquisition Phase

(the actual sending of data to the DBS). Those errors are usually due to data conversion errors.

Error Table 2 contains "UV" (uniqueness violation) errors. Those errors are detected during the Application Phase

(also known as Phase 2).

Duplicate rows are silently discarded by the DBS and do not factor into any of these number.

However arow in the second error table usually means a row had a uniqueness violation of the UPI.

FastLoad takes the number of rows read in, subtracts the number of rows in the 2 error tables,

then subtracts the number of rows actually applied to target table. Anything left over means there were rows  that did not end up in the error tables or the target table and therefore must have been silently discarded by the DBS,  and the reason for that must have been because they were duplicates.

Error Table 2 shows "30", not "15". There were 30 rows that violated the uniqueness condition.

There are no "pairs" in the equation.

Note 2 :  (http://www.coffingdw.com/utilities/tdutil/sample_fastload_script.htm)

Fastload locks :

Fast load locks are one of the major issues in fast load. I have seen this kind of scenarios in production environment and there are various ways by which we can handle this issue.

  When do fast load locks happen?
- When the fast load script results in failure (error code 8 or 12)
- When fast load script is aborted by dba or some other session
- Fast load fails (Bad record, file)
- When you have forgot to add end loading statement in your fload script


What can be done to release fast load locks?

1. Finish the fast load, which means leave your hands off of the error tables and restart fast load. When it finishes the table lock will be released. If the following error persists then do step 2.

Error is: RDBMS error 2652: Operation not allowed: _db_._table_ is being Loaded.

2. Create a dummy fast load job as following without any content for loading as follows.

Logon tdpid/username, password;
begin loading databasename.tablename;
/* empty loading to release fast load lock */
end loading;
logoff;

3. If second step also fails then, drop the empty locked table (because if you can't restart Fast load you most likely dropped the error tables)

Please note that before using the DROP solution, try to close your already opened fastload session.

4. If still this error persists and not able to drop the table :( , then please kill the fastload session from pmon/manager , drop the table and rerun actual fast load script .

I hope this solution works, if not then please contact your DBA

Three Key Requirements for FastLoad to Run

FastLoad can be run from either MVS/ Channel (mainframe) or Network (LAN) host. In either case, FastLoad requires three key components. They are a log table, an empty target table and two error tables. The user must name these at the beginning of each script.

Log Table: FastLoad needs a place to record information on its progress during a load. It uses the table called Fastlog in the SYSADMIN database. This table contains one row for every FastLoad running on the system. In order for your FastLoad to use this table, you need INSERT, UPDATE and DELETE privileges on that table.

Empty Target Table: We have already mentioned the absolute need for the target table to be empty. FastLoad does not care how this is accomplished. After an initial load of an empty target table, you are now looking at a populated table that will likely need to be maintained.

If you require the phenomenal speed of FastLoad, it is usually preferable, both for the sake of speed and for less interaction with the Data Dictionary, just to delete all the rows from that table and then reload it with fresh data. The syntax DELETE <databasename>.<tablename> should be used for this. But sometimes, as in some of our FastLoad sample scripts (see Figure 4-2), you want to drop that table and recreate it versus using the DELETE option. To do this, FastLoad has the ability to run the DDL statements DROP TABLE and CREATE TABLE. The problem with putting DDL in the script is that is no longer restartable and you are required to rerun the FastLoad from the beginning. Otherwise, we recommend that you have a script for an initial run and a different script for a restart.

Two Error Tables: Each FastLoad requires two error tables. These are error tables that will only be populated should errors occur during the load process. These are required by the FastLoad utility, which will automatically create them for you; all you must do is to name them. The first error table is for any translation errors or constraint violations. For example, a row with a column containing a wrong data type would be reported to the first error table. The second error table is for errors caused by duplicate values for Unique Primary Indexes (UPI). FastLoad will load just one occurrence for every UPI. The other occurrences will be stored in this table. However, if the entire row is a duplicate, FastLoad counts it but does not store the row. These tables may be analyzed later for troubleshooting should errors occur during the load. For specifics on how you can troubleshoot, see the section below titled, “What Happens When FastLoad Finishes.”

FastLoad Has Two Phases

Teradata is famous for its end-to-end use of parallel processing. Both the data and the tasks are divided up among the AMPs. Then each AMP tackles its own portion of the task with regard to its portion of the data. This same “divide and conquer” mentality also expedites the load process. FastLoad divides its job into two phases, both designed for speed. They have no fancy names but are typically known simply as Phase 1 and Phase 2. Sometimes they are referred to as Acquisition Phase and Application Phase.

PHASE 1: Acquisition

The primary function of Phase 1 is to transfer data from the host computer to the Access Module Processors (AMPs) as quickly as possible. For the sake of speed, the Parsing Engine of Teradata does not does not take the time to hash each row of data based on the Primary Index. That will be done later. Instead, it does the following:

When the Parsing Engine (PE) receives the INSERT command, it uses one session to parse the SQL just once. The PE is the Teradata software processor responsible for parsing syntax and generating a plan to execute the request. It then opens a Teradata session from the FastLoad client directly to the AMPs. By default, one session is created for each AMP. Therefore, on large systems, it is normally a good idea to limit the number of sessions using the SESSIONS command. This capability is shown below.

Simultaneously, all but one of the client sessions begins loading raw data in 64K blocks for transfer to an AMP. The first priority of Phase 1 is to get the data onto the AMPs as fast as possible. To accomplish this, the rows are packed, unhashed, into large blocks and sent to the AMPs without any concern for which AMP gets the block. The result is that data rows arrive on different AMPs than those they would live, had they been hashed.

So how do the rows get to the correct AMPs where they will permanently reside? Following the receipt of every data block, each AMP hashes its rows based on the Primary Index, and redistributes them to the proper AMP. At this point, the rows are written to a worktable on the AMP but remain unsorted until Phase 1 is complete.

Phase 1 can be compared loosely to the preferred method of transfer used in the parcel shipping industry today. How do the key players in this industry handle a parcel? When the shipping company receives a parcel, that parcel is not immediately sent to its final destination. Instead, for the sake of speed, it is often sent to a shipping hub in a seemingly unrelated city. Then, from that hub it is sent to the destination city. FastLoad’s Phase 1 uses the AMPs in much the same way that the shipper uses its hubs. First, all the data blocks in the load get rushed randomly to any AMP. This just gets them to a “hub” somewhere in Teradata country. Second, each AMP forwards them to their true destination. This is like the shipping parcel being sent from a hub city to its destination city!

PHASE 2: Application

Following the scenario described above, the shipping vendor must do more than get a parcel to the destination city. Once the packages arrive at the destination city, they must then be sorted by street and zip code, placed onto local trucks and be driven to their final, local destinations.

Similarly, FastLoad’s Phase 2 is mission critical for getting every row of data to its final address (i.e., where it will be stored on disk). In this phase, each AMP sorts the rows in its worktable. Then it writes the rows into the table space on disks where they will permanently reside. Rows of a table are stored on the disks in data blocks. The AMP uses the block size as defined when the target table was created. If the table is Fallback protected, then the Fallback will be loaded after the Primary table has finished loading. This enables the Primary table to become accessible as soon as possible. FastLoad is so ingenious, no wonder it is the darling of the Teradata load utilities!

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>

                    <SELECT STATEMENTS>

.END EXPORT

.BEGIN EXPORT <>

.EXPORT OUTFILE <FILENAME2>

               <SELECT STATEMENTS>

.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