| >>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; |
