Re: Interactive SQL faster than Batch SQL?

[vhamberg@xxxxxxxxxxx]

Yeahm what Rob said.

Here's something from InfoCenter. Notice that it has UPDPROD(*YES) - you 
probably want that. You might know that to STRSRVJOB on a batch job, the job 
should not be held, but the job queue should be held. This can be dicey in a 
production environment unless you submit your job somewhere that will not 
affect anyone else.
Examine query optimizer debug messages in the job log
Query optimizer debug messages issue informational messages to the job log 
about the implementation of a query. These messages explain what happened 
during the query optimization process. For example, you can learn:
Why an index was or was not used 
Why a temporary result was required 
Whether joins and blocking are used 
What type of index was advised by the optimizer 
Status of the job's queries 
Indexes used 
Status of the cursor
The optimizer automatically logs messages for all queries it optimizes, 
including SQL, call level interface, ODBC, OPNQRYF, and SQL Query Manager.
Viewing debug messages using STRDBG command:
STRDBG command puts a job into debug mode. It also specifies certain attributes 
of the debugging session. For example, it can specify whether database files in 
production schemas can be updated while in debug mode. For example, use the 
following command:
STRDBG PGM(Schema/program) UPDPROD(*YES)
STRDBG places in the job log information about all SQL statements that run.

There's more on the messages at 

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/rzajq/pimes.htm#pimes

and

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/rzajq/pimesodp.htm#pimesodp

Good luck, and please post the results - we can maybe help and can certainly 
learn.

Vern
-------------- Original message -------------- 

> No breakpoints necessary. Just let the bugger run. STRDBG PGM(*NONE) 
> will put a lot of nice performance suggestions in your joblog. 
> 
> Rob Berendt 
> -- 
> Group Dekko Services, LLC 
> Dept 01.073 
> PO Box 2000 
> Dock 108 
> 6928N 400E 
> Kendallville, IN 46755 
> http://www.dekko.com 
> 
> 
> 
> 
> 
> fkany@xxxxxxxxxxxxxxxxxx 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 04/20/2005 04:38 PM 
> Please respond to 
> Midrange Systems Technical Discussion 
> 
> 
> To 
> Midrange Systems Technical Discussion 
> cc 
> 
> Subject 
> Re: Interactive SQL faster than Batch SQL? 
> 
> 
> 
> 
> 
> 
> 
> 
> Vern, 
> 
> I'm not clear on what exactly you'd like me to do. I've never checked the 
> optimizer while running a program in debug. Are you asking me to setup 
> the 
> batch program for debug, set a break point at the beginning of the 
> program, 
> run the program, and when the break point hits just release the break 
> point 
> and let the progam run, then look at the job log after the progam 
> completes? 
> 
> Frank 
> 
> 
> 
> 
> 
> 
> vhamberg@xxxxxxxxxxx@midrange.com on 04/20/2005 04:27:54 PM 
> 
> Please respond to Midrange Systems Technical Discussion 
> 
> 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 
> 
> To: Midrange Systems Technical Discussion 
> cc: 
> 
> Subject: Re: Interactive SQL faster than Batch SQL? 
> 
> 
> In addition to what David said, it would be interesting to run each job in 
> debug - you can do the batch one with STRSRVJOB of the batch job on hold 
> or 
> in a held job queue. The optimizer could be choosing different ways to 
> process the statement. Let us know whether you can do this and what 
> results 
> you get. The optimizer messages will be in the job log. 
> 
> Vern 
> 
> -------------- Original message -------------- 
> 
> > fkany@xxxxxxxxxxxxxxxxxx wrote: 
> > > When I submit an SBMJOB an SQL RPG program, it takes almost an hour to 
> > > process. When I manually run the SQL statements using STRSQL, the 
> > > processing takes about 10 minutes. 
> > 
> > Interactive jobs run at a much higher priority than batch jobs. 
> > 
> > The theory is: Interactive jobs need to run very fast while they are 
> > running, but a majority of their time (CPU wise) is spent waiting for a 
> > user to respond. Thus they run fast for very short periods of time. 
> > 
> > A batch job, on the other hand, needs to have systems resources for a 
> > longer period of time, but it doesn't need to run as fast because a user 
> > isn't sitting behind a screen waiting for a response. 
> > 
> > There's more too it, but I think that's a pretty reasonable summary. 
> > 
> > david 
> > 
> > 
> > -- 
> > David Gibbs 
> > david@xxxxxxxxxxxx 
> > 
> > Receipt of this message does not grant you permission to send me 
> > Unsolicited Commercial Email 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>