473,726 Members | 2,021 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3804
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.goo glegroups.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.goo glegroups.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.tablespa ces 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.goo glegroups.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
2436
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 would execute different stored procedures conditionally based on where the form is submitted from. How can I tell which page the form is submitted from? I think I should be able to have a hidden form field on my forms and post that to...
1
5534
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 and bufferpool , the statement itself will fail ...why i'm asking is if we chose a different page size ( ofcourse less than bufferpool page size ) only it shoud affect the performance of db2 .. Thanks in Advance Raja Shekar
1
3237
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 site is a raw materials management section, another is a packaging dept section. I want to group together all the raw materials pages in one section, but some of them apply specifically to the packing dept. so i also want the relevent pages in the...
2
371
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. 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...
0
1481
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 how db2 handles multiple temp tablespaces of the same page size? 2) I had read somewhere that with multiple temp tablespaces of the
1
1709
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 template's <head> so that when I post, the script would work automatically. Now my problem is that when I have multiple posts using the same .js file, only the newest post on top works. Does anyone have a solution to this? besides putting multiple .js in...
4
4003
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 tablespaces to the max page size defined into database (32K ) ? Thanks for your help Roberto.
3
1857
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 in advance for helping me out. Question - I am implementing a drop down javascript box for photos and would like to duplicate the same script for multiple photos. Looking at the code, I see the interference (ie, the second picture still...
8
7469
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 coding events for each of the 4 gridviews I have.
0
8890
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9402
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9182
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8101
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6702
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.