Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Drop sbspace containing a referenceless object?

    Posted 03/31/23 08:24 PM

    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



    ------------------------------
    David Grove
    ------------------------------


  • 2.  RE: Drop sbspace containing a referenceless object?

    Posted 04/03/23 01:24 PM

    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
    ------------------------------



  • 3.  RE: Drop sbspace containing a referenceless object?

    Posted 04/04/23 05:35 AM

    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


  • 4.  RE: Drop sbspace containing a referenceless object?

    Posted 04/04/23 12:35 PM
    Edited by David Grove 04/04/23 12:44 PM

    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


  • 5.  RE: Drop sbspace containing a referenceless object?

    Posted 04/04/23 04:11 PM

    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


  • 6.  RE: Drop sbspace containing a referenceless object?

    Posted 04/04/23 05:28 PM

    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


  • 7.  RE: Drop sbspace containing a referenceless object?

    Posted 04/07/23 01:48 PM

    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.

    DG



    ------------------------------
    David Grove
    ------------------------------

    Original Message


Related Content