SQL help with clearing out old plans and packages

[Jerry Cramer]

I am attempting to generate FREE PACKAGE statements to get rid of
package from old versions of vendor products. I am refering to
SYSIBM.SYSPACKAGE to select packages generated by the DBRMLIB's of
past releases. The PDSNAMES in the IN clause of the first select
below are the old release DBRM libraries but I wanted to ensure that I
did not FREE any packages that were used in the prior release and were
still used in the current release. Therefore I was using the NOT
EXISTS clause to exclude the PACKAGES from the current release.

I was attempting to use:
SELECT 'FREE PACKAGE '||
STRIP(COLLID)||'.'||STRIP(NAME)||'; --'||PDSNAME
FROM SYSIBM.SYSPACKAGE A
WHERE A.PDSNAME IN (
'OLD1.DBRMLIB',
'OLD2.DBRMLIB',
'OLD3.DBRMLIB')
AND NOT EXISTS
(SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKAGE B
WHERE B.PDSNAME IN (
'current.dbrmlib'));

However when I run this I get zero rows. The problem is that I want it
to count the COLLID/NAME combination as a distinct entry and it
appears that since
the NAME column is the same in the two dbrmlibs it get excluded by the
NOT EXIST SELECT. See the sample of data below.
---------+---------+---------+---------+---------+---------+-------
SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'BPASQLG';
---------+---------+---------+---------+---------+---------+-------
COLLID NAME PDSNAME
---------+---------+---------+---------+---------+---------+-------
RBPAPLAN_SQL BPASQLG DB2.PLAT.PL99D1.POST#1.DIST.DBRMLIB
RBPAP512_SQL BPASQLG DB2.PLAT.P512AE.DBRMLIB

What I want is for the prior release entry RBPAP512_SQL.BPASQLG to be
selected to create a FREE PACKAGE statement and the current release
entry RBPAPLAN_SQL.BPASQLG to be excluded.
ANY suggestions?

[Jerry Cramer]

Well I got this to work by adding some equal checks to the NOT EXIST
SELECT. I changed
AND NOT EXISTS
(SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKAGE B
WHERE B.PDSNAME IN (
'current.dbrmlib'));

changed to
AND NOT EXISTS
(SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKAGE B
WHERE B.PDSNAME IN (
'current.dbrmlib')
AND a.name = b.name
AND A.collid = b.collid);



CR******@US.IBM.COM (Jerry Cramer) wrote in message news:<51**************************@posting.google. com>...

I am attempting to generate FREE PACKAGE statements to get rid of
package from old versions of vendor products. I am refering to
SYSIBM.SYSPACKAGE to select packages generated by the DBRMLIB's of
past releases. The PDSNAMES in the IN clause of the first select
below are the old release DBRM libraries but I wanted to ensure that I
did not FREE any packages that were used in the prior release and were
still used in the current release. Therefore I was using the NOT
EXISTS clause to exclude the PACKAGES from the current release.

I was attempting to use:
SELECT 'FREE PACKAGE '||
STRIP(COLLID)||'.'||STRIP(NAME)||'; --'||PDSNAME
FROM SYSIBM.SYSPACKAGE A
WHERE A.PDSNAME IN (
'OLD1.DBRMLIB',
'OLD2.DBRMLIB',
'OLD3.DBRMLIB')
AND NOT EXISTS
(SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKAGE B
WHERE B.PDSNAME IN (
'current.dbrmlib'));

However when I run this I get zero rows. The problem is that I want it
to count the COLLID/NAME combination as a distinct entry and it
appears that since
the NAME column is the same in the two dbrmlibs it get excluded by the
NOT EXIST SELECT. See the sample of data below.
---------+---------+---------+---------+---------+---------+-------
SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'BPASQLG';
---------+---------+---------+---------+---------+---------+-------
COLLID NAME PDSNAME
---------+---------+---------+---------+---------+---------+-------
RBPAPLAN_SQL BPASQLG DB2.PLAT.PL99D1.POST#1.DIST.DBRMLIB
RBPAP512_SQL BPASQLG DB2.PLAT.P512AE.DBRMLIB

What I want is for the prior release entry RBPAP512_SQL.BPASQLG to be
selected to create a FREE PACKAGE statement and the current release
entry RBPAPLAN_SQL.BPASQLG to be excluded.
ANY suggestions?