MS Access caused (we think) problem with AS400

[Larry]

I ran a ODBC SQL query this morning using three inner joins to pull
some data off of our AS400. This was a passthrough query, which talks
to the server (the AS400) directly, rather than going through the MS
Jet engine. In the past, when I've done this, nobody has even
noticed. Today, my system locked up, and I had to end task, I'm on NT
Workstation 4.0.

The AS/400 proceeded to go to 90% CPU utilization and the server job
that allows the query to run started chewing up hard drive space at an
alarming rate..

I accidentally started the same query again this afternoon with
similar results. Anyone have any advice as to:

1) What happened and why did it?
2) How can I avoid this in the future?

Our operator thought the problem was that the AS/400 doesn't know that
the PC has disconnected and goes into a loop.

Any one else run into this before?

Larry
Larry Blankenship
bla...@netins.net
~~~~~~~~~~~~~~~~~~~~~~~~
Having a positive attitude won't solve all your problems,
but it will annoy enough people to make it worth your while.
~~~~~~~~~~~~~~~~~~~~~~~~~~

[Thomas]

Larry:

Last time I recall seeing anything even similar was with PC Support File
Transfer over SNA back around 1990.

It isn't clear if this was a query you've run the same successfully in
the past or if your past queries were only similar to this one. And any
chance you could post the basics of the query itself?

Tom Liotta

In article <bov88t4bqbl5s1tr3...@4ax.com>,

--
Tom Liotta, AS/400 Systems Programmer
The PowerTech Group, Inc.; http://www.400security.com

Sent via Deja.com
http://www.deja.com/

[Thomas Stüfe]

plus, as an afterthought, have you tried to run the same SQL statement at
the greenscreen using STRSQL and do you get the same effect?

[Thomas Stüfe]

Hello, Larry,

What OS/400 release? What exactly is your SQL statement and how big would
have been - if you would have seen it - the result set of the operation?

Larry <bla...@netins.net> schrieb in im Newsbeitrag:
bov88t4bqbl5s1tr3...@4ax.com...

[Kent Milligan]

Need to look at how the query was implemented by DB2, some DB2 query
implementations do make use of temporary storage - so what you saw does make
some sense. If you're not very experienced with the AS/400 optimizer then I
would recommend attending the AS/400 SQL Performance Workshop (ILS Course
#S6140).

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km...@us.removethis.ibm.com GO HAWKEYES!!
(opinions stated are not necessarily those of my employer)

[Larry]

On Sat, 10 Feb 2001 09:52:36 +0100, "Thomas Stüfe"
<thomas...@t-online.de> wrote:

>Hello, Larry,
>
>What OS/400 release? What exactly is your SQL statement and how big would
>have been - if you would have seen it - the result set of the operation?
>
>
>

Thanks for your response. I think I figured out the problem, but I'd
like your opinion. The query was across three tables..for the sake of
argument, let's call them the Load Header, Load Detail, and Customer
Master tables.

The problem was that I was trying to do the query across three
different files with the criteria going against fields in two of the
three tables.

So it went something like this:

Get all customers that the local warehouse has shipped something to in
the last few months.

Or in SQL

SELECT Customer Number, Location Code, Customer Name
FROM Customer Master
Inner Join Load Detail on customer number = customer number(from
customer master) and location code = location code(from customer
master) (this gives me all loads for a given customer)
inner join Load Header on load number = load number (from load detail)
Where warehouse = local warehouse and load status = 'shipped' and load
date > 01/01/00

I believe what happened is it had to make a table duplicating all of
the customers which it then had to add all of the loads in the load
detail to at which point it added all of the records in the load
header file before it could start sorting out the actual criteria.

(I feel dumber than a geriatric Floridian the day after Election day.)

It's no wonder it hung.

I also found out after the fact that I wasn't getting a true picture
of the response, since I was sending the results to the screen rather
than to a print queue.

Now, my next question:

Is there any way, in v4r4 to prevent this kind of thing from
happening, other than me not doing stupid things?

My current thought is to never query against more than one table at a
time, and simply complete the first query before trying to hit the
next. Is it better to do this with SQL subqueries, or simply execute
pass through ODBC queries in succession. In other words, the above
example would then become this:

1) Get all load numbers and any other pertinent data from Order
header where the load date is later than 30 days ago and the warehouse
is the local warehouse

2) For each load in #1, get the customer number and location code
from the load detail file. and update customer number and location
code

3) for each unique customer and location code in #2, get the customer
name from the customer master file and update the customer name .

Thanks again for your help,

[Thomas Stüfe]

Hello, Larry,

the first thing I would try is, execute exact the same SQL query at the 5250
terminal, to rule out the possibility that somehow the Client Access ODBC
driver is messing around. If the latter is the case, try to get an update
for the driver or open a problem request at IBM.

In general, SQL queries can take very long if not very good formulated. This
is not a flaw of the SQL engine but a principal problem - how would the SQL
engine know whether you accept a complicated query to run for days or not?

However, with DB2/400 there is a way to specify a time value the query must
not surpass. Strictly spoken this is not a timeout, as the optimizer tries
to guess the time the SQL query would take right away and does not execute
it if it thinks it would take longer than allowed.

So e.g. if you would say: SELECT * FROM TAB1 - and TAB1 has 3 zillion
records - and you specify as execution time 1 second - then the optimizer
returns right away saying "query would take too long".

You specify this timeout using hte OS/400 command CHGQRYA ... QRYTIMLMT(??).
You have to specify this command for the Client Access server job which is
serving your ODBC driver. Maybe you'll have to speak with your AS/400
department to ask for help. You could e.g. write a tiny stored procedure
which calls this CL command and execute it using ExecSQL.

Alternativly, if you are using C or C++ for ODBC programming, there is a
ODBC connection attribute SQL_ ATTR_QUERY_TIMEOUT - you set it using
SQLSetConnectionAttr (). I don't know what it does , though, and whether it
corresponds to CHGQRYA ... QRYTIMLMT(??).

Hope that could help,

Thomas Stuefe

Larry <bla...@netins.net> schrieb in im Newsbeitrag:

o63f8tg97s9krr40u...@4ax.com...