Results 1 to 4 of 4

Thread: package cache

  1. #1
    Join Date
    Jul 2005
    Posts
    103

    Unanswered: package cache

    AIX 5.3 (JFS2)
    DB2 8.2

    Do all queries issued against a database get put in the package cache? To be clear, and say differently, I want to know if there is ever a an occasion for a given query to not have an entry in what is reported by the "db2 get snapshot for dynamic sql on db" command?

    My intent is to use this to identify what queries (and tables) are involved in excessive execution times.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That is true for Dynamic SQL, but not if the SQL statement is embeded static SQL in a application (C, COLBOL, etc) that is pre-compiled, compiled, and a bind package takes place. SQL Stored proecedures are also bound in packages.

    Also, if the package cache is too small, and a package is bumped out to make room for a new package, then you may not see it with the snapshot.

    Also, make sure your Statement Monitor is set ON in the dbm config.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2005
    Posts
    103
    I don't suppose you know whether or not there is much embedded/compiled/bound sql with SAP?

    There is currently a healthy listing in my package cache so there must be a good deal of dynamic sql.

    Thanks again.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should be able to tell by looking at the list of packages in the Control Center. Obviously, you need to ignore any packages that come with DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •