Re: AS400 and ODBC

["Vuong Nguyen" <vuongn1@xxxxxxxxxxx>]

Eric, I am very grateful for your concern, patience, understanding, times 
and especially your help.

The AS/400 file which I was trying to access through ODBC has a little over 
600,000 records.  I used ODBC direct link to the file.  I used MS Access 
queries to filter certain data I need.  One of the filter was job number.  
If I used small job ranges in my queries, the retrieving process from AS/400 
to PC took less than 5 minutes, BUT...when I increase the job ranges, almost 
all jobs, it tooks quite a while.

I have notice something:

During regular working hours, on this large job ranges, the querie kepts 
running and running without any end.  But when I run this thing again after 
hours, when no one around and no user accessing the AS/400, it's done in 
less than 10 minutes.  Would it be that I used too much of AS/400 CPU to do 
my thing?  When I run the WRKSYSACT command, I see the numbers in USER, 
assuming it means me, fluctuated around 40%.

Eric, I don't have any order by on my ODBC request.  I just retrieve the 
data by certain criterias and dump it in an MS Access temp table and work 
the data from there.

Also, would it possible to pause other users whose want to access this file 
until I am done with what I want to do in the AS/400?  Another word, make 
the AS/400 file temporarily exclusive to me and put on hold any requests 
from other users.

Please advise.

Thanks again for all your help.  Vuong.

>From: eric.delong@pmsi-services.com
>      Vuong,
>      A couple of things to check...... Make sure the file you are using 
>specifies "Access path maintenance" as *IMMED. Also, for your ODBC request, 
>are you using an "ORDER BY" statement, and if so, do you have an access 
>path (Logical file) on the AS400 that corresponds to your Order By clause? 
>Your problem sounds (to me) like the AS400 is having to build an access 
>path to process your request. On small files, it's not too much of a 
>problem, but as the file grows, the request will take longer to complete.
>
>      hth
>      eric.delong@pmsi-services.com

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---