iSeries Access ODBC Driver

[FaStOnE]

- Ladies and Germs -

We're having an issue that just cropped up out of no where and I'm kinda perplexed by this... (shocking, I know)

We have an ODBC job that several (20+) of our Supply Chain users utilize to retrieve SKU information from our Replenishment system for placing orders. In a nutshell, it uses a MS Access Script via ODBC to access a Logical View on the iSeries and retrieve records based on Store Number/Region, etc... thus, allowing them to analyze the data and replenish the store.

This has been working very well for the last 9-12 months and seems to always have a decent response time (1-3 minutes .. since it has to do some manipulating on the PC side).

To coin a Southern Phrase ... Last week, this job just blowed up!

The PC would kick off the job to retrieve the data set from the iSeries. I could see the job run on the iSeries, but the data would never be returned to the PC. We received an SQL0666 Status.

Checking the Visual Explain for the job, it shows that the job was received and returned the data set in less than 10-20 seconds. But, based on the SQL Error code, it was acting like the iSeries was telling the PC that it could not return the data set within the required Governor Settings (which is 60 seconds by default).

After getting IBM on the phone and going through traces, Visual Explains, DB Monitors, etc... we ended up removing the Govenor on the ODBC Connection, thus giving no control over rogue jobs, but... this work around got us through and allowed them to get the data.

My question is ... WHY???

No PTFs were added to the iSeries. No Updates to the Work Stations. No changes to the Network or Firewalls.

But, something had to trigger this!

I'm looking for someone that might have had similar issues and/or have suggestions here for data retrieval via iSeries Access ODBC Connections. What is a normal process for connecting being able to depend on it? Any ideas on what I could check/test/change to make this problem go away?

Sorry to get long-winded, but this is very irritating... and I'm running out of Hay-Stacks to look through.

Thanks for any suggestions!

[MichaelCatalani]

Re: iSeries Access ODBC Driver

You might, if you have the ability, go back in time to see how large the data sets were over time. (Or how many records were in the logical) I'm wondering if this is something where the data sets (or files its extracting from) are getting larger, and are now causing the time-out issues.

Also, going off of memory, there are ODBC settings that control whether the data set is returned as *FIRSTIO or *ALLIO. You should be able to see this in visual explain as to which method was chosen. (If its *FIRSTIO, then that sounds like it would be wrong for what I believe the job is trying to do.)

It could also be an issue with the QAQQINI file for the job. Look under the INI section in visual explain for the odbc statement run.

Lastly, without seeing the database or ODBC command, I suppose it could be related to an access path thats been removed.

If you can compare visual explain statements between executions that didnt fail versus the one that did, you might be able to spot where a difference was.

[abercrombieande]

Re: iSeries Access ODBC Driver

I don't have an answer just guesses.
Network issue?
MS Upgrade?

what if you try using linked tables?

[FaStOnE]

Re: iSeries Access ODBC Driver

You might, if you have the ability, go back in time to see how large the data sets were over time. (Or how many records were in the logical) I'm wondering if this is something where the data sets (or files its extracting from) are getting larger, and are now causing the time-out issues.

The data sets have been pretty static for the prior 2 months. The retrieval is from an SQL Logical View (Create View) over a 27 Million record table. The table was built with DDS though ... not DDL. Could switching this to DDL provide performance increases?!?

Also, going off of memory, there are ODBC settings that control whether the data set is returned as *FIRSTIO or *ALLIO. You should be able to see this in visual explain as to which method was chosen. (If its *FIRSTIO, then that sounds like it would be wrong for what I believe the job is trying to do.)

This is a good thought .. I know I have seen where it is pulling *FIRSTIO -- Not sure how much of a difference *ALLIO would make... but, it's worth a try, right?

It could also be an issue with the QAQQINI file for the job. Look under the INI section in visual explain for the odbc statement run.

Everything in the QAQQINI file is still system defaults. Nothing has changed in this table. Not saying there isn't anything I could tweak here, but ... just haven't utilized this option at all.

Lastly, without seeing the database or ODBC command, I suppose it could be related to an access path thats been removed.

One of the first things I looked for ... everything is still there.

[tomholden]

Re: iSeries Access ODBC Driver

and I'm running out of Hay-Stacks to look through.

Thanks for any suggestions!

find bigger Haystacks?

if you aren't on v6.1 or higher changing the IGNORED_DERIVED_INDEX value in the QAQQINI file to *YES might offer some improvement...

[FaStOnE]

Re: iSeries Access ODBC Driver

find bigger Haystacks?

if you aren't on v6.1 or higher changing the IGNORED_DERIVED_INDEX value in the QAQQINI file to *YES might offer some improvement...

We're on v6r1 ... actually getting ready to bump up to v7r1 in the next quarter.

[MichaelCatalani]

Re: iSeries Access ODBC Driver

Just out of curiousity, what is the value of the QQRYTIMLMT system value? (Or what was it, if you just changed it.)

An actual data set delivery of 10-20 seconds may be high enough to trigger an SQL0666 on a query limit set to 30-60 seconds, if the optimizer estimate shows it would be higher than the limit. This is just one of those flakey things where the optimizer may be gathering info from the memory pools, contention, or the *FIRSTIO setting, and estimating that it would take longer than it really would, long enough to where it would think it would exceed the time limit.

[FaStOnE]

Re: iSeries Access ODBC Driver

Just out of curiousity, what is the value of the QQRYTIMLMT system value? (Or what was it, if you just changed it.)

It's set to *NOMAX -- and that hasn't changed. Although, we do know that if the client isn't going to put a Governor on it, then we have to either with the QQRYTIMLMT or in the QQAQINI

[MichaelCatalani]

Re: iSeries Access ODBC Driver

Yea, I was just trying to get a feel for whether the PC's governer setting at 60 was what was actually being used.

The SQL0666 error "usually" means that the query optimizer believes that the estimated time to return the result set will be longer than the query timeout value. The problem with SQL getting smarter and smarter is that it starts taking into account system / memory / pool contention performance, and basing its estimates on that. (Which can cause it to choose a radically different access plan than the one it typically uses, which could result in an estimate which exceeds the timeout value. Or even use the same access plan, but under the belief the time to execute will be much longer. ) In other words, the only thing that may have been different on your system between a successful run and a SQL0666 run was that the optimizer determined there was heavy memory pool contention.

You could probably up the governer time on the client side to a few minutes and get rid of the issue as well, and still have a governor in place. Yoo *might* also need to performance monitor your pools, especially the pools that the server jobs run in, to see if there's any kind of contention issues there.