>>log;
>>create schema trafodion.lob130;

--- SQL operation complete.
>>set schema trafodion.lob130;

--- SQL operation complete.
>>create table t130lob1 (c1 blob);

--- SQL operation complete.
>>create table t130lob2 (c1 int not null, c2 blob , primary key (c1));

--- SQL operation complete.
>>create table t130lob3 (c1 int not null,
+>c2 blob, c3 blob, primary key (c1));

--- SQL operation complete.
>>create table t130lob4 (c1 int not null,
+>c2 char(10), c3 clob, primary key (c1));

--- SQL operation complete.
>>create table t130char(c1 int not null, c2 char (20), primary key (c1));

--- SQL operation complete.
>>create table t130var(c1 int not null, c2 varchar(100), primary key (c1));

--- SQL operation complete.
>>create external table date_dim
+>  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
+>  for hive.hive.date_dim;

--- SQL operation complete.
>>
>>?section dml_insert
>>insert into t130lob1 values(NULL);

--- 1 row(s) inserted.
>>select * from t130lob1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 1 row(s) selected.
>>
>>insert into t130lob2 values(1,stringtolob('inserted row11'));

--- 1 row(s) inserted.
>>insert into t130lob2 values(2,stringtolob('inserted row12'));

--- 1 row(s) inserted.
>>insert into t130lob2 values(3,stringtolob('inserted row13'));

--- 1 row(s) inserted.
>>insert into t130lob2 values(3,stringtolob('xxxx'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>select * from t130lob2;

C1           C2
-----------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          1  LOBH0000000200010398925915096716996919398925915109826613118212427913592310259020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          2  LOBH0000000200010398925915096716996919398925915109990051018212427913596302251020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          3  LOBH0000000200010398925915096716996919398925915110000589018212427913596408099020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

--- 3 row(s) selected.
>>
>>
>>select lobtostring(c2,10) from t130lob2;

(EXPR)    
----------

inserted r
inserted r
inserted r

--- 3 row(s) selected.
>>select lobtostring(c2,2) from t130lob2;

(EXPR)
------

in    
in    
in    

--- 3 row(s) selected.
>>select c1,lobtostring(c2,100) from t130lob2;

C1           (EXPR)
-----------  ----------------------------------------------------------------------------------------------------

          1  inserted row11                                                                                      
          2  inserted row12                                                                                      
          3  inserted row13                                                                                      

--- 3 row(s) selected.
>>
>>delete from t130lob2;

--- 3 row(s) deleted.
>>--test insert with param (assumes that caller will pass in string format.)
>>prepare s from insert into t130lob2 values (1, ?);

--- SQL command prepared.
>>execute s using 'fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll';

--- 1 row(s) inserted.
>>select lobtostring(c2,50) from t130lob2;

(EXPR)                                            
--------------------------------------------------

fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll              

--- 1 row(s) selected.
>>
>>--negative test for values tuple list
>>prepare s from insert into t130lob2 values (1,'gg'),(2,'hh');

*** ERROR[4483] This LOB conversion function is not allowed in the VALUES clause with multiple input value rows. Use it with a single value row.

*** ERROR[8822] The statement was not prepared.

>>prepare s from insert into t130lob2 values (1,stringtolob('hh')),(2,stringtolob('gg'));

*** ERROR[4483] This LOB conversion function is not allowed in the VALUES clause with multiple input value rows. Use it with a single value row.

*** ERROR[8822] The statement was not prepared.

>>
>>--test update with param 
>>prepare s from update t130lob2 set c2=? where c1 =1;

--- SQL command prepared.
>>execute s using 'xyxyxyxyxyxyx';

--- 1 row(s) updated.
>>select lobtostring(c2,50) from t130lob2;

(EXPR)                                            
--------------------------------------------------

xyxyxyxyxyxyx                                     

--- 1 row(s) selected.
>>
>>--test insert select from a source char column to lob
>>delete from t130lob2;

--- 1 row(s) deleted.
>>insert into t130char values (1,'fixed char 1'),(2,'fixed char 2'),(3,'fixed char 3');

--- 3 row(s) inserted.
>>insert into t130lob2(c1,c2) select c1,c2 from t130char;

--- 3 row(s) inserted.
>>select lobtostring(c2,50) from t130lob2;

(EXPR)                                            
--------------------------------------------------

fixed char 1                                      
fixed char 2                                      
fixed char 3                                      

--- 3 row(s) selected.
>>
>>--test insert select from a source varchar column to lob
>>delete from t130lob2;

--- 3 row(s) deleted.
>>insert into t130var values (1,'var char 1'),(2,'var char 22222222222222222222222'),(3,'var char 33333333333333333333333333333333333333333333333333333');

--- 3 row(s) inserted.
>>insert into t130lob2(c1,c2) select c1,c2 from t130var;

--- 3 row(s) inserted.
>>select lobtostring(c2,100) from t130lob2;

(EXPR)
----------------------------------------------------------------------------------------------------

var char 1                                                                                          
var char 22222222222222222222222                                                                    
var char 33333333333333333333333333333333333333333333333333333                                      

--- 3 row(s) selected.
>>
>>--insert select from a source lob column to a target varchar column.
>>delete from t130var;

--- 3 row(s) deleted.
>>insert into t130var select c1, lobtostring(c2,100) from t130lob2;

--- 3 row(s) inserted.
>>delete from t130var;

--- 3 row(s) deleted.
>>----negative case
>>insert into t130var select c1,c2 from t130lob2;

*** ERROR[4035] Type LOB cannot be cast to type VARCHAR(100).

*** ERROR[8822] The statement was not prepared.

>>delete from t130var;

--- 0 row(s) deleted.
>>delete from t130lob2;

--- 3 row(s) deleted.
>>
>>--test insert select from a source hive varchar column to lob
>>delete from t130lob2;

--- 0 row(s) deleted.
>>insert into t130lob2 select [first 10] d_date_sk,d_date_id from hive.hive.date_dim;

--- 10 row(s) inserted.
>>select lobtostring(c2,4) from t130lob2 order by c1;

(EXPR)
------

AAAA  
AAAA  
AAAA  
AAAA  
AAAA  
AAAA  
AAAA  
AAAA  
AAAA  
AAAA  

--- 10 row(s) selected.
>>delete from t130lob2;

--- 10 row(s) deleted.
>>
>>
>>--insert select from a source lob column to a target lob column
>>delete from t130lob3;

--- 0 row(s) deleted.
>>insert into t130lob2 values(1,stringtolob('inserted row11'));

--- 1 row(s) inserted.
>>insert into t130lob2 values(2,stringtolob('inserted row12'));

--- 1 row(s) inserted.
>>insert into t130lob2 values(3,stringtolob('inserted row13'));

--- 1 row(s) inserted.
>>insert into t130lob3 select c1,c2,c2 from t130lob2;

--- 3 row(s) inserted.
>>select c1, lobtostring(c2,100),lobtostring(c3,100) from t130lob3;

C1           (EXPR)                                                                                                (EXPR)
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------

          1  inserted row11                                                                                        inserted row11                                                                                      
          2  inserted row12                                                                                        inserted row12                                                                                      
          3  inserted row13                                                                                        inserted row13                                                                                      

--- 3 row(s) selected.
>>delete from t130lob3;

--- 3 row(s) deleted.
>>delete from t130lob2;

--- 3 row(s) deleted.
>>
>>
>>
>>
>>--test values clause
>>values((select * from t130lob1));

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 1 row(s) selected.
>>
>>--test empty_blob(), empty_clob()
>>delete from t130lob2;

--- 0 row(s) deleted.
>>insert into t130lob2 values(0,stringtolob('inserted row10'));

--- 1 row(s) inserted.
>>insert into t130lob2 values (1, empty_blob());

--- 1 row(s) inserted.
>>select lobtostring(c2,10) from t130lob2;

(EXPR)    
----------

inserted r
          

--- 2 row(s) selected.
>>delete from t130lob2;

--- 2 row(s) deleted.
>>insert into t130lob2 values(0,stringtolob('inserted row10'));

--- 1 row(s) inserted.
>>insert into t130lob2 values (1, empty_clob());

--- 1 row(s) inserted.
>>select lobtostring(c2,10) from t130lob2;

(EXPR)    
----------

inserted r
          

--- 2 row(s) selected.
>>update t130lob2 set c2=stringtolob('inserted row11');

--- 2 row(s) updated.
>>select lobtostring(c2,30) from t130lob2;

(EXPR)                        
------------------------------

inserted row11                
inserted row11                

--- 2 row(s) selected.
>>update t130lob2 set c2=empty_blob();

--- 2 row(s) updated.
>>select lobtostring(c2,10) from t130lob2;

(EXPR)    
----------

          
--- 2 row(s) selected.
>>update t130lob2 set c2=stringtolob('inserted row11',append);

--- 2 row(s) updated.
>>select lobtostring(c2,30) from t130lob2;

(EXPR)                        
------------------------------

inserted row11                
inserted row11                

--- 2 row(s) selected.
>>update t130lob2 set c2=stringtolob('more inserted row11',append);

--- 2 row(s) updated.
>>select lobtostring(c2,50) from t130lob2;

(EXPR)                                            
--------------------------------------------------

inserted row11more inserted row11                 
inserted row11more inserted row11                 

--- 2 row(s) selected.
>>update t130lob2 set c2=empty_blob();

--- 2 row(s) updated.
>>select lobtostring(c2,10) from t130lob2;

(EXPR)    
----------

          
--- 2 row(s) selected.
>>
>>?section dml_join
>>insert into t130lob3 values (1,stringtolob('inserted row21a'),stringtolob('inserted row21b'));

--- 1 row(s) inserted.
>>insert into t130lob3 values (2,stringtolob('inserted row22a'),stringtolob('inserted row22b'));

--- 1 row(s) inserted.
>>insert into t130lob3 values (3,stringtolob('inserted row23a'),stringtolob('inserted row23b'));

--- 1 row(s) inserted.
>>insert into t130lob3 values (4,stringtolob('inserted row24a'),stringtolob('inserted row24b'));

--- 1 row(s) inserted.
>>
>>prepare s from 
+>select c1, lobtostring(c2,25), lobtostring(c3,25)  from t130lob3;

--- SQL command prepared.
>>
>>
>>
>>select lobtostring(t130lob2.c2,30) from t130lob2, t130lob3 where t130lob2.c1 = t130lob3.c1;

(EXPR)                        
------------------------------

                              
--- 1 row(s) selected.
>>
>>values (filetolob('./myfile.txt'));

*** ERROR[8434] Invalid target column for LOB function. The column needs to be blob/clob type.

--- 0 row(s) selected.
>>values( stringtolob('xxxxxx'));

*** ERROR[8434] Invalid target column for LOB function. The column needs to be blob/clob type.

--- 0 row(s) selected.
>>
>>?section dml_update
>>
>>update t130lob2 set c2=stringtolob('updated c2 in all rows');

--- 2 row(s) updated.
>>select c1, lobtostring(c2,30) from t130lob2;

C1           (EXPR)                        
-----------  ------------------------------

          0  updated c2 in all rows        
          1  updated c2 in all rows        

--- 2 row(s) selected.
>>
>>update t130lob2 set c2=stringtolob('updated row21a') where c1=1;

--- 1 row(s) updated.
>>select c1, lobtostring(c2,30) from t130lob2;

C1           (EXPR)                        
-----------  ------------------------------

          0  updated c2 in all rows        
          1  updated row21a                

--- 2 row(s) selected.
>>select c1, lobtostring(c2,30) from t130lob2 where c1=1;

C1           (EXPR)                        
-----------  ------------------------------

          1  updated row21a                

--- 1 row(s) selected.
>>
>>
>>?section dml_update_append
>>
>>update t130lob2 set c2=stringtolob('appended row21a',append) where c1=1;

--- 1 row(s) updated.
>>select c1, lobtostring(c2,100) from t130lob2;

C1           (EXPR)
-----------  ----------------------------------------------------------------------------------------------------

          0  updated c2 in all rows                                                                              
          1  updated row21aappended row21a                                                                       

--- 2 row(s) selected.
>>select c1, lobtostring(c2,100) from t130lob2 where c1=1;

C1           (EXPR)
-----------  ----------------------------------------------------------------------------------------------------

          1  updated row21aappended row21a                                                                       

--- 1 row(s) selected.
>>
>>update t130lob2 set c2=stringtolob(' appended c2 to all rows',append);

--- 2 row(s) updated.
>>select c1, lobtostring(c2,60) from t130lob2;

C1           (EXPR)
-----------  ------------------------------------------------------------

          0  updated c2 in all rows appended c2 to all rows              
          1  updated row21aappended row21a appended c2 to all rows       

--- 2 row(s) selected.
>>
>>?section dml_delete
>>
>>delete from t130lob3 where c1=1;

--- 1 row(s) deleted.
>>select c1, lobtostring(c2,30), lobtostring(c3,30)  from t130lob3;

C1           (EXPR)                          (EXPR)
-----------  ------------------------------  ------------------------------

          2  inserted row22a                 inserted row22b               
          3  inserted row23a                 inserted row23b               
          4  inserted row24a                 inserted row24b               

--- 3 row(s) selected.
>>
>>delete from t130lob2 ;

--- 2 row(s) deleted.
>>select * from t130lob2;

--- 0 row(s) selected.
>>
>>?section update_stats
>>
>>-- positive tests; LOB columns should be silently skipped
>>update statistics for table t130lob1 on every column;

--- SQL operation complete.
>>
>>update statistics for table t130lob4 on c1 to c2;

--- SQL operation complete.
>>
>>-- negative tests; attempts to explicitly create stats on LOB
>>-- columns should fail with error 9246
>>update statistics for table t130lob4 on c3;

*** ERROR[9246] UPDATE STATISTICS is not supported on LOB columns. Column C3 is a LOB column.

--- SQL operation failed with errors.
>>
>>update statistics for table t130lob2 on (c1,c2);

*** ERROR[9246] UPDATE STATISTICS is not supported on LOB columns. Column C2 is a LOB column.

--- SQL operation failed with errors.
>>
>>update statistics for table t130lob4 on c2 to c3;

*** ERROR[9246] UPDATE STATISTICS is not supported on LOB columns. Column C3 is a LOB column.

--- SQL operation failed with errors.
>>
>>log;
>>--setup
>>sh echo "Test for file input and extract";
>>create table tlob130txt1 (c1 int not null, c2 clob, primary key (c1));

--- SQL operation complete.
>>create table tlob130bin1 (c1 int not null, c2 blob, primary key (c1));

--- SQL operation complete.
>>create table tlob130txt_limit50(c1 int not null, c2 clob(50), primary key (c1));

--- SQL operation complete.
>>create table tlob130bin_limit1K(c1 int not null, c2 blob(1 K), primary key (c1));

--- SQL operation complete.
>>
>>sh cp $scriptsdir/executor/deep.jpg $rundir/executor/;
>>sh cp $scriptsdir/executor/anoush.jpg $rundir/executor/;
>>sh cp $scriptsdir/executor/lob_input_* $rundir/executor/;
>>sh cp $scriptsdir/executor/TEST130_argfile* $rundir/executor/;
>>
>>-- inserts
>>-- first line
>>insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt'));

--- 1 row(s) inserted.
>>
>>-- second line
>>insert into tlob130txt1 values (2, filetolob('lob_input_b1.txt'));

--- 1 row(s) inserted.
>>
>>-- third line
>>insert into tlob130txt1 values (3, filetolob('lob_input_c1.txt'));

--- 1 row(s) inserted.
>>
>>select lobtostring(c2, 40 ) from tlob130txt1;

(EXPR)                                  
----------------------------------------

Hey diddle diddle,
                     
The cat and the fiddle,
                
The cow jumped over the moon.
          

--- 3 row(s) selected.
>>
>>--updates
>>
>>--should update with full poem
>>update tlob130txt1 set c2=filetolob('lob_input_d1.txt', append) where c1 = 3;

--- 1 row(s) updated.
>>
>>select lobtostring(c2, 200 ) from tlob130txt1;

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hey diddle diddle,
                                                                                                                                                                                     
The cat and the fiddle,
                                                                                                                                                                                
The cow jumped over the moon.
The little dog laughed,
To see such sport,

And the dish ran away with the spoon.
                                                                                        

--- 3 row(s) selected.
>>
>>-- should see wrong text in the last few lines
>>update tlob130txt1 set c2=filetolob('lob_input_e1.txt') where c1 =3 ;

--- 1 row(s) updated.
>>
>>select lobtostring(c2, 200 ) from tlob130txt1;

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hey diddle diddle,
                                                                                                                                                                                     
The cat and the fiddle,
                                                                                                                                                                                
The little dog cried,
To see such sport,

And the dish ran away with the fork !
                                                                                                                        

--- 3 row(s) selected.
>>
>>--delete
>>
>>-- go back to having just the first  line. 
>>delete from tlob130txt1 where c1 =2;

--- 1 row(s) deleted.
>>delete from tlob130txt1 where c1 =3;

--- 1 row(s) deleted.
>>
>>--test limits
>>
>>insert into tlob130txt_limit50 values(1,filetolob('lob_input_e1.txt'));

*** ERROR[8442] Unable to access ExpLOBInterfaceInsert interface. Call to ExpLOBInterfaceInsert returned error LOB_MAX_LIMIT_ERROR(560). Error detail: 0. Cause: .

--- 0 row(s) inserted.
>>insert into tlob130bin_limit1K values(1,filetolob('anoush.jpg'));

*** ERROR[8442] Unable to access ExpLOBInterfaceInsert interface. Call to ExpLOBInterfaceInsert returned error LOB_MAX_LIMIT_ERROR(560). Error detail: 0. Cause: .

--- 0 row(s) inserted.
>>--test extract 
>>
>>log;
>>sh rm t130_extract_command;
>>
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_txt1.txt');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200010398925915096719754919398925915135562869418212427913851004829020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'tlob130_txt1.txt');
Success. Targetfile :tlob130_txt1.txt  Length : 19

--- SQL operation complete.
>>
>>--binary input/update
>>
>>insert into tlob130bin1 values (1 , filetolob('deep.jpg'));

--- 1 row(s) inserted.
>>--extract // should have a viewable picture file
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_deep.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200010398925915096719915619398925915136686288718212427913862324790020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'tlob130_deep.jpg');
Success. Targetfile :tlob130_deep.jpg  Length : 159018

--- SQL operation complete.
>>
>>update tlob130bin1 set c2=filetolob('anoush.jpg') ;

--- 1 row(s) updated.
>>
>>--extract into a different file // should have a different viewable picture.
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush.jpg');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200010398925915096719915619398925915136686288718212427913862324790020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'tlob130_anoush.jpg');
Success. Targetfile :tlob130_anoush.jpg  Length : 230150

--- SQL operation complete.
>>
>>
>>
>>
>>-- Test for hdfs input
>>--cleanup -- comment the following lines out if you want to debug this test and save intermediate files.
>>
>>?section lob_hdfs_cleanup
>>log;
>>create table tlob130txt2 (c1 int not null, c2 clob, primary key (c1));

--- SQL operation complete.
>>create table tlob130bin2 (c1 int not null, c2 blob, primary key (c1));

--- SQL operation complete.
>>
>>sh regrhadoop.ksh fs -copyFromLocal lob_input_a1.txt /user/trafodion/lobs/lob_input_a1.txt;
>>sh regrhadoop.ksh fs -copyFromLocal lob_input_b1.txt /user/trafodion/lobs/lob_input_b1.txt;
>>sh regrhadoop.ksh fs -copyFromLocal lob_input_c1.txt /user/trafodion/lobs/lob_input_c1.txt;
>>sh regrhadoop.ksh fs -copyFromLocal lob_input_d1.txt /user/trafodion/lobs/lob_input_d1.txt;
>>sh regrhadoop.ksh fs -copyFromLocal lob_input_e1.txt /user/trafodion/lobs/lob_input_e1.txt;
>>sh regrhadoop.ksh fs -copyFromLocal deep.jpg /user/trafodion/lobs/deep.jpg;
>>sh regrhadoop.ksh fs -copyFromLocal anoush.jpg /user/trafodion/lobs/anoush.jpg;
>>-- the next one is a really long file name intended to test error message 8557
>>sh regrhadoop.ksh fs -copyFromLocal lob_input_a1.txt /user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt;
>>sh sleep(20);
>>
>>
>>insert into tlob130txt2 values (1, filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

--- 1 row(s) inserted.
>>
>>-- second line
>>insert into tlob130txt2 values (2, filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'));

--- 1 row(s) inserted.
>>
>>-- third line
>>insert into tlob130txt2 values (3, filetolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt'));

--- 1 row(s) inserted.
>>select lobtostring(c2, 40 ) from tlob130txt2;

(EXPR)                                  
----------------------------------------

Hey diddle diddle,
                     
The cat and the fiddle,
                
The cow jumped over the moon.
          

--- 3 row(s) selected.
>>
>>--updates
>>
>>--should update with full poem
>>update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_d1.txt', append) where c1 = 3;

--- 1 row(s) updated.
>>select lobtostring(c2, 200 ) from tlob130txt2;

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hey diddle diddle,
                                                                                                                                                                                     
The cat and the fiddle,
                                                                                                                                                                                
The cow jumped over the moon.
The little dog laughed,
To see such sport,

And the dish ran away with the spoon.
                                                                                        

--- 3 row(s) selected.
>>update tlob130txt2 set c2=NULL where c1=3;

--- 1 row(s) updated.
>>select * from tlob130txt2;

C1           C2
-----------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          1  LOBH0000000200010398925915096720669219398925915143384552018212427913929275257020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          2  LOBH0000000200010398925915096720669219398925915143509995318212427913931502076020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          3  ?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 3 row(s) selected.
>>update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_d1.txt', append) where c1 = 3;

--- 1 row(s) updated.
>>select lobtostring(c2, 200 ) from tlob130txt2;

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hey diddle diddle,
                                                                                                                                                                                     
The cat and the fiddle,
                                                                                                                                                                                
The little dog laughed,
To see such sport,

And the dish ran away with the spoon.
                                                                                                                      

--- 3 row(s) selected.
>>
>>-- should see wrong text in the last few lines
>>update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_e1.txt') where c1 =3 ;

--- 1 row(s) updated.
>>select lobtostring(c2, 200 ) from tlob130txt2;

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hey diddle diddle,
                                                                                                                                                                                     
The cat and the fiddle,
                                                                                                                                                                                
The little dog cried,
To see such sport,

And the dish ran away with the fork !
                                                                                                                        

--- 3 row(s) selected.
>>
>>--delete
>>
>>
>>-- go back to having just the first  line. 
>>delete from tlob130txt2 where c1 =2;

--- 1 row(s) deleted.
>>delete from tlob130txt2 where c1 =3;

--- 1 row(s) deleted.
>>-- test extract 
>>
>>log;
>>sh rm t130_extract_command;
>>
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_txt2.txt');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200010398925915096720669219398925915143384552018212427913929275257020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'hdfs:///user/trafodion/lobs/tlob130_txt2.txt');
Success. Targetfile :hdfs:///user/trafodion/lobs/tlob130_txt2.txt  Length : 19

--- SQL operation complete.
>>
>>--binary input/update
>>
>>insert into tlob130bin2 values (1 , filetolob('deep.jpg'));

--- 1 row(s) inserted.
>>--extract // should have a viewable picture file
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_deep.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200010398925915096720819419398925915144231512618212427913937737310020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'hdfs:///user/trafodion/lobs/tlob130_deep.jpg');
Success. Targetfile :hdfs:///user/trafodion/lobs/tlob130_deep.jpg  Length : 159018

--- SQL operation complete.
>>
>>update tlob130bin2 set c2=filetolob('anoush.jpg') ;

--- 1 row(s) updated.
>>
>>--extract into a different file // should have a different viewable picture.
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_anoush.jpg');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200010398925915096719915619398925915136686288718212427913862324790020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'hdfs:///user/trafodion/lobs/tlob130_anoush.jpg');
Success. Targetfile :hdfs:///user/trafodion/lobs/tlob130_anoush.jpg  Length : 230150

--- SQL operation complete.
>>
>>
>>sh clitestdriver 2 < TEST130_argfile 2>&1 | tee -a LOG130;
*************************************
Blob test extract to file in chunks 
************************************
Extract from a lob column in a lob table

Input lob table name :
Table name : TRAFODION.LOB130.tlob130bin1
Input lob column name to extract from :
Column Name : c2
Input a filename to extract to : 
Output File Name : lobc2out.jpg
Extracting  lob handle for column c2...
LOB handle for c2: LOBH0000000200010398925915096719915619398925915136686288718212427913862324790020"TRAFODION"."LOB130"
Extracting LOB data length for the above handle...
LOB data length :230150
Extracting lob data into file in chunks ...
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 10000 bytes to file : lobc2out.jpg
Wrote 150 bytes to file : lobc2out.jpg
>>
>>
>>
>>------------------------------------------------------------------------------
>>
>>?section lob_misc_cleanup
>>log;
>>-- 2 clob columns
>>create table tlob130txt3 (c1 int not null, c2 int, c3 clob, c4 clob, primary key (c1));

--- SQL operation complete.
>>insert into tlob130txt3 values (1, 1,filetolob('lob_input_a1.txt'), filetolob('lob_input_b1.txt'));

--- 1 row(s) inserted.
>>insert into tlob130txt3 values (2, 2,filetolob('lob_input_c1.txt'), filetolob('lob_input_d1.txt'));

--- 1 row(s) inserted.
>>select lobtostring(c3,100), lobtostring(c4,100)from tlob130txt3;

(EXPR)                                                                                                (EXPR)
----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------

Hey diddle diddle,
                                                                                   The cat and the fiddle,
                                                                            
The cow jumped over the moon.
                                                                        The little dog laughed,
To see such sport,

And the dish ran away with the spoon.
                  

--- 2 row(s) selected.
>>
>>-- 2 blob columns
>>create table tlob130bin3 (c1 int not null, c2 int, c3 blob, c4 blob, primary key (c1));

--- SQL operation complete.
>>insert into tlob130bin3 values (1, 1, filetolob('deep.jpg'), filetolob('anoush.jpg'));

--- 1 row(s) inserted.
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_deep2.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200020398925915096721905119398925915153005398018212427914025446780020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'tlob130_deep2.jpg');
Success. Targetfile :tlob130_deep2.jpg  Length : 159018

--- SQL operation complete.
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_anoush2.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200030398925915096721905119398925915153223853618212427914027706616020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'hdfs:///user/trafodion/lobs/tlob130_anoush2.jpg');
Success. Targetfile :hdfs:///user/trafodion/lobs/tlob130_anoush2.jpg  Length : 230150

--- SQL operation complete.
>>
>>-- combination blob and clob columns
>>create table tlob130bt (c1 int not null, c2 int, c3 blob, c4 clob, primary key (c1));

--- SQL operation complete.
>>insert into tlob130bt values (1,1, filetolob('lob_input_a1.txt'), filetolob('anoush.jpg'));

--- 1 row(s) inserted.
>>
>>select lobtostring(c3,40) from tlob130bt;

(EXPR)                                  
----------------------------------------

Hey diddle diddle,
                     

--- 1 row(s) selected.
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush3.jpg',create,truncate);/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract lobtofile(LOB 'LOBH0000000200030398925915096722237619398925915156466167118212427914060068242020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ' , 'tlob130_anoush3.jpg',create,truncate);
Success. Targetfile :tlob130_anoush3.jpg  Length : 230150

--- SQL operation complete.
>>
>>--test alter 
>>alter table tlob130bt add column c5 blob;

--- SQL operation complete.
>>--should show default value NULL
>>select c5 from tlob130bt;

C5
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 1 row(s) selected.
>>
>>?section lob_gc_cleanup
>>log;
>>create table tlob130gc (c1 int not null, c2 blob, c3 blob, primary key (c1));

--- SQL operation complete.
>>--insert a few rows
>>insert into tlob130gc values (1, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (2, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (3, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (4, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (5, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (6, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (7, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>insert into tlob130gc values (8, stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>--create holes in the lob data file
>>delete from tlob130gc where c1=2;

--- 1 row(s) deleted.
>>delete from tlob130gc where c1=6;

--- 1 row(s) deleted.
>>update tlob130gc set c2=stringtolob('xxxx')where c1=7;

--- 1 row(s) updated.
>>--check contents of table
>>select c1,lobtostring(c2,10),lobtostring(c3,10) from tlob130gc;

C1           (EXPR)      (EXPR)    
-----------  ----------  ----------

          1  aaaa        bbbbb     
          3  aaaa        bbbbb     
          4  aaaa        bbbbb     
          5  aaaa        bbbbb     
          7  xxxx        bbbbb     
          8  aaaa        bbbbb     

--- 6 row(s) selected.
>>cqd LOB_GC_LIMIT_SIZE '0';

--- SQL operation complete.
>>--this next insert should trigger a GC
>>insert into tlob130gc values(9,stringtolob('aaaa'), stringtolob('bbbbb'));

--- 1 row(s) inserted.
>>--check contents of table. Should have one extra row compared to above select
>>select c1,lobtostring(c2,10),lobtostring(c3,10) from tlob130gc;

C1           (EXPR)      (EXPR)    
-----------  ----------  ----------

          1  aaaa        bbbbb     
          3  aaaa        bbbbb     
          4  aaaa        bbbbb     
          5  aaaa        bbbbb     
          7  xxxx        bbbbb     
          8  aaaa        bbbbb     
          9  aaaa        bbbbb     

--- 7 row(s) selected.
>>cqd LOB_GC_LIMIT_SIZE reset;

--- SQL operation complete.
>>
>>?section lob_get_cleanup
>>log;
>>create table tlob130gt (c1 int not null, c2 blob, c3 clob, c4 blob, primary key (c1));

--- SQL operation complete.
>>create table tlob130gt2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1));

--- SQL operation complete.
>>get lob stats for table tlob130gt2;


Lob Information for table: "TRAFODION".LOB130.TLOB130GT2
=========================

  ColumnName :  C2
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967233649_0001
  LOB EOD :  0
  LOB Used Len :  0
  ColumnName :  C3
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967233649_0002
  LOB EOD :  0
  LOB Used Len :  0
  ColumnName :  C4
  Lob Location :  External HDFS Location
  LOB Data File:  External HDFS File
  LOB EOD :  0
  LOB Used Len :  0

--- SQL operation complete.
>>select * from table(lob stats(tlob130gt2));

CATALOG_NAME                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                       OBJECT_NAME                                                                                                                                                                                                                                                       COLUMN_NAME                                                                                                                                                                                                                                                       LOB_LOCATION                                                                                                                                                                                                                                                      LOB_DATA_FILE                                                                                                                                                                                                                                                     LOB_DATA_FILE_SIZE_EOD  LOB_DATA_FILE_SIZE_USED
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------  -----------------------

TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT2                                                                                                                                                                                                                                                        C2                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967233649_0001                                                                                                                                                                                                                                                         0                        0
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT2                                                                                                                                                                                                                                                        C3                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967233649_0002                                                                                                                                                                                                                                                         0                        0
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT2                                                                                                                                                                                                                                                        C4                                                                                                                                                                                                                                                                External HDFS Location                                                                                                                                                                                                                                            External HDFS File                                                                                                                                                                                                                                                                     0                        0

--- 3 row(s) selected.
>>insert into tlob130gt values (1, stringtolob('xxxx'), stringtolob('yyyy'), stringtolob('zzzzzzzzzzzzzz'));

--- 1 row(s) inserted.
>>insert into tlob130gt values (2, stringtolob('xxxxx'), stringtolob('yyyyy'), stringtolob('zzzzzzzzzzzzzzz'));

--- 1 row(s) inserted.
>>insert into tlob130gt values (3, stringtolob('xxxxxx'), stringtolob('yyyyyy'), stringtolob('zzzzzzzzzzzzzzzz'));

--- 1 row(s) inserted.
>>get lob stats for table tlob130gt;


Lob Information for table: "TRAFODION".LOB130.TLOB130GT
=========================

  ColumnName :  C2
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967230440_0001
  LOB EOD :  15
  LOB Used Len :  15
  ColumnName :  C3
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967230440_0002
  LOB EOD :  15
  LOB Used Len :  15
  ColumnName :  C4
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967230440_0003
  LOB EOD :  45
  LOB Used Len :  45

--- SQL operation complete.
>>select * from table(lob stats(tlob130gt));

CATALOG_NAME                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                       OBJECT_NAME                                                                                                                                                                                                                                                       COLUMN_NAME                                                                                                                                                                                                                                                       LOB_LOCATION                                                                                                                                                                                                                                                      LOB_DATA_FILE                                                                                                                                                                                                                                                     LOB_DATA_FILE_SIZE_EOD  LOB_DATA_FILE_SIZE_USED
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------  -----------------------

TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C2                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967230440_0001                                                                                                                                                                                                                                                        15                       15
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C3                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967230440_0002                                                                                                                                                                                                                                                        15                       15
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C4                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967230440_0003                                                                                                                                                                                                                                                        45                       45

--- 3 row(s) selected.
>>delete from tlob130gt where c1=2;

--- 1 row(s) deleted.
>>insert into tlob130gt values (2, stringtolob('xxxxxxxxxxxxxxx'), stringtolob('yyyyyyyyyyyyyyyy'), stringtolob('zzzzzzzzzzzzzzzzzzzzzzzzzz'));

--- 1 row(s) inserted.
>>get lob stats for table tlob130gt;


Lob Information for table: "TRAFODION".LOB130.TLOB130GT
=========================

  ColumnName :  C2
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967230440_0001
  LOB EOD :  30
  LOB Used Len :  25
  ColumnName :  C3
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967230440_0002
  LOB EOD :  31
  LOB Used Len :  26
  ColumnName :  C4
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967230440_0003
  LOB EOD :  71
  LOB Used Len :  56

--- SQL operation complete.
>>select * from table(lob stats(tlob130gt));

CATALOG_NAME                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                       OBJECT_NAME                                                                                                                                                                                                                                                       COLUMN_NAME                                                                                                                                                                                                                                                       LOB_LOCATION                                                                                                                                                                                                                                                      LOB_DATA_FILE                                                                                                                                                                                                                                                     LOB_DATA_FILE_SIZE_EOD  LOB_DATA_FILE_SIZE_USED
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------  -----------------------

TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C2                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967230440_0001                                                                                                                                                                                                                                                        30                       25
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C3                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967230440_0002                                                                                                                                                                                                                                                        31                       26
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C4                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967230440_0003                                                                                                                                                                                                                                                        71                       56

--- 3 row(s) selected.
>>
>>?section lob_external
>>--test external lobs
>>create table tlob130ext (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1));

--- SQL operation complete.
>>
>>create table tlob130ext2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1));

--- SQL operation complete.
>>create table tlob130_not_external (c1 int not null , c2 blob, c3 blob, c4 blob, primary key (c1));

--- SQL operation complete.
>>insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

--- 1 row(s) inserted.
>>insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'));

--- 1 row(s) inserted.
>>insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt'));

--- 1 row(s) inserted.
>>
>>
>>log;
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract name(LOB '/g" | sed "s/$/');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract name(LOB 'LOBH0000000200020398925915096723884819398925915180791519818212427914304316031020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ');
 LOB filename : /user/trafodion/lobs/LOBP_03989259150967238848_0002

--- SQL operation complete.
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract offset(LOB '/g" | sed "s/$/');/g" >> t130_extract_command;
>>obey t130_extract_command;
>>extract offset(LOB 'LOBH0000000200020398925915096723884819398925915180791519818212427914304316031020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ');
 LOB Offset : 43

--- SQL operation complete.
>>
>>
>>
>>
>>--negative cases
>>insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

*** ERROR[1432] Input LOB type 8 does not match column's storage type: 2 Column name: C2 .

*** ERROR[8822] The statement was not prepared.

>>insert into tlob130ext values(3, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

*** ERROR[1432] Input LOB type 2 does not match column's storage type: 8 Column name: C4 .

*** ERROR[8822] The statement was not prepared.

>>update tlob130ext set c4=stringtolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt', append) where c1=1;

*** ERROR[1432] Input LOB type 2 does not match column's storage type: 8 Column name: C4 .

*** ERROR[8822] The statement was not prepared.

>>update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt', append) where c1=1;

*** ERROR[15001] A syntax error occurred at or before: 
update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_a
1.txt', append) where c1=1;
              ^ (94 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>update tlob130ext set c3=externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt') where c1=1;

*** ERROR[1432] Input LOB type 8 does not match column's storage type: 2 Column name: C3 .

*** ERROR[8822] The statement was not prepared.

>>update tlob130ext set c4=stringtolob('cannot allow this') where c1=1;

*** ERROR[1432] Input LOB type 2 does not match column's storage type: 8 Column name: C4 .

*** ERROR[8822] The statement was not prepared.

>>delete from tlob130ext where c1=1;

--- 1 row(s) deleted.
>>insert into tlob130ext values(1, stringtolob('first lob'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

*** ERROR[1432] Input LOB type 8 does not match column's storage type: 2 Column name: C3 .

*** ERROR[8822] The statement was not prepared.

>>insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

--- 1 row(s) inserted.
>>-- the next one should see error 8557
>>insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),
+>externaltolob('hdfs:///user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt'));

*** ERROR[8557] The file name passed to externaltolob exceeds 256 bytes.

--- 0 row(s) inserted.
>>
>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext;

(EXPR)                                              (EXPR)                                              (EXPR)
--------------------------------------------------  --------------------------------------------------  --------------------------------------------------

first lob                                           Hey diddle diddle,
                                 Hey diddle diddle,
                               
second lob                                          The cat and the fiddle,
                            The cat and the fiddle,
                          
third lob                                           The cow jumped over the moon.
                      The cow jumped over the moon.
                    

--- 3 row(s) selected.
>>update tlob130ext set c3=stringtolob('can allow this') where c1=1;

--- 1 row(s) updated.
>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext;

(EXPR)                                              (EXPR)                                              (EXPR)
--------------------------------------------------  --------------------------------------------------  --------------------------------------------------

first lob                                           can allow this                                      Hey diddle diddle,
                               
second lob                                          The cat and the fiddle,
                            The cat and the fiddle,
                          
third lob                                           The cow jumped over the moon.
                      The cow jumped over the moon.
                    

--- 3 row(s) selected.
>>
>>
>>
>>update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_d1.txt') where c1=2;

--- 1 row(s) updated.
>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext;

(EXPR)                                              (EXPR)                                              (EXPR)
--------------------------------------------------  --------------------------------------------------  --------------------------------------------------

first lob                                           can allow this                                      Hey diddle diddle,
                               
second lob                                          The cat and the fiddle,
                            The little dog laughed,
To see such sport,

And th
third lob                                           The cow jumped over the moon.
                      The cow jumped over the moon.
                    

--- 3 row(s) selected.
>>
>>
>>update tlob130ext set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt') where c1=2;

--- 1 row(s) updated.
>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext;

(EXPR)                                              (EXPR)                                              (EXPR)
--------------------------------------------------  --------------------------------------------------  --------------------------------------------------

first lob                                           can allow this                                      can allow this                                    
The cat and the fiddle,
                            The cat and the fiddle,
                            The cat and the fiddle,
                          
third lob                                           The cow jumped over the moon.
                      The cow jumped over the moon.
                    

--- 3 row(s) selected.
>>get lob stats for table tlob130ext;


Lob Information for table: "TRAFODION".LOB130.TLOB130EXT
=========================

  ColumnName :  C2
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967238848_0001
  LOB EOD :  70
  LOB Used Len :  42
  ColumnName :  C3
  Lob Location :  /user/trafodion/lobs
  LOB Data File:  LOBP_03989259150967238848_0002
  LOB EOD :  125
  LOB Used Len :  68
  ColumnName :  C4
  Lob Location :  External HDFS Location
  LOB Data File:  External HDFS File
  LOB EOD :  0
  LOB Used Len :  0

--- SQL operation complete.
>>select * from table(lob stats(tlob130ext));

CATALOG_NAME                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                       OBJECT_NAME                                                                                                                                                                                                                                                       COLUMN_NAME                                                                                                                                                                                                                                                       LOB_LOCATION                                                                                                                                                                                                                                                      LOB_DATA_FILE                                                                                                                                                                                                                                                     LOB_DATA_FILE_SIZE_EOD  LOB_DATA_FILE_SIZE_USED
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------  -----------------------

TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130EXT                                                                                                                                                                                                                                                        C2                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967238848_0001                                                                                                                                                                                                                                                        70                       42
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130EXT                                                                                                                                                                                                                                                        C3                                                                                                                                                                                                                                                                /user/trafodion/lobs                                                                                                                                                                                                                                              LOBP_03989259150967238848_0002                                                                                                                                                                                                                                                       125                       68
TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130EXT                                                                                                                                                                                                                                                        C4                                                                                                                                                                                                                                                                External HDFS Location                                                                                                                                                                                                                                            External HDFS File                                                                                                                                                                                                                                                                     0                        0

--- 3 row(s) selected.
>>sh clitestdriver 2 < TEST130_argfile2 2>&1 | tee -a LOG130;
*************************************
Blob test extract to file in chunks 
************************************
Extract from a lob column in a lob table

Input lob table name :
Table name : TRAFODION.LOB130.tlob130ext
Input lob column name to extract from :
Column Name : c4
Input a filename to extract to : 
Output File Name : lobc4ext.txt
Extracting  lob handle for column c4...
LOB handle for c4: LOBH0000000800030398925915096723884819398925915180923835018212427914305639837020"TRAFODION"."LOB130"
Extracting LOB data length for the above handle...
LOB data length :19
Extracting lob data into file in chunks ...
Wrote 19 bytes to file : lobc4ext.txt
>>sh cat lobc4ext.txt |tee -a LOG130;
Hey diddle diddle,
>>
>>--insert select from a source external lob column to a target external lob column
>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext;

(EXPR)                                              (EXPR)                                              (EXPR)
--------------------------------------------------  --------------------------------------------------  --------------------------------------------------

first lob                                           can allow this                                      can allow this                                    
The cat and the fiddle,
                            The cat and the fiddle,
                            The cat and the fiddle,
                          
third lob                                           The cow jumped over the moon.
                      The cow jumped over the moon.
                    

--- 3 row(s) selected.
>>insert into tlob130ext2 select * from tlob130ext;

--- 3 row(s) inserted.
>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext2;

(EXPR)                                              (EXPR)                                              (EXPR)
--------------------------------------------------  --------------------------------------------------  --------------------------------------------------

first lob                                           can allow this                                      can allow this                                    
The cat and the fiddle,
                            The cat and the fiddle,
                            The cat and the fiddle,
                          
third lob                                           The cow jumped over the moon.
                      The cow jumped over the moon.
                    

--- 3 row(s) selected.
>>----negative test
>>insert into tlob130_not_external select * from tlob130ext;

*** ERROR[1432] Input LOB type 8 does not match column's storage type: 2 Column name: C4 .

*** ERROR[8822] The statement was not prepared.

>>?section update_lob_handle
>>-- test lob update via "update lob" command
>>log;
>>delete from t130lob5;

--- 0 row(s) deleted.
>>insert into t130lob5 values (1,empty_blob());

--- 1 row(s) inserted.
>>sh clitestdriver 6 < TEST130_argfile3 2>&1 | tee -a LOG130;
*************************************
Blob test update  lobhandle from a buffer 
Input lob table name :
Table name : TRAFODION.LOB130.t130lob5
Input lob column name to get handle from :
Column Name : c2
Extracting  lob handle for column c2...
LOB handle for c2: LOBH0000000200010398925915096725384219398925915186962270218212427914365164689020"TRAFODION"."LOB130"
>>select lobtostring(c2,20) from t130lob5;

(EXPR)              
--------------------

zzzzzzzzzzzzzzzzzzzz

--- 1 row(s) selected.
>>sh clitestdriver 7 < TEST130_argfile3 2>&1 | tee -a LOG130;
*************************************
Blob test append  lobhandle from a buffer 
Input lob table name :
Table name : TRAFODION.LOB130.t130lob5
Input lob column name to get handle from :
Column Name : c2
Extracting  lob handle for column c2...
LOB handle for c2: LOBH0000000200010398925915096725384219398925915186962270218212427914365164689020"TRAFODION"."LOB130"
>>select lobtostring(c2,40) from t130lob5;

(EXPR)                                  
----------------------------------------

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

--- 1 row(s) selected.
>>sh clitestdriver 8 < TEST130_argfile3 2>&1 | tee -a LOG130;
*************************************
Blob test empty  lobhandle from a buffer 
Input lob table name :
Table name : TRAFODION.LOB130.t130lob5
Input lob column name to get handle from :
Column Name : c2
Extracting  lob handle for column c2...
LOB handle for c2: LOBH0000000200010398925915096725384219398925915186962270218212427914365164689020"TRAFODION"."LOB130"
>>select lobtostring(c2,20) from t130lob5;

(EXPR)              
--------------------

                    
--- 1 row(s) selected.
>>sh clitestdriver 6 < TEST130_argfile3 2>&1 | tee -a LOG130;
*************************************
Blob test update  lobhandle from a buffer 
Input lob table name :
Table name : TRAFODION.LOB130.t130lob5
Input lob column name to get handle from :
Column Name : c2
Extracting  lob handle for column c2...
LOB handle for c2: LOBH0000000200010398925915096725384219398925915186962270218212427914365164689020"TRAFODION"."LOB130"
>>select lobtostring(c2,40) from t130lob5;

(EXPR)                                  
----------------------------------------

zzzzzzzzzzzzzzzzzzzz                    

--- 1 row(s) selected.
>>delete from t130lob5;

--- 1 row(s) deleted.
>>
>>
>>-- test_empty_blob() with external tables
>>delete from tlob130ext;

--- 3 row(s) deleted.
>>-- following empty_blob will be of type 'external'
>>insert into tlob130ext values(1,empty_blob(),empty_clob(),empty_blob());

--- 1 row(s) inserted.
>>select c2 from tlob130ext;

C2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LOBH0000000200010398925915096723884819398925915195303539218212427914449438981020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

--- 1 row(s) selected.
>>-- following should return error since only external lobs will be allowed
>>update tlob130ext set c4=stringtolob('gggg');

*** ERROR[1432] Input LOB type 2 does not match column's storage type: 8 Column name: C4 .

*** ERROR[8822] The statement was not prepared.

>>-- following should work. 
>>update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt');

--- 1 row(s) updated.
>>delete from tlob130ext;

--- 1 row(s) deleted.
>>
>>-- test to ensure all lob dependent tables and schemas containing lob tables
>>-- get dropped cleanly.
>>
>>?section lob_drop_table_schema
>>log;
>>create schema trafodion.lobsch;

--- SQL operation complete.
>>set schema trafodion.lobsch;

--- SQL operation complete.
>>create table tlob130ts1 (c1 int not null, c2 blob, primary key (c1));

--- SQL operation complete.
>>create table tlob130ts2 (c1 int not null, c2 blob, primary key (c1));

--- SQL operation complete.
>>drop table tlob130ts1;

--- SQL operation complete.
>>get tables;

Tables in Schema TRAFODION.LOBSCH
=================================

LOBDescChunks__03989259150967266691_0001
LOBDescHandle__03989259150967266691_0001
LOBMD__03989259150967266691
SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
TLOB130TS2

=======================
 7 row(s) returned

--- SQL operation complete.
>>drop schema trafodion.lobsch cascade;

--- SQL operation complete.
>>select OBJECT_NAME,OBJECT_TYPE from TRAFODION."_MD_".OBJECTS where catalog_name='TRAFODION' AND schema_name='LOBSCH';

--- 0 row(s) selected.
>>--go back to the schema used for the rest of the tests.
>>set schema trafodion.lob130;

--- SQL operation complete.
>>
>>
>>-- cleanup
>>?section lob_general_cleanup
>>log;