473,545 Members | 1,899 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Options to set will not be saved under DB2 Console

Hi @ll,

when i want to set the PrefetchSize on my database this setting will not
be set. DB2 shell says that the command is successfull but when i list
my details with.

list tablespaces show detail

This is the output:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4697
Useable pages = 4697
Used pages = 4697
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 9080
Useable pages = 9080
Used pages = 9080
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
I tried this command to get 200 Megs on tablespace prefetch size but its
not working.

UPDATE DATABASE CONFIGURATION FOR DB2 USING DFT_PREFETCH_SZ 200

I think i have the permission to set this i am as a system owner inside
and have instance proviliges for sure.

Thx for reading and writing folks,

Fabian
Nov 12 '05 #1
4 2324
It's been saud many times in the forum but can always be restated:
Read The ##%*##%!! Manual (RTFM)

DFT_PREFETCH_SZ is specified in pages.

1. Specifying 200megs for prefetch use is not done using this parameter.
2. The default value specified here is used only when creating new
tablespaces. It will not change your existing ones.
3. ALTER TABLESPACE is used to change prefetch size for an existing
tablespace.
4. If you make prefetch size too large; it can have a severe negative
impact on overall performance.

Optimum settings for prefetch size are determined prmarily from hardware
characteristics of your disk storage and I/O channel architecture.
Performance objectives can also influence this parameter.

See the "Administra tion Guide: Performance" manual for additional
details. Use the index at the back to locate the appropriate page where
a parameter is explained. Follow the references to related parameters
and learn about them too. Read general information about prefetch to
inderstand what it does and how it works. The default value of 32 isn't
a bad starting point.

Philip Sherman

Fabian Knopf wrote:
Hi @ll,

when i want to set the PrefetchSize on my database this setting will not
be set. DB2 shell says that the command is successfull but when i list
my details with.

list tablespaces show detail

This is the output:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4697
Useable pages = 4697
Used pages = 4697
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 9080
Useable pages = 9080
Used pages = 9080
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
I tried this command to get 200 Megs on tablespace prefetch size but its
not working.

UPDATE DATABASE CONFIGURATION FOR DB2 USING DFT_PREFETCH_SZ 200

I think i have the permission to set this i am as a system owner inside
and have instance proviliges for sure.

Thx for reading and writing folks,

Fabian


Nov 12 '05 #2
Fabian.... what you did with "UPDATE DATABASE CONFIGURATION FOR DB2
USING DFT_PREFETCH_SZ 200" was that you updated the database
configuration file. This parameter will be used WHEN you create's a
NEW tablespace, not change the prefetchsize on the current
tablespace's.
IF you want to change prefetchsize on a existing tablespace you have
to use ALTER TABLESPACE TABLESPACE_NAME PREFETCHSIZE NEW_SIZE

db2 list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4576
Useable pages = 4576
Used pages = 4576
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 411
Useable pages = 411
Used pages = 411
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
db2 alter tablespace USERSPACE1 PREFETCHSIZE 64
DB20000I The SQL command completed successfully.

db2 list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4576
Useable pages = 4576
Used pages = 4576
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 411
Useable pages = 411
Used pages = 411
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 1
Nov 12 '05 #3
> UPDATE DATABASE CONFIGURATION FOR DB2 USING DFT_PREFETCH_SZ 200

The above command will work for your new tablespaces. (when you use
CREATE TABLE command the next time for some new tablespace). To change
the prefetch size of your existing tablespaces, use a similar SQL.

db2 "alter tablespace userspace1 prefetchsize 128"

hth,
dotyet
Fabian Knopf <F.*****@gmx.de > wrote in message news:<2v******* ******@uni-berlin.de>...
Hi @ll,

when i want to set the PrefetchSize on my database this setting will not
be set. DB2 shell says that the command is successfull but when i list
my details with.

list tablespaces show detail

This is the output:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4697
Useable pages = 4697
Used pages = 4697
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 9080
Useable pages = 9080
Used pages = 9080
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
I tried this command to get 200 Megs on tablespace prefetch size but its
not working.

UPDATE DATABASE CONFIGURATION FOR DB2 USING DFT_PREFETCH_SZ 200

I think i have the permission to set this i am as a system owner inside
and have instance proviliges for sure.

Thx for reading and writing folks,

Fabian

Nov 12 '05 #4
Philip Sherman wrote:
It's been saud many times in the forum but can always be restated:
Read The ##%*##%!! Manual (RTFM)

DFT_PREFETCH_SZ is specified in pages.

1. Specifying 200megs for prefetch use is not done using this parameter.
2. The default value specified here is used only when creating new
tablespaces. It will not change your existing ones.
3. ALTER TABLESPACE is used to change prefetch size for an existing
tablespace.
4. If you make prefetch size too large; it can have a severe negative
impact on overall performance.

Optimum settings for prefetch size are determined prmarily from hardware
characteristics of your disk storage and I/O channel architecture.
Performance objectives can also influence this parameter.

See the "Administra tion Guide: Performance" manual for additional
details. Use the index at the back to locate the appropriate page where
a parameter is explained. Follow the references to related parameters
and learn about them too. Read general information about prefetch to
inderstand what it does and how it works. The default value of 32 isn't
a bad starting point.

Philip Sherman

Fabian Knopf wrote:
Hi @ll,

when i want to set the PrefetchSize on my database this setting will
not be set. DB2 shell says that the command is successfull but when i
list my details with.

list tablespaces show detail

This is the output:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4697
Useable pages = 4697
Used pages = 4697
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 9080
Useable pages = 9080
Used pages = 9080
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
I tried this command to get 200 Megs on tablespace prefetch size but its
not working.

UPDATE DATABASE CONFIGURATION FOR DB2 USING DFT_PREFETCH_SZ 200

I think i have the permission to set this i am as a system owner
inside and have instance proviliges for sure.

Thx for reading and writing folks,

Fabian


Hi Philipp,

thx for the advice to read the #+$§!$§" manual. ;-)

it worked for me ! First read then think then post ! :-)

cu,

Fabian
Nov 12 '05 #5

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

Similar topics

8
22722
by: Frank Ratzlow | last post by:
Hi folks, I have a list of entries where some entries contain the special character (quotes) within in the string. If I try to save this entry everything after the quotes is skipped. The reason for this is quite obvious, since the resulting html tag looks like this: <option value="go "again" selected="selected">nochmal &quot;go</option>
2
5204
by: BrianP | last post by:
Hi, I have had to invent a work-around to get past what looks like a JavaScript bug, the malfunctioning Perl-like JavaScript array functions including SPLICE() and UNSHIFT(). I have boiled it down to a very simple test case which can be cut-n-pasted into a .html file and viewed in a browser: ...
15
6826
by: dixie | last post by:
I have a command to open the Access Options dialogue from code: DoCmd.RunCommand acCmdOptions It also opens the Database Window behind it. Is it possible to open the Options without having the database window open as well? dixie
6
1552
by: A.M | last post by:
Hi, I need to develop an application that acts as both consolde or windows formas application based on command line args. If have two choice as compiler output options: Console Application or Windows Application. If I choose Console Application as compiler output, and then I run the application in windows mode,it shows a blank black...
1
2235
by: Aldi | last post by:
Hi, Thanks for the privious response on /clr option. Now that I have included /clr option another problem jumps up, and my books do not show anything about these problems: compiler: MS.net: VC++7.0 project: elementary using console The error I get is: hello3 Command line error D2016 : '/RTC1' and '/clr' command-line
7
1532
by: Leo Breebaart | last post by:
I have another question where I am not so much looking for a solution but rather hoping to get some feedback on *which* solutions people here consider good Pythonic ways to approach a issue. The situation is this: I am writing fairly large console scripts in Python. They have quite a few command-line options, which lead to configuration...
6
24972
by: ilo | last post by:
When I want to delete a data from a table that this tabl has a trigger and this trigger reached another tables to delete the data in cursor I have this messeage: DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. My trigger : CREATE TRIGGER ON . FOR DELETE
3
2476
by: Learning.Net | last post by:
How to read a Unicode data saved as ASCII in notepad file as txt ? I tried using streamReader but it is not showing Unicode data. eg. using (StreamReader sr = new StreamReader(test.txt) { string line; // Read and display lines from the file until the end of // the file is reached. while ((line = sr.ReadLine()) != null) {
4
1585
by: =?Utf-8?B?UmljaGFyZA==?= | last post by:
Hi, I have an ASP .NET page with codebehind in a vb file. The page has a dropdown control, and a JS function which used HTTP XML Request to get the data from the database, and populate the dropdown. Actually, the JS creates the OPTION elements using th retrieved db data, and adds them to the SELECT element using DOM objects. The dropdown...
0
7411
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7669
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. ...
0
7926
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...
0
7773
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...
1
5343
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...
0
4962
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...
0
3468
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...
1
1901
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
1
1028
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.