Preventing -805 after product release/maintenance

Posted By: Joe Huang Technical Content,

It is possible to experience SQLCODE -805 after a product or application release because a BIND of a DBRM was missed. When this happens, it can take time to identify and do the appropriate BIND.  Reviewing the before and after DBRM libraries can prevent this situation. A large financial institution like our company, several minutes of down time means loss of business and disgruntled customers. That is something we should all do our due diligence to prevent. Although most of the -805 should be detected during the UNIT testing but there are times, we are not able to cover all possible scenarios in lower environments. Therefore, we need a way to identify the changed DBRMs before release/maintenance in the planning stage to prevent the annoying -805.

While I was a Db2 system administrator, I found the documentations provided by the vendors are not always accurate or up to date. To capture all the required BINDs, we must go over a huge pile of HOLDDATA looking for packages that needs a BIND. And occasionally, the HOLDDATA doesn’t cover all the packages. Therefore, to prevent the potential -805, I would run an extra step to BIND ADD all packages to capture the missing BIND.  And as you can imagine, this shotgun approach is not efficient and often draw criticism from my management and peers.

Unfortunately, there is no such vendor product available in the market to identify the changed DBRMs. I was even advised by IBM tech support that this is a great idea, and I should open a public enhancement request. Not sure how long this request will be taken to be developed, I decided to devote some time and effort to develop a home-grown procedure. After many hours of developing and testing, I believe I have a usable procedure to share with the IDUG community. Currently, my procedure is being used by my peers before the maintenance of Db2 or Db2 related software upgrades e.g., Db2 Administration Tool, OMEGAMON for Db2, Db2 Log Analysis Tool etc. 

And here are my steps.  All steps use IBM provided utilities.  All you need to do is to plug in the old and new DBRM datasets.

And here is the output of the comparison, total 7 packages need BIND after new maintenance. 

Output can be found in //SORTOUT


Please cut and paste the JCL and give it a try, I would like to hear your feedback.