IBM Support

How to clean up orphaned objects?

Question & Answer


Question

How can we clean up the orphaned objects?

Cause

Customer does a Drop View on a version table versus a Drop Table can caused orphan entries

Answer

The following workaround/catalog to clean up the orphaned objects.

1. Find all the objects which are orphaned:
>>>> nzsqa tblDict -sys | grep TV
Table 221002 (SYSTEM.ADMIN._TV_221017_1) records=-1 fields=2 refs=1
Table 221022 (SYSTEM.ADMIN._TV_221017_2) records=-1 fields=3 refs=1
OR
>>>> select OBJID, OBJNAME from _T_object where objname like '_TV_%';
OBJID | OBJNAME
--------+--------------
221002 | _TV_221017_1
221022 | _TV_221017_2

2. Verify whether entries related to above are really orphaned/stale or not.
SYSTEM.ADMIN(ADMIN)=> select * from _T_object where OBJBASEOID=221017;
OBJID | OBJOWNER | OBJMODIFIER | OBJCLASS | OBJDB | OBJNAME | OBJCREATED | OBJMODIFIED | OBJDELIM | RESTOREDOID | OBJBASEOID | OBJVISIBLEOID | OBJSCHEMAOID | OBJINCVER | OBJINCVERRST
--------+----------+-------------+----------+-------+--------------+---------------------+---------------------+----------+-------------+------------+---------------+--------------+-----------+--------------
221002 | 500 | 500 | 4963 | 1 | _TV_221017_1 | 2016-12-01 10:58:08 | 2016-12-01 10:58:08 | f | 0 | 221017 | 221042 | 6 | 0 | 0
221017 | 500 | 500 | 4962 | 1 | _BTS_221017 | 2016-12-01 10:58:08 | 2016-12-01 10:58:08 | f | 0 | 221017 | 221042 | 6 | 0 | 0
221022 | 500 | 500 | 4963 | 1 | _TV_221017_2 | 2016-12-01 10:58:08 | 2016-12-01 10:58:08 | f | 0 | 221017 | 221042 | 6 | 0 | 0
221026 | 500 | 500 | 4952 | 1 | _BV_221017 | 2016-12-01 10:58:17 | 2016-12-01 10:58:17 | f | 0 | 221017 | 221042 | 6 | 0 | 0
(4 rows)
NOTE: Please see, above query doesn't contain entry related to actual table. This is orphaned object for sure.

3. Delete all related entries from _T_depend where OBJID in ((OBJBASEOID, OBJVISIBLEOID, OBJID of Base view) from _T_object for above idenitified objects).
SYSTEM.ADMIN(ADMIN)=> select * from _T_depend;
CLASSID | OBJID | OBJSUBID | REFCLASSID | REFOBJID | REFOBJSUBID | DEPTYPE | DBID | REFDBID | REFVERSION
---------+--------+----------+------------+----------+-------------+---------+------+---------+------------
4962 | 221017 | 0 | 4963 | 221002 | 0 | n | 1 | 1 | 1
4962 | 221017 | 0 | 4963 | 221022 | 0 | n | 1 | 1 | 2
4952 | 221026 | 0 | 4962 | 221017 | 0 | n | 1 | 1 | 0
4961 | 221042 | 0 | 4952 | 221026 | 0 | n | 1 | 1 | 0
4961 | 221042 | 0 | 4963 | 221022 | 0 | n | 1 | 1 | 0
4961 | 221042 | 0 | 4963 | 221002 | 0 | n | 1 | 1 | 0
4961 | 221042 | 0 | 4962 | 221017 | 0 | n | 1 | 1 | 0
(7 rows)
SYSTEM.ADMIN(ADMIN)=> delete from _T_depend where OBJID in (221017, 221042, 221026);
DELETE 7

4. Drop all dependent objects:
SYSTEM.ADMIN(ADMIN)=> drop view _BV_221017;
DROP VIEW
SYSTEM.ADMIN(ADMIN)=> drop table _TV_221017_2;
DROP TABLE
SYSTEM.ADMIN(ADMIN)=> drop table _TV_221017_1;
DROP TABLE

5. Delete orphaned/stale entry from _T_object for orphaned base table set.
SYSTEM.ADMIN(ADMIN)=> select * from _T_object where OBJBASEOID=221017;
OBJID | OBJOWNER | OBJMODIFIER | OBJCLASS | OBJDB | OBJNAME | OBJCREATED | OBJMODIFIED | OBJDELIM | RESTOREDOID | OBJBASEOID | OBJVISIBLEOID | OBJSCHEMAOID | OBJINCVER | OBJINCVERRST
--------+----------+-------------+----------+-------+--------------+---------------------+---------------------+----------+-------------+------------+---------------+--------------+-----------+--------------
221017 | 500 | 500 | 4962 | 1 | _BTS_221017 | 2016-12-01 10:58:08 | 2016-12-01 10:58:08 | f | 0 | 221017 | 221042 | 6 | 0 | 0
(1 rows)
SYSTEM.ADMIN(ADMIN)=> delete from _T_object where OBJNAME='_BTS_221017';
DELETE 1

SYSTEM.ADMIN(ADMIN)=> select * from _T_object where OBJBASEOID=221017;
OBJID | OBJOWNER | OBJMODIFIER | OBJCLASS | OBJDB | OBJNAME | OBJCREATED | OBJMODIFIED | OBJDELIM | RESTOREDOID | OBJBASEOID | OBJVISIBLEOID | OBJSCHEMAOID | OBJINCVER | OBJINCVERRST
-------+----------+-------------+----------+-------+---------+------------+-------------+----------+-------------+------------+---------------+--------------+-----------+--------------
(0 rows)

6. ****Space reclaimed!****
[nz@vmnps-dw25 nz]$ nz_altered_tables

# Of Versioned Tables 0
Total # Of Versions 0

Database | Schema | Table Name | Size (Bytes) | # Of Versions
----------+--------+------------+--------------+---------------
(0 rows)

Also following commands "Returns nothing".
>>>> nzsqa tblDict -sys | grep TV
>>>> nzsqa extentmap -id 1003 -committed | grep 221002

NOTE: Steps mentioned above needs to be done in exact same order to reclaim space from orphaned objects. Otherwise there are chances of catalog inconsistency.

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"IBM Netezza Analytics","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

More support for:
IBM PureData System

Software version:
1.0.0

Document number:
294053

Modified date:
17 October 2019

UID

swg22000193