Informix

 View Only
Expand all | Collapse all

Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

  • 1.  Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

    IBM Champion
    Posted Mon July 31, 2023 03:59 PM

    IDS 12.10.FC14

    Solaris 10  1/13

    I wanted to move all sblobs in a certain sbspace to another sbspace.  I used this IBM tech note to do so: How to move a table's sblobs .  It worked fine.

    Because I''m paranoid, I then used 'oncheck -pS' to confirm that no sblobs remained in the original smspace.  I was surprised to that a single sblob remained.  I examined the oncheck output, and saw that there was an actual sblob (had nonzero size) and had a ref count = 1.

    Of course that made me curios about this sblob.  I wondered what table referenced it.  So, I used Milan Rafaj's query generator (post #8 in the following thread: https://community.ibm.com/community/user/datamanagement/communities/community-home/digestviewer/viewthread?GroupId=4147&MessageKey=9560395b-149e-485b-b203-033d203e84fb&CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=digestviewer )

    The resulting query produced no results.  That is, for every table with sblobs, it reported zero references to the specific sblob that oncheck reported as physically instantiated, and having a ref count = 1.

    I looked further, and noted that it was created in 2019.  Many databases in that instance have come and gone since 2019.

    So, here's the quandary: How can oncheck report a ref count=1 for an instantiated object that is not actually referenced by any table in the database?

    OK, I guess I really don't care "why" (other than intellectual curiosity); the real question is how to get rid of these zombie sblobs.  It's not like I can do a DELETE (there's no table from which to delete).

    DG



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


  • 2.  RE: Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

    IBM Champion
    Posted Tue August 01, 2023 01:47 PM

    Edit: "the real question is how to get rid of this zombie sblob."

    (Not "these zombie sblobs".  There is only one.)



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



  • 3.  RE: Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

    IBM Champion
    Posted Tue August 01, 2023 08:31 PM

    Hi,

    Did you try oncheck -cS ?

    Otherwise you might have to get HCL to dial in and take a look.

    Regards,
    David.



    ------------------------------
    David Williams
    ------------------------------



  • 4.  RE: Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

    IBM Champion
    Posted Wed August 02, 2023 01:49 PM

    Thank you, David.  (Nice name!)

    I did an 'oncheck -cS <sbspace>' and got:

    informix@ifmx-prod-jnu>oncheck -cS |more

    Validating space 'blbsbs' ...

      sbspace Metadata Partition                       Partnum      Used      Free
      blbsbs:'informix'.TBLSpace                       0x400001        6        44
      blbsbs:'informix'.sbspace_desc                   0x400002        2         2
      blbsbs:'informix'.chunk_adjunc                   0x400003        2         2
      blbsbs:'informix'.LO_ud_free                     0x400004      161     42430
      blbsbs:'informix'.LO_hdr_partn                   0x400005   139620     22052

      Large Objects
      ID               Ref Size       Allocced       Creat   Last
      Sbs# Chk#  Seq#  Cnt    (Bytes)    Pages Extns Flags   Modified
      ---- ---- ----- ---- ---------- -------- ----- ----- ------------------------
         4    4 122721    1      16359        9     1 L-N-H Sat Jun  1 01:43:23 2019

    So, an object exists, and it is the only object in that sbspace.  But, no table references it, so I can't do an SQL 'DELETE' command.  (Because there is no table to enter in the 'FROM' clause.)

    Also, I could not find (in the documentation) the meaning of the "Creat Flags".  I was curious because all the other sblobs (they are all in other sbspaces) have a value of "N-N-H" for Creat Flags, instead of "L-N-H", that this zombie object has.

    DG



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



  • 5.  RE: Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

    IBM Champion
    Posted Thu August 03, 2023 03:52 AM

    Hi,

    Thanks!

    What does oncheck -pS give for the sbspace?

    How do the "Create Flags" and "Status Flags" compare to the other sblobs?

    Regards,

    David.



    ------------------------------
    David Williams
    ------------------------------



  • 6.  RE: Smart BLOB 16359 bytes, w/ ref count = 1, yet apparently not used by any table

    IBM Champion
    Posted Thu August 03, 2023 12:49 PM
    Edited by David Grove Thu August 03, 2023 12:51 PM

    Status Flags of all sblobs are the same.

    Create Flags are similarly the same, EXCEPT for logging.  the mystery sblob is "LO_LOG".  All the rest are "LO_NOLOG".  Just in the past few days, I changed them from logging to no logging because I was doing some work unloading and loading a lot of photos, and I didn't want to run out of log space.

    (This is not a production instance.)

    DG


    Edit: P.S.  So, now I guess I know the meaning of the flags in oncheck -cS.  Makes sense, although I still think it would be nice if the flag information were included in the documentation for the 'oncheck -cS' command.


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