Re: Database server jobs and SQL tuning

My knowledge of SQL is almost none, while VB & ODBC is in fact zero, but if 
the application is governed by the same kind of AS/400 data base rules that 
apply to RPG & CL, you may have inadvertently written a poor performing 
library access structure that is easily fixed.

When we use library list, the user profile gets hit ONE time to verify that 
user has authority to the objects in the list.  If you qualify access to an 
object by what library it is in, AS/400 has to verify security authority 
every time you specify the library, irrespective of whether that was 
validated 2 microseconds ago on another line of code or even is inside the 
library list being used.  Thus, depending on how you qualify object locations 
in your code, and your use of library list, it is possible to totally wipe 
out performance, without gaining any security benefits whatsoever.

>  From:    MccalliM@Midas-Kapiti.com (McCallion, Martin)
>  
>  Hi folks.
>  
>  We're currently in the process of testing an application where we've
>  ported the database from SQL Server to the AS/400.  The client is
>  written in VB and uses ODBC to communicate with the database.  One of my
>  colleagues is currently testing it, and he has _33_ instances of the
>  QZDASOINIT job servicing his user profile at present (he is working at a
>  single workstation, I should add).  To my mind this seems preposterously
>  many.  Does anyone know what causes the system to start a new instance
>  of this job when there is an existing connection already?
>  
>  What testing we have managed to do so far has given very disappointing
>  results in terms of performance.  Clearly we'd all expect the AS/400 to
>  wipe the floor with NT, and I'm fairly sure that the performance issues
>  can be resolved with a little tuning.  Performance way well be related
>  to the vast number of jobs referred to above, which is why I raised that
>  first.
>  
>  I spent most of yesterday searching the web and Infocenter for SQL
>  performance information, and I'm investigating the DBMON commands
>  (STRDBMON and ENDDBMON).  But I wondered whether anyone had any ideas;
>  I'm sure some of you have been through something like this.
>  
>  On specific question was, what would the difference be if we created our
>  database as an SQL collection?  I found an IBM page that describes the
>  very thing we're trying to do
>  (http://www.as400.ibm.com/tstudio/dataware/migrate/Index_M.htm) and we
>  had already done most of it, except that we just created a library and
>  added the journalling objects manually.
>  
>  Any thoughts, manuals, web sites, gratefully received.
>  
>  Cheers,
>  
>  Martin.


Al Macintyre  ©¿©
http://www.cen-elec.com MIS Manager Programmer & Computer Janitor
+---
| 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
+---