473,216 Members | 1,213 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

No system temporary table space and a SQL query

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
6 3276
<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
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
"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
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

"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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Didier ROS | last post by:
Hi, I am a newbie I want to create a temporary table and I get the following error message : mysql> CREATE TEMPORARY TABLE tempemp AS SELECT * FROM emp; ERROR 1044: Access denied for user:...
2
by: airkart | last post by:
Hello, I've scoured groups and the web, and haven't found a question like mine answered. I'm using Visual Studio 2003 with the Crystal Reports it comes bundled with and SQL Server 2000....
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
18
by: zebi | last post by:
hello, What's your opinion : The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 / multiprocessor ) is DMS ou SMS ? Thanks ZEB
0
by: GB | last post by:
The main and index table space have page sizes of 8k The long data table space has page size of 4k The page size of the temporary table is 4k REORG TABLE USERBLAH.MYTABLE USE TEMPTBS; ...
11
by: frizzle | last post by:
Hi groupies I'm building a news site, to wich a user can add new items into a mySQL db. It's still in testfase, but it's so extremely slow, i want to figure out what i'm doing wrong, or what to...
11
by: rawu | last post by:
hi , all . I got a question about creating indexes in db2 . a table has 44236333 rows an index planed to be build include 2 field ( 6 byte ) accordding to db2 document temporary...
1
by: ellenraju | last post by:
When we create an object in oracle we can specify table space, if not it will take default table space, while creating global temporary table how can we specify the table space...and even if we...
5
by: Peter Sutton | last post by:
Greetings all, My booking system includes 2 tables: tblRooms with the key field RoomID, and some other fields AND a tblBooked Rooms with a number of fields including RoomID and BookedDate. ...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.