473,883 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Temporary Tablespace type to encrease performance

hello,

What's your opinion :
The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 /
multiprocessor ) is DMS ou SMS ?

Thanks

ZEB
Nov 12 '05
18 8782
There is overhead for DMS incurred by going through the SMP and EMP ... and if I am not mistaken some synchronous writes occur during the creation of the temporary table if using DMS. Of course regardless of type, we all remember that it is not advantageous to have > 1 temporary table space of the same page size ...

Bob
Consulting I/T Specialist
IBM Toronto Lab
IBM Software Services for Data Management
[My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]

"Mark A" <ma@switchboard .net> wrote in message news:tM******** ********@news.u swest.net...
"Bob [IBM]" <No**********@i bm.com
wrote in message news:bm******** **@news.btv.ibm .com...
Mark - I am fully aware of the benchmark .. but thanks as always for your insight. I still never suggest anything but SMS for the reason
of never knowing if you will have enough space and also the additional overhead of using DMS table spaces. Of course, your mileage
may vary.


I am not sure what you mean by overhead, but I believe that the overall performance is better on DMS, if set up in an optimal manner (each container
on its own drive or array).

DBA's do get fewer complaints with SMS because it is less likely to run out of space. However, if one creates the temporary tablespaces with a
sufficient size (several times the largest table if possible) that is not likely to happen. Disk space is cheap.
Nov 12 '05 #11
> "Bob [IBM]" <No**********@i bm.com> wrote in message
There is overhead for DMS incurred by going through the SMP
and EMP ... and if I am not mistaken some synchronous writes occur
during the creation of the temporary table if using DMS. Of course
regardless of type, we all remember that it is not advantageous to
have > 1 temporary table space of the same page size ...
Bob


There is overhead on both DMS and SMS, but they may be in different areas.
You only mentioned the overhead attributable to DMS. Overall, DMS performs
better if set up properly with enough space to ensure that one does not run
out. I presume that is why the IBM benchmark team used DMS.
Nov 12 '05 #12


Mark A wrote:
"Bob [IBM]" <No**********@i bm.com> wrote in message
There is overhead for DMS incurred by going through the SMP
and EMP ... and if I am not mistaken some synchronous writes occur
during the creation of the temporary table if using DMS. Of course
regardless of type, we all remember that it is not advantageous to
have > 1 temporary table space of the same page size ...
Bob


There is overhead on both DMS and SMS, but they may be in different areas.
You only mentioned the overhead attributable to DMS. Overall, DMS performs
better if set up properly with enough space to ensure that one does not run
out. I presume that is why the IBM benchmark team used DMS.


AFAIK, only on SUN and Linux suggest using DMS as system temporary tablespace.
On Sun, you'd better use DMS raw device.
Nov 12 '05 #13
Can you elaborate on some of the overhead you see in SMS? I cannot state any specifically so would be interested in your experiences in the field in this area please.

--

Bob
Consulting I/T Specialist
IBM Toronto Lab
IBM Software Services for Data Management
[My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]
"Mark A" <ma@switchboard .net> wrote in message news:C3******** ********@news.u swest.net...
"Bob [IBM]" <No**********@i bm.com> wrote in message
There is overhead for DMS incurred by going through the SMP
and EMP ... and if I am not mistaken some synchronous writes occur
during the creation of the temporary table if using DMS. Of course
regardless of type, we all remember that it is not advantageous to
have > 1 temporary table space of the same page size ...
Bob


There is overhead on both DMS and SMS, but they may be in different areas.
You only mentioned the overhead attributable to DMS. Overall, DMS performs
better if set up properly with enough space to ensure that one does not run
out. I presume that is why the IBM benchmark team used DMS.
Nov 12 '05 #14
"Bob [IBM]" <No**********@i bm.com> wrote in message
Can you elaborate on some of the overhead you see in SMS? I cannot
state any specifically so would be interested in your experiences in the
field in this area please.


I am not an expert in this area, although I have heard some discussion of
this topic, not all of which I remember in detail off-hand. I think it might
be beneficial to ask the DB2 Linux benchmark team about why they chose DMS
and whether they benchmarked different DMS vs SMS tablespace scenarios.

Someone earlier suggested that the DMS vs. SMS decision is operating system
dependent, so that would be something to follow up on also.

Nov 12 '05 #15
Benchmarks usually get done in a few weeks, so the advantages of
performance (which DMS sometimes delivers for temp tablespaces) over SMS
can be pronounced. The advantages of SMS (ease of administration,
ability to grow and shrink quickly) matter less in a benchmark, where
the workload is well understood and is completed quickly, versus a
database you will maintain for years. Finally, writing a benchmark kit
(i.e. a TPC-H kit or TPC-C kit) is a lot of work, so sometimes
performance benchmarks might stick with a choice they inherit if it
works okay - i.e. if I/O performed well on the original TPC-H (or TPC-D)
kit with DMS temps, they might leave that as is, and focus on other
query tuning and techniques (like MQT's) when running such a benchmark
on Linux.

There were once large performance advantages for raw over SMS for
relational databases - these advantages are shrinking, disappearing, or
even becoming advantages for SMS as disk caching techniques at the OS
and disk subsystem levels improve (and disk technologies like EMC and
Shark aim more at the needs of high end databases like DB2).

Mark A wrote:
"Bob [IBM]" <No**********@i bm.com> wrote in message
Can you elaborate on some of the overhead you see in SMS? I cannot
state any specifically so would be interested in your experiences in the
field in this area please.

I am not an expert in this area, although I have heard some discussion of
this topic, not all of which I remember in detail off-hand. I think it might
be beneficial to ask the DB2 Linux benchmark team about why they chose DMS
and whether they benchmarked different DMS vs SMS tablespace scenarios.

Someone earlier suggested that the DMS vs. SMS decision is operating system
dependent, so that would be something to follow up on also.


Nov 12 '05 #16
"Blair Adamache" <ba*******@2muc hspam.yahoo.com > wrote in message
news:bn******** **@hanover.toro lab.ibm.com...
Benchmarks usually get done in a few weeks, so the advantages of
performance (which DMS sometimes delivers for temp tablespaces) over SMS
can be pronounced. The advantages of SMS (ease of administration,
ability to grow and shrink quickly) matter less in a benchmark, where
the workload is well understood and is completed quickly, versus a
database you will maintain for years. Finally, writing a benchmark kit
(i.e. a TPC-H kit or TPC-C kit) is a lot of work, so sometimes
performance benchmarks might stick with a choice they inherit if it
works okay - i.e. if I/O performed well on the original TPC-H (or TPC-D)
kit with DMS temps, they might leave that as is, and focus on other
query tuning and techniques (like MQT's) when running such a benchmark
on Linux.

There were once large performance advantages for raw over SMS for
relational databases - these advantages are shrinking, disappearing, or
even becoming advantages for SMS as disk caching techniques at the OS
and disk subsystem levels improve (and disk technologies like EMC and
Shark aim more at the needs of high end databases like DB2).


I agree about the administrative advantages of SMS over DMS. I think that
DMS is sometimes overused, especially for transaction systems with large
bufferpools.

However, tables created by DB2 in temporary tablespace are frequently
accessed with tablespace scans, which can be quite large in ad-hoc query or
data warehouse environments . I would suspect that there are advantages for
DMS in this type of environment, especially if one places the containers in
an optimal configuration.

To a large degree, the overhead associated with DBA maintenance activities
relating to DMS for temporary tablespaces can be minimized with a generous
allocation of space, especially since it is a shared space and disk is
cheap. Because of this, I suspect there is a better case to be made for use
of DMS for temporary tablespaces than regular tablespaces (which may require
more require frequent administration) .
Nov 12 '05 #17
"Mark A" <ma@switchboard .net> writes:
DBA's do get fewer complaints with SMS because it is less likely to run out
of space. However, if one creates the temporary tablespaces with a
sufficient size (several times the largest table if possible) that is not
likely to happen. Disk space is cheap.


True, but you may have to do regular re-orgs to keep things contiguous if you
want to keep the performance advantage.

My policy is SMS is the standard for everything unless performance
constraints require better performance, but our installation is
not performance bound.

Joseph
Nov 12 '05 #18
> "Mark A" <ma@switchboard .net> writes:
DBA's do get fewer complaints with SMS because it is less likely to run outof space. However, if one creates the temporary tablespaces with a
sufficient size (several times the largest table if possible) that is not
likely to happen. Disk space is cheap.
True, but you may have to do regular re-orgs to keep things contiguous if

you want to keep the performance advantage.

My policy is SMS is the standard for everything unless performance
constraints require better performance, but our installation is
not performance bound.

Joseph


Temporary DB2 tables are created and dropped by DB2 during the SQL unit of
work. There is nothing to reorganize, or am I missing something?
Nov 12 '05 #19

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

Similar topics

4
13055
by: Ulrich Sprick | last post by:
Hi all, (DB2 V7.1 for WinNT) I am looking for a way to determine the free space in my tablespace (containers), but I can't find out. The tablespace in question is a system managed tablespace in a raw partition. The Control Center always reports 100% usage (although I can insert data...). The "list tablespace containers for n show detail" command shows the number of usable pages, but they are almost equal to the total number of pages. ...
11
4618
by: Hemant Shah | last post by:
Folks, I have a perl script that creates and uses global termporary table. This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the database to UDB 8 on Linux. The select statement that used to take less than a minute now take 10 minutes. Do I have to tune something on Linux and/or DB2? I tried two different ways to create temporary tablespace but both of them give same slow performance:
5
6429
by: N | last post by:
Hi, I got an error during load on a couple of the tables. And it seems to be complaining that I'm running out of tempspace (possibly during index rebuild). Below is the load command and error. What I do not understand is why DB2 is trying to rebuild the index in TBS_STEMP03 even though I explicitly tell it to use TBS_STEMP01 in the load statement. According to my calculation, TBS_STEMP01 should have enough space for LOAD
6
3338
by: gimme_this_gimme_that | last post by:
I'm new to DB2 ... The following statement results in a SQL1585N message : "A system temporary table space with sufficient page size does not exist" Note that the column FIELD_DETAIL is a BLOB. Is there something inefficent about the SQL and CASE statement below that makes this error occur?
2
2468
by: Andy S. | last post by:
Hi, I'm trying to declare and use temporary tables. I have written the following code in Java. Creating the tablespace (i can see the tablespace created using the Control Center), the temporary table and even inserting the values execute fine (even the executeUpdate while inserting returns a positive number indicating that rows have been inserted). However, when I select * from the temporary table, 0 rows are returned and the while loop...
11
6334
by: rawu | last post by:
hi , all . I got a question about creating indexes in db2 . a table has 44236333 rows an index planed to be build include 2 field ( 6 byte ) accordding to db2 document temporary tablespaces requirement size will be (6+9)*44236333*3.2/1024/1024/1024 = 1.98G
1
1670
by: nkumarin001 | last post by:
Hi, I created permanent tablespace with no objects in it so i tried to switch from permanent tablespace to temporary tablespace and i got the following error... SQL> alter tablespace student temporary; alter tablespace student temporary * ERROR at line 1: ORA-03218: invalid option for CREATE/ALTER TABLESPACE
4
5910
by: sandeep.iitk | last post by:
Hi, One query is failing on the database and its a long query which was running fine earlier. One possible reason we can think of is increase in data in tables in query. It is failing with following error:- SQL0659N Maximum size of a table object has been exceeded. SQLSTATE=54032 (SQLSTATE 54032) -659 Earlier we were having the temp file system size of around 60 GB. then once query failed with the error "File system full"
6
3330
by: Troels Arvin | last post by:
Hello, I have recently run a rather large data import where the imported data i pumped through some updatable views equipped with INSTEAD OF triggers. For various reasons, the exact same data where imported into the exact same table/view/trigger structures (but with slightly different tablespace configuration, see later). The involved hardware was different (one running on x86_64, one running on PPC), but with comparable CPU, memory and...
0
11125
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...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9568
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
7962
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
7114
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
5794
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
4607
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
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.