Introduction to the use of LOB (Large object) objects in DB2 for z/OS

Match to store complex files, images, and videos, DB2 for os/390 Version 6 introduces LOB (Large object) technology to support a single LOB object less than 2G while allowing a single LOB column to store 65536 terabytes of data altogether. DB2 supports the following three types of LOB objects:
BLOB (BInary Large object): A binary LOB object for storing images, sounds, and video;

CLOB (Character Large object): A character lob object for storing character data;

Dbclob (Double byte Character Large Object): Used to store data based on large DBCS (Double-byte character set) characters.

This article describes how to create a DB2 table containing BLOB objects, as well as backup, recovery, and other maintenance operations on LOB tables, all of which are based on DB2 for z/Os Version 10.

Suppose we need to create a table in database test to store the image, the table name is picture, which contains two columns: Name and data, where name is the name of the image, and data is the image, which is stored using a Blob object.

First, create a table space

CREATE tablespace Picture_ds

In TEST USING stogroup Sysdeflt

Priqty 4000

Secqty 2000

Numparts 31

Segsize 64

Locksize any

Freepage 10

Bufferpool BP0

Close YES;

CREATE LOB tablespace Data_ds

In TEST USING stogroup Sysdeflt

Priqty 4000

Secqty 2000

Locksize LOB

Bufferpool BP0

Close YES;

Using a Blob object requires creating two tablespaces: One is the base table space for storing the picture of the table, the other is the auxiliary tablespace that stores the LOB object, and the Create LOB tablespace is required when creating the table space of the LOB object. DB2 also supports the way inline lob, LOB objects and table data are stored in the same tablespace, and the inline lob is not discussed in this article.

Second, create tables and indexes

CREATE TABLE Picture

(

NAME CHAR not NULL,

DATA BLOB (6M) not NULL,

PRIMARY KEY (NAME)

)

In Picture_ds;

CREATE UNIQUE INDEX

Ix_picture

On the picture (NAME)

USING Stogroup Sysdeflt

Priqty 1000

Secqty 200;

CREATE AUX TABLE Aux_data

In TEST. Data_ds

STORES picture

COLUMN DATA;

CREATE UNIQUE INDEX Ix_data

On Aux_data;

When you create a table picture, specifying the data column format for blob,6m indicates that the maximum image is 6M. For query efficiency, we also created an index for the Aux_data table.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/db2/