- Teradata Fastload Reference Manual Pdf
- Teradata Fastload Reference Manual Download
- Teradata Fastload Reference Manual Free
- Teradata Fastload Reference Manual Pdf
The SAS/ACCESS FastLoad facility using the TPT API is similar to the native Teradata FastLoad utility. They share these limitations. FastLoad can load only empty tables. It cannot append to a table that already contains data. If you try to use FastLoad when appending to a table that contains rows, the append step fails. Teradata FastLoad is a command line utility that can be used to load large amount of data into an empty table on Teradata database. The performance will be greater than line by line or batch processing mechanism. This article provides example of using FastLoad to load CSV file into Teradata database.
Message Posted: Wed, 28 Nov 2007 @ 18:56:20 GMT
<Prev | Next> | <<First | Next> | Last>> |
Subj: | LSN error using FASTLOAD |
From: | Duell, Bob |
Hi,
I hope someone can help with a mysterious problem. We use SAS software to interact with Teradata. One feature enables SAS to invoke Teradata's FASTLOAD utility using a native SAS data source to provide rows to be inserted into a table. This has always worked well.
However, we have recently begun getting this error message from SAS:
ERROR: Fastload could not allocate data sessions: Associated LSN was not found.
The closest thing I can find in the Teradata Messages manual is this (from page 6-60):
*3029 Associated LSN was not found.*
![Teradata Fastload Reference Manual Teradata Fastload Reference Manual](/uploads/1/2/6/5/126520148/845690860.jpg)
*Explanation: *An LSN associate connect request has been received for a host/LSN which cannot be found in the system.
*Generated By: *Session Control.
*For Whom: *Support Representative.
*Notes: *This error is returned by an internal consistency check. This error causes an error message to be returned to the host.
*Remedy: *Check the LSN sent to the DBC to see that it already exists.
The problem cannot be reproduced; running the job a second time loads the table just fine so I assume it has something to do with system congestion or maybe a timing problem.
Can someone please help me diagnose this problem? If I can get a clear explanation on what this error means I may be able to get additional assistance from SAS.
Note: We upgraded the Teradata Utilities on our UNIX server in September, just prior to a system upgrade from V2R5. Our current release is V2R.06.01.01.74. The FASTLOAD version is 07.08.00.05. I don't remember ever seeing this message before upgrading.
Thanks very much,
Teradata Fastload Reference Manual Pdf
Bob
<Prev | Next> | <<First | Next> | Last>> |
Let’s look at an actual FastLoad script that you might see in the real world. In the script below, every comment line is placed inside the normal Teradata comment syntax, [/*. . . . */]. FastLoad and SQL commands are written in upper case in order to make them stand out. In reality, Teradata utilities, like Teradata itself, are by default not case sensitive. You will also note that when column names are listed vertically we recommend placing the comma separator in front of the following column. Coding this way makes reading or debugging the script easier for everyone. The purpose of this script is to update the Employee_Profile table in the SQL01 database. The input file used for the load is named EMPS.TXT. Below the sample script each step will be described in detail.
Normally it is not a good idea to put the DROP and CREATE statements in a FastLoad script. The reason is that when any of the tables that FastLoad is using are dropped, the script cannot be restarted. It can only be rerun from the beginning. Since FastLoad has restart logic built into it, a restart is normally the better solution if the initial load attempt should fail. However, for purposes of this example, it shows the table structure and the description of the data being read.
/* !/bin/ksh* */ /* FASTLOAD SCRIPT TO LOAD THE */ /* Version 1.1 */ /* ++++++++++++++++++++++++++++*/ /* Setup the FastLoad Parameters */ | Always good to identify the script and author in comments. Since this script does not drop the target or error tables, it is restartable. This is a good thing for production jobs. |
SESSIONS 100; /*or, the number of sessions supportable*/ | |
TENACITY 4; /* the default is no tenacity, means no retry */ SLEEP 10; /* the default is 6, means retry in 6 minutes */ LOGON CW/SQL01,SQL01; | Wait 10 Min between retries. |
SHOW VERSIONS; /* Shows the Utility’s release number */ | |
/* Set the Record type to a comma delimited for FastLoad */ RECORD 2; | |
SET RECORD VARTEXT ‘,’; | Specifies if record layout is vartext with a comma delimiter. |
Notice that all fields are defined as VARCHAR. When using VARTEXT, the fields do not contain the length field like in these formats: text, FastLoad, or unformatted. | |
Defines the flat file name. | |
/* Optional to show the layout of the input */ SHOW | |
/* Begin the Load and Insert Process into the */ | |
ERRORFILES SQL01.Emp_Err1, SQL01.Emp_Err2 | Names the error tables. Sets the number of rows at which to pause & record progress in the restart log before loading further. |
Defines the insert statement to use for loading the rows. | |
Continues loading process with Phase 2. | |
Logs off of Teradata. |
Step One: Before logging onto Teradata, it is important to specify how many sessions you need. The syntax is [SESSIONS {n}].
Step Two: Next, you LOGON to the Teradata system. You will quickly see that the utility commands in FastLoad are similar to those in BTEQ. FastLoad commands were designed from the underlying commands in BTEQ. However, unlike BTEQ, most of the FastLoad commands do not allow a dot [“.”] in front of them and therefore need a semi-colon. At this point we chose to have Teradata tell us which version of FastLoad is being used for the load. Why would we recommend this? We do because as FastLoad’s capabilities get enhanced with newer versions, the syntax of the scripts may have to be revisited.
Step Three: If the input file is not a FastLoad format, before you describe the INPUT FILE structure in the DEFINE statement, you must first set the RECORD layout type for the file being passed by FastLoad. We have used VARTEXT in our example with a comma delimiter. The other options are FastLoad, TEXT, UNFORMATTED OR VARTEXT. You need to know this about your input file ahead of time.
Step Four: Next, comes the DEFINE statement. FastLoad must know the structure and the name of the flat file to be used as the input FILE, or source file for the load.
Teradata Fastload Reference Manual Download
Step Five: FastLoad makes no assumptions from the DROP TABLE statements with regard to what you want loaded. In the BEGIN LOADING statement, the script must name the target table and the two error tables for the load. Did you notice that there is no CREATE TABLE statement for the error tables in this script? FastLoad will automatically create them for you once you name them in the script. In this instance, they are named “Emp_Err1” and “Emp_Err2”. Phase 1 uses “Emp_Err1” because it comes first and Phase 2 uses “Emp_Err2”. The names are arbitrary, of course. You may call them whatever you like. At the same time, they must be unique within a database, so using a combination of your userid and target table name helps insure this uniqueness between multiple FastLoad jobs occurring in the same database.
In the BEGIN LOADING statement we have also included the optional CHECKPOINT parameter. We included [CHECKPOINT 100000]. Although not required, this optional parameter performs a vital task with regard to the load. In the old days, children were always told to focus on the three “R’s’ in grade school (“reading, ‘riting, and ‘rithmatic”). There are two very different, yet equally important, R’s to consider whenever you run FastLoad. They are RERUN and RESTART. RERUN means that the job is capable of running all the processing again from the beginning of the load. RESTART means that the job is capable of running the processing again from the point where it left off when the job was interrupted, causing it to fail. When CHECKPOINT is requested, it allows FastLoad to resume loading from the first row following the last successful CHECKPOINT. We will learn more about CHECKPOINT in the section on Restarting FastLoad.
Teradata Fastload Reference Manual Free
Step Six: FastLoad focuses on its task of loading data blocks to AMPs like little Yorkshire terrier’s do when playing with a ball! It will not stop unless you tell it to stop. Therefore, it will not proceed to Phase 2 without the END LOADING command.
In reality, this provides a very valuable capability for FastLoad. Since the table must be empty at the start of the job, it prevents loading rows as they arrive from different time zones. However, to accomplish this processing, simply omit the END LOADING on the load job. Then, you can run the same FastLoad multiple times and continue loading the worktables until the last file is received. Then run the last FastLoad job with an END LOADING and you have partitioned your load jobs into smaller segments instead of one huge job. This makes FastLoad even faster!
Of course to make this work, FastLoad must be restartable. Therefore, you cannot use the DROP or CREATE commands within the script. Additionally, every script is exactly the same with the exception of the last one, which contains the END LOADING causing FastLoad to proceed to Phase 2. That’s a pretty clever way to do a partitioned type of data load.
Teradata Fastload Reference Manual Pdf
Step Seven: All that goes up must come down. And all the sessions must LOGOFF. This will be the last utility command in your script. At this point the table lock is released and if there are no rows in the error tables, they are dropped automatically. However, if a single row is in one of them, you are responsible to check it, take the appropriate action and drop the table manually.