Re: SQL Tools and Performance

[CRPence <CRPbottle@xxxxxxxxx>]

On 12-Mar-2015 12:06 -0500, John Yeung wrote:
> On Thu, Mar 12, 2015 at 12:32 PM, Gerald Kern wrote:
> > Vern - putting the B and pressing enter displays the bottom of the
> > result set *IMMED.
>
> As usual, Chuck did cover this possibility.  The default value for
> the REFRESH parameter on our system is *ALWAYS, and as I'm trying it
> right now, it does seem to be able to jump to the bottom pretty
> quickly. (If I'm reading Chuck right, he's saying that *FORWARD can
> cause scrolling to the bottom to take more time.)
>
> I must say the quickness was a little surprising to me.  I could
> have sworn it wasn't always that fast.  I will say that STRSQL is
> still a fairly new thing at our shop, and I'm still in that awkward
> phase where I sometimes use RUNQRY/WRKQRY and sometimes use STRSQL.
> Maybe Vern and I are both remembering the slowness of getting to the
> bottom of a RUNQRY result, or maybe he has his refresh set to
> *FORWARD.

  The Query/400 report writer for xxxQRY commands [is a run-time 
component of the OS and is the same report writer for the STRSQL when 
making *LOCAL SELECT query requests] operates the same as the STRSQL 
REFRESH(*FORWARD) reporting; effectively identical processing, with few 
exceptions [row numbering being the most conspicuous difference for 
output to display], between the two interfaces.

  With the STRSQL REFRESH(*ALWAYS) the SQL cursors are opened as a 
SCROLL cursor, and the Query/400 report writer has code specifically for 
positioning to mimic the effects of the FETCH options *other* than NEXT; 
the NEXT option is [in effect, if not actually] the only option for a 
cursor not defined as SCROLLable.

  To effect positioning for a *BOT request using REFRESH(*ALWAYS), the 
Query/400 code mimics a FETCH LAST followed by a FETCH PRIOR sufficient 
to fill one /page/ of the Display Data panel, and then IIRC due to reuse 
of code that depends on the processing of rows retrieved with the 
effective FETCH NEXT [per direction of forward vs reversed], the code 
finally mimics the FETCH NEXT against which the formatting\reporting on 
the remaining rows occurs.

  To effect positioning for a *BOT request using REFRESH(*FORWARD), the 
Query/400 report writer code mimics repeated FETCH NEXT requests.  After 
each fetch, the returned rows are formatted for presentation in the 
format of the output device [which in this case is inherently the 
display where positioning is allowed, but this commentary is included, 
so as to allude to the similar effect that occurs for output to 
printer], and then each formatted row is written to that output device.