Useful DB2 SPUFI examples for OAM Object Support

[Ed_Arnold]

Some pretty interesting queries at here that you might want to try to see if OAM thinks things are okay.

 https://www-304.ibm.com/support/docview.wss?uid=isg3S1002070

Examples of:

• To find "orphaned" objects on DB2 DASD (Objects that have an Object Directory Table Entry, but no corresponding row(s) in either the 4K or 32K tables)
  
• To delete any "orphaned" Object Directory Table rows for either the 32K table or 4K table
• To find Objects where the Object Directory entry shows "Tape", but data is still on DB2 DASD ("should have been deleted")

If anybody tries these and hits an error, would you share your actions you took to correct the situation?

Ed

[J9CMOD]

Hi Ed,
I know this post is "old" but I was anxious to read all the Tips & Tricks and your areas of the ODUG, and the link here no longer works.  If it's still valid, can you post the new link, or what I can search on to find it?
Thanks!
Janine

[Ed_Arnold]

J9 - right you are....I see that that technote has been archived.  You might want to check with OAM support before running any of these just in case things have changed in the newer releases.

Anyway, here's the technote:


To find "orphaned" objects on DB2 DASD (Objects that have an Object Directory Table Entry, but no corresponding row(s) in either the 4K or 32K tables):

For the 32K table:

SELECT * FROM GROUP03.OSM_OBJ_DIR A
WHERE A.ODSIZE > 3980
AND A.ODLOCFL = 'D'
AND NOT EXISTS  (SELECT * FROM GROUP03.OSM_32K_OBJ_TBL B
                 WHERE A.ODCLID = B.OTCLID AND
                       A.ODNAME = B.OTNAME);

For the 4K table:

SELECT * FROM GROUP03.OSM_OBJ_DIR A
WHERE A.ODSIZE <= 3980
AND A.ODLOCFL = 'D'
AND NOT EXISTS  (SELECT * FROM GROUP03.OSM_04K_OBJ_TBL B
                 WHERE A.ODCLID = B.OTCLID AND
                       A.ODNAME = B.OTNAME);

To delete any "orphaned" Object Directory Table rows:

For the 32K table:

DELETE FROM GROUP03.OSM_OBJ_DIR A
WHERE A.ODSIZE > 3980
AND A.ODLOCFL = 'D'
AND NOT EXISTS (SELECT * FROM GROUP03.OSM_32K_OBJ_TBL B
                 WHERE A.ODCLID = B.OTCLID AND
                       A.ODNAME = B.OTNAME);

For the 4K table:

DELETE FROM GROUP03.OSM_OBJ_DIR A
WHERE A.ODSIZE <= 3980
AND A.ODLOCFL = 'D'
AND NOT EXISTS (SELECT * FROM GROUP03.OSM_04K_OBJ_TBL B
                 WHERE A.ODCLID = B.OTCLID AND
                       A.ODNAME = B.OTNAME);

To find Objects where the Object Directory entry shows "Tape", but data is still on DB2 DASD ("should have been deleted" 32K table entries):

SELECT * FROM GROUP03.OSM_OBJ_DIR A
WHERE A.ODSIZE > 3980
AND A.ODLOCFL = 'T'
AND EXISTS (SELECT * FROM GROUP03.OSM_32K_OBJ_TBL B
                 WHERE A.ODCLID = B.OTCLID AND
                       A.ODNAME = B.OTNAME);

To find Objects where the Object Directory entry shows "Tape", but data is still on DB2 DASD ("should have been deleted" 4K table entries):


SELECT * FROM GROUP03.OSM_OBJ_DIR A
WHERE A.ODSIZE <= 3980
AND A.ODLOCFL = 'T'
AND EXISTS  (SELECT * FROM GROUP03.OSM_04K_OBJ_TBL B
                 WHERE A.ODCLID = B.OTCLID AND
                       A.ODNAME = B.OTNAME);

To find unique collection IDs used by a particular Storage Group:

SELECT DISTINCT ODCLID FROM GROUP03.V_OSM_OBJ_DIR;

To Update a row in the Object Directory table:

UPDATE GROUP03.OSM_OBJ_DIR
     SET ODBKLOC='TMD009' WHERE ODNAME='GROUP03.OBJA' AND ODCLID=1;

Ed