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.
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
294053
Modified date:
17 October 2019
UID
swg22000193