BFILE LOB data extraction example [CR #621844]

Disregard the example in Unstructured Data Analytics in Sybase IQ > Large Object Data Load and Unload > Exporting large object data > BFILE function. The SELECT command extracts data, but the extracted data contains two extraneous columns and cannot be reloaded.

This new example extracts LOB data using the BFILE function and reloads the data.

1. Create table LobA:

   create table LobA
     (rowid  int primary key,
       col1   clob null,
       col2   blob null)
   

   Assume LobA has two rows of data.

2. Extract the non-LOB data and the paths to the files into which the LOB data is extracted:

   BEGIN
     SET TEMPORARY OPTION 
       Temp_Extract_Name1 = LobA_data.txt';
     SELECT rowid,
       'row' + string(rowid) + '.' + 'col1',
       'row' + string(rowid) + '.' + 'col2'
     FROM LobA;
   END
   

   The file LobA_data.txt is created and contains this non-LOB data and these filenames:

     1,row1.col1,row1.col2,
     2,row2.col1,row2.col2,
   

3. Perform the LOB data extraction:

   SELECT
     BFILE('row' + string(rowid) + '.' + 'col1',col1),
     BFILE('row' + string(rowid) + '.' + 'col2',col2)
   FROM LobA;
   

   After the extraction, there is a file for each cell of LOB data extracted. For example, if table LobA contains two rows of data with rowid values of 1 and 2, you have these files:

   • row1.col1

   • row1.col2

   • row2.col1

   • row2.col2

4. Reload the extracted data:

   LOAD TABLE LobA
   (rowid,
     col1 ASCII FILE (',') NULL('NULL'),
     col2 BINARY FILE (',') NULL('NULL'))
   FROM LobA_data.txt'
   DELIMITED BY ','
   ROW DELIMITED BY '\n'
   ESCAPES OFF;