473,326 Members | 2,102 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,326 software developers and data experts.

Multiple temp tablespaces with same page size

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.

Apr 5 '06 #1
6 3765
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
Apr 5 '06 #2
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)?
Apr 5 '06 #3
"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.
Apr 6 '06 #4

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.

Apr 6 '06 #5
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

Apr 6 '06 #6
"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".
Apr 6 '06 #7

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

Similar topics

2
by: Terri | last post by:
I have form1.asp which contains a form which is posted to and processed by a page called process.asp. I want to create a second form on form2.asp and also post it to process.asp. On process.asp I...
1
by: Raja Shekar | last post by:
HI Every body , I would like to know whether is it mandatory to give Tablespace page size and Bufferpool page size equal..? i also heard like while creating tablespace if pages size of tablespace...
1
by: clickon | last post by:
I have a navigation a quite complex navigation structure and i want to have more than one way to navigate to the same page. It is a portal for a manufacturing company, one of the areas of the...
2
by: mike_dba | last post by:
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. ...
0
by: sethwai | last post by:
Hi, A friend of mine inheritted a system that had 2 4k temp tablespaces each assigned to a different bufferpool. This brought up a number of questions for me. 1) Does anyone know in general...
1
by: bigjimmy | last post by:
I'm currently testing http://fsviewr.lackadaisical.com/ on my blogger. I use the script in my post and put the js file location (<script type="text/javascript" src="fjs.js"></script>) in my...
4
by: RobertoRigoli | last post by:
Hi, I should create a new tablespaces with a page size to 32K, with e buffer pool dedicated with the same page size. The temporary tablespaces page size is 4K, I have to modify the temporary...
3
by: vanald04 | last post by:
Tried searching for the right article and saw some similar questions that if I knew more would probably be helpful, but I couldn't get the coding exactly right for my specific situation. So, thanks...
8
by: stoogots2 | last post by:
I would like to reuse the code for gridview sorting for each of the several GridViews that I have on one page. I've not done this before, so I am seeking a more elegant and reusable solution than...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.