Plans, Packages and Collections

1
Plans, Packages and Collections

• Pam Odden

2
Objectives

• What is a DBRM?
• What is a plan?
• What is a package?
• What is a collection?
• Program preparation guidelines

3
What is a DBRM?

• A module containing SQL statements extracted from
  a source program by the DB2 precompiler
• It is stored as a member of a partitioned data
  set.
• When a DBRM is bound into a plan, all its SQL
  statements are placed into the SYSIBM.SYSSTMT
  catalog table. When a DBRM is bound into a
  package, they are placed in the
  SYSIBM.SYSPACKSTMT table.

4
What is a Plan?

• An executable module containing the access path
  logic produced by the DB2 optimizer.
• It can contain one or more DBRMs and/or
  packages.
• A plan is created by the BIND command.
• DB2 catalog tables are consulted to determine the
  best access path
• Information about the plan is then stored in
  other DB2 catalog tables
• The executable plan itself is stored in the DB2
  Directory
• In earlier versions of DB2, DBRMs were bound
  directly into plans. The concept of a package
  was added later.

5
What is a Package?

• A single, bound DBRM with optimized access paths.
  
• By using packages the access logic is packaged at
  a lower level of granularity. The package name
  is added to the package list of a plan, so the
  package may be executed when the plan is
  executed.
• Benefits of using packages
• If a program changes, only that programs DBRM
  needs to be rebound. Without packages, the whole
  plan needs to be rebound at every change
• Bind parameters, such as isolation level, can be
  specified at the package level.
• Packages can be versioned, and multiple versions
  can exist in the DB2 catalog at the same time.
  By running the appropriate load module, DB2 knows
  which package to execute.
• If you are using DB2 remotely, you can specify
  the location at which to bind the package.

6
What is a Collection?

• An identifier specified for each package used to
  group the packages into logical groups, or
  collections. It is not a physical DB2 object.
• Packages are included in plans according to their
  collection ids.
• For each of our systems at CCSD we have a
  collection for batch and a collection for online.
  We also have a batch and online collection for
  all subroutines. The subroutine collections are
  bound into all plans so subroutines can be shared
  across systems.
• For example
• CM6011BA Batch collection for Payroll programs
• CSUBRTNB Batch subroutines
• CPAYROL1 Online collection for Payroll
  programs
• CSUBRTNO Online subroutines

7
Using Collection ID to run against Multiple
Databases

• By specifying a different collection identifier
  for a package, the same DBRM can be bound into
  different packages, enabling easy access to
  tables with the same structure but different
  qualifiers.
• For example, say you have two databases with the
  same tables but different qualifiers SSASIDB1
  and SSASINYR. And say it would be convenient to
  access both databases with the same program.
• Write the program, using unqualified embedded SQL
• Bind the program twice. Once into a collection
  id like CSASI, with SSASIDB1 specified as the
  qualifier. Once into a collection id like
  CNEWYR, with SSASINYR specified as the qualifier.
• DB2 BIND PACKAGE gt QUALIFIER(SSASIDB1)

8
Using Collection ID to run against Multiple
Databases (cont.)

• DSNT254I DSNTBCM2 BIND OPTIONS FOR
• PACKAGE CCSDTSN.CS1020BA.SAMPLE4.()
• ACTION REPLACE
• OWNER PJO
• QUALIFIER SSASIDB1
• VALIDATE BIND
• EXPLAIN NO
• ISOLATION CS
• RELEASE
• COPY
• Include both collections in the plan
• The program then specifies which collection to
  use, via the SET CURRENT PACKAGESET statement
• EXEC SQL
• SET CURRENT PACKAGESET HOST-VAR
• END-EXEC.

9
What does this mean to me?

• A program must be compiled into the correct
  collection.
• COMMAND gt
  
• 
  
• ISPF LIBRARY
  
• PROJECT gt PDFLIB
  
• GROUP gt PJO gt gt
  gt
• TYPE gt COBOL
  
• MEMBER gt S2530016 (Blank for
  member selection list)
• DB2 SUB-SYSTEM gt TSN (DSN, TSN, or
  BSN)
• TARGET SYSTEM gt BATCH (BATCH, CICS,
  or TSO)
• LOADLIB QUALIFER gt TEST (TEST,
  INTERIM, ALT, SASI, or PROD)
• PKG. COLLECTION ID gt CS2530BA
  (Package Collection ID)
• COMPONENT SELECTION (S to Select
  Component)
• DB2 PRE-COMPILER gt S
  
• CICS TRANSLATOR gt
  
• LINKAGE EDITOR gt S
  
• DB2 BIND PACKAGE gt S (NOT A PLAN)
  

10
What does this mean to me?

• Following is the runtime error that resulted from
  compiling program SAMPLE4 into CS1015BA instead
  of into CS1020BA
• DSN SYSTEM (TSN)
  
• DSN
  
• RUN PROG (SAMPLE4) PLAN (S1020BAT)
  
• DSN ENDED DUE TO ERROR
  
• SYSTEM ABEND CODE 806 REASON CODE 00000004
  
• 1000(1) ERROR SELECTING ATTP
  
• DSNT408I SQLCODE -805, ERROR DBRM OR PACKAGE
  NAME CCSDTSN..SAMPLE4.1725D57-
• 4054A3E64 NOT FOUND IN PLAN S1020BAT.
  REASON 03
• DSNT418I SQLSTATE 51002 SQLSTATE RETURN CODE
  
• My JCL expects to be running plan S1020BAT
• //PJOSAMP JOB CLASSA,MSGCLASSX,NOTIFYPJO
• //
• //STEP010 EXEC PGMIKJEFT01,DYNAMNBR20
• //STEPLIB DD DSNSYS1.TEST.LOAD,DISPSHR
• //SYSTSPRT DD SYSOUT

11
What does this mean to me?

• Following is the bind error that resulted from
  compiling program SAMPLE4 into CS1021BA (typo
  doesnt exist) instead of into CS1020BA
• DSN SYSTEM(TSN)
  
• DSN
  
• BIND PACKAGE(CS1021BA) MEMBER(SAMPLE4)
  CURRENTDATA(NO) DEGREE(1)
• DSNT235I DSNTBCM2 BIND AUTHORIZATION ERROR
  
• USING PJO AUTHORITY
  
• PACKAGE CCSDTSN.CS1021BA.SAMPLE4.()
  
• PRIVILEGE CREATE IN
  
• DSNT233I UNSUCCESSFUL BIND FOR
  
• PACKAGE CCSDTSN.CS1021BA.SAMPLE4.()
  
• DSN
• Following comment should be in all DB2 programs,
  according to CCSD programming standards
• 
  
• DB2 NOTES PROGRAM IS PACKAGED
  
• IN COLLECTION-ID CSASIBA___ FOR
  PLAN SASIBAT__
• 
  

12
What does this mean to me?

• Results of a successful bind
• (This appears at the very end of your compile
  output)
• SELECT SCHOOLNUM, COUNT()
• BIND PACKAGE(CS2530BA) MEMBER(S2530016)
  CURRENTDATA(NO) DEGREE(1)
• DSNT254I - DSNTBCM2 BIND OPTIONS FOR
  
• PACKAGE CCSDDSN.CS2530BA.S2530016.()
  
• ACTION REPLACE
  
• OWNER SYSAD2
  
• QUALIFIER SYSAD2
  
• VALIDATE BIND
  
• EXPLAIN YES
  
• ISOLATION CS
  
• RELEASE
  
• COPY
  
• DSNT255I - DSNTBCM2 BIND OPTIONS FOR
  
• PACKAGE CCSDDSN.CS2530BA.S2530016.()
  
• SQLERROR NOPACKAGE
  

13
Troubleshooting

• Some results of unsuccessful binds
• IEF453I PJO77 - JOB FAILED - JCL ERROR
• LINE 633 COL 12 MEMBER "S625401 " COULD NOT BE
  INCLUDED
• LINE 638 COL 12 MEMBER "S625502 " COULD NOT BE
  INCLUDED
• LINE 1113 COL 16 STATEMENT REFERENCES COLUMN
  "LASTNAME", WHICH IS NOT
• LINE 1113 COL 16 STATEMENT REFERENCES COLUMN
  "FIRSTNAME", WHICH IS NOT
• JOB06842 HASP165 PJO77 ENDED AT N1 MAXCC8
• PACKAGE(CS1020BA) MEMBER(MY077)
  CURRENTDATA(NO) DEGREE(1)
• 00I BIND SQL ERROR
  
• USING PJO AUTHORITY
  
• PLAN(NOT APPLICABLE)
  
• DBRMMY077
  
• STATEMENT1810
  
• SQLCODE-206
  
• SQLSTATE42703
  
• TOKENSALTNUMXXX
  

14
Troubleshooting

• Result of a table being altered or dropped, and a
  program using it was not rebound
• START OF M6515266
• DATE 02/27/2003
• TIME 141700
• BAD READ ON CALENDAR
• SQLCODE -904
• 00E30305 00000801 CM6515BA.M6515266.1721649E0F4251
  64 DSN
• Use qw 904
• -904 UNSUCCESSFUL EXECUTION CAUSED BY AN
  UNAVAILABLE RESOURCE. REASO
• reason-code, TYPE OF RESOURCE
  resource-type, AND RESOURCE NAME
• resource-name
• Look down to find Resource Type 801
• -------------------------------------------------
  -----------------------
• Table 3. Resource Types
  
• -------------------------------------------------
  -----------------------

15
Helpful Hint

• In SDSF, type KEYS and press enter to set up
  function keys
• Set PF18 to qw
• In SDSF job output, you can place the cursor
  under an error message or SQL code and press F18
  (shift/F6) to see information about the error.
• PF13 . . HELP
• PF14 . . SPLIT
• PF15 . . END
• PF16 . . RETURN
• PF17 . . IFIND
• PF18 . . qw
• PF19 . . UP
• PF20 . . DOWN
• PF21 . . SWAP
• PF22 . . LEFT
• PF23 . . RIGHT
• PF24 . . RETRIEVE

16
Summary

• Collection matters. Use the correct collection
  id.
• Use BIND results at end of compile output to
  troubleshoot.
• Use qw errormsg or qw sqlcode on the TSO command
  line to quickly look up errors
• When you are assigned a new program numbering
  scheme, such as S3025xxx, you will have a new
  plan and new collection id. The plan must be
  granted authority in DB2 before it can be
  executed.