ETL CLOB from Oracle to Teradata

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.

Re: ETL CLOB from Oracle to Teradata

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.

Re: ETL CLOB from Oracle to Teradata

Cherry:

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.

Re: ETL CLOB from Oracle to Teradata

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.

Re: ETL CLOB from Oracle to Teradata

Multiload reference:

"The maximum row size for a MultiLoad job, data plus indicators, isapproximately 64,000 bytes. This limit is a function of:• The row size limit of the Teradata Database• The MultiLoad work tables and error tablesMultiLoad cannot accommodate a row size that is near the row sizelimit of the Teradata Database because of the operational overheadassociated with MultiLoad work tables and error tables. If theMultiLoad job exceeds this limit, the Teradata Database returnsError Message 3577 (row size or sort key size overflow), andterminates the job."