473,320 Members | 1,814 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,320 software developers and data experts.

Temporary Systemtablespace is too small

Hello there,

I have an issue, that a big select statement causes the followong
error:"SQL1585N Es ist kein temporärer Systemtabellenbereich mit
einer ausreichenden Seitengröße vorhanden"
So I created one using:

CREATE SYSTEM TEMPORARY TABLESPACE SMOTBSMSST32 PAGESIZE 32 K
MANAGED BY SYSTEM
USING ('D:\DB2\NODE0000\SQL00001\SMOTBSMSST32' )
EXTENTSIZE 16
OVERHEAD 12.67
PREFETCHSIZE AUTOMATIC
TRANSFERRATE 0.18
BUFFERPOOL SMOBP32 ;

The used Bufferpool was created with "CREATE BUFFERPOOL "SMOBP32" SIZE
100 PAGESIZE 32 K NOT EXTENDED STORAGE;"

So I think I have done all necessary work to get my big SQL Statement
working. The result set contains about 140 columns, containing some
columns of a table which itself needs to have a tablspace with 32k
pagesize. So, if I ever want to use this tabel in a select, I have to
look, that a record in the result set is not bigger than 32k. Am I
right?
Im using "DB2 v8.1.7.445", "s040812", "WR21342" und FixPak "7"
Oracle doesn't have any problems on the same big SQL Statement.

Can I define a temporary tabelspace bigger tha 32k pagesize? I don't
want to change the SQL statement, because it is dynamically generated
inside an EJB application using an intellibo library.

Thanx for your help

Matthias
Stuttgart, Germany

Nov 30 '06 #1
1 2026
Matthias Dennig wrote:
Hello there,

I have an issue, that a big select statement causes the followong
error:"SQL1585N Es ist kein temporärer Systemtabellenbereich mit
einer ausreichenden Seitengröße vorhanden"
So I created one using:

CREATE SYSTEM TEMPORARY TABLESPACE SMOTBSMSST32 PAGESIZE 32 K
MANAGED BY SYSTEM
USING ('D:\DB2\NODE0000\SQL00001\SMOTBSMSST32' )
EXTENTSIZE 16
OVERHEAD 12.67
PREFETCHSIZE AUTOMATIC
TRANSFERRATE 0.18
BUFFERPOOL SMOBP32 ;

The used Bufferpool was created with "CREATE BUFFERPOOL "SMOBP32" SIZE
100 PAGESIZE 32 K NOT EXTENDED STORAGE;"

So I think I have done all necessary work to get my big SQL Statement
working. The result set contains about 140 columns, containing some
columns of a table which itself needs to have a tablspace with 32k
pagesize. So, if I ever want to use this tabel in a select, I have to
look, that a record in the result set is not bigger than 32k. Am I
right?
Im using "DB2 v8.1.7.445", "s040812", "WR21342" und FixPak "7"
Oracle doesn't have any problems on the same big SQL Statement.

Can I define a temporary tabelspace bigger tha 32k pagesize? I don't
want to change the SQL statement, because it is dynamically generated
inside an EJB application using an intellibo library.

Thanx for your help

Matthias
Stuttgart, Germany
One thing to check is row size. If any of the returned COLUMNS use a
FUNCTION such as LEFT(), REPLACE(), you can try wrapping them in a
VARCHAR(func(<col>), 100) or something to limit the record length.

B.

Nov 30 '06 #2

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

Similar topics

13
by: Ney André de Mello Zunion | last post by:
Hello. Binding a temporary to a non-const reference is illegal. Everybody should be tired of hearing that. So should I. But then I found myself wondering about a small piece of code I was...
19
by: Kai-Uwe Bux | last post by:
Hi folks, I have trouble writing a class, derving from stringstream, that collects item and once it's done will write them to std::cout in one go. It works fine except when I use it as a...
5
by: White Wolf | last post by:
Hi, I would like to double check how long a temporary returned by a function lives? Suppose I have an instance of a class type C, which has a member function returning some sort of...
3
by: Joe Weinstein | last post by:
Hi all. I am debugging a JDBC application. I find that a certain query that has an order-by clause fails with a SQL1585N A system temporary table space with sufficient page size does not exist....
2
by: John Saunders | last post by:
I deploy web applications in what may be an odd manner. For every web site "x", I have an "x2" web site which points to an empty directory. I can then use Copy Project in VS.NET to deploy to the...
8
by: Martijn van Oosterhout | last post by:
Currently you can create temporary tables that are deleted at the end of the session. But how about temporary views? It's just a table with a rule so I don't imagine it would be terribly difficult....
1
by: Stefan van Roosmalen | last post by:
Hi there, Is there a way to list the TEMPORATY tables? I have tried SHOW TABLES, but this command only list the regular tables. Thank you very much for your answer. Regards, Stefan.
3
by: RainBow | last post by:
I understand that a compiler synthesises a default constructor if none is provided by the user ( of course depending on the situation if synthesis of such c'tor is actually needed in the program...
0
by: lee.walczak | last post by:
I actually post a topic relating to my problem here: (http://groups.google.co.uk/group/comp.lang.python/browse_thread/ thread/a073d532c4481bfe?hl=en# ) But I thought it could be useful to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.