Issue using Informatica to load Teradata tables.

Third Party Software

Issue using Informatica to load Teradata tables.

I have an issue while loading data from sql server to Teradata database. It takes a very long time to load 100K rows. when i checked the log I guess it is doing a row level insert instead of a bulk load. Here is a part of the session log
MASTER> PETL_24006 Starting data movement.
MAPPING> TM_6660 Total Buffer Pool size is 12000000 bytes and Block size is 64000 bytes.
READER_1_1_1> DBG_21438 Reader: Source is [SA3ADLSQL02], user [ABCDEF]
READER_1_1_1> BLKR_16003 Initialization completed successfully.
WRITER_1_*_1> WRT_8148 Writer: Target is database [JQU], user [ABCDEF], bulk mode [N/A]
WRITER_1_*_1> CMN_1021 Database driver event...
CMN_1021 [Teradata Event Using array fetches. Teradata Event Using Single Row Inserts. connect string = [Siebel_Warehouse_Dev]. userid = [ABCDEF]]
WRITER_1_*_1> WRT_8124 Target Table JQU_ProspectOwnerTracker :SQL INSERT statement:

Now how can i set the option to do a BULK LOAD.? I remember there is a place to do that but I cant find it. I'd appreciate your help.



Re: Issue using Informatica to load Teradata tables.

It has been a while since I did informatic, but you set up a Teradata loader as a "writer" in the session tool.

you then set your target to file instead of database, and choose your teradata writer. If you need more details, I can try to research it more...

Re: Issue using Informatica to load Teradata tables.

Hi Feroz,

To load 100K records, I would suggest to use fastload / mload utilities from teradata, instead of using an ODBC connection.
In workflow manager, you would need to define a "Loader" connection, that would use either fastload or mload (based on your requirement).
Then in your specific workflow, you would need use this loader connection (instead of the ODBC connection).
During execution, When informatica sees that it has to use the loader connection, it splits the execution in 2 steps -
1. it would create the control file, outfile by itself & dump the data into the out file.
2. Then it would take the out file as the input , invoke fastload / mload, & would load the data into the target table.

I'm sure this would save a LOT of time for you !!

Let me know if this works for you ..

Thanks & Regards,

Re: Issue using Informatica to load Teradata tables.

Thanks Mahesh and all,
I used the external loader fastload option to load the data, It did save a lot of time.

Re: Issue using Informatica to load Teradata tables.

When i tried to use the Teradata_Mload_External_loader in the loader option in Informatica, i couldnt able to find in workflow manager Connection type as loader. i can view None,FTP , where i need to configure to get the Loader type in connection.