You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sa...@apache.org on 2017/05/02 17:24:12 UTC
[2/7] incubator-trafodion git commit: Support for insert-select for
tables containing LOB columns.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b8cfdc87/core/sql/regress/executor/EXPECTED130
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED130 b/core/sql/regress/executor/EXPECTED130
index 8481fe4..0542d39 100644
--- a/core/sql/regress/executor/EXPECTED130
+++ b/core/sql/regress/executor/EXPECTED130
@@ -63,9 +63,9 @@ C1
C1 C2
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
- 1 LOBH0000000200010238590751241267070319238590751254203356618212357742378141700020"TRAFODION"."LOB130"
- 2 LOBH0000000200010238590751241267070319238590751255113052818212357742389924709020"TRAFODION"."LOB130"
- 3 LOBH0000000200010238590751241267070319238590751256002486018212357742398569388020"TRAFODION"."LOB130"
+ 1 LOBH000000020001001830865086431665681818308650874102010718212359906316509152020"TRAFODION"."LOB130"
+ 2 LOBH000000020001001830865086431665681818308650874653085818212359906323581515020"TRAFODION"."LOB130"
+ 3 LOBH000000020001001830865086431665681818308650875141948918212359906328520376020"TRAFODION"."LOB130"
--- 3 row(s) selected.
>>
@@ -178,14 +178,38 @@ var char 33333333333333333333333333333333333333333333333333333
--- 3 row(s) selected.
>>
->>--test insert select from a source hive varchar column to lob
+>>--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,40) from t130lob2;
+>>select lobtostring(c2,40) from t130lob2 order by c1;
(EXPR)
----------------------------------------
@@ -202,6 +226,46 @@ AAAAAAAAGLJNECAA
AAAAAAAAHLJNECAA
--- 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));
@@ -216,7 +280,7 @@ C1
>>--test empty_blob(), empty_clob()
>>delete from t130lob2;
---- 10 row(s) deleted.
+--- 0 row(s) deleted.
>>insert into t130lob2 values (1, empty_blob());
--- 1 row(s) inserted.
@@ -590,7 +654,7 @@ And the dish ran away with the fork !
>>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 'LOBH0000000200010238590751241275912619238590751341141492618212357743249653246020"TRAFODION"."LOB130"
' , 'tlob130_txt1.txt');
+>>extract lobtofile(LOB 'LOBH000000020001001830865086432349061818308650940724338718212359906984262764020"TRAFODION"."LOB130"
' , 'tlob130_txt1.txt');
Success. Targetfile :tlob130_txt1.txt Length : 19
--- SQL operation complete.
@@ -606,7 +670,7 @@ Success. Targetfile :tlob130_txt1.txt Length : 19
>>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 'LOBH0000000200010238590751241276057419238590751351241808618212357743351377111020"TRAFODION"."LOB130"
' , 'tlob130_deep.jpg');
+>>extract lobtofile(LOB 'LOBH000000020001001830865086432359101818308650946460854518212359907041579214020"TRAFODION"."LOB130"
' , 'tlob130_deep.jpg');
Success. Targetfile :tlob130_deep.jpg Length : 159018
--- SQL operation complete.
@@ -622,7 +686,7 @@ Success. Targetfile :tlob130_deep.jpg Length : 159018
>>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 'LOBH0000000200010238590751241276057419238590751351241808618212357743351377111020"TRAFODION"."LOB130"
' , 'tlob130_anoush.jpg');
+>>extract lobtofile(LOB 'LOBH000000020001001830865086432359101818308650946460854518212359907041579214020"TRAFODION"."LOB130"
' , 'tlob130_anoush.jpg');
Success. Targetfile :tlob130_anoush.jpg Length : 230150
--- SQL operation complete.
@@ -744,7 +808,7 @@ And the dish ran away with the fork !
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_txt2.txt');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH0000000200010238590751241278039119238590751361756602718212357743456430010020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_txt2.txt');
+>>extract lobtofile(LOB 'LOBH000000020001001830865086432481141818308650954225440318212359907119296896020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_txt2.txt');
Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt Length : 19
--- SQL operation complete.
@@ -760,7 +824,7 @@ Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt Length : 19
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_deep.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH0000000200010238590751241278182219238590751370769831118212357743546724266020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_deep.jpg');
+>>extract lobtofile(LOB 'LOBH000000020001001830865086432492771818308650960155056018212359907178472834020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_deep.jpg');
Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg Length : 159018
--- SQL operation complete.
@@ -776,7 +840,7 @@ Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg Length : 159018
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_anoush.jpg');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH0000000200010238590751241276057419238590751351241808618212357743351377111020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_anoush.jpg');
+>>extract lobtofile(LOB 'LOBH000000020001001830865086432359101818308650946460854518212359907041579214020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_anoush.jpg');
Success. Targetfile :hdfs:///lobs/tlob130_anoush.jpg Length : 230150
--- SQL operation complete.
@@ -795,240 +859,33 @@ Column Name : c2
Input a filename to extract to :
Output File Name : lobc2out.jpg
Extracting lob handle for column c2...
-LOB handle for c2: LOBH0000000200010238590751241276057419238590751351241808618212357743351377111020"TRAFODION"."LOB130"
+LOB handle for c2: LOBH000000020001001830865086432359101818308650946460854518212359907041579214020"TRAFODION"."LOB130"
Extracting LOB data length for the above handle...
LOB data length :230150
-Extracting lob data into file in chunks of 1000 ...
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
-Wrote 1000 bytes to file : lobc2out.jpg
+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
>>
>>
@@ -1076,7 +933,7 @@ And the dish ran away with the spoon.
>>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 'LOBH0000000200020238590751241280977919238590751387875790718212357743717775017020"TRAFODION"."LOB130"
' , 'tlob130_deep2.jpg');
+>>extract lobtofile(LOB 'LOBH000000020002001830865086432700361818308650973720712718212359907314128843020"TRAFODION"."LOB130"
' , 'tlob130_deep2.jpg');
Success. Targetfile :tlob130_deep2.jpg Length : 159018
--- SQL operation complete.
@@ -1085,7 +942,7 @@ Success. Targetfile :tlob130_deep2.jpg Length : 159018
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_anoush2.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH0000000200030238590751241280977919238590751388595276918212357743725014951020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_anoush2.jpg');
+>>extract lobtofile(LOB 'LOBH000000020003001830865086432700361818308650974204477218212359907318971086020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_anoush2.jpg');
Success. Targetfile :hdfs:///lobs/tlob130_anoush2.jpg Length : 230150
--- SQL operation complete.
@@ -1112,7 +969,7 @@ Hey diddle diddle,
>>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 'LOBH0000000200030238590751241281586319238590751394919981418212357743788172209020"TRAFODION"."LOB130"
' , 'tlob130_anoush3.jpg',create,truncate);
+>>extract lobtofile(LOB 'LOBH000000020003001830865086432746671818308650978649997618212359907363638742020"TRAFODION"."LOB130"
' , 'tlob130_anoush3.jpg',create,truncate);
Success. Targetfile :tlob130_anoush3.jpg Length : 230150
--- SQL operation complete.
@@ -1213,12 +1070,12 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT2
ColumnName : C2
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412857314_0001
+ LOB Data File: LOBP_00183086508643303323_0001
LOB EOD : 0
LOB Used Len : 0
ColumnName : C3
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412857314_0002
+ LOB Data File: LOBP_00183086508643303323_0002
LOB EOD : 0
LOB Used Len : 0
ColumnName : C4
@@ -1233,8 +1090,8 @@ Lob Information for table: "TRAFODION".LOB130.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
/lobs LOBP_02385907512412857314_0001 0 0
-TRAFODION LOB130 TLOB130GT2 C3
/lobs LOBP_02385907512412857314_0002 0 0
+TRAFODION LOB130 TLOB130GT2 C2
/lobs LOBP_00183086508643303323_0001 0 0
+TRAFODION LOB130 TLOB130GT2 C3
/lobs LOBP_00183086508643303323_0002 0 0
TRAFODION LOB130 TLOB130GT2 C4
External HDFS Location External HDFS File 0 0
--- 3 row(s) selected.
@@ -1255,17 +1112,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT
ColumnName : C2
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412854101_0001
+ LOB Data File: LOBP_00183086508643300979_0001
LOB EOD : 15
LOB Used Len : 15
ColumnName : C3
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412854101_0002
+ LOB Data File: LOBP_00183086508643300979_0002
LOB EOD : 15
LOB Used Len : 15
ColumnName : C4
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412854101_0003
+ LOB Data File: LOBP_00183086508643300979_0003
LOB EOD : 45
LOB Used Len : 45
@@ -1275,9 +1132,9 @@ Lob Information for table: "TRAFODION".LOB130.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
/lobs LOBP_02385907512412854101_0001 15 15
-TRAFODION LOB130 TLOB130GT C3
/lobs LOBP_02385907512412854101_0002 15 15
-TRAFODION LOB130 TLOB130GT C4
/lobs LOBP_02385907512412854101_0003 45 45
+TRAFODION LOB130 TLOB130GT C2
/lobs LOBP_00183086508643300979_0001 15 15
+TRAFODION LOB130 TLOB130GT C3
/lobs LOBP_00183086508643300979_0002 15 15
+TRAFODION LOB130 TLOB130GT C4
/lobs LOBP_00183086508643300979_0003 45 45
--- 3 row(s) selected.
>>delete from tlob130gt where c1=2;
@@ -1294,17 +1151,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT
ColumnName : C2
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412854101_0001
+ LOB Data File: LOBP_00183086508643300979_0001
LOB EOD : 30
LOB Used Len : 25
ColumnName : C3
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412854101_0002
+ LOB Data File: LOBP_00183086508643300979_0002
LOB EOD : 31
LOB Used Len : 26
ColumnName : C4
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412854101_0003
+ LOB Data File: LOBP_00183086508643300979_0003
LOB EOD : 71
LOB Used Len : 56
@@ -1314,9 +1171,9 @@ Lob Information for table: "TRAFODION".LOB130.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
/lobs LOBP_02385907512412854101_0001 30 25
-TRAFODION LOB130 TLOB130GT C3
/lobs LOBP_02385907512412854101_0002 31 26
-TRAFODION LOB130 TLOB130GT C4
/lobs LOBP_02385907512412854101_0003 71 56
+TRAFODION LOB130 TLOB130GT C2
/lobs LOBP_00183086508643300979_0001 30 25
+TRAFODION LOB130 TLOB130GT C3
/lobs LOBP_00183086508643300979_0002 31 26
+TRAFODION LOB130 TLOB130GT C4
/lobs LOBP_00183086508643300979_0003 71 56
--- 3 row(s) selected.
>>
@@ -1325,6 +1182,12 @@ TRAFODION
>>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:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt'));
--- 1 row(s) inserted.
@@ -1405,8 +1268,7 @@ 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.
-
+ The cow jumped over the moon.
--- 3 row(s) selected.
>>update tlob130ext set c3=stringtolob('can allow this') where c1=1;
@@ -1423,8 +1285,7 @@ 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.
-
+ The cow jumped over the moon.
--- 3 row(s) selected.
>>
@@ -1446,8 +1307,7 @@ To see such sport,
And th
third lob The cow jumped over the moon.
- The cow jumped over the moon.
-
+ The cow jumped over the moon.
--- 3 row(s) selected.
>>
@@ -1478,12 +1338,12 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130EXT
ColumnName : C2
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412872171_0001
+ LOB Data File: LOBP_00183086508643314774_0001
LOB EOD : 70
LOB Used Len : 42
ColumnName : C3
Lob Location : /lobs
- LOB Data File: LOBP_02385907512412872171_0002
+ LOB Data File: LOBP_00183086508643314774_0002
LOB EOD : 125
LOB Used Len : 68
ColumnName : C4
@@ -1498,8 +1358,8 @@ Lob Information for table: "TRAFODION".LOB130.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
<TRUNCATED>