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!