trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sand...@apache.org
Subject [3/8] incubator-trafodion git commit: Support for external lobs. Data files are stored externally in HDFS and only filehandles and file locations are stored in internal Trafodion tables.
Date Wed, 01 Jun 2016 04:22:26 GMT
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a3f2024/core/sql/regress/executor/EXPECTED130
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED130 b/core/sql/regress/executor/EXPECTED130
index bf7d45f..a119aed 100644
--- a/core/sql/regress/executor/EXPECTED130
+++ b/core/sql/regress/executor/EXPECTED130
@@ -53,9 +53,9 @@ C1
 C1           C2
 -----------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ----------------------------------------
 
-          1  LOBH00000200010669695009493408479919669695009498814985418212329581527003582020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                         
-          2  LOBH00000200010669695009493408479919669695009499452607918212329581533443121020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                         
-          3  LOBH00000200010669695009493408479919669695009500088291418212329581539790325020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                         
+          1  LOBH0000000200010321792085830706042419321792085835428245518212331057036978909020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                         
+          2  LOBH0000000200010321792085830706042419321792085835962263118212331057042314939020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                         
+          3  LOBH0000000200010321792085830706042419321792085836459216918212331057047401716020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                         
 
 --- 3 row(s) selected.
 >>
@@ -278,7 +278,8 @@ C1           (EXPR)                          (EXPR)
 >>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/;
+>>sh cp $scriptsdir/executor/TEST130_argfile* $rundir/executor/;
+>>
 >>-- inserts
 >>-- first line
 >>insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt'));
@@ -386,7 +387,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 'LOBH00000200010669695009493412345019669695009536909968118212329581908062051020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'tlob130_txt1.txt');
+>>extract lobtofile(LOB 'LOBH0000000200010321792085830709525319321792085869850100918212331057381219508020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'tlob130_txt1.txt');
 Success. Targetfile :tlob130_txt1.txt  Length : 19
 
 --- SQL operation complete.
@@ -402,7 +403,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 'LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'tlob130_deep.jpg');
+>>extract lobtofile(LOB 'LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'tlob130_deep.jpg');
 Success. Targetfile :tlob130_deep.jpg  Length : 159018
 
 --- SQL operation complete.
@@ -418,7 +419,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 'LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'tlob130_anoush.jpg');
+>>extract lobtofile(LOB 'LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'tlob130_anoush.jpg');
 Success. Targetfile :tlob130_anoush.jpg  Length : 230150
 
 --- SQL operation complete.
@@ -538,7 +539,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 'LOBH00000200010669695009493413536419669695009549202243618212329582030992428020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'hdfs:///lobs/tlob130_txt2.txt');
+>>extract lobtofile(LOB 'LOBH0000000200010321792085830710639319321792085881807198418212331057500820343020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'hdfs:///lobs/tlob130_txt2.txt');
 Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt  Length : 19
 
 --- SQL operation complete.
@@ -554,7 +555,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 'LOBH00000200010669695009493413599619669695009555279429118212329582091774912020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'hdfs:///lobs/tlob130_deep.jpg');
+>>extract lobtofile(LOB 'LOBH0000000200010321792085830710684919321792085887364039618212331057556357004020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'hdfs:///lobs/tlob130_deep.jpg');
 Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg  Length : 159018
 
 --- SQL operation complete.
@@ -570,7 +571,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 'LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'hdfs:///lobs/tlob130_anoush.jpg');
+>>extract lobtofile(LOB 'LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'hdfs:///lobs/tlob130_anoush.jpg');
 Success. Targetfile :hdfs:///lobs/tlob130_anoush.jpg  Length : 230150
 
 --- SQL operation complete.
@@ -589,7 +590,7 @@ Column Name : c2
 Input a filename to extract to : 
 Output File Name : lobc2out.jpg
 Extracting  lob handle for column c2...
-LOB handle for c2: LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130"
+LOB handle for c2: LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130"
 Extracting LOB data length for the above handle...
 LOB data length :230150
 Extracting lob data into file in chunks of 1000 ...
@@ -868,7 +869,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 'LOBH00000200020669695009493415412319669695009566298720718212329582202018367020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'tlob130_deep2.jpg');
+>>extract lobtofile(LOB 'LOBH0000000200020321792085830712377119321792085897256767518212331057655277665020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'tlob130_deep2.jpg');
 Success. Targetfile :tlob130_deep2.jpg  Length : 159018
 
 --- SQL operation complete.
@@ -877,7 +878,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 'LOBH00000200030669695009493415412319669695009566813234018212329582207185399020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'hdfs:///lobs/tlob130_anoush2.jpg');
+>>extract lobtofile(LOB 'LOBH0000000200030321792085830712377119321792085897715067718212331057660007758020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'hdfs:///lobs/tlob130_anoush2.jpg');
 Success. Targetfile :hdfs:///lobs/tlob130_anoush2.jpg  Length : 230150
 
 --- SQL operation complete.
@@ -904,7 +905,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 'LOBH00000200030669695009493415804519669695009570711099518212329582246227037020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                     ' , 'tlob130_anoush3.jpg',create,truncate);
+>>extract lobtofile(LOB 'LOBH0000000200030321792085830712722219321792085901159787918212331057694392521020"TRAFODION"."LOB130"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     ' , 'tlob130_anoush3.jpg',create,truncate);
 Success. Targetfile :tlob130_anoush3.jpg  Length : 230150
 
 --- SQL operation complete.
@@ -994,6 +995,42 @@ C1           (EXPR)      (EXPR)
 >>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 :  /lobs
+  LOB Data File:  LOBP_03217920858307150891_0001
+  LOB EOD :  0
+  LOB Used Len :  0
+  ColumnName :  C3
+  Lob Location :  /lobs
+  LOB Data File:  LOBP_03217920858307150891_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                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307150891_0001                                                                                                                                                                                                                                                         0                        0
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT2                                                                                                                                                                                                                                                        C3                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307150891_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.
@@ -1011,17 +1048,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT
 
   ColumnName :  C2
   Lob Location :  /lobs
-  LOB Data File:  LOBP_06696950094934183379_0001
+  LOB Data File:  LOBP_03217920858307149714_0001
   LOB EOD :  15
   LOB Used Len :  15
   ColumnName :  C3
   Lob Location :  /lobs
-  LOB Data File:  LOBP_06696950094934183379_0002
+  LOB Data File:  LOBP_03217920858307149714_0002
   LOB EOD :  15
   LOB Used Len :  15
   ColumnName :  C4
   Lob Location :  /lobs
-  LOB Data File:  LOBP_06696950094934183379_0003
+  LOB Data File:  LOBP_03217920858307149714_0003
   LOB EOD :  45
   LOB Used Len :  45
 
@@ -1031,9 +1068,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_06696950094934183379_0001                                                                                                                                                                                                                                                        15                       15
-TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C3                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_06696950094934183379_0002                                                                                                                                                                                                                                                        15                       15
-TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C4                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_06696950094934183379_0003                                                                                                                                                                                                                                                        45                       45
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C2                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307149714_0001                                                                                                                                                                                                                                                        15                       15
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C3                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307149714_0002                                                                                                                                                                                                                                                        15                       15
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C4                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307149714_0003                                                                                                                                                                                                                                                        45                       45
 
 --- 3 row(s) selected.
 >>delete from tlob130gt where c1=2;
@@ -1050,17 +1087,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT
 
   ColumnName :  C2
   Lob Location :  /lobs
-  LOB Data File:  LOBP_06696950094934183379_0001
+  LOB Data File:  LOBP_03217920858307149714_0001
   LOB EOD :  30
   LOB Used Len :  25
   ColumnName :  C3
   Lob Location :  /lobs
-  LOB Data File:  LOBP_06696950094934183379_0002
+  LOB Data File:  LOBP_03217920858307149714_0002
   LOB EOD :  31
   LOB Used Len :  26
   ColumnName :  C4
   Lob Location :  /lobs
-  LOB Data File:  LOBP_06696950094934183379_0003
+  LOB Data File:  LOBP_03217920858307149714_0003
   LOB EOD :  71
   LOB Used Len :  56
 
@@ -1070,11 +1107,209 @@ 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_06696950094934183379_0001                                                                                                                                                                                                                                                        30                       25
-TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C3                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_06696950094934183379_0002                                                                                                                                                                                                                                                        31                       26
-TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C4                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_06696950094934183379_0003                                                                                                                                                                                                                                                        71                       56
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C2                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307149714_0001                                                                                                                                                                                                                                                        30                       25
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C3                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307149714_0002                                                                                                                                                                                                                                                        31                       26
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130GT                                                                                                                                                                                                                                                         C4                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307149714_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.
+>>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.
+>>insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///lobs/lob_input_b1.txt'),externaltolob('hdfs:///lobs/lob_input_b1.txt'));
+
+--- 1 row(s) inserted.
+>>insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///lobs/lob_input_c1.txt'),externaltolob('hdfs:///lobs/lob_input_c1.txt'));
+
+--- 1 row(s) inserted.
+>>--negative cases
+>>insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///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:///lobs/lob_input_a1.txt'),filetolob('hdfs:///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:///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:///lobs/lob_input_a1.txt', append) where c1=1;
+
+*** ERROR[15001] A syntax error occurred at or before: 
+update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_a1.txt', append)
+                                                                              ^ (79 characters from start of SQL statement)
+
+*** ERROR[8822] The statement was not prepared.
+
+>>update tlob130ext set c3=externaltolob('hdfs:///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:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///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:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt'));
+
+--- 1 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:///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:///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 :  /lobs
+  LOB Data File:  LOBP_03217920858307159876_0001
+  LOB EOD :  61
+  LOB Used Len :  42
+  ColumnName :  C3
+  Lob Location :  /lobs
+  LOB Data File:  LOBP_03217920858307159876_0002
+  LOB EOD :  106
+  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                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307159876_0001                                                                                                                                                                                                                                                        61                       42
+TRAFODION                                                                                                                                                                                                                                                         LOB130                                                                                                                                                                                                                                                            TLOB130EXT                                                                                                                                                                                                                                                        C3                                                                                                                                                                                                                             
                                    /lobs                                                                                                                                                                                                                                                             LOBP_03217920858307159876_0002                                                                                                                                                                                                                                                       106                       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: LOBH0000000800030321792085830715987619321792085938826265518212331058071128297020"TRAFODION"."LOB130"
+Extracting LOB data length for the above handle...
+LOB data length :19
+Extracting lob data into file in chunks of 1000 ...
+Wrote 19 bytes to file : lobc4ext.txt
+>>sh cat lobc4ext.txt |tee -a LOG130;
+Hey diddle diddle,
+>>
 >>
 >>-- test to ensure all lob dependent tables and schemas containing lob tables
 >>-- get dropped cleanly.
@@ -1101,9 +1336,9 @@ TRAFODION
 Tables in Schema TRAFODION.LOBSCH
 =================================
 
-LOBDescChunks__06696950094934193691_0001
-LOBDescHandle__06696950094934193691_0001
-LOBMD__06696950094934193691
+LOBDescChunks__03217920858307180754_0001
+LOBDescHandle__03217920858307180754_0001
+LOBMD__03217920858307180754
 SB_HISTOGRAMS
 SB_HISTOGRAM_INTERVALS
 TLOB130TS2

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a3f2024/core/sql/regress/executor/TEST130
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST130 b/core/sql/regress/executor/TEST130
index 48da1ed..2f10aed 100755
--- a/core/sql/regress/executor/TEST130
+++ b/core/sql/regress/executor/TEST130
@@ -42,7 +42,7 @@ drop table t130lob1 cascade;
 drop table t130lob2 cascade;
 drop table t130lob3 cascade;
 drop table t130lob4 cascade;
-
+drop table tlob130ext cascade;
 
 ?section setup
 log LOG130 clear;
@@ -167,7 +167,8 @@ create table tlob130bin_limit1K(c1 int not null, c2 blob(1 K), primary key (c1))
 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/;
+sh cp $scriptsdir/executor/TEST130_argfile* $rundir/executor/;
+
 -- inserts
 -- first line
 insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt'));
@@ -448,6 +449,9 @@ drop table tlob130gt;
 ?section lob_get_test
 log LOG130;
 create table tlob130gt (c1 int not null, c2 blob, c3 clob, c4 blob, primary key (c1));
+create table tlob130gt2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1));
+get lob stats for table tlob130gt2;
+select * from table(lob stats(tlob130gt2));
 insert into tlob130gt values (1, stringtolob('xxxx'), stringtolob('yyyy'), stringtolob('zzzzzzzzzzzzzz'));
 insert into tlob130gt values (2, stringtolob('xxxxx'), stringtolob('yyyyy'), stringtolob('zzzzzzzzzzzzzzz'));
 insert into tlob130gt values (3, stringtolob('xxxxxx'), stringtolob('yyyyyy'), stringtolob('zzzzzzzzzzzzzzzz'));
@@ -458,6 +462,41 @@ insert into tlob130gt values (2, stringtolob('xxxxxxxxxxxxxxx'), stringtolob('yy
 get lob stats for table tlob130gt;
 select * from table(lob stats(tlob130gt));
 
+?section lob_external
+--test external lobs
+create table tlob130ext (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1));
+insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt'));
+insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///lobs/lob_input_b1.txt'),externaltolob('hdfs:///lobs/lob_input_b1.txt'));
+insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///lobs/lob_input_c1.txt'),externaltolob('hdfs:///lobs/lob_input_c1.txt'));
+--negative cases
+insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt'));
+insert into tlob130ext values(3, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),filetolob('hdfs:///lobs/lob_input_a1.txt'));
+update tlob130ext set c4=stringtolob('hdfs:///lobs/lob_input_a1.txt', append) where c1=1;
+update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_a1.txt', append) where c1=1;
+update tlob130ext set c3=externaltolob('hdfs:///lobs/lob_input_b1.txt') where c1=1;
+update tlob130ext set c4=stringtolob('cannot allow this') where c1=1;
+delete from tlob130ext where c1=1;
+insert into tlob130ext values(1, stringtolob('first lob'),externaltolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt'));
+insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt'));
+
+select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext;
+update tlob130ext set c3=stringtolob('can allow this') where c1=1;
+select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext;
+
+
+
+update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_d1.txt') where c1=2;
+select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext;
+
+
+update tlob130ext set c2=filetolob('hdfs:///lobs/lob_input_b1.txt') where c1=2;
+select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext;
+get lob stats for table tlob130ext;
+select * from table(lob stats(tlob130ext));
+sh clitestdriver 2 < TEST130_argfile2 2>&1 | tee -a LOG130;
+sh cat lobc4ext.txt |tee -a LOG130;
+
+
 -- test to ensure all lob dependent tables and schemas containing lob tables
 -- get dropped cleanly.
 
@@ -482,6 +521,11 @@ set schema trafodion.lob130;
 log;
 sh rm t130_*;
 sh regrhadoop.ksh fs -rm /lobs/tlob130*
+sh regrhadoop.ksh fs -rm /lobs/lobinput_a1.txt
+sh regrhadoop.ksh fs -rm /lobs/lobinput_b1.txt
+sh regrhadoop.ksh fs -rm /lobs/lobinput_c1.txt
+sh regrhadoop.ksh fs -rm /lobs/lobinput_d1.txt
+sh regrhadoop.ksh fs -rm /lobs/lobinput_e1.txt
 sh rm lobinput_*;
 sh rm deep.jpg;
 sh rm anoush.jpg;
@@ -500,9 +544,11 @@ drop table tlob130bin3;
 drop table tlob130bt;
 drop table tlob130txt2;
 drop table tlob130bin2; 
+drop table tlob130ext;
 sh rm TMP130;
 sh rm tlob130txt2;
 sh rm lobc2out.jpg;
+sh rm lobc4ext.txt;
 sh rm TEST130_argfile;
 drop table tlob130gc;
 drop table tlob130gt;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a3f2024/core/sql/regress/executor/TEST130_argfile2
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST130_argfile2 b/core/sql/regress/executor/TEST130_argfile2
index f123030..181570c 100644
--- a/core/sql/regress/executor/TEST130_argfile2
+++ b/core/sql/regress/executor/TEST130_argfile2
@@ -1,3 +1,3 @@
 TRAFODION.LOB130.tlob130ext
 c4
-lobc4ext.jpg
+lobc4ext.txt



Mime
View raw message