Buffer pool and de-compression questions

Louis P Walton Jr

Buffer pool and de-compression questions
Question 1.
When compressed tablespace tables are loaded into buffers are they de-
compressed when they go in or after they are physical read?

Question 2.
I have a table with a length of 21032 bytes. Its TS is in a 32K buffer.
The DB2 manual says a 32k buffer is made up of 8 4K buffers. Here is my
question. Then a record is read and is put in the 32K buffer, does is
go into a 32K area and waste around 11k buffer bytes. Or does it go into
6 4K buffers and only waste 3000 bytes? I am thinking 11k bytes but am
not sure.

Here is what the table looks like --
CREATE TABLE AAA.BBB
(CC CHAR(3) NOT NULL WITH DEFAULT,
MID CHAR(15) NOT NULL WITH DEFAULT,
SEQN DECIMAL(7) NOT NULL WITH DEFAULT,
TINFO VARCHAR(21000) NOT NULL WITH DEFAULT


Can you also point me to the manuals where you got your answers.

Thanks for your help,

Pirk

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Buffer pool and de-compression questions
(in response to Louis P Walton Jr)
If it's DB2 compression, then the pages are STILL compressed in the
bufferpool and they are decompressed only when they are needed.


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Pirk Walton
Sent: 14 December 2004 16:31
To: [login to unmask email]
Subject: Buffer pool and de-compression questions

Question 1.
When compressed tablespace tables are loaded into buffers are they de-
compressed when they go in or after they are physical read?

Question 2.
I have a table with a length of 21032 bytes. Its TS is in a 32K buffer.
The DB2 manual says a 32k buffer is made up of 8 4K buffers. Here is
my
question. Then a record is read and is put in the 32K buffer, does
is
go into a 32K area and waste around 11k buffer bytes. Or does it go
into
6 4K buffers and only waste 3000 bytes? I am thinking 11k bytes but
am
not sure.

Here is what the table looks like --
CREATE TABLE AAA.BBB
(CC CHAR(3) NOT NULL WITH DEFAULT,
MID CHAR(15) NOT NULL WITH DEFAULT,
SEQN DECIMAL(7) NOT NULL WITH DEFAULT,
TINFO VARCHAR(21000) NOT NULL WITH DEFAULT


Can you also point me to the manuals where you got your answers.

Thanks for your help,

Pirk

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Andy Lankester

Re: Buffer pool and de-compression questions
(in response to Phil Grainger)
Q1: (De)Compression is a row level process, so decompression is only done
when a row is being examined for selection criteria or FETCHed. Compression
is done on insert/update/load

Q2: not sure but I think the unit of transfer is the full 32K

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Pirk Walton
Sent: 14 December 2004 16:31
To: [login to unmask email]
Subject: Buffer pool and de-compression questions


Question 1.
When compressed tablespace tables are loaded into buffers are they de-
compressed when they go in or after they are physical read?

Question 2.
I have a table with a length of 21032 bytes. Its TS is in a 32K buffer.
The DB2 manual says a 32k buffer is made up of 8 4K buffers. Here is my
question. Then a record is read and is put in the 32K buffer, does is
go into a 32K area and waste around 11k buffer bytes. Or does it go into
6 4K buffers and only waste 3000 bytes? I am thinking 11k bytes but am
not sure.

Here is what the table looks like --
CREATE TABLE AAA.BBB
(CC CHAR(3) NOT NULL WITH DEFAULT,
MID CHAR(15) NOT NULL WITH DEFAULT,
SEQN DECIMAL(7) NOT NULL WITH DEFAULT,
TINFO VARCHAR(21000) NOT NULL WITH DEFAULT


Can you also point me to the manuals where you got your answers.

Thanks for your help,

Pirk

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rob --- Sr. Database Administrator --- CFS Crane

Re: Buffer pool and de-compression questions
(in response to Andy Lankester)
Q2: On V8 the CI matches the page size so would assume that is the only choice on V8. Not sure about V7, but would assume the full 32K based on my own swag. I say this since the workfile only uses 4K or 32K regardless of how much above 4K you go in your sort requirement, and assume the same (allocate it all based on page size of the ts) approach is being used by the buffer manager.
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Andy Lankester
Sent: Tuesday, December 14, 2004 10:56 AM
To: [login to unmask email]
Subject: Re: Buffer pool and de-compression questions

Q1: (De)Compression is a row level process, so decompression is only done
when a row is being examined for selection criteria or FETCHed. Compression
is done on insert/update/load

Q2: not sure but I think the unit of transfer is the full 32K

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Pirk Walton
Sent: 14 December 2004 16:31
To: [login to unmask email]
Subject: Buffer pool and de-compression questions


Question 1.
When compressed tablespace tables are loaded into buffers are they de-
compressed when they go in or after they are physical read?

Question 2.
I have a table with a length of 21032 bytes. Its TS is in a 32K buffer.
The DB2 manual says a 32k buffer is made up of 8 4K buffers. Here is my
question. Then a record is read and is put in the 32K buffer, does is
go into a 32K area and waste around 11k buffer bytes. Or does it go into
6 4K buffers and only waste 3000 bytes? I am thinking 11k bytes but am
not sure.

Here is what the table looks like --
CREATE TABLE AAA.BBB
(CC CHAR(3) NOT NULL WITH DEFAULT,
MID CHAR(15) NOT NULL WITH DEFAULT,
SEQN DECIMAL(7) NOT NULL WITH DEFAULT,
TINFO VARCHAR(21000) NOT NULL WITH DEFAULT


Can you also point me to the manuals where you got your answers.

Thanks for your help,

Pirk

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



*******************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
*******************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Nick Cianci

Re: Buffer pool and de-compression questions
(in response to Rob --- Sr. Database Administrator --- CFS Crane)
Hi Rob

FYI:- I remember it being said (either an IDUG or an IBM course ~
can't remember now) that 32K page were still handled in 4K I/O chunks.
However this was circa DB2 V3 or V4; so I won't vouch for the validity of
that statement now.


Pirk,
The data page that you are storing that record is a 32K page; so I
would expect DB2 to read the full 32K.

However just because your column definition is VARchar(21,000) don't assume
that you are wasting the other 11K (although it is a possibility). Unless
you've defined the Number of rows per page as 1 it is still possible to get
more than one record per page. For instance where you have 2 instances of
TINFO with a length of 15,000 bytes, or if you had compression on the table,
you may still get multiple rows on a page; and use the rest of that space.

HTH

Cheers,
Nick F. Cianci

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Crane, Rob --- Sr. Database Administrator --- CFS
Sent: Wednesday, December 15, 2004 5:10 AM
To: [login to unmask email]
Subject: Re: Buffer pool and de-compression questions

Q2: On V8 the CI matches the page size so would assume that is the only
choice on V8. Not sure about V7, but would assume the full 32K based on my
own swag. I say this since the workfile only uses 4K or 32K regardless of
how much above 4K you go in your sort requirement, and assume the same
(allocate it all based on page size of the ts) approach is being used by the
buffer manager.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Andy Lankester
Sent: Tuesday, December 14, 2004 10:56 AM
To: [login to unmask email]
Subject: Re: Buffer pool and de-compression questions

Q1: (De)Compression is a row level process, so decompression is only done
when a row is being examined for selection criteria or FETCHed. Compression
is done on insert/update/load

Q2: not sure but I think the unit of transfer is the full 32K

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Pirk Walton
Sent: 14 December 2004 16:31
To: [login to unmask email]
Subject: Buffer pool and de-compression questions


Question 1.
When compressed tablespace tables are loaded into buffers are they de-
compressed when they go in or after they are physical read?

Question 2.
I have a table with a length of 21032 bytes. Its TS is in a 32K buffer. The
DB2 manual says a 32k buffer is made up of 8 4K buffers. Here is my
question. Then a record is read and is put in the 32K buffer, does is go
into a 32K area and waste around 11k buffer bytes. Or does it go into 6
4K buffers and only waste 3000 bytes? I am thinking 11k bytes but am
not sure.

Here is what the table looks like --
CREATE TABLE AAA.BBB
(CC CHAR(3) NOT NULL WITH DEFAULT,
MID CHAR(15) NOT NULL WITH DEFAULT,
SEQN DECIMAL(7) NOT NULL WITH DEFAULT,
TINFO VARCHAR(21000) NOT NULL WITH DEFAULT


Can you also point me to the manuals where you got your answers.

Thanks for your help,

Pirk

----------------------------------------------------------------------------

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: Buffer pool and de-compression questions
(in response to Nick Cianci)
Rob

To your second question: You can compress your tablespace, so that possibly
2 rows fit in a 32K page and you waste less space.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm