RE: Database server jobs and SQL tuning -- MIDRANGE-L

Wow.  Is this documented, Al?  Could you point to some kind of reference if
you have it handy, please?

- Dan Bale

> -----Original Message-----
> From: MacWheel99@aol.com [SMTP:MacWheel99@aol.com]
> Sent: Wednesday, May 17, 2000 1:26 PM
> To:   MIDRANGE-L@midrange.com
> Subject:      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
+---