Re: Optimizing iSeries acess with VB6 (was accessing ...)

[vhamberg@xxxxxxxxx]

Sure. I'll give you the Reader's Digest version (less
digested than what I posted below <g>). Details are
in "Database Performance and Query Optimization" at
<http://publib.boulder.ibm.com/html/as400/v5r1/ic2924/inf
o/rzajq/rzajqmst.pdf>.

If you run SQL with debug on (STRDBG is all you need to
enter), optimization messages will be placed in the job
log. (This applies to serviced jobs, too.) These will
include what access method was chosen (arrival, index-
only, etc.) by the optimizer. It will tell you what
indexes were considered. And it will give you messages
about temporary indexes that were created and what it
suggests you create in the way of logical files or SQL
indexes.

For ODBC you need to STRSRVJOB on the QZDASOINIT job
that is handling your request. This was hard to do, as
it might be done by the time you figure out which one to
service. But CAE V5R1's ODBC driver lets you specify
that you want STRDBG run in the server job, so that
these messages will be put into that job's log.

This technique should apply to ODBC, OLE/DB, and
net.data, when it uses the SQL LEI(?).

This link has some performance diagnostic pointers -
<http://publib.boulder.ibm.com/html/as400/v5r1/ic2924/ind
ex.htm?info/rzaii/rzaiiodbc62.HTM>

These books have some of the best info I've seen
<http://publib.boulder.ibm.com/pubs/pdfs/as400/V4R2PDF/qb
kade01.pdf> &
<http://publib.boulder.ibm.com/pubs/pdfs/as400/V4R4PDF/QB
3AU900.PDF>. They're both still listed on the
current 'Client Access for Windows Family Library' web
page <http://www-
1.ibm.com/servers/eserver/iseries/access/calib.htm>
> Oh, please elaborate on this.
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> http://www.rutgersinsurance.com
> ----- Original Message -----
> From: "Vernon Hamberg" <vhamberg@attbi.com>
> To: <midrange-l@midrange.com>
> Sent: Tuesday, August 13, 2002 12:04 AM
> Subject: Re: access as/400 database files w/Visual Basic 6
>
>
> > Performance with ODBC and OLE/DB is completely dependent on good use of
> > indexes, so turn on STRDBG in the serving job QZDASOINIT. V5R1 version of
> > ODBC driver lets you turn this on from the data source definition. Then
> you
> > can get index recommendations. You CAN get excellent response from ODBC
> > with good indexes and larger block sizes, etc. Likewise with OLE/DB. Check
> > the Knowledge Base.
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>