Re: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?

[BILL_GALLAGHER]

Ian,

Increasing the package cache size would just alleviate the symptoms, but
not address the problem.  The problem is that the application should have
been written using static SQL.  I can increase the package cache to improve
the hit ratio, but as the volume of data and transactions for the
application continue to grow, the package cache will have to grow
accordingly as well.  If the application can be rewritten using static SQL,
we should be able to keep the package cache at a reasonably sized, fixed
size.  That's my goal, and I need to be able to convince the application
folks that that's what they need to address now as opposed to later when
the machine starts getting constrained by CPU/memory.

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT  06083



                                                                                       
                                     
                   "Ian D. Bjorhovde"                                                  
                                     
                   <[EMAIL PROTECTED]>        To:     [EMAIL PROTECTED]     
                                     
                   Sent by:                      cc:                                   
                                     
                   [EMAIL PROTECTED]        Subject:     Re: DB2EUG: Overhead for 
Dynamic SQL in UDB/AIX?              
                   a.best.com                                                          
                                     
                                                                                       
                                     
                                                                                       
                                     
                   03/21/01 04:00 AM                                                   
                                     
                   Please respond to                                                   
                                     
                   db2eug                                                              
                                     
                                                                                       
                                     
                                                                                       
                                     




From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 20, 2001 2:20 PM
Subject: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?

> ...
>
> After two weeks of monitoring, I'm seeing that we're getting about a 70%
> hit ratio in the Package Cache, which is actually better than I expected,
> but nowhere near what I would expect had this been written using static
SQL
> or at least dynamic SQL with parameter markers.  I would expect that we
> could easily see 95%-98% hit ratio or better in the Package Cache if this
> were the case.

Have you tried tuning the package cache size (PCKCACHESZ in db cfg)?
If not, increasing it should have some effect on the hit ratio.

Is the DB2 optimizer smart enough to recognize dynamic SQL, parameterize
it and store the modified statement in the package cache?  Given all
of the features of the optimizer, I would be surprised if this wasn't
already a feature.






=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod