Re: Interactive SQL faster than Batch SQL?

[fkany@xxxxxxxxxxxxxxxxxx]

For who ever wants to read it here's parts of the job log that show the
query optimizer messages:
(If interested in seeing all the messages, just let me know and I'll post
it.)
=================================================================================
Message . . . . : **** Starting optimizer debug message for query .
Cause . . . . . : The optimizer debug messages that follow provide query
optimization information about how the query was implemented. This message
is the first debug message for the query and indicates the start of a set of
debug messages. Debug message CPI434B marks the end of the debug messages
for this specific query. All the debug messages in between this message and
CPI434B describe the overall implementation for this query.
CPI432C Information 00 04/21/05 08:27:09.282496 QQQIMPLE QSYS *STMT QSQRUN3 
QSYS *STMT
>From module . . . . . . . . : QQQIMPLE
>From procedure . . . . . . : QQQSNDPMSG
Statement . . . . . . . . . : 4197
To module . . . . . . . . . : QSQOPEN
To procedure . . . . . . . : FULL_OPEN
Statement . . . . . . . . . : 19206
Message . . . . : All access paths were considered for file FRL00144.
Cause . . . . . : The OS/400 Query optimizer considered all access paths
built over member FRL00144 of file FRL00144 in library LTL400AVX3. The list
below shows the access paths considered.

(files removed from text)

>From module . . . . . . . . : QQQIMPLE
>From procedure . . . . . . : QQQSNDPMSG
Statement . . . . . . . . . : 4197
To module . . . . . . . . . : QSQOPEN
To procedure . . . . . . . : FULL_OPEN
Statement . . . . . . . . . : 19206
Message . . . . : Access path of file FRL00165 was used by query.
Cause . . . . . : Access path for member FRL00165 of file FRL00165 in
library LTL400AVX3 was used to access records from member FRL00144 of file
FRL00144 in library LTL400AVX3 for reason code 1. The reason codes and their
meanings follow: 1 - Record selection.

(The query appears to have chosen it's own access path instead of the access 
path I specified
in the SQL statement.  I guess I'll have to change my SQL statement to use the 
same access path.)

>From module . . . . . . . . : QSQOPEN
>From procedure . . . . . . : SNDINFOMSG
Statement . . . . . . . . . : 20940
To module . . . . . . . . . : QSQINS
To procedure . . . . . . . : SQL_Insert
Statement . . . . . . . . . : 8544
Message . . . . : ODP created.
Cause . . . . . : An Open Data Path (ODP) has been created. No reusable ODP
could be found.
Recovery . . . : If a cursor is being
opened many times in an application, it is more efficient to use a reusable
ODP, and not create an ODP every time. This also applies to repeated runs of
INSERT, UPDATE, DELETE, and SELECT INTO statements. If ODPs are being
created on every open, see the close message to determine why the ODP is
being deleted.

>From module . . . . . . . . : QSQOPEN
>From procedure . . . . . . : SNDINFOMSG
Statement . . . . . . . . . : 20940
To module . . . . . . . . . : QSQINS
To procedure . . . . . . . : SQL_Insert
Statement . . . . . . . . . : 8544
Message . . . . : Blocking used for query.
Cause . . . . . : Blocking has been used in the implementation of this
query. SQL will retrieve a block of records from the database manager on the
first FETCH statement. Additional FETCH statements have to be issued by the
calling program, but they do not require SQL to request more records, and
therefore will run faster. Recovery . . . : SQL attempts to utilize
blocking whenever possible. In cases where the cursor is not update
capable, and commitment control is not active, there is a possibility that
blocking will be used.

>From module . . . . . . . . : QQQVAP
>From procedure . . . . . . : QQQSNDPMSG
Statement . . . . . . . . . : 7490
To module . . . . . . . . . : QSQOPEN
To procedure . . . . . . . : FULL_OPEN
Statement . . . . . . . . . : 19206
Message . . . . : The OS/400 Query access plan has been rebuilt.
Cause . . . . . : The access plan was rebuilt for reason code 7. The reason
codes and their meanings follow:7 - OS/400 Query requires the access
plan to be rebuilt because of system programming changes.
Recovery . . . : Excessive
rebuilds should be avoided and may indicate an application design problem.
Technical description . . . . . . . . : The access plan was rebuilt
because of reason code 7. This reason code has a sub-type identifier of
X'8002' that is used for internal OS/400 tracking.






rob@xxxxxxxxx@midrange.com on 04/21/2005 08:30:44 AM

Please respond to Midrange Systems Technical Discussion
       <midrange-l@xxxxxxxxxxxx>

Sent by:    midrange-l-bounces@xxxxxxxxxxxx


To:    Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc:

Subject:    Re: Interactive SQL faster than Batch SQL?


Still;  all-in-all it may not be an index problem, etc that STRDBG can
find.  It may be the issue with all of those UDF's.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com