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类型相关的函数有：

LOBTOSTRINGLOBTOFILELOBTOBUFFERSTRINGTOLOBFILETOLOBBUFFERTOLOB

下面演示如何把一个字符串保存到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.