Re[2]: AS400 and ODBC -- MIDRANGE-L


     Hmmm, this is strange....
     
     Two possibilities come to mind. Pool size allocated to the subsystem 
     that the request is processed in, and the physical connection between 
     the PC and the AS400. 
     
     I'd assume that your ODBC request is being serviced in QCMN susbystem. 
     Try WRKSYSSTS when you run your request, Look for high fault rates. If 
     you see a pool that seems to be getting a large number of faults, you 
     may need to tune the system to allocate more memory to the pool in 
     question. 
     
     The physical connection also could account for your slow response, 
     though you say that it performs well after hours. I'd probably ignore 
     this possibility for now and try to determine if system tuning could 
     solve the problem.
     
     eric.delong@pmsi-services.com 


______________________________ Reply Separator _________________________________
Subject: Re: AS400 and ODBC 
Author:  <MIDRANGE-L@midrange.com> at INET_WACO
Date:    10/18/99 10:46 AM


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 
+---
     


+---
| 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
+---