RE: Still trouble with ODBC application

Oliver,

I haven't been following this thread but your saying the application does a
Select every two minutes made me wonder.  If your table is very large and
your ODBC driver is doing EBCIDC to ASCII conversion on rows when they're
selected you quite possibly will see significant performance degredation.

Is it possible for you to move the "Select Count(*)" processes to your
iSeries and then retrieve  summarized results?  A trigger, or stored
procedure, on the file can update counters which your application then
retrieves.  Or, if possible, you may want to modify your application to
generate the summaries you are looking for.  In either case, going after
summary level info will be faster than trying to summarize a large file
every two minutes.

Another thing you may want to look at, if you haven't already, is your
driver "Collection Pooling" setting.  This allows you to set the retry wait
time and connection time-out period.  It also allows you to enable and
disable performance monitoring.  IBM's Client Access ODBC driver help also
states "Connection pooling enables your application to use a connection from
a pool of connections that do not need to be reestablished for each use.
Once a connection has been created and placed in a pool, an application can
reuse that connection without performing the complete connection process,
improving performance."

Hope this helps.

-----Original Message-----
From: midrange-l-admin@midrange.com
[mailto:midrange-l-admin@midrange.com]On Behalf Of ouuch@t-online.de
Sent: Tuesday, November 12, 2002 10:05 PM
To: midrange-l@midrange.com
Subject: Still trouble with ODBC application


Hello,

well, I'm still fighting with this ODBC application. Thanks for the manual
tips, so far.

I've now pinned it down to the following behaviour:

Throughout the whole day, single records are retrieved and updated -
basically this means
writing two stati (start process, end process).

In addition, every 2 minutes a series of select count(*) is run:

        select count(*) from vrosif
        select count(*) from vrosif where sistat='40'
        select count(*) from vrosif where sistat='41'
        select count(*) from vrosif where sistat='43'
        select count(*) from vrosif where sistat='45'

I have PowerLock security auditing installed and this gives me a report of
all ODBC sql
statements received by the AS/400.

This report shows me that the AS/400 does only some of the select count(*)
commands and
then the connection is restarted - I have no idea what is causing this?
It could be a bug in the application, the ODBC driver or the AS/400
returning something
unexpected?

The main problem is, this is a production system and very important for our
daily operations.
So I can't play around with this Win2K PC too much.

I've tried the ODBC trace utility of Client Access - but this is just a
stupid joke. Half an hour
of trace gives about 4 meg of text file with absolutely no timestamp! And
the trace seriously
slows down the ODBC connection.

So, my main questions are:

-  Anybody know a decent ODBC trace utility for a Win2K pc?
-  I can do a communication trace of the problem, but have no idea how to
read it. Would
anybody on the list care to take a look? I'd just want to know who breaks
off the
connection?

thanx,

Oliver
_______________________________________________
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.