blobs question

[causacn]

Hi,

the column "picture" in table emp_photo from sample db is defined as
blob(102400)
As a Blob type, the actual data should be stored separately from the
base table.
However, when i look at the table structure of emp_photo, the all data
are stored in same default "USERSPACE1 "

What is the normal way to design a table with blob column?
Should i just simply add a column with blob type or need to define one
more column (rowid) to reference actual data in other tablespace?

Thanks!

[Knut Stolze]

ca*****@gmail.c om wrote:

Hi,

the column "picture" in table emp_photo from sample db is defined as
blob(102400)
As a Blob type, the actual data should be stored separately from the
base table.
However, when i look at the table structure of emp_photo, the all data
are stored in same default "USERSPACE1 "

What is the normal way to design a table with blob column?
That depends on your requirements. You can keep the LOBs together with the
primary data in the same tablespace, or you can place it separately. If
you are using raw devices for the primary data, the recommendation is to
place LOBs in a separate tablespace that is file system based so that you
can take advantage of the operating system's file system cache for the
LOBs.
Should i just simply add a column with blob type or need to define one
more column (rowid) to reference actual data in other tablespace?

There is a difference between tablespaces and tables. If you create a table
in a DMS tablespace (managed by database), then you have the option to
place LOB data from all tables in that tablespace in a separate LONG
tablespace. There is nothing on the table-level that you should adjust
(like your suggestion to vertically partition the table).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Pierre Saint-Jacques]

That's because you are definng the table in an SMS tblspc.
When you look in its container, you'll see most likely 4 files.
SQL0000x.DAT, SQL0000x.INX, SQL0000x.LB,SQL 0000x.LBA
The x is the file and table id, DAT is the 4k page data which has a column
for LOB which points to the lov=cation of the LOB in the .LB file, the .LBA
is also used to manage LOB storage.
So, yes, the LOBs are stored in a separate file from the actual data.

Now, if you want to store the LOBs in a separate tblspc. that the data,
you'll have to use DMS tablespace and read Knut's answer to your question.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<ca*****@gmail. com> a écrit dans le message de news:
11************* ********@g43g20 00...legro ups.com...

Hi,

the column "picture" in table emp_photo from sample db is defined as
blob(102400)
As a Blob type, the actual data should be stored separately from the
base table.
However, when i look at the table structure of emp_photo, the all data
are stored in same default "USERSPACE1 "

What is the normal way to design a table with blob column?
Should i just simply add a column with blob type or need to define one
more column (rowid) to reference actual data in other tablespace?

Thanks!