Suppose you have loaded a table with million of rows and you have updated the DB2 statistics of that table and you want all the packages associated with that table to make use of the new statistics. If your installation has BMC Catalog Manager, then you can follow the below steps to rebind all the packages associated with that DB2 table so the packages can make use of the new statistics.
Type the table name
DBXT- ---------- CATALOG MANAGER 09.03.00 Primary Menu --------------------
Command ===>
Select action and type object information. Then press Enter.
Action 0. (L) List using customizable lists
1. (S) Search for catalog objects
2. (C) Create objects
3. (G) Grant privileges
4. (O) CATALOG MANAGER options processing
5. (D) DB2 Commands
6. (M) Maintain logs menu
7. (Q) List SQL for edit & execution
8. (R) About this Release/CATALOG MANAGER Quick Reference
Obj type TB 11. DB Database 16. TS Tablespace 21. PL Plan 26. ST Strings
12. SG Stogroup 17. SU SysPrivUser 22. AL Alias 27. LO Location
13. TB Table 18. SY Synonym 23. US User 28. CK Checks
14. VW View 19. PG Package 24. CO Column 29. PR Procs
15. IX Index 20. CI Collection 25. DM DBRM 30. XT Aux Tabl
Qualifier ABCDETST.MY_TABLE
Initial attach to DBXT
Copyrights (c) 1987-2009 BMC Software, Inc.
as an unpublished licensed work.
All rights reserved.
Type “PG” against the table name to list all the related packages
DBXT-R -------------------------- TABLE LIST -------------------- ROW 1 OF 1
Command ===> Scroll ===> CSR
01
CMD will show commands for this list. Type command and press ENTER
L:AL CA CK CO DB DS FK IC IM IX KC MX PA PG PK PL RI SG SY TM TS TT UA US VW
LIKE ABCDETST.MY_TABLE
C Table Name Database Tblspace ColsPK Type Rows Pages
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
pg ABCDETST.MY_TABLE ABCDETST S9331493 22 19 T 1114K 30K
****************************** BOTTOM OF DATA *******************************
Type “REBIND ALL” in the command to rebind all the packages
DBXT-R ------------------------- PACKAGE LIST ------------------- ROW 1 OF 8
Command ===> rebind all Scroll ===> CSR
02
CMD will show commands for this list. Type command and press ENTER
LISTS: AL CA CI IM IS IX MX PA PL PR SY TB TR TS UA US VW
QUALIFIER: TABLE=ABCDETST.MY_TABLE
Cm Collection Id Name Ty Version Owner Bnd Date ValidOper
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
COLLXOXX XY0677 2011-01...328423 ABCDETST 2012-01-12 Y Y
COLLXOXX XY0678 2010-12...001154 ABCDETST 2012-01-12 Y Y
COLLXOXX XY0679 2010-11...349583 ABCDETST 2012-01-12 Y Y
COLLXOXX XY0678 2011-09...199423 ABCDETST 2012-01-12 Y Y
COLLXBXX XY5230 2011-11...876290 ABCDETST 2012-01-12 Y Y
COLLXBXX XY5220 2011-11...329011 ABCDETST 2012-01-12 Y Y
COLLXBXX XY5230 2011-12...200788 ABCDETST 2012-01-12 Y Y
COLLXBXX XY5220 2012-01...226956 ABCDETST 2012-01-12 Y Y
****************************** BOTTOM OF DATA *******************************
Type “Y” to generate the REBIND card
DBXT-R ----------------------- Rebind Packages -----------------------------
Command ===>
Generate Rebind . . Y (Y/N)
Enable/Disable . . N (Y/N Specify Enable or Disable parms)
--------- Rebind Using Options From The Package Except As Specified --------
More: +
Owner . . . . . . .
Qualifier . . . . .
Validate . . . . . (R:RUN, B:BIND)
Flag . . . . . . . (I:Information, W:Warning, E:Error, C:Completion)
Isolation . . . . . (R/C/S/N/U/T R:Repeatable Read, C/S:Cursor Stability,
N:no commit, U:uncommitted read, T:Read Stability)
Release . . . . . . (C:Commit, D:Deallocate, blank)
Explain . . . . . . (Y/N)
Current Data . . . (Y/N/blank)
Degree . . . . . . (1/A A-any. Amount of I/O parallelism)
Defer Prepare . . . (Y/N) Defer prepare of SQL that refers to remote
objects until execution.
Dynamicrules . . . (R/B/D/E/H/I R-run, B-bind, D-definebind,
E-definerun, H-invokebind, I-invokerun)
Type “Y” to create the batch job
DBXT-R ------------------------- Confirm DSN ------------- Row 1 to 15 of 60
Command ===> Scroll ===> CSR
03
Edit DSN Commands . . . . . . . N (Y/E/N E-remove TSO WRITEs and DSN output)
Save DSN in SQL table . . . . . N (A/R/Y/N A-append, R-replace, Y-append)
Execute DSN Commands now . . . . N (Y/N) TSO id used : TSUBAQF
Create batch job . . . . . . . . y (Y/N)
Name of saved DSN . . . . . . . 20101014_162801
-------------------------------- DSN Commands -------------------------------
DSN SYSTEM(DBXT)
REBIND PACKAGE(COLLXOXX.XY0677.(2011-01-28-12.34.18.328423))+
ENABLE(*)+
OWNER(ABCDETST) QUALIFIER(ABCDETST) VALIDATE(BIND)+
CURRENTDATA(NO) ISOLATION(CS) RELEASE(COMMIT)+
EXPLAIN(YES) DEGREE(1) KEEPDYNAMIC(NO)+
REOPT(NONE) DBPROTOCOL(DRDA) IMMEDWRITE(NO)+
ENCODING(37) ROUNDING(HALFEVEN)
REBIND PACKAGE(COLLXOXX.XY0678.(2010-12-21-15.06.56.001154))+
ENABLE(*)+
OWNER(ABCDETST) QUALIFIER(ABCDETST) VALIDATE(BIND)+
CURRENTDATA(NO) ISOLATION(CS) RELEASE(COMMIT)+
EXPLAIN(YES) DEGREE(1) KEEPDYNAMIC(NO)+
REOPT(NONE) DBPROTOCOL(DRDA) IMMEDWRITE(NO)+
ENCODING(37) ROUNDING(HALFEVEN)
Submit the below job
DBXT-R ------------------------- DSN Utility -------------------------------
Command ===>
JXY Dataset . . . 'TSUXXXX.BMCCAT.JOB(DSNCMDS)'
Set JXY options . . . . N (Y/N - Change options for creating JXY)
Build Job . . . . . . . Y (Y/N - Create JXY, save in JXY dataset)
Edit Dataset . . . . . . Y (Y/N - Edit JXY dataset)
Submit . . . . . . . . . N (Y/N - Submit JXY dataset)
------------------------------ Utility Options -----------------------------
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.