Is anyone ETL'ing a clob from Oracle to Teradata or does anyone have any thoughts how one would? What strategies are you using? We usually leverage Informatica to ETL into our data warehouse, and Informatica does support CLOBs, however, I'm not sure if the way Informatica handles CLOBs is compatible with Teradata.
I've tried using Multiload as well as an INSERT INTO with no success so far, so any help would be greatly appreciated.
The Teradata load utilities (BTEQ, Fastload, Multiload) currently do not have good support for LOBs. The best-supported interfaces for loading LOB data are the open APIs (ODBC, JDBC). If Informatica's (or any other vendor's) ETL product supports loading LOBs using ODBC or JDBC, it should work with Teradata.
You can load LOBs in many different ways ( http://carlosal.wordpress.com/2008/11/23/insertar-ficheros-de-imagen-en-teradata-usando-windows-scripting-host-y-ado/ ), BUT Teradata load utilities don't deal very well with LOBs.
If the CLOBs are < 64000 I'd try and load them as VARCHAR(64000) with fastload/multiload.
Otherwise, you can always split the CLOBs in chunks of 64000 chars (or 32000 if Oracle, or...) in the export process, load them in staging tables and convert to CLOB if needed.
Since I last posted on this thread back in 2006, the utility support for large objects has improved somewhat. In addition to the BTEQ support, The TPT (parallel transporter) utility now supports large objects.
"The maximum row size for a MultiLoad job, data plus indicators, is approximately 64,000 bytes. This limit is a function of: • The row size limit of the Teradata Database • The MultiLoad work tables and error tables MultiLoad cannot accommodate a row size that is near the row size limit of the Teradata Database because of the operational overhead associated with MultiLoad work tables and error tables. If the MultiLoad job exceeds this limit, the Teradata Database returns Error Message 3577 (row size or sort key size overflow), and terminates the job."