1. Introduction
The document purports to detail the issues and suggested solutions when working with bulk loader capabilities of Teradata as target using BODS.
2. Teradata and bulk loader capabilities
Teradata is capable of loading bulk data using Teradata Parallel Transporter. Teradata Parallel Transporter was designed for increased functionality and customer ease of use for faster, easier and deeper integration. This capability can be utilized to load huge volume of data in ETL jobs.
3. BODS level settings
In order to enable bulk loader, we need to set some options in BODS data flow. Double click the target Teradata table in the DF and go to the bulk loader tab. There in the drop down select the ‘Parallel transporter’ option.
To facilitate bulk loading BODS will create few custom tables in the target data base, viz,
· Error table (Table created if there is any data errors in the load)
· Unique Violation table (Table created if there is any records which violated unique key constraint)
· Work table (Table created as a work table for bulk loader task)
· Log table (Table created to hold the log of the bulk loader transaction)
When the bulk loader is enabled we need to define data bases for these tables.
See the images below for more information.
In the below image bulk loader option is selected as 'Parallel Transporter'. Error table is defined
as TERA1.ET_TERA_TARGET(teradataschema.ET_targettablename). Similarly UV table also defined. If we want to deal with non-english characters then we can opt character set as UTF-8.
In the below image log table and Work table are named as TERA1.TERATARGET_LT and TERA1.TERATARGET_WT respectively.
Also the maximum sessions are limited to 16. If it is not limited bulk loader may create many sessions with target database and server may hang up.
4. Common errors in bulk load
4.1 Existence of bulk loader tables already in the database
When a bulk loader job is run normally a log table and a Work table created and dropped by the job itself. If there is data error or unique key violation, Error table and Unique Violation tables are created. If a job fails abnormally these tables may not be dropped automatically.
So manually these tables to be dropped from the database (where it was previously defined in the bulk loader option) before running the job next time. Else it will throw the error like ‘ET /UV/LT/WT table already existing’.
4.2 Teradata multi loading
Teradata MultiLoad, also called "MultiLoad," "MLoad" or "ML," is a command-driven parallel load utility for high-volume batch maintenance on multiple tables and views of the Teradata Database. It is specially designed for high-speed batch creation and maintenance of large databases.
When we are doing a bulk loading this ‘MLoad’ becomes active. In case the job fails abnormally in between, there can be lock on the target table. This lock has to be released before running the job again. Else it will give an error as ‘Parallel Transporter Error’. We can run the below release commands in the database.
release mload tablename;
release mload tablename in apply;
4.3 Bulk loader directory in the BODS server
In order to use bulk loader capability we need to set up a folder location in the BODS server. When bulk loader jobs are continuously run these folder becomes full. In this case we may get an error like ‘No more space left on the Device’. If such error occurs this directory has to be cleared before running the job next time.
5. Conclusion
Even if there is a unique key violation or data error the bulk loading job will run successfully in BODS. We have to trace back and query the back end table for analyzing the data. Using bulk loader option we can improve the job performance by more than 50%. When you are using bulk loader we will get a job validation error ‘Log directory not found’. But we can ignore this error and run the job.
- SAP Managed Tags:
11 Comments
