470,631 Members | 1,601 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

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 1965
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Ney André de Mello Zunion | last post: by
19 posts views Thread by Kai-Uwe Bux | last post: by
5 posts views Thread by White Wolf | last post: by
3 posts views Thread by Joe Weinstein | last post: by
2 posts views Thread by John Saunders | last post: by
8 posts views Thread by Martijn van Oosterhout | last post: by
1 post views Thread by Stefan van Roosmalen | last post: by
3 posts views Thread by RainBow | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.