Monday, June 24, 2013

0 DB2 Package

Package is the logical grouping for the resource that require to execute the routine . It is also known as “Access plan” .  Rebind package is the same concept as recompile in other DBMS ( MSSQL, Sybase and Oracle).

  • List all the packages
  • Rebind the package
  • Rebind all package
  • PKGCACHE procedure - generate a summary report of package cache metrics

 

List all the packages

 
 
 
 --- ALl packages: db2 list package for all
   select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   order by pkgschema, pkgname, pkgversion;
 
   --- Package own by NULLID schema: db2 list package for system
   select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   where pkgschema = 'NULLID'
   order by pkgschema, pkgname, pkgversion;
 
   -- Packages own by user: db2 list package 
   select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   where pkgschema = USER
   order by pkgschema, pkgname, pkgversion; 

image

image

REOPTVAR:  Indicates whether the access path is determined again at execution time using input variable values.

  • A = Access path is re optimized for every OPEN or EXECUTE request
  • N = Access path is determined at bind time
  • O = Access path is re optimized only at the first OPEN or EXECUTE request; it is subsequently cached

Rebind the package

  • VALID column indicate whether the package still valid and if it is N, that means the package is not valid and needs to be rebind. 
  • If there is the current user session is using the package, the bind would not complete until the user’s unit of the work complete. the bind would need to hold the exclusive lock in the package in the syscat.package table.

 

SELECT PKGNAME, PKGSCHEMA, PKGVERSION, UNIQUE_ID, BOUNDBY, REOPTVAR,
  TOTAL_SECT, CREATE_TIME, EXPLICIT_BIND_TIME, LAST_BIND_TIME,
  VALID, FORMAT, ISOLATION, BLOCKING
  FROM SYSCAT.PACKAGES
  WHERE PKGNAME = 'P5582207';

image

 db2 rebind P5582207 reopt once

 

image

image

Another way to rebind the package is to use SYSPROC.REBIND_ROUTINE_PACKAGE

However, the SYSPROC.REBIND_ROUTINE_PACKAGE will need to use the routine name ( store procedure name ).

 

To Find the routine name from package name we have to join through the SYSCAT.ROUTINEDEP

  SELECT DISTINCT A.ROUTINENAME, B.BNAME FROM SYSCAT.ROUTINES A, 
SYSCAT.ROUTINEDEP B, SYSCAT.PACKAGES C WHERE
B.ROUTINENAME = A.SPECIFICNAME AND B.BNAME = C.PKGNAME AND C.PKGNAME = 'P5582207'

image

CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'P','BONUS_INCREASE','RESOLVE ANY REOPT ONCE APREUSE YES')

image

Rebind all package

Below command will rebind the invalid package only.

db2rbind sample  -l /tmp/rebindall.log

image

use all parameter would bind all packages.

db2rbind sample  -l /tmp/rebindall.log all

image

 

PKGCACHE procedure - generate a summary report of package cache metrics

Generate the report for what is in the package cache

db2 "call monreport.pkgcache"

-- Last 30 mins

db2 “call monreport.pkgcache(30)”

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates