No system temporary table space and a SQL query

gimme_this_gimme_that

I'm new to DB2 ...

The following statement results in a SQL1585N message :
"A system temporary table space with sufficient page size does not
exist"

Note that the column FIELD_DETAIL is a BLOB.

Is there something inefficent about the SQL and CASE statement
below that makes this error occur?

Is there a more efficient rendering?

How *large* should the temporary table need to be? At
least as large as the largest BLOB?
Thanks.
SELECT DISTINCT ANNOUNCEMENT_HIGHLIGHT.ANNOUNCEMENT_HIGHLIGHT_ID,
HEADLINE, ANNOUNCEMENT_TYPE, DESCRIPTION,
TITLE, START_DATE, END_DATE, FILENAME , LINK_OR_TEXT, GENERIC,
(CASE WHEN LENGTH(FILE_DETAIL) > 0 THEN 'YES' ELSE 'NO' END)
FILE_UPLOADED
FROM ANNOUNCEMENT_HIGHLIGHT , GROUP_ANNOUNCMENT_LOOKUP
WHERE GROUP_ANNOUNCMENT_LOOKUP.ANNOUNCEMENT_HIGHLIGHT_ID =
ANNOUNCEMENT_HIGHLIGHT.ANNOUNCEMENT_HIGHLIGHT_ID
AND GROUP_ANNOUNCMENT_LOOKUP.GROUP_ID IN (
0,8,4,538,647,651,654,945,27,0)
AND CURRENT TIMESTAMP >= START_DATE
AND CURRENT TIMESTAMP <= END_DATE ORDER BY START_DATE DESC, TITLE

Nov 12 '05 #1

Mark A

<gi*******************@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I'm new to DB2 ...

The following statement results in a SQL1585N message :
"A system temporary table space with sufficient page size does not
exist"

Note that the column FIELD_DETAIL is a BLOB.

Is there something inefficent about the SQL and CASE statement
below that makes this error occur?

Is there a more efficient rendering?

How *large* should the temporary table need to be? At
least as large as the largest BLOB?
Thanks.
SELECT DISTINCT ANNOUNCEMENT_HIGHLIGHT.ANNOUNCEMENT_HIGHLIGHT_ID,
HEADLINE, ANNOUNCEMENT_TYPE, DESCRIPTION,
TITLE, START_DATE, END_DATE, FILENAME , LINK_OR_TEXT, GENERIC,
(CASE WHEN LENGTH(FILE_DETAIL) > 0 THEN 'YES' ELSE 'NO' END)
FILE_UPLOADED
FROM ANNOUNCEMENT_HIGHLIGHT , GROUP_ANNOUNCMENT_LOOKUP
WHERE GROUP_ANNOUNCMENT_LOOKUP.ANNOUNCEMENT_HIGHLIGHT_ID =
ANNOUNCEMENT_HIGHLIGHT.ANNOUNCEMENT_HIGHLIGHT_ID
AND GROUP_ANNOUNCMENT_LOOKUP.GROUP_ID IN (
0,8,4,538,647,651,654,945,27,0)
AND CURRENT TIMESTAMP >= START_DATE
AND CURRENT TIMESTAMP <= END_DATE ORDER BY START_DATE DESC, TITLE

I don't know how large the columns that are involved in the join, so it is
hard to say how big of a page size you need. The largest page size is 32K.

It is usually prudent to have a least one system temporary tablespace with
page size 32K for such situations. SMS is best for system temporary
tablespaces, so you only need to define a path, and not a tablespace size.

But you will first need to create a bufferpool with 32K page size that will
be used by the 32K system temporary tablespace (or any other 32K page size
tablespace), if one does not already exist. Bufferpool size of 1000 pages
(32K of real memory) should be enough for most situations. If you are memory
constrained, then make it a smaller, maybe 250 pages).

Nov 12 '05 #2

Serge Rielau

gi*******************@yahoo.com wrote:

I'm new to DB2 ...

The following statement results in a SQL1585N message :
"A system temporary table space with sufficient page size does not
exist"

Note that the column FIELD_DETAIL is a BLOB.

Is there something inefficent about the SQL and CASE statement
below that makes this error occur?

Is there a more efficient rendering?

How *large* should the temporary table need to be? At
least as large as the largest BLOB?
Thanks.
SELECT DISTINCT ANNOUNCEMENT_HIGHLIGHT.ANNOUNCEMENT_HIGHLIGHT_ID,
HEADLINE, ANNOUNCEMENT_TYPE, DESCRIPTION,
TITLE, START_DATE, END_DATE, FILENAME , LINK_OR_TEXT, GENERIC,
(CASE WHEN LENGTH(FILE_DETAIL) > 0 THEN 'YES' ELSE 'NO' END)
FILE_UPLOADED
FROM ANNOUNCEMENT_HIGHLIGHT , GROUP_ANNOUNCMENT_LOOKUP
WHERE GROUP_ANNOUNCMENT_LOOKUP.ANNOUNCEMENT_HIGHLIGHT_ID =
ANNOUNCEMENT_HIGHLIGHT.ANNOUNCEMENT_HIGHLIGHT_ID
AND GROUP_ANNOUNCMENT_LOOKUP.GROUP_ID IN (
0,8,4,538,647,651,654,945,27,0)
AND CURRENT TIMESTAMP >= START_DATE
AND CURRENT TIMESTAMP <= END_DATE ORDER BY START_DATE DESC, TITLE

DB2 only stores a forward reference to the LOB within the row.
Teh amount of space needed to that reference varies by the worst case
size of the LOB. You will find details in the CREATE TABLE description.
The CASE expression (not a statement !) is most certainly innocent.
Either way. By default a DB2 database gets created with 4K pages. That
is the widest row (including for temp or sort) can be just shy of 4K wide.
DB2 supports up to 32K page sizes.
If you are truly ramping up with DB2 you can save yoruself soeme
learning by downloading DB2 V8.2.2 (FP9) and creating the DB with 32K
pagesize.
Otherwise you need to:
CREATE BUFFERPOOL with 32K pagesize
CREATE SYSTEM TEMPORARY TABLESPACE with 32K pagesize.
Then try again, it should work.
That's a one time thing for the DB, btw.

Cheres
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3

Mark A

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

DB2 supports up to 32K page sizes.
If you are truly ramping up with DB2 you can save yoruself soeme learning
by downloading DB2 V8.2.2 (FP9) and creating the DB with 32K pagesize.
Otherwise you need to:
CREATE BUFFERPOOL with 32K pagesize
CREATE SYSTEM TEMPORARY TABLESPACE with 32K pagesize.
Then try again, it should work.
That's a one time thing for the DB, btw.

Cheres
Serge

Making all pages larger than 4K may be convenient, but not good performance
of an OLTP database or a "mixed" database. Might be OK for Decision Support
databases.

Nov 12 '05 #4

Serge Rielau

Mark A wrote:

Making all pages larger than 4K may be convenient, but not good performance
of an OLTP database or a "mixed" database. Might be OK for Decision Support
databases.

.... and SAP.
This is about finding the sweet spot between performance and
maintainability. Multiple page sizes imply multiple bufferpools.
In teh case of SAP all tables are stored beyond 4K page size. Makes no
sense to have 4K just for the catalog. Hence the feature.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #5

Mark A

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

Mark A wrote:

Making all pages larger than 4K may be convenient, but not good
performance of an OLTP database or a "mixed" database. Might be OK for
Decision Support databases.

... and SAP.
This is about finding the sweet spot between performance and
maintainability. Multiple page sizes imply multiple bufferpools.
In teh case of SAP all tables are stored beyond 4K page size. Makes no
sense to have 4K just for the catalog. Hence the feature.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

I agree that it makes no sense to just have the catalog in 4K tablespace if
everything else is bigger than 4K. But just because SAP uses bigger than 4K
does not mean that is the optimal solution for OLTP. In fact, it might not
even mean that it is optimal for SAP.

But if SAP has a lot of row sizes that approach or exceed 4K then it might
make sense to go higher than 4K for all page sizes. But in general, if the
vast majority of row sizes are small enough, you will get better utilization
of bufferpools in you stick to smaller page sizes for OLTP. Since OLTP
typically needs just one or only a few rows from each physical page loaded
into the bufferpool for a particular SQL statement, you don't want a lot of
un-needed rows from a larger page size taking up bufferpool space.

Of course, it the bufferpools are sufficiently large relative to the size of
the database, most of this becomes irrelevant.

For decision support system, where a large number of rows will need to be in
the bufferpool anyway due to a tablespace scan, you might as well have
larger page sizes and get the rows into the bufferpool in bigger chunks.

Nov 12 '05 #6

Serge Rielau

Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

Mark A wrote:

Making all pages larger than 4K may be convenient, but not good
performance of an OLTP database or a "mixed" database. Might be OK for
Decision Support databases.

... and SAP.
This is about finding the sweet spot between performance and
maintainability. Multiple page sizes imply multiple bufferpools.
In teh case of SAP all tables are stored beyond 4K page size. Makes no
sense to have 4K just for the catalog. Hence the feature.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

I agree that it makes no sense to just have the catalog in 4K tablespace if
everything else is bigger than 4K. But just because SAP uses bigger than 4K
does not mean that is the optimal solution for OLTP. In fact, it might not
even mean that it is optimal for SAP.

But if SAP has a lot of row sizes that approach or exceed 4K then it might
make sense to go higher than 4K for all page sizes. But in general, if the
vast majority of row sizes are small enough, you will get better utilization
of bufferpools in you stick to smaller page sizes for OLTP. Since OLTP
typically needs just one or only a few rows from each physical page loaded
into the bufferpool for a particular SQL statement, you don't want a lot of
un-needed rows from a larger page size taking up bufferpool space.

Of course, it the bufferpools are sufficiently large relative to the size of
the database, most of this becomes irrelevant.

For decision support system, where a large number of rows will need to be in
the bufferpool anyway due to a tablespace scan, you might as well have
larger page sizes and get the rows into the bufferpool in bigger chunks.

It's an option. You are not coerced into using it. ;-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #7