Mike, it definitively looks as you are going thru transmission problems. THis can be verified by running the qyery on the actual server. From what you write I don't think it would prove any different from what you are seeing now. The query is fine it's data flow that is not. So: 1) If you can do an explain do ensure that blocking is enabled. Use dynexpln as the quickest way to do. Blocking happens if a) The cursor is defined as fetch or read only or b) It'S not but the bind parm. specifies blocking unambiguous 2) Geiven that blocking is enabled, verify that you RQRIOBLK is a) A multiple of the row size coming back or as close as possible. b) Is big enough to take on as many rows as possible (Default is 32k). 3) You may have to snapshot to verify that if even blocking is enabled, blocking is happening. If blocking is not happening then your rows are coming back to the wkstn. one be one and that would be your problem. Even if explains says yes, DB2 may refuse to block because your rqrioblk is too big for aplheapsz or some other parm.
If you still have issues, I'd start to look at the comm. mgr. level to find out the size of send buffers on the server size and the size of the receive buffers on the client size. Obviously, you'd want then to be as close to rqrioblk as possible. Lastly, yes the OPTIMIZE FOR clause in the qyery will help. Optimize for as many rows as you'd fit in the rqrioblk or aq multiple thereof. HTH, Pierre. [EMAIL PROTECTED] wrote: > Mike,Ok I'm getting seriously out of my depth here but as no one else > seems to be playing.....If the data can be retrieved from DB2 in ~5s > and a transfer can theoretically be done in ~ 5s I guess there must be > a way of controlling how the data gets passed _to_ the network. I > recall that a second meaning of the OPTIMISE clause is to control > packet size for network access. Have you looked into this area? I am > not sure if you can a) get the OPTIMISE clause onto your code if it is > coming from diverse sources or b) if here are other (non-DB2 maybe) > places where you can control that.I have copied this to the 'other' > list to see if any gurus there have any ideas.Regards,Steve T > > -----Original Message----- > From: Mike Wiles [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 1 November 2001 7:58 AM > To: [EMAIL PROTECTED] > Subject: Re: Speeding up query _OUTPUT > > The five minutes has nothing to do with zip, or the > conversion. For my testing I'm not even running the query > through this, I'm just running the query itself, and only > the query itself. I've done this into a few different > programs, but I'm going to simplify the scenario by > describing exactly what I am doing.1) on a completely > separate machine (in this case, the laptop I am typing this > email on) I start FoxPro2) in Foxpro I connect to the DB2 > database3) in Foxpro, I start running the query.4) around > five minutes later, I get the entire 64 megs of data (I can > see the records counting up as they stream across to me.)5) > I copy the resulting data to a dbf file5) as an experiment, > I take the dbf file and FTP it back to the database server. > The 64 meg file takes 5 seconds to FTP. I do this to crudely > benchmark my network performanceAs you can see, this has > nothing to do with the web server, or converting, or zipping > or unzipping. As far as I'm concerned, that stuff is not my > problem. Since I'm the DBA, I need to insure that the data > gets to the client, whether it be a web server, a Foxpro > program, a Visual Basic program, our mainframe, or whatever, > within the specified amount of time. In this case, the > specified amount of time is two minutes.I'm fairly certain > that the query itself is running in about five seconds, > because I can see the CPU utilization jumping up for about > five seconds. During this time DB2 reports "UOW Executing". > For the remaining 4 minutes 55 seconds, CPU falls to 4%, and > DB2 reports the connection is "UOW Waiting". So... if I'm > correct, and all the work is happening in the initial five > seconds, how do I get the data to come out quicker? Are > there switches to twiddle? Or am I wrong, and that 100% is > just the dynamic sql being processed, and that 4% usage is > really all that's needed to continue running the query and > shove it out to the client?Mike > > -----Original Message----- > From: Steve Tennant > [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, October 30, 2001 7:32 PM > To: [EMAIL PROTECTED] > Subject: Re: [DB2-L] Speeding up query _OUTPUT > > Intriguing.If you have transmitted your file and > you say it takes ~5 secs then isn't the problem > likely to lie in the phrases "..., convert to > Excel or whatever file format is requested, then > zip the resulting file..."?How long does it take > you to a) do the conversion to whatever in a > stand-alone test and b) how long does it take you > to then zip the result of that. I am a big fan > of zip/unzip but if it is the on one Wnnnn, surely > that in itself is going to take a measurable time > for a ~64Mb file, and as to conversion > time....Just thoughts.Steve T >
begin:vcard n:Saint-Jacques;Pierre tel;cell:514-233-8679 tel;fax:514-737-1268 tel;work:514-737-4515 x-mozilla-html:FALSE org:SES Consultants Inc. adr:;;233 Simcoe Cr.;Mount-Royal;QC;H3P 1X1;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:President fn:Pierre Saint-Jacques end:vcard
