How to break down 'Other DB2 Time' and I/O wait time by pageset

ramesh bala

How to break down 'Other DB2 Time' and I/O wait time by pageset
Folks, I have been struggling with a nagging production performance issue the past 3 weeks with a distributed threads.
Insight for DB2 , shows the thread sometimes has a 'Other DB2 time' in the order of 7-8 seconds and sometimes I/O wait times in that order.
Can somebody tell me how
1) I can break the 'Other DB2 Time' further into its subcomponents ?
2) How I can break down the 'I/O wait time' by pagesets (tablespaces/indexes).

I appreciate your help
regards
Ramesh



Roger Miller

Re: How to break down 'Other DB2 Time' and I/O wait time by pageset
(in response to ramesh bala)
If this is still ongoing and you don't have DB2 PM, I think the service
group will format your accounting record with DB2 PM to split out the
times. To get the next level down, tuning on the performance trace for SQL
and formatting that does the job.

Roger Miller



Richard Humphris

Re: How to break down 'Other DB2 Time' and I/O wait time by pages et
(in response to Roger Miller)
Hi Ramesh,

Just found this out a little while ago, INSIGHT has a I/O pageset request
trace you can start:
- go to option 8 (user started reports)
- enter the command "LIST" (from the command line)
- then insight will show you all the members from hlq.REQUESTS
- scroll to request "DSIOSTAT" and enter a "S" to start it
- when the start menu comes up, just press enter (and it will start)
- note: you may have to go back to the main menu, then enter "8"
again to go back into user started reports to see DSIOSTAT running and
information on pageset i/o activity.

Usage notes on DSIOSTAT (why I don't like it):
1) you'll only see the screen get updated when you press enter.
And even if you press enter the numbers only change once every 5 minutes.
And there is no past history.
2) Note: the numbers are only for the "active" DB2 datasets during
the last 5 minute interval. Actually, active means active with I/O to the
datasets. So, your very good (but very small) bufferpool probably rarely
does any I/O and so will never appear in this report even though pages
reside in memory and getpages are heavily referencing those in-core pages.

But, you can write a better request than the one INSIGHT provides:
1) Edit dataset hlq.REQUESTS
2) Select DSIO$NEW (to create a new member by that name)
3) Copy DSIOSTAT (into DSIO$NEW)
4) look near the end of the request
5) Change "INTERVAL = DEMAND" to "INTERVAL = 5 MINUTES"
6) Comment out " TERMINAL ..." line like "* TERMINAL ..."
7) add a line like "
OUTFILE(DSN('TESTP.C63401.CNTL.DSIOSTAT.A')OLD)"
8) now you're done editing member.
2) Now allocate a dataset like "TESTP.C63401.CNTL.DSIOSTAT.A" which
is DSORG=PS, RECFM=VB, LRECL=4092, BLKSIZE=4096 (actually lrecl can be
smaller, but who cares)
3) Go thru above procedure to start your DSIO$NEW request... now it
will write the information to a file every 5 minutes and you can view it at
your leisure.
4) Note: still only "active" db2 datasets are reported.

Rich Humphris
CNA



Isaac Yassin

Re: How to break down 'Other DB2 Time' and I/O wait time by pages et
(in response to Richard Humphris)
Hi,

Just a small add-on:
You can issue LIST even without going to option 8 first,
You're lucky as INSIGHT allows you to write your own reports very
easily.

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Humphris,Richard P.
Sent: Friday, November 22, 2002 5:37 PM
To: [login to unmask email]
Subject: Re: How to break down 'Other DB2 Time' and I/O wait time by
pages et


Hi Ramesh,

Just found this out a little while ago, INSIGHT has a I/O pageset
request trace you can start:
- go to option 8 (user started reports)
- enter the command "LIST" (from the command line)
- then insight will show you all the members from hlq.REQUESTS
- scroll to request "DSIOSTAT" and enter a "S" to start it
- when the start menu comes up, just press enter (and it will
start)
- note: you may have to go back to the main menu, then enter
"8" again to go back into user started reports to see DSIOSTAT running
and information on pageset i/o activity.

Usage notes on DSIOSTAT (why I don't like it):
1) you'll only see the screen get updated when you press enter.
And even if you press enter the numbers only change once every 5
minutes. And there is no past history.
2) Note: the numbers are only for the "active" DB2 datasets
during the last 5 minute interval. Actually, active means active with
I/O to the datasets. So, your very good (but very small) bufferpool
probably rarely does any I/O and so will never appear in this report
even though pages reside in memory and getpages are heavily referencing
those in-core pages.

But, you can write a better request than the one INSIGHT provides:
1) Edit dataset hlq.REQUESTS
2) Select DSIO$NEW (to create a new member by that
name)
3) Copy DSIOSTAT (into DSIO$NEW)
4) look near the end of the request
5) Change "INTERVAL = DEMAND" to "INTERVAL = 5 MINUTES"
6) Comment out " TERMINAL ..." line like "* TERMINAL
..."
7) add a line like "
OUTFILE(DSN('TESTP.C63401.CNTL.DSIOSTAT.A')OLD)"
8) now you're done editing member.
2) Now allocate a dataset like "TESTP.C63401.CNTL.DSIOSTAT.A"
which is DSORG=PS, RECFM=VB, LRECL=4092, BLKSIZE=4096 (actually lrecl
can be smaller, but who cares)
3) Go thru above procedure to start your DSIO$NEW request...
now it will write the information to a file every 5 minutes and you can
view it at your leisure.
4) Note: still only "active" db2 datasets are reported.

Rich Humphris
CNA



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Richard Humphris

Re: How to break down 'Other DB2 Time' and I/O wait time by pages et
(in response to Isaac Yassin)
Hi Issac,

Thanks for your insight tip. Both Ramesh and I have Insight monitors; but I
think something as useful at "list" should just have it's own panel so when
you goto to option "xx", it's as if you issued the "list" command. I agree
that Insight's request writing capability is extremely powerful and useful.
But we've only exploited that feature for a few requests here; but I can
imagine people doing some really nice things with this monitor.


Note to Ramesh:

I should add my comment to change the INTERVAL cmd to "INTERVAL = 5 MINUTES"
is based on our "DSSTIME=5" setting in DSNZPARM; which I believe is IBM's
default for DSSTIME (but the range of acceptable numbers is 1 to 1440).

Rich H.



Sherri Sterling

How do I
(in response to Richard Humphris)
I have a script running on an AIX 4.3 box that does db2 commands. What
kind of priviledge would this script that runs under root have to have. I
was thinking sysopr like on the mainframe. But sysctrl allows way too
much access. How do others do this type of thing.

Sherri



Jeremy Schleicher

Re: How do I
(in response to Sherri Sterling)
It all depends on what the script does, If you are going to make
changes to the dbm or db config, then it needs to have at least sysmaint,
if you are doing things with the data then the id needs to have select and
connect granted to that id.


Jeremy Schleicher
System DBA AIX and Windows
May Department Stores Company.



Sherri Sterling
DIGEST NOMIME To: [login to unmask email]
<[login to unmask email] cc:
.TREAS.GOV> Subject: How do I
Sent by: DB2 Data
Base Discussion List
<[login to unmask email]
SOC.COM>


12/19/2002 10:27 AM
Please respond to
DB2 Data Base
Discussion List






I have a script running on an AIX 4.3 box that does db2 commands. What
kind of priviledge would this script that runs under root have to have. I
was thinking sysopr like on the mainframe. But sysctrl allows way too
much access. How do others do this type of thing.

Sherri



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can