Add Chapter 2, Section 2.5 & 2.6
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/9421e119 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/9421e119 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/9421e119 Branch: refs/heads/master Commit: 9421e119bbd47974a818a3d68e662066f30b89d6 Parents: 4282d41 Author: liu.yu <[email protected]> Authored: Tue Sep 12 17:31:51 2017 +0800 Committer: liu.yu <[email protected]> Committed: Tue Sep 12 17:31:51 2017 +0800 ---------------------------------------------------------------------- .../src/asciidoc/_chapters/work_with_lob.adoc | 210 ++++++++++++++++++- 1 file changed, 209 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/9421e119/docs/lob_guide/src/asciidoc/_chapters/work_with_lob.adoc ---------------------------------------------------------------------- diff --git a/docs/lob_guide/src/asciidoc/_chapters/work_with_lob.adoc b/docs/lob_guide/src/asciidoc/_chapters/work_with_lob.adoc index c92f704..eb561c4 100644 --- a/docs/lob_guide/src/asciidoc/_chapters/work_with_lob.adoc +++ b/docs/lob_guide/src/asciidoc/_chapters/work_with_lob.adoc @@ -527,4 +527,212 @@ For example, if the external file changes in size, the descriptor file needs to + Similarly, if a column is declared without the `STORAGE 'External'` attribute, the `EXTERNALTOLOB` function cannot be used to insert/update data into that column. + -That is, once the storage for a LOB column is declared at CREATE time, the attribute is set and cannot be modified. \ No newline at end of file +That is, once the storage for a LOB column is declared at CREATE time, the attribute is set and cannot be modified. + +[#select column from a sql table containing lob columns] +== Select Column from a SQL Table Containing LOB Columns + +Selecting a row from a table will give back the lob handle for the lob column. + +NOTE: The entry in each lob column in the SQL table only contains the LOB handle. + +Once the LOB handle has been retrieved, it can be used to retrieve the actual lob data from HDFS. + +[#syntax] +=== Syntax + +``` +SELECT lob_query-expr[, lob_query-expr] FROM table-name +``` + +``` +lob_query_expr is: +lobtostring(lob column) | +lob column +``` + +[#examples] +=== Examples + +* This example selects c2 from table tlob1 and returns the lob handle of c2. ++ +``` +SELECT c2 FROM tlob1; +C2 +---------------------------------------------------------------------------------------------------- + +LOBH00000200010423909193650389683319694857010382259683718212310961182290216021"TRAFODION"."SEABASE" + +--- 1 row(s) selected. +``` + +[#extract lob data from a sql table containing lob columns] +== Extract LOB Data from a SQL Table Containing LOB Columns + +Extract lob data can be done in two ways: + +* Extract lob data into a file for a given lob handle ++ +Extract from a LOB column straight into an on-platform linux or HDFS file in one shot. + ++ +Trafodion engine will take care of buffering the data and retrieve the lob data into the target file. + +* Extract lob data into a user specified buffer ++ +Extract from a LOB column into a target user buffer of a specified size. ++ +Trafodion engine will retrieve exactly the amount of requested data. ++ +The user/application will be responsible for redriving the extract until EOD. ++ +The extracted buffer data can then be written to files or another location as the application chooses. + +[#extract lob data into a file for a given lob handle] +=== Extract Lob Data into a File for a Given Lob Handle + +[#syntax] +==== Syntax + +``` +EXTRACT LOBTOFILE (LOB 'lob handle as quoted string', 'filename URI format' [OPTION]) +``` + +``` +OPTION is: +[, TRUNCATE] +[, CREATE, TRUNCATE] +[, APPEND] +[, CREATE, APPEND] +``` + +[#semantics] +==== Semantics + +* `TRUNCATE` ++ +If the target file exists, Trafodion will truncate and write to it. ++ +If the target file does not exist, an error will be raised. + +* `CREATE, TRUNCATE` ++ +If the target file exists, Trafodion will truncate and write to it. ++ +If the target file does not exist, create a table, then write to it. + +* `APPEND` ++ +If the target file exists, Trafodion will append to it. ++ +If the target file does not exist, an error will be raised. + +* `CREATE, APPEND` ++ +If the target file exists, Trafodion will append to it. ++ +If the target file does not exist, create a table, then write to it. + +[#considerations] +==== Considerations + +If the target file exists, the `OPTION` must be specified, or else an error will be raised. This is the default behavior. + +If the target file does not exist, you can create a target file by specifying the `OPTION`. + +[#examples] +==== Examples + +* This example extracts LOB to the tlob130_txt1.txt: + ++ +``` +EXTRACT LOBTOFILE (LOB 'LOBH00000200010520117997292583625519884121437206093184618212317486018305654020"TRAFODION"."LOB130"', 'tlob130_txt1.txt'); +Success. Targetfile:tlob130_txt1.txt Length: 19 +``` + +* This example extracts LOB to the tlob130_deep.jpg: ++ +``` +EXTRACT LOBTOFILE (LOB 'LOBH00000200010520117997292583681719884121437210516812518212317486062586654020"TRAFODION"."LOB130"', +Success. Targetfile:tlob130_deep.jpg Length: 159018 +``` + +[#extract lob data into a user specified buffer] +=== Extract Lob Data into a User Specified Buffer + +Extract from a LOB column into a target user buffer of a specified size. Trafodion engine will retrieve exactly the amount of requested data. The user/application will be responsible for redriving the extract until EOD. Then the extracted buffer data can be written to files or another location as the application chooses. + +Extract LOB data into a user specified buffer like a cursor until EOD is returned. For this method, the user specifies an input buffer and specifies the input length in an in/out variable. + +For each execution of the extract, Trafodion will return SUCCESS, ERROR or EOD. +For the cases of SUCCESS or EOD, a length will also be returned to the user, so the user knows exactly how much data was actually extracted and returned. + +[#syntax] +==== Syntax + +``` +EXTRACT LOBTOBUFFER (LOB 'lob handle as quoted string', LOCATION lob output buffer address as long, SIZE input/output address of length container as long) +``` + +[#extract lob length for a given lob handle] +=== Extract Lob Length for a Given Lob Handle + +Extract from a LOB column straight into an on-platform linux or HDFS file in one shot. + +[#syntax] +==== Syntax + +``` +EXTRACT LOBLENGTH (LOB 'lob handle as quoted string'[, LOCATION address of length container for lob length]) +``` + +[#semantics] +==== Semantics + +* `_LOCATION address of length container for lob length_` + ++ +This is used by programs/applications that will use this syntax to retrieve the LOB length prior to extracting data. The address should be an address of a 64-bit container that will hold the LOB length. ++ +If the length is omitted or 0, only the status message is returned that displays the length. + +[#examples] +==== Examples + +* This example extracts LOB length and returns 30. + ++ +``` +EXTRACT LOBLENGTH (LOB 'LOBH0000000800030554121478481170502119554121478546064413218212330526373762019024"TRAFODION"."TESTEXTLOB"'); +LOB Length: 30 +--- SQL operation complete. +``` + +* This example extracts LOB length and returns 4. + ++ +``` +EXTRACT LOBLENGTH (LOB 'LOBH00000200010423909193650389683319694857010382259683718212310961182290216021"TRAFODION"."SEABASE"'); +LOB Length: 4 +--- SQL operation complete. +``` + +[#considerations] +=== Considerations + +* LOB Max Extract Data Length ++ +CQD `LOB_OUTPUT_SIZE` (default 32000) controls the maximum data length that can be extracted. + +* LOB Max Extract Chunk Length ++ +CQD `LOB_MAX_CHUNK_MEM_SIZE` (512 MB expressed in bytes [536870912]) controls the maximum chunk of data that can be read from HDFS into memory and written to the target file location. + +* LOB Max Size ++ +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
