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.SYSPACKA GE 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.SYSPACKA GE A
WHERE A.PDSNAME IN (
'OLD1.DBRMLIB',
'OLD2.DBRMLIB',
'OLD3.DBRMLIB')
AND NOT EXISTS
(SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKA GE B
WHERE B.PDSNAME IN (
'current.dbrmli b'));

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.SYSPACKA GE
WHERE NAME = 'BPASQLG';
---------+---------+---------+---------+---------+---------+-------
COLLID NAME PDSNAME
---------+---------+---------+---------+---------+---------+-------
RBPAPLAN_SQL BPASQLG DB2.PLAT.PL99D1 .POST#1.DIST.DB RMLIB
RBPAP512_SQL BPASQLG DB2.PLAT.P512AE .DBRMLIB

What I want is for the prior release entry RBPAP512_SQL.BP ASQLG to be
selected to create a FREE PACKAGE statement and the current release
entry RBPAPLAN_SQL.BP ASQLG 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.SYSPACKA GE B
WHERE B.PDSNAME IN (
'current.dbrmli b'));

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



CR******@US.IBM .COM (Jerry Cramer) wrote in message news:<51******* *************** ****@posting.go ogle.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.SYSPACKA GE 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.SYSPACKA GE A
WHERE A.PDSNAME IN (
'OLD1.DBRMLIB',
'OLD2.DBRMLIB',
'OLD3.DBRMLIB')
AND NOT EXISTS
(SELECT COLLID, NAME, PDSNAME
FROM SYSIBM.SYSPACKA GE B
WHERE B.PDSNAME IN (
'current.dbrmli b'));

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.SYSPACKA GE
WHERE NAME = 'BPASQLG';
---------+---------+---------+---------+---------+---------+-------
COLLID NAME PDSNAME
---------+---------+---------+---------+---------+---------+-------
RBPAPLAN_SQL BPASQLG DB2.PLAT.PL99D1 .POST#1.DIST.DB RMLIB
RBPAP512_SQL BPASQLG DB2.PLAT.P512AE .DBRMLIB

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