DDF conversation overhead - DB2 z/OS V8

Jim Irwin

DDF conversation overhead - DB2 z/OS V8
I am looking into a response time issue with one of our user
applications and need some verification from the DB2 user community.
The application uses a vendor product that runs on a windows server and
retrieves data from one of our DB2 subsystems running on z/OS. We are
seeing some transaction with 2+ seconds total in-DB2 time. When I total
the in-DB2 cpu and DB2 wait times I get a total of under 1 second.
However the thread is doing a total of 5 SQL requests from the server .
Am I correct in saying that, since there are no user stored procedures
involved, each of these requests is a separate conversation between the
server and DB2 and that the conversation time could account for the rest
of the in-DB2 time?
Jim Irwin
Technical Support Supervisor

The secret of a good meeting is to have a good beginning and a good
ending; and to have the two as close together as possible.


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Robert Catterall

Re: DDF conversation overhead - DB2 z/OS V8
(in response to Jim Irwin)
I believe that this would not be the case, Jim.  My understanding is that class 2 (i.e., in-DB2) elapsed time does not include time required to transmit data (result set rows, output parameters from called stored procedures, etc.).

A large figure for in-DB2 not-accounted-for time (as you're seeing) is often due to a dispatching problem, with even DB2 itself not getting dispatched readily when it has work to do.  Is your mainframe system running at a really high rate of utilization (say, 95% or more) when the DDF-using application runs?  What priority has been assigned to the DB2 address spaces in your system?

Are any of the five SQL requests to which you refer in your note SELECT statements (cursor-based or otherwise)?  If so, do they have large or small result sets?  Is DB2 DDF block fetch being used for these queries?

Robert


Irwin, Jim wrote: P.msoNormal { FONT-WEIGHT: normal; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; COLOR: black; FONT-FAMILY: "MS Sans Serif", "sans serif" } LI.msoNormal { FONT-WEIGHT: normal; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; COLOR: black; FONT-FAMILY: "MS Sans Serif", "sans serif" } BODY { FONT-WEIGHT: normal; FONT-SIZE: 12pt; MARGIN-LEFT: 50px; COLOR: black; FONT-FAMILY: "MS Sans Serif", "sans serif" } HR { WIDTH: 100%; COLOR: #00ffff; HEIGHT: 1px }
I am looking into a response time issue with one of our user applications and need some verification from the DB2 user community.  The application uses a vendor product that runs on a windows server and retrieves data from one of our DB2 subsystems running on z/OS.  We are seeing some transaction with 2+ seconds total in-DB2 time.  When I total the in-DB2 cpu and DB2 wait times I get a total of under 1 second.  However the thread is doing a total of 5 SQL requests from the server .  Am I correct in saying that, since there are no user stored procedures involved, each of these requests is a separate conversation between the server and DB2 and that the conversation time could account for the rest of the in-DB2 time?
Jim Irwin
Technical Support Supervisor
 
The secret of a good meeting is to have a good beginning and a good ending; and to have the two as close together as possible.
 

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services
--
Robert Catterall
President
Catterall Consulting
770-263-5322

www.catterallconsulting.com


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

Lance D Ziegler1

Re: DDF conversation overhead - DB2 z/OS V8
(in response to Robert Catterall)


Jim,

Are you saying that you have one unit of work (DBAT) from the windows server, and you see a high CL1 elapsed time number for the DBAT, yet the total CL2 (in-DB2, which actually INCLUDES the CL3 DB2 wait times) is a fraction of that number?  Are you looking at an on-line DB2z monitor, or, are you actually post-processing accounting records?  If so, do you have ACCUMACC set to a NON-ZERO number in DB2z V8?  

Robert mentioned issues involving NOT ACCOUNTED time .. please check out the following TECHNOTE that details this topic ...
http://www-1.ibm.com/support/docview.wss?rs=64&context=SSEPEK&q1=II07124&uid=swg21045823&loc=en_US&cs=utf-8&lang=en
American Express made the following annotations on Wed Jan 02 2008 16:03:15 ------------------------------------------------------------------------------ "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." American Express a ajouté le commentaire suivant le Wed Jan 02 2008 16:03:15 Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire indiqué et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes pas le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du courrier ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par erreur, veuillez nous en aviser par courrier et détruire immédiatement le courrier et les pièces jointes. Merci. ****************************************************************************** -------------------------------------------------------------------------------
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services