Provinzial with DB/IQ Packman

The company

Provinzial Rheinland, an insurance company based in Düsseldorf, Germany, is affiliated to the Financial Group of ‘Sparkassen’ and belongs to the group of public insurers.

Their business regions cover the districts of Cologne, Düsseldorf, Trier and Koblenz, and include approximately 2,500 staff in 760 branch offices. Their products are also marketed by the associated Savings banks. IT-departments at Provinzial Rheinland employ more than 200 staff in Düsseldorf .


The challenge

In earlier DB2 installations the BIND strategy was based upon plans and associated DBRMs. This procedure grew rapidly throughout the DB2 systems. Due to the static linking of all applications, the whole complex was not easy to handle. For this reason, packages were introduced in 2004 and the BIND PLAN was replaced by BIND PACKAGE. The increased number of BINDs forced the DB2 Catalog to grow rapidly. As nearly all BINDs were executed with EXPLAIN=YES, the corresponding PLAN_TABLE grew accordingly, became complicated to utilize and could not be reorganized or cleaned up.

Due to the statically linked applications, there was no way to locate packages still in use by the sub-programs and which packages could be deleted (FREE).

In 2008, a new z9 mainframe was made available for the new ‘agents’ application system, that is now run by the ‘Sparkassen’ IT Services. The Database Management System used is DB2 for z/OS (Version 8), which is about to replace the IMS data bases. So the current problem of unused packages would have increased even more in the future.

As too many packages “live” in the system, diverse DB2 utilities and third-party programs become difficult to use and run ineffectively because of the huge number of packages. Many of which are obsolete.


The solution

Provinzial Rheinland started utilizing DB2 in 1987. In 1997, they successfully introduced DB/IQ QA to assure the quality of all DB2 application programs and SQL written.

At the beginning of 2007, after successful evaluation under project manager Jost Müller, they started utilizing a further product from InSoft: DB/IQ PackMan, which solves the problems of LOADLIB / DBRM and DB2 Package Management ensuring system integrity.

Our targets were:

  1. to free thousands of redundant packages in the DB2 Catalog and reorganize it
  2. to minimize a general commonly used gigantic PLAN_TABLE

By :

  1. Freeing redundant packages and cleaning up the DB2 Catalog

All package versions within the DB2 catalog are checked, as to whether they are found or not found in the appropriate load modules. If ‘not found’, they are marked as candidates to be freed and made readily available for follow-up jobs.

This kind of scanning supports all types of load modules, linked with different host languages, including C and C++. PackMan scans each CSECT of the load modules as well as DLLs, thus enabling all package versions to be properly assigned. Further options are available to generate reports cross-referencing matching load modules and packages or to generate jobs for the automatic creation of back-up packages before initiating the FREE-run.

Even large modules (16 MB or more, because of the static links) were processed very quickly and without too much overhead. “This utility is very useful – 50% of the package versions could be freed.”

  1. Cleaning of PLAN_TABLE(s)

DB/IQ-PackMan includes multiple facilities to reduce the PLAN_TABLEs. One of these facilities incorporates a MISMATCH function, which will delete all PLAN_TABLE entries, found to have no matching package version. Besides the standard PLAN_TABLE, Packman supports all other Explain and Visual Explain related tables.

“This utility is extremely fast and very effective – now it can always be used to minimize the PLAN_TABLEs”

In former times it was necessary to unload the whole PLAN_TABLE. The two most recent versions (of PLANS) were saved and reloaded by a batch program. This was complex and impossible for packages, because even older versions were still required.


Feedback from the client

The most positive experiences made with Insoft were:

  • there is no other comparable product available with the same variety of features and outstanding results
  • the excellent co-operation in the past
  • the fast support for current issues
  • the quick implementation of product improvements suggested during the evaluation

PackMan provides further additional functions, amongst others, to determine empty DBRMs, which can be deleted and mass generation of DBRM members from packages.

As InSoft lined out, all -805 / -818 problems can be avoided when applications are transferred into the production environment using PackMan.

The results are faster as well as a 100% hit ratio compared to the individual in-house developed (help) programs. CPU- and BIND-times have been reduced, there are no DB2 release dependencies. Transparency for packages / load modules / DBRMs is now ensured.

Our decision for yet another InSoft product was the best we could make and we are pleased to count on it when upgrading to DB2 V9.