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

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 "Administration 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

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

> 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.Knopf@gmx.de> wrote in message news:<2vbalsF2k0eueU1@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

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 "Administration 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