Question & Answer
How do you remove now-obsolete DB2 packages and plans after upgrading to IBM Rational Asset Analyzer (RAA) 22.214.171.124 or RAA for System z 126.96.36.199, or higher?
Starting with Fix Pack 7 (188.8.131.52) for Rational Asset Analyzer 6.1, and Fix Pack 8 (184.108.40.206) for Rational Asset Analyzer for System z 6.1, all metadata database access was modified to use JDBC connections instead of static SQL, thus eliminating the need to BIND or re-BIND the application's executables at installation time, or after executing the RUNSTATS utility. Since the static SQL is no longer necessary for Asset Analyzer, neither are the corresponding DBRM packages and package list plans. Rational Asset Analyzer for System z continues to use package DMHTEP2 and plan &plnprfx.TEP2 (a customized version of DB2's DSNTEP2 for dynamic SQL execution), as well as packages for DB2 triggers. However, all other packages and plans are obsolete for any installation of Rational Asset Analyzer Fix Pack 7 (220.127.116.11) or above, and Rational Asset Analyzer for System z Fix Pack 8 (18.104.22.168) or above.
The following addresses how to report on and remove the obsolete RAA packages and plans in your DB2 system. This activity is optional, and requires proper database authority, for example, DBADM, SYSAD, or SYSCTRL, for the user intending to run the queries and commands.
Refer to the attached zip file (CleanPckg.zip) which contains the files needed to perform these tasks. The zip file includes the following files: dmhDropPackages.bat, dmhDropPackages.rexx, DMHFREE, dmhGetPackages.bat, and GETPKGPL.
Rational Asset Analyzer for System z
Refer to the sample, GETPKGPL, for SQL SELECT queries which can be utilized to query and report on the Rational Asset Analyzer plans and packages. Minor customization is required and the instructions are included in the prologue of the file. You can execute the SQL SELECT queries using SPUFI (SQL Processor Using File Input) or other database interface facility.
Refer to the sample JCL file, DMHFREE, which can be utilized for executing the DB2 FREE PACKAGE and FREE PLAN DSN subcommands. Customization is required and instructions are included in the prologue of the file.
The FREE PACKAGE DSN subcommand deletes packages from DB2. Executing this subcommand deletes corresponding table entries from the SYSIBM.SYSPACKAGE catalog table and drops authorization against each package name.
The FREE PLAN DSN subcommand deletes application plans from DB2. Executing this subcommand deletes corresponding table entries from the SYSIBM.SYSPLAN catalog table and drops all authorization against each application plan name.
1. Upload file GETPKGPL, customize, and execute the SQL queries contained within.
2. Review your query results:
- Results from SYSPACKAGE for > 0 rows is an indication you have packages to be freed.
Results from SYSPLAN for > 0 rows is an indication you have plans to be freed.
If your results indicate 0 rows from both queries, you have no clean up to perform.
- You can update the JCL to remove those FREE PACKAGE and FREE PLAN DSN subcommands for packages and plans that do not appear in your query results. While modifying the list of PACKAGEs and PLANs is optional, if you do not remove these entries, the DMHTEP2 step of the Job will report RC=8 (e.g. package does not exist … unsuccessful free for …, plan does not exist, free for plan … not successful).
4. Submit the DMHFREE JCL, and verify successful completion.
5. Re-run the queries in the GETPKGPL file, and verify your results are 0 rows.
Rational Asset Analyzer
Refer to the dmhGetPackages.bat file for executing a SQL SELECT statement to query and report on the Rational Asset Analyzer packages.
Refer to the dmhDropPackages.bat and dmhDropPackages.rexx files for executing the DB2 DROP PACKAGE commands.
Values for database name, database schema, log directory (logDir) and temp directory (tempDir) are retrieved from your RAA <installationDir>\config\Common.cfg
1. Copy the .rexx and .bat files to your RAA <installationDir>\bin directory.
2. Execute the batch command file for querying the SYSCAT.PACKAGES catalog view by running the dmhGetPackages.bat from a command window. Syntax format:
3. Review your query results:
- Results from PACKAGES for > 0 rows is an indication you have packages to be freed.
If your results indicate 0 rows, you have no clean up to perform.
4. Execute the Batch command file for performing the DROP PACKAGE commands by running the dmhDropPackages.bat from a command window. Syntax format:
5. Review your results by browsing the \log\dmhDropPackages.log.
6. Re-run dmhGetPackages.bat, confirming your result is 0 rows.
7. Clean up RAA work files:
Delete dmhDropPackages.sql from the \temp\ directory.
Delete dmhDropPackages.log from the \log\ directory.
Was this topic helpful?
02 August 2018