Trafodion LOB大对象

Trafodion支持大对象类型，包括blob和clob，clob(Character Large Object)使用CHAR来保存数据，如保存XML文档，blob(Binary Large Object)，二进制大对象，可以存储二进制文件如图片。
注：不建议在LOB字段上创建主键，也不建议在LOB字段上创建索引，一个表可以包含多个LOB字段。

下面例子演示创建带有blob和clob字段的表，

>>cqd traf_blob_as_varchar 'off';

--- SQL operation complete.
>>cqd traf_clob_as_varchar 'off';

--- SQL operation complete.
>>create table test_lob_tbl (a blob, b clob);

--- SQL operation complete.
>>showddl test_lob_tbl;

CREATE TABLE TRAFODION.SEABASE.TEST_LOB_TBL
  (
    A                                BLOB DEFAULT NULL NOT SERIALIZED
  , B                                CLOB DEFAULT NULL NOT SERIALIZED
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.

LOB类型相关的函数有：

• LOBTOSTRING
• LOBTOFILE
• LOBTOBUFFER
• STRINGTOLOB
• FILETOLOB
• BUFFERTOLOB

下面演示如何把一个字符串保存到clob中以及如何把一个文件保存到blob中，

>>insert into test_lob_tbl values(filetolob('test.txt'),stringtolob('abcdefg'));

--- 1 row(s) inserted.

现在，让我们来从lob字段中拿出刚刚保存的文件和字符串吧
（注：用extract命令将保存在LOB对象中的文本、图片等二进制文件抽取并保存到对应的文件中）

>>select a from test_lob_tbl;

A
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LOBH0000000200010452004884911267714619452004885257433198118212355808993728656021"TRAFODION"."SEABASE"
>>extract lobtofile (LOB 'LOBH0000000200010452004884911267714619452004885257433198118212355808993728656021"TRAFODION"."SEABASE"', 'test.txt1');
Success. Targetfile :test.txt1  Length : 8

--- SQL operation complete.

>>select lobtostring(b) from test_lob_tbl;

(EXPR)
-------
abcdefg

下面命令用来查询LOB对象的详细信息，

>>showddl test_lob_tbl,lob details;

CREATE TABLE TRAFODION.SEABASE.TEST_LOB_TBL
  (
    A                                BLOB DEFAULT NULL NOT SERIALIZED
  , B                                CLOB DEFAULT NULL NOT SERIALIZED
  )
 ATTRIBUTES ALIGNED FORMAT
;

LOB Metadata
============

CREATE TABLE TRAFODION.SEABASE.LOBMD__04520048849112677146
  (
    LOBNUM                           SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , STORAGETYPE                      SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , LOCATION                         VARCHAR(4096) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , PRIMARY KEY (LOBNUM ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

************************************************
LobNum: 1

Data Storage
============

Location: /lobs
DataFile: LOBP_04520048849112677146_0001

LOB Descriptor Handle
=====================

CREATE TABLE TRAFODION.SEABASE."LOBDescHandle__04520048849112677146_0001"
  (
    DESCPARTNKEY                     LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , NUMCHUNKS                        INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , LOBLEN                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  )
  STORE BY (DESCPARTNKEY ASC)
  SALT USING 8 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

LOB Descriptor Chunks
=====================

CREATE TABLE TRAFODION.SEABASE."LOBDescChunks__04520048849112677146_0001"
  (
    DESCPARTNKEY                     LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , DESCSYSKEY                       LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , CHUNKNUM                         INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , CHUNKLEN                         LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , DATAOFFSET                       LARGEINT DEFAULT NULL NOT SERIALIZED
  , STRINGPARAM                      VARCHAR(400) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , PRIMARY KEY (DESCPARTNKEY ASC, DESCSYSKEY ASC, CHUNKNUM ASC)
  )
  SALT USING 8 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

************************************************
LobNum: 2

Data Storage
============

Location: /lobs
DataFile: LOBP_04520048849112677146_0002

LOB Descriptor Handle
=====================

CREATE TABLE TRAFODION.SEABASE."LOBDescHandle__04520048849112677146_0002"
  (
    DESCPARTNKEY                     LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , NUMCHUNKS                        INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , LOBLEN                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  )
  STORE BY (DESCPARTNKEY ASC)
  SALT USING 8 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

LOB Descriptor Chunks
=====================

CREATE TABLE TRAFODION.SEABASE."LOBDescChunks__04520048849112677146_0002"
  (
    DESCPARTNKEY                     LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , DESCSYSKEY                       LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , CHUNKNUM                         INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , CHUNKLEN                         LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , DATAOFFSET                       LARGEINT DEFAULT NULL NOT SERIALIZED
  , STRINGPARAM                      VARCHAR(400) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , PRIMARY KEY (DESCPARTNKEY ASC, DESCSYSKEY ASC, CHUNKNUM ASC)
  )
  SALT USING 8 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.