You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2017/10/11 20:04:06 UTC
[1/3] incubator-trafodion git commit: Add Chapter 2,
Section 2.7 - 2.12
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 1c9769bc8 -> 6a00d4d36
Add Chapter 2, Section 2.7 - 2.12
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/07226cf1
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/07226cf1
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/07226cf1
Branch: refs/heads/master
Commit: 07226cf18c07516acc49aef12480d39c16876a1c
Parents: 4c9423f
Author: liu.yu <yu...@esgyn.cn>
Authored: Thu Sep 28 16:26:58 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Thu Sep 28 16:26:58 2017 +0800
----------------------------------------------------------------------
.../lob_guide/src/asciidoc/_chapters/about.adoc | 2 +-
.../asciidoc/_chapters/working_with_lob.adoc | 378 ++++++++++++++++++-
docs/lob_guide/src/asciidoc/index.adoc | 2 +-
3 files changed, 379 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/07226cf1/docs/lob_guide/src/asciidoc/_chapters/about.adoc
----------------------------------------------------------------------
diff --git a/docs/lob_guide/src/asciidoc/_chapters/about.adoc b/docs/lob_guide/src/asciidoc/_chapters/about.adoc
index 94e15f2..167e308 100644
--- a/docs/lob_guide/src/asciidoc/_chapters/about.adoc
+++ b/docs/lob_guide/src/asciidoc/_chapters/about.adoc
@@ -61,7 +61,7 @@ This is a new guide.
• 2.9 <<garbage collection,Garbage Collection>> +
• 2.10 <<cleanup of a sql table containing lob columns,Cleanup of a SQL Table Containing LOB Columns>> +
• 2.11 <<showddl for lobs,SHOWDDL for LOBs>> +
-• 2.12 <<getting command for lob tables,Getting Command for LOB Tables>>
+• 2.12 <<getting statement for lob tables,Getting Statement for LOB Tables>>
|===
== Notation Conventions
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/07226cf1/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
----------------------------------------------------------------------
diff --git a/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
index 2e40cf1..fe1c76c 100644
--- a/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
+++ b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
@@ -751,4 +751,380 @@ CQD `LOB_MAX_SIZE` (default 10G expressed in M [10000M]).
* Extract Target Locations
+
-The file to extract to can be a local linux file or a local HDFS file.
\ No newline at end of file
+The file to extract to can be a local linux file or a local HDFS file.
+
+[#deleting column from a sql table containing lob columns]
+== Deleting Column from a SQL Table Containing LOB columns
+
+[#syntax]
+=== Syntax
+
+```
+DELETE lob-column-name FROM table-name [WHERE CLAUSE]
+```
+
+[#considerations]
+=== Considerations
+
+When one or more rows containing LOB columns are deleted from LOB table, only the metadata information is dropped and the hdfs data remains as it is. The references to the lob data are removed from the lob descriptor file.
+
+This mechanism has not been implemented yet as a separate utility but it is triggered as a part of insert, update and append operations. For more information, see <<garbage collection,Garbage Collection>>.
+
+[#dropping a sql table containing lob columns ]
+== Dropping a SQL Table Containing LOB Columns
+
+Drop works like any other drop table. All dependent tables are deleted. All files in hdfs (data and descriptor) files are also deleted.
+
+For more information, see <<drop_table_statement,DROP TABLE Statement>> in http://trafodion.incubator.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual].
+
+[#garbage collection]
+== Garbage Collection
+
+When a lob datafile for a column has reached a certain limit, defined by a CQD `LOB_GC_LIMIT_SIZE`, then a compaction is triggered automatically. +
+The default GC Limit is 10GB and can be changed if needed.
+
+The need for GC arises because when a delete operation or an update operation is performed, the old data black in the hdfs file will be left as unused. +
+In the case of update, the old data will be left as unused and the new data will be written into a new section, so all these “holes” in the LOB data file are needlessly occupying space.
+
+The LOB descriptor chunks file is looked at to see which ranges and offsets are actually used. The LOB datafile is temporarily saved. The compaction is done into a new tempfile. When the sections have all been copied into the tempfile, Trafodion will delete the existing lob data file and rename the tempfile.
+
+Finally, the saved copy of the LOB datafile is dropped. The saved copy is there just in case you need to fall back to it in case of an error. Since this operation is triggered as part of an IUD operation, a definite slowdown will occur for that insert/update operation compared to subsequent inserts/updates.
+
+Also, each lob column of a table can be compacted separately as needed. GC does not have to be done to all columns of the LOB table all at once.
+
+NOTE: Currently the GC is done in the same transaction as the transaction being used for the insert or update operation. If any part of the GC fails, then the entire transaction is aborted.
+
+When Trafodion has support for local transactions, Trafodion will do the GC in a separate transaction or in a separate process, so you can fail the GC with a warning and allow the insert to go through.
+
+Setting the CQD `LOB_GC_LIMIT_SIZE` to 0 would prevent GC from occurring.
+
+[#cleanup of a sql table containing lob columns]
+== Cleanup of a SQL Table Containing LOB Columns
+
+Cleanup works like cleanup of any other table. The command ensures all dependent SQL LOB tables and hdfs files are dropped ignoring errors if any.
+
+For more information, see <<cleanup_statement,CLEANUP Statement>> in http://trafodion.incubator.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual].
+
+[#showddl for lob]
+== SHOWDDL for LOB
+
+SHOWDDL for LOB with a special option will show all the dependent objects, names and details about the table.
+
+[#syntax]
+=== Syntax
+
+```
+SHOWDDL table-name, LOB DETAILS
+```
+
+[#examples]
+=== Examples
+
+* This example displays the details of the table t1ob1.
+
++
+
+```
+>>SHOWDDL tlob1, LOB DETAILS;
+CREATE TABLE TRAFODION.SEABASE.TLOB1
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , C2 BLOB DEFAULT NULL NOT SERIALIZED
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+LOB Metadata
+============
+
+CREATE TABLE TRAFODION.SEABASE.LOBMD_04239091936503896833
+ (
+ LOBNUM SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , STORAGETYPE SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , LOCATION VARCHAR(4096) CHARACTER SET ISO88591 COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , PRIMARY KEY (LOBNUM ASC)
+ )
+;
+
+************************************************
+LobNum: 1
+
+Data Storage
+============
+
+Location: /user/trafodion/lobs
+DataFile: LOBP_04239091936503896833_0001
+
+LOB Descriptor Handle
+=====================
+
+CREATE TABLE TRAFODION.SEABASE."LOBDescHandle_04239091936503896833_0001"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , NUMCHUNKS INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , LOBLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ )
+ STORE BY (DESCPARTNKEY ASC)
+ SALT USING 8 PARTITIONS
+;
+
+LOB Descriptor Chunks
+=====================
+
+CREATE TABLE TRAFODION.SEABASE."LOBDescChunks_04239091936503896833_0001"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DESCSYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKNUM INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , INTPARAM LARGEINT DEFAULT NULL SERIALIZED
+ , STRINGPARAM VARCHAR(400) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED
+ , PRIMARY KEY (DESCPARTNKEY ASC, DESCSYSKEY ASC, CHUNKNUM ASC)
+ )
+ SALT USING 8 PARTITIONS
+;
+
+--- SQL operation complete.
+```
+
+* This example displays the details of the table tlob130ext.
+
++
+
+```
+>>CREATE TABLE tlob130ext (c1 INT NOT NULL, c2 BLOB, c3 CLOB, c4 BLOB STORAGE 'EXTERNAL', primary key (c1));
+
+--- SQL operation complete.
+
+>>SHOWDDL tlob130ext, LOB DETAILS;
+
+CREATE TABLE TRAFODION.SCH.TLOB130EXT
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , C2 BLOB DEFAULT NULL NOT SERIALIZED
+ , C3 CLOB DEFAULT NULL NOT SERIALIZED
+ , C4 BLOB DEFAULT NULL NOT SERIALIZED
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+LOB Metadata
+============
+
+CREATE TABLE TRAFODION.SCH.LOBMD__04474425229029907479
+ (
+ LOBNUM SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , STORAGETYPE SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , LOCATION VARCHAR(4096) CHARACTER SET ISO88591 COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , PRIMARY KEY (LOBNUM ASC)
+ )
+;
+
+************************************************
+LobNum: 1
+
+Data Storage
+============
+
+Location: /user/trafodion/lobs
+DataFile: LOBP_04474425229029907479_0001
+
+LOB Descriptor Handle
+=====================
+
+CREATE TABLE TRAFODION.SCH."LOBDescHandle__04474425229029907479_0001"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , NUMCHUNKS INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , LOBLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ )
+ STORE BY (DESCPARTNKEY ASC)
+ SALT USING 8 PARTITIONS
+;
+
+LOB Descriptor Chunks
+=====================
+
+CREATE TABLE TRAFODION.SCH."LOBDescChunks__04474425229029907479_0001"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DESCSYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKNUM INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DATAOFFSET LARGEINT DEFAULT NULL SERIALIZED
+ , STRINGPARAM VARCHAR(400) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED
+ , PRIMARY KEY (DESCPARTNKEY ASC, DESCSYSKEY ASC, CHUNKNUM ASC)
+ )
+ SALT USING 8 PARTITIONS
+;
+
+************************************************
+LobNum: 2
+
+Data Storage
+============
+
+Location: /user/trafodion/lobs
+DataFile: LOBP_04474425229029907479_0002
+
+LOB Descriptor Handle
+=====================
+
+CREATE TABLE TRAFODION.SCH."LOBDescHandle__04474425229029907479_0002"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , NUMCHUNKS INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , LOBLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ )
+ STORE BY (DESCPARTNKEY ASC)
+ SALT USING 8 PARTITIONS
+;
+
+LOB Descriptor Chunks
+=====================
+
+CREATE TABLE TRAFODION.SCH."LOBDescChunks__04474425229029907479_0002"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DESCSYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKNUM INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DATAOFFSET LARGEINT DEFAULT NULL SERIALIZED
+ , STRINGPARAM VARCHAR(400) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED
+ , PRIMARY KEY (DESCPARTNKEY ASC, DESCSYSKEY ASC, CHUNKNUM ASC)
+ )
+ SALT USING 8 PARTITIONS
+;
+
+************************************************
+LobNum: 3
+
+Data Storage
+============
+
+<External HDFS location>
+<External HDFS file>
+
+LOB Descriptor Handle
+=====================
+
+CREATE TABLE TRAFODION.SCH."LOBDescHandle__04474425229029907479_0003"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , NUMCHUNKS INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , LOBLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ )
+ STORE BY (DESCPARTNKEY ASC)
+ SALT USING 8 PARTITIONS
+;
+
+LOB Descriptor Chunks
+=====================
+
+CREATE TABLE TRAFODION.SCH."LOBDescChunks__04474425229029907479_0003"
+ (
+ DESCPARTNKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DESCSYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKNUM INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , CHUNKLEN LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
+ , DATAOFFSET LARGEINT DEFAULT NULL SERIALIZED
+ , STRINGPARAM VARCHAR(400) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED
+ , PRIMARY KEY (DESCPARTNKEY ASC, DESCSYSKEY ASC, CHUNKNUM ASC)
+ )
+ SALT USING 8 PARTITIONS
+;
+
+--- SQL operation complete.
+```
+
+[#get statement for lob tables]
+== Get Statement for LOB Tables
+
+The GET Statement for lob tables gives column specific information on any lob columns in a table.
+
+[#syntax]
+=== Syntax
+
+```
+GET LOB STATS FOR TABLE table-name;
+SELECT * FROM TABLE(LOB STATS table-name);
+```
+
+[#examples]
+=== Examples
+
+Suppose the following tables have been created:
+
+```
+>>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.
+```
+
+* This Get Statement displays statistics for the table tlob130gt, the lob information is formatted for machine readability.
+
++
+
+```
+>>SELECT left(trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name) , 20), left(trim(column_name),5), left(trim(lob_location),5) , left(trim(lob_data_file),20),LOB_DATA_FILE_SIZE_EOD,LOB_DATA_FILE_SIZE_USED from table(lob stats(tlob130gt));
+
+(EXPR) (EXPR) (EXPR) (EXPR) LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED
+------ ------ ------ ------ ---------------------- ----------------------
+
+TRAFODION.SCH.TLOB13 C2 /user/trafodion/lobs LOBP_044744252290302 15 10
+TRAFODION.SCH.TLOB13 C3 /user/trafodion/lobs LOBP_044744252290302 15 10
+TRAFODION.SCH.TLOB13 C4 /user/trafodion/lobs LOBP_044744252290302 45 30
+
+--- 3 row(s) selected.
+```
+
+
+* This Get Statement displays statistics for the table tlob130gt2, the lob information is formatted for human readability.
+
++
+
+```
+>>GET LOB STATS FOR TABLE tlob130gt2;
+
+Lob Information for table: "TRAFODION".LOB130.TLOB130GT2
+=========================
+
+ColumnName: C2
+Lob Location: /user/trafodion/lobs
+LOB Data File: LOBP_07468755986685501835_0001
+LOB EOD: 0
+LOB Used Len: 0
+ColumnName: C3
+Lob Location: /user/trafodion/lobs
+LOB Data File: LOBP_07468755986685501835_0002
+LOB EOD: 0
+LOB UsedLen: 0
+ColumnName: C4
+Lob Location: External HDFS Location
+LOB Data File: External HDFS File
+LOB EOD: 0
+LOB Used Len: 0
+
+--- SQL operation complete.
+```
+
+* This Get Statement displays statistics for the table tlob130gt2, the lob information is formatted for machine readability.
+
++
+
+```
+>>SELECT left(trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name), 20), left(trim(column_name),5), left(trim(lob_location),15), left(trim(lob_data_file),20),LOB_DATA_FILE_SIZE_EOD,LOB_DATA_FILE_SIZE_USED from table(lob stats(tlob130gt2));
+
+(EXPR) (EXPR) (EXPR) (EXPR) LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED
+------ ------ ------ ------ ---------------------- ----------------------
+
+TRAFODION.SCH.TLOB13 C2 /user/trafodion/lobs LOBP_044744252290300 0 0
+TRAFODION.SCH.TLOB13 C3 /user/trafodion/lobs LOBP_044744252290300 0 0
+TRAFODION.SCH.TLOB13 C4 External HDFS Location External HDFS File 0 0
+
+--- 3 row(s) selected.
+```
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/07226cf1/docs/lob_guide/src/asciidoc/index.adoc
----------------------------------------------------------------------
diff --git a/docs/lob_guide/src/asciidoc/index.adoc b/docs/lob_guide/src/asciidoc/index.adoc
index a6c776c..0505e92 100644
--- a/docs/lob_guide/src/asciidoc/index.adoc
+++ b/docs/lob_guide/src/asciidoc/index.adoc
@@ -50,4 +50,4 @@ include::../../shared/revisions.txt[]
include::asciidoc/_chapters/about.adoc[]
include::asciidoc/_chapters/introduction.adoc[]
-include::asciidoc/_chapters/work_with_lob.adoc[]
\ No newline at end of file
+include::asciidoc/_chapters/working_with_lob.adoc[]
\ No newline at end of file
[3/3] incubator-trafodion git commit: Merge [TRAFODION-2755] PR 1247
Create Traf SQL LOB Guide, part 2
Posted by db...@apache.org.
Merge [TRAFODION-2755] PR 1247 Create Traf SQL LOB Guide, part 2
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/6a00d4d3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/6a00d4d3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/6a00d4d3
Branch: refs/heads/master
Commit: 6a00d4d3609a97e95e8a0646cb796bd006832bc0
Parents: 1c9769b f463ce2
Author: Dave Birdsall <db...@apache.org>
Authored: Wed Oct 11 20:03:10 2017 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Wed Oct 11 20:03:10 2017 +0000
----------------------------------------------------------------------
.../lob_guide/src/asciidoc/_chapters/about.adoc | 2 +-
.../asciidoc/_chapters/working_with_lob.adoc | 407 ++++++++++++++++++-
docs/lob_guide/src/asciidoc/index.adoc | 2 +-
3 files changed, 408 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
[2/3] incubator-trafodion git commit: Incorporate Comments 1
Posted by db...@apache.org.
Incorporate Comments 1
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/f463ce29
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/f463ce29
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/f463ce29
Branch: refs/heads/master
Commit: f463ce29b16b9be39c5c5814ab9357a1f50b5b2f
Parents: 07226cf
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Oct 10 17:28:36 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Oct 10 17:28:36 2017 +0800
----------------------------------------------------------------------
.../asciidoc/_chapters/working_with_lob.adoc | 105 ++++++++++++-------
1 file changed, 67 insertions(+), 38 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/f463ce29/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
----------------------------------------------------------------------
diff --git a/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
index fe1c76c..783c9b6 100644
--- a/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
+++ b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc
@@ -781,7 +781,7 @@ For more information, see <<drop_table_statement,DROP TABLE Statement>> in http:
== Garbage Collection
When a lob datafile for a column has reached a certain limit, defined by a CQD `LOB_GC_LIMIT_SIZE`, then a compaction is triggered automatically. +
-The default GC Limit is 10GB and can be changed if needed.
+The default Garbage Collection (GC) Limit is 10GB and can be changed if needed.
The need for GC arises because when a delete operation or an update operation is performed, the old data black in the hdfs file will be left as unused. +
In the case of update, the old data will be left as unused and the new data will be written into a new section, so all these “holes” in the LOB data file are needlessly occupying space.
@@ -1037,57 +1037,37 @@ CREATE TABLE TRAFODION.SCH."LOBDescChunks__04474425229029907479_0003"
--- SQL operation complete.
```
-[#get statement for lob tables]
-== Get Statement for LOB Tables
+[#get lob statistics for a lob table ]
+== Get Lob Statistics for a LOB Table
+
+There are two ways to get lob statistics for a lob table:
-The GET Statement for lob tables gives column specific information on any lob columns in a table.
+* Get Statement: the lob information is formatted for human readability.
+* Select Statement: the lob information is formatted for machine readability.
+
+
+[#get statment]
+=== Get Statement
[#syntax]
-=== Syntax
+==== Syntax
```
GET LOB STATS FOR TABLE table-name;
-SELECT * FROM TABLE(LOB STATS table-name);
```
[#examples]
-=== Examples
-
-Suppose the following tables have been created:
-
-```
->>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.
-```
+==== Examples
-* This Get Statement displays statistics for the table tlob130gt, the lob information is formatted for machine readability.
+* This Get Statement displays statistics for the table tlob130gt2.
+
```
->>SELECT left(trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name) , 20), left(trim(column_name),5), left(trim(lob_location),5) , left(trim(lob_data_file),20),LOB_DATA_FILE_SIZE_EOD,LOB_DATA_FILE_SIZE_USED from table(lob stats(tlob130gt));
-
-(EXPR) (EXPR) (EXPR) (EXPR) LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED
------- ------ ------ ------ ---------------------- ----------------------
-
-TRAFODION.SCH.TLOB13 C2 /user/trafodion/lobs LOBP_044744252290302 15 10
-TRAFODION.SCH.TLOB13 C3 /user/trafodion/lobs LOBP_044744252290302 15 10
-TRAFODION.SCH.TLOB13 C4 /user/trafodion/lobs LOBP_044744252290302 45 30
-
---- 3 row(s) selected.
-```
-
-
-* This Get Statement displays statistics for the table tlob130gt2, the lob information is formatted for human readability.
+>>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
@@ -1108,16 +1088,65 @@ Lob Location: External HDFS Location
LOB Data File: External HDFS File
LOB EOD: 0
LOB Used Len: 0
+--- SQL operation complete.
+```
+
+[#select statment]
+=== Select Statement
+
+[#syntax]
+==== Syntax
+
+```
+SELECT * FROM TABLE(LOB STATS table-name);
+```
+
+[#examples]
+==== Examples
+
+* This Select Statement displays statistics for the table tlob130gt.
+
++
+
+```
+>>CREATE TABLE tlob130gt (c1 INT NOT NULL, c2 BLOB, c3 CLOB, c4 BLOB, PRIMARY KEY (c1));
--- SQL operation complete.
+
+>>SELECT left(trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name) , 20),
+left(trim(column_name),5),
+left(trim(lob_location),5),
+left(trim(lob_data_file),20),
+LOB_DATA_FILE_SIZE_EOD,
+LOB_DATA_FILE_SIZE_USED
+FROM TABLE(lob stats(tlob130gt));
+
+(EXPR) (EXPR) (EXPR) (EXPR) LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED
+------ ------ ------ ------ ---------------------- ----------------------
+
+TRAFODION.SCH.TLOB13 C2 /user/trafodion/lobs LOBP_044744252290302 15 10
+TRAFODION.SCH.TLOB13 C3 /user/trafodion/lobs LOBP_044744252290302 15 10
+TRAFODION.SCH.TLOB13 C4 /user/trafodion/lobs LOBP_044744252290302 45 30
+
+--- 3 row(s) selected.
```
-* This Get Statement displays statistics for the table tlob130gt2, the lob information is formatted for machine readability.
+* This Select Statement displays statistics for the table tlob130gt2.
+
```
->>SELECT left(trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name), 20), left(trim(column_name),5), left(trim(lob_location),15), left(trim(lob_data_file),20),LOB_DATA_FILE_SIZE_EOD,LOB_DATA_FILE_SIZE_USED from table(lob stats(tlob130gt2));
+>>CREATE TABLE tlob130gt2 (c1 INT NOT NULL, c2 BLOB, c3 CLOB, c4 BLOB STORAGE 'EXTERNAL', PRIMARY KEY (c1));
+
+--- SQL operation complete.
+
+>>SELECT left(trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name), 20),
+left(trim(column_name),5),
+left(trim(lob_location),15),
+left(trim(lob_data_file),20),
+LOB_DATA_FILE_SIZE_EOD,
+LOB_DATA_FILE_SIZE_USED
+FROM TABLE(lob stats(tlob130gt2));
(EXPR) (EXPR) (EXPR) (EXPR) LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED
------ ------ ------ ------ ---------------------- ----------------------