PACKAGE GETPAGE QUESTION

Jolene Huen

PACKAGE GETPAGE QUESTION
Listers -

I am currently trying to track down a problem with VERY similar symptoms to
this one, reported almost two years ago...excessive getpages on the
catalog - specifically SYSPACKAUTH, SYSPACKAGE, SYSUSERAUTH and SYSRESAUTH.

For one consistent example:

The SQL
inserts a row into a table with only one index

BMC's Apptune is showing the following objects being accessed
the data table - avg 2 getpages
the data index - avg 3 getpages
DSNKAX02 (index on SYSPACKAUTH) - avg 9 getpages
DSNKKX02 (index on SYSPACKAGE) - avg 3 getpages
SYSUSERAUTH - avg 2 getpages
DSNAGH01 (index on SYSRESAUTH) - avg 8 getpages

The plan
is bound with CACHESIZE(4096)
has EXECUTE granted to PUBLIC
contains 9 collections

The package
contains only the one SQL statement, described above
is owned by the same authid that owns the plan
is in the 2nd collection in the package list

Our catalog was recently reorged, and the catalog getpages were not reduced.

Thinking the package authorization information was getting flushed from
cache, forcing reads to the catalog, we increased our CACHEPAC ZPARM from
32K to 1.5M. We saw our number of package cache overwrites reduced to
zero, but still there are lots of getpages to the catalog.

Any suggestions?
Thanks!
Jolene Huen

On Tue, 20 Feb 2001 13:59:30 -0500, RICHARD E MOLERA
<[login to unmask email]> wrote:

>Jeff,
>
>Thanks for the follow-up! Please let me know what you find....
>
>Rick
>
>
>
>
>
>
>"Jeff A L'Italien" <[login to unmask email]> on 02/20/2001 12:24:47 PM
>
>Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>To: [login to unmask email]
>cc: (bcc: RICHARD E MOLERA/SallieMae)
>Subject: Re: PACKAGE GETPAGE QUESTION
>
>
>
>LL,
>
>I checked the plan and it was bound with ACQUIRE(USE). However, I did
speak
>with a buddy of mine about this problem and I think that we've isolated the
>cause. For whatever reason, the developer who created the plan bound it
with
>CACHESIZE(0) which definitely DOES come into play when validating the
>authorizations and thus could potentially have to run up against the
catalog
>during invocation. I've rebound with plan with CACHESIZE(256) and will
keep an
>eye on it to see if it does drop the access to the catalog tables. Thanks
for
>your input.
>
>Regards,
>Jeff L'Italien
>American Express
>
>
>
>From: "Lockwood Lyon" <[login to unmask email]>@RYCI.COM> on 02/20/2001 10:49
AM
> EST
>
>Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>
>
>Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
>
>
>To: [login to unmask email]
>cc:
>Subject: Re: PACKAGE GETPAGE QUESTION
>
>
>Just a shot in the dark here, but is the plan bound with ACQUIRE(ALLOCATE)?
>
>Also (another guess), the "first" execution of the SQL statement may be the
>first allocation of these objects (tablespace and index pagesets), so DB2
may
>require the header pages and space map pages. In addition, depending on
the
>number of levels in the index, the query may need to search down through
the
>index "tree" to get to an index leaf page.
>
>As for the access to the catalog tables, I don't know. Any DDL in the
package
>with hard-coded table owner-ids? Any LOCK TABLE statements? Any PREPARE /
>EXECUTE statements? (Just guessing here, too.)
>
>- - LL
>
>
>"Jeff A L'Italien" <[login to unmask email]> on 02/19/2001 04:39:32 PM
>
>Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>To: [login to unmask email]
>cc: (bcc: RICHARD E MOLERA/SallieMae)
>Subject: PACKAGE GETPAGE QUESTION
>
>
>
>DB2ers,
>
>I had a question regarding the number of getpages accessed by the first
>statement within a package during plan invocation. In viewing the average
>number of getpages for the offending statement using BMC's apptune, it
seemed
>fairly large (over 30). The statement is performing index-only access to
>return the data and resolve the predicate using 4 matching columns within
the
>predicate. The index has been defined as non-unique and in our test
>environment consists only of the root page and 1 leaf page. At most, I
would
>expect to see only 2 getpages for the statement. In running an Omegamon
trace
>against the plan, the other getpages being attributed to this statement are
>those which are accessing catalog tables such as SYSPACKAUTH, SYSRESAUTH
and
>SYSUSERAUTH. The plan and corresponding package have been in existence for
>quite some time, and PUBLIC execute access has been granted to the plan.
I've
>checked the VALIDATE parm at the package and plan level, and both are set
to
>'B'. At this point, I'm at a loss as to why the catalog access is
occurring
>and it seems to be doing so only when this is the first SQL statement
which is
>executed once the plan is invoked. In other cases with the same plan, when
>another SQL statement is first executed, I don't see the catalog table
access.
>Any help is appreciated.
>
>Regards,
>Jeff L'Italien
>American Express
>
>
>
the
>
>
>
>
>
the
>
>
>
>
>Lockwood Lyon -- Meijer Technical Support
>(616) 735-7553 (office)
>(616) 791-5131 (fax)
>
>Copyright (c) 2001 by Lockwood Lyon. All rights reserved. These opinions
are
>mine and not necessarily those of my employer, Meijer, Inc.
>
>
>
the
>
>
>
>
>
the
>
>
>
>
>





Jolene Huen

Re: PACKAGE GETPAGE QUESTION
(in response to Jolene Huen)
Hello, all -

Just thought I'd let you know what we eventually did, which appears to have
solved this problem.

Our package list looks like this:

ROWS
IN
COLLID SYSPACKAUTH
------------------ -----------
H249ALL_O 1728
PEN_ONL_J_DB2 113
ALL_ONL_J_DB2 315
H249ALL 3096
PEN_ONL_Q_DB2 0
ALL_ONL_Q_DB2 9
PEN_ONL_T_DB2 0
ALL_ONL_T_DB2 12
COMMOD 142

Looking in more detail at every statement in the execution, we found that
these catalog getpages were showing up on the first statement executed in
every package not in the first collection in the package list.

The DB2 Administration Guide says:

"You can cache more package authorization information by granting package
execute authority to collection.*, by granting package execute to PUBLIC
for some packages or collections, or by increasing the size of the cache."

As I said earlier, we first tried increasing the size of the cache, which
didn't seem to help.

Next, we tried granting package execute on H249ALL_O.* to the auth-id
that is the owner of all packages in that collection (the first collection
in the package list).

That seems to have helped tremendously
- the catalog accesses have all but disappeared for statements in the
first and second collections in the package list
- the average elapsed time, CPU time and getpages have all been
dramatically improved (50-70%)for the transaction
- we DO still see the catalog getpages on the first statement executed
in any collection after the first two in the list, but it's not as
significant a problem as is was before

We still have more analysis to do of our collection strategy to determine
if more collections could benefit from this type of grant, but the grant of
execute authority to collection.* seems to have been the answer.

Regards -
Jolene Huen