Multiple temp tablespaces with same page size

[mike_dba]

Can anyone tell me why a EEE system might be created to have two
separate tablespaces with the same 4k pagesize ? They both appear to
be in use as I can see the underlying files being modified.

These were created with SMS and reside on the same disk and filesystem
(separate directory on filesystem). One is called temp_4k and the
other temp_sys. I would assume that the temp_sys was intended for use
by the db2 catalog. I know of no way to tell db2 to use a specific
tablespace - like use temp_sys for catalog and temp_4k for operations
like sort,etc.

[Knut Stolze]

mike_dba wrote:

Can anyone tell me why a EEE system might be created to have two
separate tablespaces with the same 4k pagesize ? They both appear to
be in use as I can see the underlying files being modified.
Yes, that's entirely possible.
These were created with SMS and reside on the same disk and filesystem
(separate directory on filesystem). One is called temp_4k and the
other temp_sys. I would assume that the temp_sys was intended for use
by the db2 catalog. I know of no way to tell db2 to use a specific
tablespace - like use temp_sys for catalog and temp_4k for operations
like sort,etc.

The catalog tablespace can be defined at CREATE DATABASE time. See the
CREATE DATABASE command for more details: http://tinyurl.com/zwac4

Other tablespaces are created with the CREATE TABLESPACE statement
(http://tinyurl.com/edsh4) and you have to define which tables go to a
particular tablespace using the CREATE TABLE statement
(http://tinyurl.com/csp4u). That leaves the temporary tablespaces. If I
remember correctly, then it is recommended to have just a single system or
user temporary tablespace for each page size.

You can use the LIST TABLESPACES command to determine the type of the two
tablespaces.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ian]

mike_dba wrote:

Can anyone tell me why a EEE system might be created to have two
separate tablespaces with the same 4k pagesize ? They both appear to
be in use as I can see the underlying files being modified.

Is one a USER temporary tablespace (i.e. for declared global temp
tables), and the other a SYSTEM temporary tablespace (for system
use in sorts / table queues /etc)?

[Mark A]

"mike_dba" <mi*************@yahoo.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Can anyone tell me why a EEE system might be created to have two
separate tablespaces with the same 4k pagesize ? They both appear to
be in use as I can see the underlying files being modified.

These were created with SMS and reside on the same disk and filesystem
(separate directory on filesystem). One is called temp_4k and the
other temp_sys. I would assume that the temp_sys was intended for use
by the db2 catalog. I know of no way to tell db2 to use a specific
tablespace - like use temp_sys for catalog and temp_4k for operations
like sort,etc.

Check the nodegroup of the tablespaces defined. That will give you some clue
as to what they will be used for.

[mike_dba]

Mark A wrote:

"mike_dba" <mi*************@yahoo.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Can anyone tell me why a EEE system might be created to have two
separate tablespaces with the same 4k pagesize ? They both appear to
be in use as I can see the underlying files being modified.

These were created with SMS and reside on the same disk and filesystem
(separate directory on filesystem). One is called temp_4k and the
other temp_sys. I would assume that the temp_sys was intended for use
by the db2 catalog. I know of no way to tell db2 to use a specific
tablespace - like use temp_sys for catalog and temp_4k for operations
like sort,etc.

Check the nodegroup of the tablespaces defined. That will give you some clue
as to what they will be used for.

Thanks for your suggestion. It led me to the following revelation which
was not so straightforward at first.

I see that both were assigned to IBMTEMPGROUP. However, selecting from
syscat.tablespaces reveals that they belong to different bufferpools.
TEMP_SYS belongs to IBMDEFAULTBP bufferpool (as do tablespaces
syscatspace and userspace1). TEMP_4k belongs to BP4K bufferpool along
with some other tablespaces).

Thank you for your help.

[mirof007]

IBMTEMPGROUP is the only option for system temps, you don't get to
choose where they go, I think Mark misuderstood and thought you were
talking about regular tables. It's like Bob said above, DB2 will
round-robin between temps of the same size, you can't influence which
temp will be used for your query. Also, this implies that if the two
bufferpools differ in size, you will more or less randomly get your
temp data cached either in the smaller or the larger. It seems a bit
pointless and in general I would not recommend multiple temp table
spaces with the same page size.

Regards,
Miro

[Mark A]

"mirof007" <mi******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

IBMTEMPGROUP is the only option for system temps, you don't get to
choose where they go, I think Mark misuderstood and thought you were
talking about regular tables. It's like Bob said above, DB2 will
round-robin between temps of the same size, you can't influence which
temp will be used for your query. Also, this implies that if the two
bufferpools differ in size, you will more or less randomly get your
temp data cached either in the smaller or the larger. It seems a bit
pointless and in general I would not recommend multiple temp table
spaces with the same page size.

Regards,
Miro

No, I did not misunderstand. The OP said that he assumed that "temp_sys was
intended for use by the db2 catalog."

Looking at the nodegroup revealed to the OP that they were "both were
assigned to IBMTEMPGROUP".