Just closed a case with IBM Tech Support.
They arrived at the same conclusion as Andreas: OK to force drop the sbspace.
I ran 'onspaces -d <sbspace> -f'.
The utility appeared to run, but after it warned that it was "waiting for all transactions to complete before dropping sbspace", nothing more appeared to happen. No further response. I finally interrupted it with CTRL-C, and got back to the command prompt.
Yet, the space was dropped. There was an entry in online.log to that effect. And, if I tried to run the onspaces command again, I got an error message that the sbspace didn't exist.
So, sbspace was successfully dropped.
Original Message:
Sent: Tue April 04, 2023 05:28 PM
From: David Grove
Subject: Drop sbspace containing a referenceless object?
Thank you, again, for your time and helpful info, Andreas.
It seemed to me that it should be OK to force drop. But (paranoia) I did open a case with IBM, and they are working on it. I'll update later with their conclusion (which I am expecting to be the same as yours).
DG
------------------------------
David Grove
Original Message:
Sent: Tue April 04, 2023 04:11 PM
From: Andreas Legner
Subject: Drop sbspace containing a referenceless object?
No tabid/colno info in this LO header (2 times 4 bytes at offset 96, both -1), so the LO probably never was associated with a table and rather served as an intermediary storage for some purpose, e.g. ER grouper paging (storging a large transaction to disk while it's being assembled in the grouper).
I remember problems that such large LOs could be left behind unattended, so essentially forgotten, with ref_cnt 1, so would never be cleaned out either.
I'd assume nothing really needs this LO any more and it should be safe to force-drop this sbspace.
------------------------------
Andreas Legner
Original Message:
Sent: Tue April 04, 2023 12:35 PM
From: David Grove
Subject: Drop sbspace containing a referenceless object?
Thank you, Andreas.
I do recall that we did some experimenting with ER. I would have bet my right arm that it was 2008, give or take a year. But, 2010 is not out of the realm of possibility.
But, how could the ref count get out of sync? Now that I think about it... could it be this: The databases in this instance are occasionally replaced. The replacement happens by dropping an existing database, and then using dbimport to replace it with a "fresh" version that has been dbexported from production. (Well, actually from an RSS copy of production because we can't afford to have production locked.) So, if an ER version of database is replaced by a non-ER version, maybe the reference(s) would be lost, but the sbspace wouldn't "know it". I don't really know. Just trying to invent a scenario to explain the ref count being 1, yet no discernable references within the database (or any database in the instance).
Anyway, here is the output of 'oncheck -pp 0x300005 0x54444':
informix@ifmx-test-jnu>oncheck -pp 0x300005 0x4444
addr stamp chksum nslots flag type frptr frcnt next prev
3:1001415 -633080199 6bf1 4 881 DATA 480 1548 0 0
slot ptr len flg
1 24 456 0
slot 1:
0: 53 42 48 45 0 0 0 4 0 0 0 1 0 0 0 3 SBHE............
16: 0 0 0 3 0 1 10 fc 4b a1 af cf 0 0 0 0 .......|K!/O....
32: 0 1 0 67 0 0 0 ca 0 0 0 22 0 0 12 20 ...g...J..."...
48: 0 0 0 0 0 0 0 0 ff ff ff ff 0 0 0 0 ................
64: 0 0 0 1 0 0 0 0 4b c5 1 e7 4b c5 2 23 ........KE.gKE.#
80: 0 0 0 0 4b c5 2 56 4b c5 2 56 0 0 0 1 ....KE.VKE.V....
96: ff ff ff ff ff ff ff ff 0 0 0 b2 0 0 2 8 ...........2....
112: 1 23 90 0 0 0 0 0 0 1 0 0 0 0 0 0 .#..............
128: 0 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 ................
144: ff ff 0 0 0 0 0 1 0 0 0 0 ff ff 0 0 ................
160: 0 0 0 8 0 0 0 1 1 23 90 0 0 0 0 0 .........#......
176: 0 1 0 27 0 0 ff ff ff ff 0 0 cf 41 40 46 ...'........OA@F
192: ff ff 0 0 ff ff ff ff ff ff ff ff 0 0 0 0 ................
208: ff ff ff ff ff ff ff ff ff ff ff ff cf 41 40 46 ............OA@F
224: ff ff ff ff 0 0 0 0 0 0 0 c8 0 44 5b 2 ...........H.D[.
240: 0 0 0 34 0 0 25 e 53 42 48 4d 0 44 5b 2 ...4..%.SBHM.D[.
256: 0 0 0 3 0 1d 31 94 0 0 0 0 0 0 0 0 ......1.........
272: 0 1 0 0 0 0 0 3 0 1d 40 7c 0 0 e a7 ..........@|...'
288: 0 0 0 0 0 1 0 0 0 0 0 3 0 1d 23 cd ..............#M
304: 0 0 1f 1f 0 0 0 0 0 1 0 0 0 0 0 3 ................
320: 0 1d b 4f 0 0 1f 3e 0 0 0 0 0 1 0 0 ...O...>........
336: 0 0 0 3 0 1c f1 bc 0 0 1f 5d 0 0 0 0 ......q<...]....
352: 0 1 0 0 0 0 0 3 0 1c d6 5b 0 0 1f 7c ..........V[...|
368: 0 0 0 0 0 1 0 0 0 0 0 3 0 1c 9e b9 ...............9
384: 0 0 1f 9b 0 0 0 0 0 1 0 0 0 0 0 3 ................
400: 0 1c 81 c8 0 0 1f ba 0 0 0 0 0 1 0 0 ...H...:........
416: 0 0 0 3 0 1c 65 79 0 0 1f d9 0 0 0 0 ......ey...Y....
432: 0 1 0 0 0 0 0 3 0 1c 49 13 0 0 1f f8 ..........I....x
448: 0 0 0 0 0 1 0 0 ................
informix@ifmx-test-jnu>
------------------------------
David Grove
Original Message:
Sent: Tue April 04, 2023 05:35 AM
From: Andreas Legner
Subject: Drop sbspace containing a referenceless object?
The beast seems to date back to 2010 and is 19MB large.
Any chance this system had ER (Enterprise Replication) running at that time? In which case it could be a remnant from e.g. ER grouper paging...
Could you provide the following output, maybe a tabid could be extracted should the thing ever have been associated with an actual table:
oncheck -pp 0x300005 0x4444
BR,
Andreas
------------------------------
Andreas Legner
Original Message:
Sent: Mon April 03, 2023 01:23 PM
From: David Grove
Subject: Drop sbspace containing a referenceless object?
P.S. Below is further evidence that there is exactly one large object in the sbspace. It has a reference count of "1", but, using Milan's queries (referenced above), I find no row in any table in any database in the entire instance that references the object.
informix@ifmx-test-jnu>oncheck -cS blob_sbs
Validating space 'blob_sbs' ...
sbspace Metadata Partition Partnum Used Free
blob_sbs:'informix'.TBLSpace 0x300001 18 32
blob_sbs:'informix'.sbspace_desc 0x300002 2 2
blob_sbs:'informix'.chunk_adjunc 0x300003 8 0
blob_sbs:'informix'.LO_ud_free 0x300004 18 5418
blob_sbs:'informix'.LO_hdr_partn 0x300005 174586 0
blob_sbs:'informix'.LO_ud_free 0x300006 21 5415
blob_sbs:'informix'.LO_hdr_partn 0x300007 174591 0
blob_sbs:'informix'.LO_ud_free 0x300008 22 10845
blob_sbs:'informix'.LO_hdr_partn 0x300009 188340 0
blob_sbs:'informix'.LO_ud_free 0x30000a 27 32573
blob_sbs:'informix'.LO_hdr_partn 0x30000b 199400 157283
blob_sbs:'informix'.LO_ud_free 0x30000c 5 9063
blob_sbs:'informix'.LO_hdr_partn 0x30000d 195224 0
blob_sbs:'informix'.LO_ud_free 0x30000e 3 2588
blob_sbs:'informix'.LO_hdr_partn 0x30000f 151790 0
blob_sbs:'informix'.LO_ud_free 0x300010 7 13576
blob_sbs:'informix'.LO_hdr_partn 0x300011 220529 0
Large Objects
ID Ref Size Allocced Creat Last
Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified
---- ---- ----- ---- ---------- -------- ----- ----- ------------------------
3 3 69884 1 19107840 9486 51 N-N-H Tue Apr 13 15:44:39 2010
informix@ifmx-test-jnu>
------------------------------
David Grove
Original Message:
Sent: Fri March 31, 2023 08:23 PM
From: David Grove
Subject: Drop sbspace containing a referenceless object?
Informix 12.10.FC14
Solaris 10 1/13
I have a database with an sbspace, and the output from 'oncheck' for that sbspace ( 'oncheck -pe blob_sbs | grep "SBLOBSpace LO" ') is as follows:
informix@ifmx-test-jnu>oncheck -pe blob_sbs|grep "SBLOBSpace LO"
SBLOBSpace LO [3,3,69884] 1550695 31
SBLOBSpace LO [3,3,69884] 1559251 31
SBLOBSpace LO [3,3,69884] 1566494 31
SBLOBSpace LO [3,3,69884] 1573796 31
SBLOBSpace LO [3,3,69884] 1581277 31
SBLOBSpace LO [3,3,69884] 1589343 31
SBLOBSpace LO [3,3,69884] 1597364 31
SBLOBSpace LO [3,3,69884] 1605289 31
SBLOBSpace LO [3,3,69884] 1612746 31
SBLOBSpace LO [3,3,69884] 1620146 31
SBLOBSpace LO [3,3,69884] 1627914 31
SBLOBSpace LO [3,3,69884] 1635603 31
SBLOBSpace LO [3,3,69884] 1642798 31
SBLOBSpace LO [3,3,69884] 1650055 31
SBLOBSpace LO [3,3,69884] 1657111 31
SBLOBSpace LO [3,3,69884] 1664584 31
SBLOBSpace LO [3,3,69884] 1672056 31
SBLOBSpace LO [3,3,69884] 1680081 31
SBLOBSpace LO [3,3,69884] 1687679 31
SBLOBSpace LO [3,3,69884] 1694817 31
SBLOBSpace LO [3,3,69884] 1702291 31
SBLOBSpace LO [3,3,69884] 1709738 31
SBLOBSpace LO [3,3,69884] 1717421 31
SBLOBSpace LO [3,3,69884] 1724716 31
SBLOBSpace LO [3,3,69884] 1731775 31
SBLOBSpace LO [3,3,69884] 1739022 31
SBLOBSpace LO [3,3,69884] 1746005 31
SBLOBSpace LO [3,3,69884] 1753479 31
SBLOBSpace LO [3,3,69884] 1760724 31
SBLOBSpace LO [3,3,69884] 1768147 31
SBLOBSpace LO [3,3,69884] 1775158 31
SBLOBSpace LO [3,3,69884] 1782797 31
SBLOBSpace LO [3,3,69884] 1790459 31
SBLOBSpace LO [3,3,69884] 1798073 31
SBLOBSpace LO [3,3,69884] 1805597 31
SBLOBSpace LO [3,3,69884] 1812984 31
SBLOBSpace LO [3,3,69884] 1819790 31
SBLOBSpace LO [3,3,69884] 1825907 31
SBLOBSpace LO [3,3,69884] 1832927 31
SBLOBSpace LO [3,3,69884] 1840290 31
SBLOBSpace LO [3,3,69884] 1846872 31
SBLOBSpace LO [3,3,69884] 1853715 31
SBLOBSpace LO [3,3,69884] 1860985 31
SBLOBSpace LO [3,3,69884] 1868232 31
SBLOBSpace LO [3,3,69884] 1875641 31
SBLOBSpace LO [3,3,69884] 1889883 31
SBLOBSpace LO [3,3,69884] 1896892 31
SBLOBSpace LO [3,3,69884] 1903439 31
SBLOBSpace LO [3,3,69884] 1909709 31
SBLOBSpace LO [3,3,69884] 1913236 3751
SBLOBSpace LO [3,3,69884] 1917052 4216
informix@ifmx-test-jnu>
I interpret this to mean that there is only one object [3, 3, 69884] in the sbspace.
I used Milan's SQL, as presented in #8 of this thread:
https://community.ibm.com/community/user/datamanagement/discussion/is-there-a-way-to-check-the-sbspace-usage-by-table
to determine which tables, in which databases, referenced this object. The result showed me that there is no table in any database in the entire instance that references this object.
May I safely use 'onspaces -f' to drop this sbspace? I'm thinking that if zero tables refer to this object, AND there are no other objects in the sbspace, it must be OK to force a drop. I'm thinking that, somehow, the reference count for this object became "out of sync".
Am I understanding 'oncheck' output properly?
In paranoia,
DG
P.S. (Edit) Disregard the proposed explanation. If a copy of a non-ER version of the database were dbimported, it would include everything from the database being copied, and that would all be consistent. So, I cannot think of an explanation.
------------------------------
David Grove
------------------------------