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 _OUTPUTThe 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 _OUTPUTIntriguing.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
