Table & Tablespace size

[Stanley Sinclair]

About to create a table which will "include" a BLOB. Am not sure how
large to make the container and the tablespace.

What I see says that BLOB is stored "separately." However, I don't
know where.

With a table kinda like:

CREATE TABLE BLOB_TABLE (
BLOB_ID INT,
BLOB_NAME VARCHAR(100),
BLOB_IMAGE BLOB(10M),
BLOB_TYPE VARCHAR(10),

What size tablespace should be used? bufferpool? how should I figure
the table size?

Where is the BLOB in fact?

[Pierre Saint-Jacques]

Stanley, the answer as usual is it depends:
1) The LOB data never goes to a buffer pool. When the lob is retrieved,
one by one, it goes to application private memory for the coordinator
agent of your appl. I believe it will be in the aslheapsz or Appl. Supp.
Layer Heap Size.
2) If SMS Tblspc.
The BLOBs will be stored in a separate file SQL000nn.LB with an file
allocator file named SQL000nn.LBA in the container directory of the
tblspc. the 000nn correlates to the tableid entry in the syscat.tables,
tableid column.
3) If DMS tablespace, in the same tblspc as the data, they will be
stored in different extents of the tblspc. than what is used by the data
pages. "CREATE TABLE MYSTUFF .... IN DMSTBSP"
4) If DMS tablespace, different than the table, the tblspc will be
defined as a "long tablespace".
"CREATE TABLE MYSTUFF .... IN DMSTBSP LONG IN DMSLOGTBSP"

Assuming every row has a blob, its pointer is about a 100 bytes (more
complex than that but work with this), then your row size is roughly 215
bytes. Build the table and use the control center size estimator to
figure out the table size. The blobs, I guess you figure an average
size for them, multiply by expected row number and come up with the
additionnal size to add to the SMS tblsp or DMS tblsp or for the
separate tblspc. I seem to remember a technique to do this.

It's late and I'll try to look for it and send it to you.
Regards, Pierre.

Stanley Sinclair wrote:

About to create a table which will "include" a BLOB. Am not sure how
large to make the container and the tablespace.

What I see says that BLOB is stored "separately." However, I don't
know where.

With a table kinda like:

CREATE TABLE BLOB_TABLE (
BLOB_ID INT,
BLOB_NAME VARCHAR(100),
BLOB_IMAGE BLOB(10M),
BLOB_TYPE VARCHAR(10),

What size tablespace should be used? bufferpool? how should I figure
the table size?

Where is the BLOB in fact?

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Pierre Saint-Jacques]

Stanley, the answer as usual is it depends:
1) The LOB data never goes to a buffer pool. When the lob is retrieved,
one by one, it goes to application private memory for the coordinator
agent of your appl. I believe it will be in the aslheapsz or Appl. Supp.
Layer Heap Size.
2) If SMS Tblspc.
The BLOBs will be stored in a separate file SQL000nn.LB with an file
allocator file named SQL000nn.LBA in the container directory of the
tblspc. the 000nn correlates to the tableid entry in the syscat.tables,
tableid column.
3) If DMS tablespace, in the same tblspc as the data, they will be
stored in different extents of the tblspc. than what is used by the data
pages. "CREATE TABLE MYSTUFF .... IN DMSTBSP"
4) If DMS tablespace, different than the table, the tblspc will be
defined as a "long tablespace".
"CREATE TABLE MYSTUFF .... IN DMSTBSP LONG IN DMSLOGTBSP"

Assuming every row has a blob, its pointer is about a 100 bytes (more
complex than that but work with this), then your row size is roughly 215
bytes. Build the table and use the control center size estimator to
figure out the table size. The blobs, I guess you figure an average
size for them, multiply by expected row number and come up with the
additionnal size to add to the SMS tblsp or DMS tblsp or for the
separate tblspc. I seem to remember a technique to do this.

It's late and I'll try to look for it and send it to you.
Regards, Pierre.

Stanley Sinclair wrote:

About to create a table which will "include" a BLOB. Am not sure how
large to make the container and the tablespace.

What I see says that BLOB is stored "separately." However, I don't
know where.

With a table kinda like:

CREATE TABLE BLOB_TABLE (
BLOB_ID INT,
BLOB_NAME VARCHAR(100),
BLOB_IMAGE BLOB(10M),
BLOB_TYPE VARCHAR(10),

What size tablespace should be used? bufferpool? how should I figure
the table size?

Where is the BLOB in fact?

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.