All Forums Third Party Software
21 Jun 2006
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 logMASTER> 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.Thanks.

ZackHowe 15 posts Joined 07/06
13 Jul 2006

It has been a while since I did informatic, but you set up a Teradata loader as a "writer" in the session 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

mahyan 6 posts Joined 07/06
14 Jul 2006

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,Mahesh.

08 Sep 2006

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

Rajendran77 5 posts Joined 09/04
29 Dec 2006

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.raja

You must sign in to leave a comment.