I am trying to import data from a file (that is created exporting from db2 table) and load it to in teradata file. The file has clob data. MLOAD does not work with clob data.
I just have 2 columns, UNQ_ID VARCHAR(26) and MSG_CNT CLOB(25000).
Thanks for your help
If your clob is only 25k, then change the definition to varchar(25000), then you could load using mload. If you want to load data as a clob, then you can only use bteq with deferred or tpt streams/tpump with deferred.
What this would mean is that you would have to seperate your non-clob data from your clob data. You would have one file that contains all the non-clob data with a pointer to the location of the clob file in the position of the clob data which should be the last element in the file/table.
so for example my data file might look like this.
Data file layout:
This is how you would have to build your files to load the clob into Teradata using the deferred method. It is ugly and a major pita to get source systems to build files that way, and if you have a lot of data and need to load it fast, it isn't going to work well.
Since your clob value is would easily fit into a varchar data type, you would be better off converting it in Teradata to a varchar field, then when you extract it, you can load it via mload like you would a regular varchar field.
Other options for loading clob data...
1. break the clob value up into sections when bringing from the source such that you could load to a staging table using fastload, and include some sort of sequence value for each set of records that holds the portion of the clob value and then use a recursive sql to put it back together and insert it into the final table. Still ugly and requires a bit more etl work getting the data out of the source, but much easier than dealing with all the additional files.
2. Break the clob up into multiple rows and just store them in a varchar field as seperate rows with a sequence value (look at how the dbqlsqltbl is setup). Then you can just construct the clob on the fly if needed.
3. Build a special java process that can load the clob data inline (I believe that it can be done using java this way, but it requires a bit of tweaking of the java program, and it won't be very fast since it would be inserting a record at a time...so much like bteq but with a single file to deal with).
If you don't have to use the clob data type, then don't...that is really the best advice. If you have to store clob data that is larger than 64k (latin) or 32k (unicode) your looking at some really convoluted ways of getting the data loaded.
There hasn't been any real progress on getting LOB (clob/blob), JSON, XML, ST_GEOMETRY data loaded using the bulk load operations and even using the deferred via BTEQ/Tpump/TPT Streams is painful if you have a lot of data to load and need to load frequently.
Now, my comments above are all based my experience when working with clob/blob data and ST_Geometry (which is a clob). Others may have figured out better ways to do this, or have different methods that might make it easier to get clob data loaded, and if so they can add their two cents.
thank you so much for sharing your knowledge.
I am first doing an export from db2 table and its in the file. since its a clob data i have reference file. so my export is creating 2 files
sample data in the counts.dat file. Its comma delimited file.