Where do I find out more?

[Bob Stearns]

I changed a varchar(255) to varchar(3000) an started receiving:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/LINUX] SQL1585N A
system temporary table space with sufficient page size does not exist.
SQLSTATE=54048 , SQL state 54048 in SQLExecDirect in
/var/www/html/GEM/animalEdit/animals_priv.ph p on line 207

Where do I find out more about the attributes of my temporary table
space and how to change them? Just RTFM is insufficient given the size
of TFM.

As an aside why can't I alter a column downward in size, only upward?
Sure, a table pass might be required, but otherwise it's theoretically
simple, right? Just change the value of syscat.columns. length or rather
the real table sysibm.syscolum ns.length under laying syscat.columns. length.

[Mark A]

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Vm******** ***@fe04.lga...

I changed a varchar(255) to varchar(3000) an started receiving:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/LINUX] SQL1585N A
system temporary table space with sufficient page size does not exist.
SQLSTATE=54048 , SQL state 54048 in SQLExecDirect in
/var/www/html/GEM/animalEdit/animals_priv.ph p on line 207

Where do I find out more about the attributes of my temporary table space
and how to change them? Just RTFM is insufficient given the size of TFM.

As an aside why can't I alter a column downward in size, only upward?
Sure, a table pass might be required, but otherwise it's theoretically
simple, right? Just change the value of syscat.columns. length or rather
the real table sysibm.syscolum ns.length under laying
syscat.columns. length.

You will need a System Temporary Tablespace with page size of at least 8K.
The default system temporary tablespace is 4K and it is usually best to just
leave it that way, and create a new one that is larger. SMS is recommended
for system temporary tablespaces.

Instead of 8K, you could define it as 32K, which would handle any other
situation (including joins) where a larger tablespace is needed.

Before you create the new system temporary tablespace, you need to create a
bufferpool with a page size that matches the page size of the tablespace,
and refer to that bufferpool in the system temporary tablespace that you
define.

[Bob Stearns]

Mark A wrote:

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Vm******** ***@fe04.lga...

I changed a varchar(255) to varchar(3000) an started receiving:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/LINUX] SQL1585N A
system temporary table space with sufficient page size does not exist.
SQLSTATE=5404 8 , SQL state 54048 in SQLExecDirect in
/var/www/html/GEM/animalEdit/animals_priv.ph p on line 207

Where do I find out more about the attributes of my temporary table space
and how to change them? Just RTFM is insufficient given the size of TFM.

As an aside why can't I alter a column downward in size, only upward?
Sure, a table pass might be required, but otherwise it's theoretically
simple, right? Just change the value of syscat.columns. length or rather
the real table sysibm.syscolum ns.length under laying
syscat.column s.length.

You will need a System Temporary Tablespace with page size of at least 8K.
The default system temporary tablespace is 4K and it is usually best to just
leave it that way, and create a new one that is larger. SMS is recommended
for system temporary tablespaces.

Instead of 8K, you could define it as 32K, which would handle any other
situation (including joins) where a larger tablespace is needed.

Before you create the new system temporary tablespace, you need to create a
bufferpool with a page size that matches the page size of the tablespace,
and refer to that bufferpool in the system temporary tablespace that you
define.

Thanks a lot. Will talk with my SYSADMIN about this. As a temporary fix
I ran the following two commands, which fixed my immediate problem.
Probably poor choices for the parameters, but I'll let me SYSADMIN fix
it for good performance; I only need it to work on my development system.
create bufferpool largeone immediate size 32 pagesize 32k
go

CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
PAGESIZE 32K
MANAGED BY SYSTEM
USING( '/db2home/db2inst1/db2inst1/NODE0000/SQL00002/SQLT320001.0' )
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL largeone
OVERHEAD 24.1
TRANSFERRATE 0.9
DROPPED TABLE RECOVERY OFF
GO

[Pierre Saint-Jacques]

This means you are probably joining this row size (3000+) with other data
with the resul that the resulting row is larger than 4026 chars.
Your default temporary tablespace has a page size of 4096 which leads to the
fact that the query result set row size cannot fit in yoyr twmp space page
size.
Create a new system temporary tablespace with a page size of 8k, or16k, or
32k as you think fit and it will solve your problem.
RTFM
1) SQL Reference Manual Appendix. Gives all max size of different objects.
2) Tempspaces are discussed in the Admin. Guide.

Very easy:
D:\SQLLIB\BIN>d b2 ? SQL1585n
SQL1585N A system temporary table space with sufficient page
size does not exist.

Explanation:

One of the following conditions could have occurred:

1. The row length of the system temporary table exceeded the
limit that can be accommodated in the largest system
temporary table space in the database.

2. The number of columns required in a system temporary table
exceeded the limit that can be accommodated in the largest
system temporary table space in the database.

The system temporary table space limits depend on its page size.
These values are:
Max Max Page size of
Record Cols temporary
Length table space
----------- ---- ------------
1957 bytes 244 2K
4005 bytes 500 4K
8101 bytes 1012 8K
16293 bytes 1012 16K
32677 bytes 1012 32K
User Response:

Create a system temporary table space of a larger page size
supported, if one does not already exist. If such a table space
already exists, eliminate one or more columns from the system
temporary table. Create separate tables or views, as required,
to hold additional information beyond the limit.

sqlcode : -1585

sqlstate : 54048

Also easy:
Do a Google search on "DB2" and "Temporay tablespace"
or "DB2" and "maximium row size"

HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Bob Stearns" <rs**********@c harter.net> a écrit dans le message de news:
Vm***********@f e04.lga...

I changed a varchar(255) to varchar(3000) an started receiving:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/LINUX] SQL1585N A
system temporary table space with sufficient page size does not exist.
SQLSTATE=54048 , SQL state 54048 in SQLExecDirect in
/var/www/html/GEM/animalEdit/animals_priv.ph p on line 207

Where do I find out more about the attributes of my temporary table space
and how to change them? Just RTFM is insufficient given the size of TFM.

As an aside why can't I alter a column downward in size, only upward?
Sure, a table pass might be required, but otherwise it's theoretically
simple, right? Just change the value of syscat.columns. length or rather
the real table sysibm.syscolum ns.length under laying
syscat.columns. length.