Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Location
    United States
    Posts
    22

    Unanswered: Z/os db2 binds - when no sql changed?

    I have heard/read, that if one has a COBOL program with embedded SQL, that the only time one needs to re-bind the associated DB2 package is when the SQL code was changed. This is because the consistency token used in the DB2 catalog is based on the DBRM and not on the LOAD. Therefore if one is merely modifying the working storage or changing a MOVE statement, then after they recompile the changed program, they do not have to re-bind the DB2 package. Is that true?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I used to know but forgot... When you precompile, it creates a DBRM which has a timestamp. This timestamp has to match some other timestamp (program?). When you make a change to your program (with no sql changes) and precompile it again, does it create a new DBRM? If it does, then I think you'll get a timestamp mistmatch if you don't rebind.

    I'll try checking with mainframe people unless someone else here knows for sure.

  3. #3
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    What happens when you try it?

    First of all, when the first SQL statement of a module is executed, DB2 attemps to find a DB2 package listed in the plan's package list with a consistency token that matches the consistency token stored in the load module. If DB2 cannot find a package with a matching consistency token, DB2 returns the -805 SQLCODE. The consistency token is generated during the precompile and embedded in the DBRM and subsequently the package, and it it also embedded in the modified source and subsequently the load module. In theory what you say is true, but it is a very bad idea. You would need to capture the 'modified' source generated by the precompile and update the modified source prior to the compile. The only reason I can think of to do this would be fear of the bind changing the access path. That is not a reasonable justification.

  4. #4
    Join Date
    Feb 2005
    Location
    United States
    Posts
    22
    Thank you everyone for your replies.
    I decided to bite the bullet and run a test where I made a non-SQL change, and re-compiled without re-binding the package. As feared, I blew up on a -805 SQL error. Upon further examination, it would appear that our compiler will create a new DBRM regardless of whether or not one changed the SQL. Since the new DBRM has a new consistency-token, therefore one would have to re-bind the package to ensure that its CONTOKEN value is in synch. I then did some research and discovered that there are vendor products out there that will intercept the module at compile time and if no SQL changes took place, the product will (I presume) prevent unnecessary DBRM's from being created which in turn will prevent unnecessary rebinds from having to take place. But we do not have such a product. Therefore, if someone discovers that at their shop, a new DBRM is not created when no SQL has changed, it could be that they have such an add-on product installed. thanks again for everyone's assistance.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2dcs View Post
    Thank you everyone for your replies.
    I decided to bite the bullet and run a test where I made a non-SQL change, and re-compiled without re-binding the package. As feared, I blew up on a -805 SQL error. Upon further examination, it would appear that our compiler will create a new DBRM regardless of whether or not one changed the SQL. Since the new DBRM has a new consistency-token, therefore one would have to re-bind the package to ensure that its CONTOKEN value is in synch. I then did some research and discovered that there are vendor products out there that will intercept the module at compile time and if no SQL changes took place, the product will (I presume) prevent unnecessary DBRM's from being created which in turn will prevent unnecessary rebinds from having to take place. But we do not have such a product. Therefore, if someone discovers that at their shop, a new DBRM is not created when no SQL has changed, it could be that they have such an add-on product installed. thanks again for everyone's assistance.
    Why would you even want to do that, even if you could? You are just introducing the possibility of human error into the process by taking someone's word that their change does not affect the package?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Rebinding the package is sufficient, the plan doesn't need to be rebound. Right?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    Rebinding the package is sufficient, the plan doesn't need to be rebound. Right?
    If one is using packages (instead of plans only), then only package needs to be rebound.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    What if one is using packages and plans?

    We had: DBRM -> package -> plan

    One DBRM was bound into one package. Then, several packages were bound into a plan (or something similar).

    As mentioned above, the consistency token is stored in the DBRM, package (and program/load module). I think this token is not stored in the plan if packages are used, not sure?

    db2dcs has a package and a plan, I suppose. When a DBRM gets re-created during precompile, the package has to be rebound so that the consitency tokens are in sync. But if the consistency token is not stored in the plan, then it doesn't need to be rebound? Or this is wrong?

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    What if one is using packages and plans?

    We had: DBRM -> package -> plan

    One DBRM was bound into one package. Then, several packages were bound into a plan (or something similar).

    As mentioned above, the consistency token is stored in the DBRM, package (and program/load module). I think this token is not stored in the plan if packages are used, not sure?

    db2dcs has a package and a plan, I suppose. When a DBRM gets re-created during precompile, the package has to be rebound so that the consitency tokens are in sync. But if the consistency token is not stored in the plan, then it doesn't need to be rebound? Or this is wrong?
    In DB2 z/OS packages (if used) are part of a plan, but only the package needs to be rebound when program is recompiled.

    When using plans only (no associated packages), then I beleive the error is -818 (timestamp conflict error), but when using packages (which are part of a plan typically containing multiple packages) then the error is -805 (no package with matching consistancy token found).

    I am a little rusty on z/OS and have not used the latest releases, but this is how it used to work.

    Prior to DB2 3.1 on MVS, there were no packages (only plans), and all CICS programs that were invoked with XCTL to go to another CICS transaction had to be in the same DB2 plan, which meant all programs in a CICS application had be bound together in the same plan, which was a nightmare (unless START TRANSID is used to go to new CICS transaction, which is very inefficient). So packages introduced in 3.1 solved this problem. For TSO Batch programs, many people still use plans without packages.

    DB2 LUW packages are different, and really more like DB2 z/OS plans, since each one stands alone. LUW still uses the plan terminolgy in some cases like "EXPLAIN PLAN ...", even though there is no such thing as a plan in LUW.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A View Post
    In DB2 z/OS packages (if used) are part of a plan, but only the package needs to be rebound when program is recompiled.

    When using plans only (no associated packages), then I beleive the error is -818 (timestamp conflict error), but when using packages (which are part of a plan typically containing multiple packages) then the error is -805 (no package with matching consistency token found).
    Up to DB2 9 for z/OS, both plans and packages can have a DBRM (and hence a contoken):
    - if a main program contains SQL, it will have a DBRM with SQL in it and hence a "real" (non-empty) plan.
    - that plan could refer to one or more packages, viz. if that main program calls one or more subprograms with DBRMs.
    - a main program without SQL still needs a plan, but without a contoken (since no DBRM).

    From DB2 10 on, plans are gradually disappearing (and replaced by packages if necessary) so that DB2 for z/OS will look more alike to DB2 for LUW.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2dcs View Post
    ... I made a non-SQL change, and re-compiled without re-binding the package. As feared, I blew up on a -805 SQL error.
    When you say "re-compile", you actually both precompiled and compiled.
    It's the precompiler which generates consistency tokens. And which produces a DBRM.

    Try re-compiling the previously stored (and then modified) output of the precompiler. (Which is not so easy anymore nowadays since the precompiler has been built into the compiler...)

    I'm not sure whether this is what the mentioned vendor products do, or if instead they just replace (after "normal" precompile/compile) the contoken in the compiled object module by the original one. I would guess the latter.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thank you, everyone.

Posting Permissions

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