473,574 Members | 2,952 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Backup using prefetch size for reading.

Hi,

I have a quick question, when one sets the prefetch size = extent size, then
when doing a backup we will have 1 agent (db2bm) doing the reads. If we have
prefetch size a multiple of extent size AND we have multiple containers
and/or PARALLEL_IO set then not db2bm but the db2 prefetchers will read the
data.
Now no matter which one reads the data, it will still be read in 1 extent
size. Wouldn't it thus be much more efficient to always use the the
prefetchers to read the data?

For example,
A) 2 containers, prefetch = extent: db2bm will read extent size from
container 1, then extent size from container 2, .....

B) 2 containers, prefetch > extent: 2 prefetchers will read 1 extent
size, 1 from container 1 and the other one from container 2.

Seems to me that B is more efficient. But since this is a OLTP database, we
have prefetch size = extent size. Would the logic then be better to state:

A) 1 container, prefetch = extent: db2bm will read extent size from
container 1
B) 2 containers, prefetch = extent: 2 prefetchers will read 1 extent
size, one from container 1 and the other one from container 2.

The limit could then maybe set as follow for DB2_PARALLEL_IO , 1 container:
# of extent prefetching = # IO servers / backup PARALLELISM

i.e.:
10 IO servers, PARALLELISM 2, 1 container: Each db2bm will request 5
extents
The limit could then maybe set as follow for DB2_PARALLEL_IO , 2 containers:
# of extent prefetching = # containers

i.e.:
10 IO servers, PARALLELISM 2, 2 container: Each db2bm will request 2
extents
Or, instead of having the db2bm use pread to get the data, to use
asynchronous IO to read the data (just like for example the log writer in V8
uses asynchronous IO to write log pages).

Thanks.
Nov 12 '05 #1
4 3673
A few parameters need to be clarified here:
1) DB2_PARALLEL_IO {ALL or tbspaceid, tbspaceid, ...} is an environment
profile variable set with the db2set command. It is effective only for
environments where your data is on a RAID type device and a single container
has been dfined for the tblspc.; nowhere else. It does not specifies the
number of containers or extents. When you look at the command, it
identifies which tablespace is defined on a RAID type device. If the
tablespace is not defined on a RAID and ALL is used, the variable is ignored
for that tbspace.
2) For backup purposes, I don't believe that prefetchers are used (set with
the dbh cfg parm. num_ioservers), I may be wrong here, I don't quite
remember. There's a PARALLELISM parm. with the backup command that
specifies the number of tblspcs. to be backed up in parallel.. Default is
one by one, starting with tblspaceid 0.
3) When running queries, the optimizer will look at your tblspc. defs. to
find out EXTENT size, PREFETCH size and no. of containers. If you run an
explain on the query, it will show prefetch enabled.
So, if extent size is 32 pages, containers=3, then your DB2 Admin. Guide
recommends that prefetch size should be set to: nmuber of containers *
extent size. So set to 96 pages.
Given that, then your three extents could be read in parallel on a table
scan. This is what enables DB2 to pre-load the required rows in the buffer
pool.
If prefetch is not enabled and/or your IO request is a single page, as in
OLTP, then your agent does the IO, not db2 and prefetchers (or IO_SERVERS).
4) As each query that is prefetch enabled issues itsIO request, it is queued
and the num_ioservers specifies the number of processes that can be
dispatched in parallel to service any or all IO requests from any or all
queries. The service is FIFO.
HTH, Pierre.

"Erik Hendrix" <he**********@h otmail.com> a écrit dans le message de
news:2d******** *************** *******@news.te ranews.com...
Hi,

I have a quick question, when one sets the prefetch size = extent size, then when doing a backup we will have 1 agent (db2bm) doing the reads. If we have prefetch size a multiple of extent size AND we have multiple containers
and/or PARALLEL_IO set then not db2bm but the db2 prefetchers will read the data.
Now no matter which one reads the data, it will still be read in 1 extent
size. Wouldn't it thus be much more efficient to always use the the
prefetchers to read the data?

For example,
A) 2 containers, prefetch = extent: db2bm will read extent size from
container 1, then extent size from container 2, .....

B) 2 containers, prefetch > extent: 2 prefetchers will read 1 extent
size, 1 from container 1 and the other one from container 2.

Seems to me that B is more efficient. But since this is a OLTP database, we have prefetch size = extent size. Would the logic then be better to state:

A) 1 container, prefetch = extent: db2bm will read extent size from
container 1
B) 2 containers, prefetch = extent: 2 prefetchers will read 1 extent
size, one from container 1 and the other one from container 2.

The limit could then maybe set as follow for DB2_PARALLEL_IO , 1 container:
# of extent prefetching = # IO servers / backup PARALLELISM

i.e.:
10 IO servers, PARALLELISM 2, 1 container: Each db2bm will request 5
extents
The limit could then maybe set as follow for DB2_PARALLEL_IO , 2 containers: # of extent prefetching = # containers

i.e.:
10 IO servers, PARALLELISM 2, 2 container: Each db2bm will request 2
extents
Or, instead of having the db2bm use pread to get the data, to use
asynchronous IO to read the data (just like for example the log writer in V8 uses asynchronous IO to write log pages).

Thanks.

Nov 12 '05 #2
Pierre Saint-Jacques <se*****@invali d.net> wrote:
2) For backup purposes, I don't believe that prefetchers are used (set with
the dbh cfg parm. num_ioservers), I may be wrong here, I don't quite
remember. There's a PARALLELISM parm. with the backup command that
specifies the number of tblspcs. to be backed up in parallel..
The Backup utility will use the prefetchers if doing so is appropriate
for the tablespace in question. (The PARALLELISM parameter will determine
how many db2bm processes run simultaneously, and the actual I/Os to the
database will be done either by db2bm or the prefetchers.) We have seen
cases where users would increase the backup parallelism to a large
number and have their backup performance go down because they were starved
for prefetchers, fwiw.
Default is one by one, starting with tblspaceid 0.


This will change in v81FP4. When PARALLELISM > 1, tablespaces will be
backed up in order of largest to smallest.

dave
Nov 12 '05 #3
Thanks very much for bringing me up to date.
These are extremely useful as I've had quite a few questions from other
people on this.
Merci, Pierre.
"David Mooney" <dm*@vnet.ibm.c om> a écrit dans le message de
news:bu******** **@hanover.toro lab.ibm.com...
Pierre Saint-Jacques <se*****@invali d.net> wrote:
2) For backup purposes, I don't believe that prefetchers are used (set with the dbh cfg parm. num_ioservers), I may be wrong here, I don't quite
remember. There's a PARALLELISM parm. with the backup command that
specifies the number of tblspcs. to be backed up in parallel..


The Backup utility will use the prefetchers if doing so is appropriate
for the tablespace in question. (The PARALLELISM parameter will determine
how many db2bm processes run simultaneously, and the actual I/Os to the
database will be done either by db2bm or the prefetchers.) We have seen
cases where users would increase the backup parallelism to a large
number and have their backup performance go down because they were starved
for prefetchers, fwiw.
Default is one by one, starting with tblspaceid 0.


This will change in v81FP4. When PARALLELISM > 1, tablespaces will be
backed up in order of largest to smallest.

dave


Nov 12 '05 #4
Hey David,

Thanks for the information on the order. When tuning/monitoring my backups I
did feel that it would be so much better to go from biggest to smallest
since the parallelism works on a tablespace level. It's nice to know this is
in FP4 now.
"David Mooney" <dm*@vnet.ibm.c om> wrote in message
news:bu******** **@hanover.toro lab.ibm.com...
Pierre Saint-Jacques <se*****@invali d.net> wrote:
2) For backup purposes, I don't believe that prefetchers are used (set with the dbh cfg parm. num_ioservers), I may be wrong here, I don't quite
remember. There's a PARALLELISM parm. with the backup command that
specifies the number of tblspcs. to be backed up in parallel..


The Backup utility will use the prefetchers if doing so is appropriate
for the tablespace in question. (The PARALLELISM parameter will determine
how many db2bm processes run simultaneously, and the actual I/Os to the
database will be done either by db2bm or the prefetchers.) We have seen
cases where users would increase the backup parallelism to a large
number and have their backup performance go down because they were starved
for prefetchers, fwiw.
Default is one by one, starting with tblspaceid 0.


This will change in v81FP4. When PARALLELISM > 1, tablespaces will be
backed up in order of largest to smallest.

dave

Nov 12 '05 #5

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

Similar topics

2
13379
by: nt | last post by:
I am having a problem with a regular backup of an SQL Server (MSDE 2000) database to a local drive. I initiate the backup once a week, by issuing the required T-SQL, via ADO. In this case, the T-SQL is: BACKUP DATABASE GPRS_Dimensioning_Archive TO local_backup WITH RETAINDAYS=21, NAME='GDA_20040706' Note that "local_backup" is a file...
6
3335
by: Eric Herber | last post by:
I've a question regarding db2 (V8.1) and database backups going to a storage manager like TSM for example. As I can see in the storage manager if I backup the complete database over the TSM API (no tablespace backups) the backup images is stored as a single backup object. Later I will be able to restore the complete database from this...
9
3261
by: Stanley Sinclair | last post by:
I just brought a new small server online. It has two disks in RAID1 (mirrored) for the operating system and logs and DB2 DBMS. DB2 data on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i controller.) I don't expect more than 200 GB total data ever in the DB2 database (starting with 120 MB), and 150 GB in other stuff. Want...
6
9742
by: Uthuras | last post by:
Greetings, We have DB2 V 8.1 with FP 4 on AIX 5.2. Our database backup is done using TSM v 4.1.2. However, we notice TSM make use of only one session even though we defined 4 sessions for backup. Here is the command db2 "backup db $dbname online use tsm open 4 sessions with 8 buffers buffer 3072 parallelism 6"
4
3797
by: Hardy | last post by:
hi gurus, now I have to backup and restore a 8 T size db2 database. from two s85 to two 670. the partitions,tablespaces of the db should be redesigned then I plan to use redirected restore. but my concern is, such big size db, I'm afraid something unexpected will destory all the effort. Who have related experience? Can you give some advice?...
4
4720
by: uthuras | last post by:
Hi all, I have DB2ESE version 8.1 with FP 4 on AIX 5.2. My database used to be 1.1TB. When the DB size is 1.1TB, it takes approximately 7 hours to backup the entire database (online backup). The backup is directed to disk and not tape. Recently, we did data archival by purging some of the records. Now, the size of the database is 500GB...
2
2975
by: rdemyan via AccessMonster.com | last post by:
For some of my really important tables, I have backup tables in the backend file. The idea is that if a user accidentally screws up data, they can go to a form and restore from the backup table (as opposed to having to use a backup copy of the back-end). In the past, for the table backup operation, I was simply deleting all of the data from...
1
1568
by: James Yang | last post by:
Sirs, We are using DB2 UDB 8.2.5 for AIX 5.3. Everytime when we perform a online incremental backup to Tivoli, the system will hang for about 10 mins or more at the begining of backup due to no update possible. Meanwhile, reading operation is ok during that time. The DB size is 3.6T and db2rhist.acs size is 40M. Any clue to solve this...
7
3216
by: yellr | last post by:
Hi, i've spent all resources, except this one. Hopefully anyone there out could help me with a idea for this problem. We have a db2 8.2 Enterprise Edition, on AIX 5.3 Platform, this is our production database. We had to do a full restore over this database for a problem with a tablespace. The problem cames after 5 days after full restore. ...
0
7805
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...
0
7726
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...
1
7817
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...
0
6454
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...
1
5622
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
5301
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
3741
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
1341
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1060
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...

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.