I received some comments regarding last week's utility showing how to compare two libraries.

Daniel Ossent wrote in noting that the technique only compared objects with the same name but differing types. For example, if library1 contained object1 with type *PGM and library2 contained object1 with type *PGM and *CMD, the comparison would not detect the missing object1 *CMD object in library 1.

To remedy the situation, perform the following steps (the DSPOBJD is the same as last week's):

a. DSPOBJD OBJ(library1/*ALL) OBJTYPE(*ALL) DETAIL(*BASIC) + OUTPUT(*OUTFILE) OUTFILE(mylib/LIBR1)

b. DSPOBJD OBJ(library2/*ALL) OBJTYPE(*ALL) DETAIL(*BASIC) + OUTPUT(*OUTFILE) OUTFILE(mylib/LIBR2)

c. To view the objects in library1 that don't appear in library2, run the following query:

SELECT * FROM mylib/LIBR1 a WHERE NOT EXISTS(SELECT 1 FROM mylib/LIBR2 b 
WHERE b.odobnm = a.odobnm AND b.odobtp = a.odobtp)

d. To view the objects in library2 that don't appear in library1, use this:

SELECT * FROM mylib/LIBR2 a WHERE NOT EXISTS (SELECT 1 FROM mylib/LIBR1 b 
WHERE b.odobnm = a.odobnm AND b.odobtp = a.odobtp)

Note that you can use other matching criteria in addition to object name and type, such as object size, object owner, source change date, and so on, by adding the corresponding fields in the WHERE clause in the sub-select statement.

Also, Sven Lorenzen offers a technique that uses system table QADBXREF to quickly compare physical and logical files between two libraries:

a. To view the physical/logical files in library1 that don't appear in library2, use the following query:

SELECT DBXFIL LOST, DBXATR FROM qadbxref WHERE DBXLIB = 'LIBRARY1'
       AND dbxfil NOT IN (SELECT dbxfil FROM qadbxref
       WHERE DBXLIB = LIBRARY2')
       ORDER BY DBXFIL desc

b. To view the files in library2 that don't appear in library1, use this:

SELECT DBXFIL LOST, DBXATR FROM qadbxref WHERE DBXLIB = ' LIBRARY2'
       AND dbxfil NOT IN (SELECT dbxfil FROM qadbxref
       WHERE DBXLIB = 'LIBRARY1')
       ORDER BY DBXFIL desc

c. To view the files in both library1 and library2, use this:

SELECT DBXFIL, DBXATR FROM qadbxref WHERE DBXLIB = 'LIBRARY1'
       AND dbxfil IN (SELECT dbxfil FROM qadbxref 
       WHERE DBXLIB = LIBRARY2')
       ORDER BY DBXFIL desc

In addition to Daniel Ossent and Sven Lorenzen, I'd like to also thank Dan Holden and Daniel Ziobron for writing in regarding the above tip.