Informix

 View Only
  • 1.  How to enumerate tables referencing BLOBs in a particular sbspace?

    Posted Thu May 26, 2022 06:32 PM
    Informix 12.10.FC14
    Solaris 10  1/13

    I have an Informix instance that contains several databases.  Only one of these databases contains any BLOBs (smart large objects).  There are 12 tables that contain, in total, over 650,000 BLOBs (mostly photos).

    I wanted to move all the sblobs to a new sbspace.  So, I created an SPL that used a FOREACH loop to ripple through every table that had at least one sblob column, and, for that table, executed an UPDATE command that used LOCOPY() to assign the BLOB column(s) to themselves.

    At first, I thought it worked like a charm.  I used oncheck -pe to count the number of LOs, and there were over 650K.

    Just to be sure, I also used the same oncheck command to count the number of LOs that remained in the original sbspace.

    I was expecting to find 0.  But, there are 76 LOs remaining.  They are all very tiny (3 - 5 pages).

    So, I have a mystery.  Just what are these objects, and what table(s) are referencing them?

    Thus, my question:  How can I determine a list of table(s)/column(s) that are referencing these mysterious LOs?

    IOW, I have some LOs in an sbspace.  How do I know what table(s) they "belong to"?

    Thank you.

    David Grove

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


  • 2.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    InnerCircle
    Posted Fri May 27, 2022 02:53 AM
    Are these remaining 76 LOs even referenced by anyone?
    -> oncheck -cS <sbspace_name>  -> Ref Cnt column > 0 ?

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    Posted Fri May 27, 2022 12:26 PM
    Edited by David Grove Fri May 27, 2022 12:32 PM
    Well, I'll be.

    All 76 of them have Reference Count = 0.

    How can that be?  What does it even mean to have an object stored in an sbspace that is invisible, inaccessible, non-retrievable, whose existence is unknown and unknowable (by any SQL command).

    How can this even happen?  If the reference count to a smart large object becomes zero, shouldn't it be deleted from the sbspace?

    Is there a way to make these zombies go away?

    Thank you for asking the question, and causing me to check that.  It would not have (and did not) occurred to me that there could be objects stored that no table in the database even "knew" about.  I mean, how would they even get there, if not INSERTed into a table?

    Does a tree falling in the wilderness make a sound?

    No doubt I lack full knowledge of the whole LO thing.  Actually, I admit I do have that deficiency.

    :)

    Regards,

    DG


    P.S.  I guess this gets philosophical real fast.  Certainly, it does help to bring in to focus the difference between the concepts of a Primary Key (which is isomorphic to existence, uniqueness, and identity) and an Object ID (which has nothing to do with any of those attributes-- it's just a handle with which to manipulate an object, which may or may not be unique, etc.).

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



  • 4.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    Posted Fri May 27, 2022 02:12 PM
    "Is there a way to make these zombies go away?"

    I should have researched first (before posting that question).

    The answer is: 'onspaces -cl'

    Thank you, again.

    DG

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



  • 5.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    Posted 30 days ago
    Edited by David Grove 30 days ago
    After I got rid of the LOBs with a reference count of 0, I looked some more at the output of 'oncheck -cS <blobspace>'.

    I observed the following three lines in the thousands of lines of that output:

    Large Objects
    ID                                        Ref         Size           Allocced                     Creat           Last
    Sbs#    Chk#       Seq#        Cnt       (Bytes)           Pages        Extns     Flags          Modified
    ----         ----         -----         ----         ----------         --------         -----         -----         ------------------------
    4              4       419173        1                0                  0                0         L-N-H        Wed Jun 10 11:55:55 2020
    4              4       419177        1                0                  0                0         L-N-H        Wed Jun 10 11:55:55 2020
    4              4       419201        1                0                  0                0         L-N-H        Wed Jun 10 11:55:55 2020


    Q1: What does it mean to have a reference count of "1" for an object of size "0 bytes", allocated "0 pages", and with "0 extents"?

    Q2: How can I determine what columns of what tables are referring to these (alleged) objects?

    Thank you for any comments.

    DG

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



  • 6.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    Posted 8 days ago
    Regarding "Q1:" above.  I was curious about it.  Wondered whether there might be a bug in 'oncheck' (reporting a non-reference to nonexistent objects).  Or maybe some mysterious objects exist that I am not aware of and don't understand.  So I opened a low-level (Priority 4) Tech Support case.  They said they didn't know.  It's not important enough to escalate it, so case closed.

    DG

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



  • 7.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    InnerCircle
    Posted 8 days ago
    Hi David,

    try this, with yourdb and yoursbspace:

    $ rm a
    $ touch a
    $ dbaccess mydb <<!
    create table test_sblob (i int, b blob) put b in (mysbspace);
    insert into test_sblob values (1, filetoblob("a", "client"));
    !
    $ oncheck -cS mysbspace

    You're simply looking at a zero length (aka. empty) sblob, as a valid value in a table's sblob column ;-)
    It will, btw., indeed not consume any user space in your sbspace, only one LO header in the LO_hdr_partn metadata partition.

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 8.  RE: How to enumerate tables referencing BLOBs in a particular sbspace?

    Posted 15 days ago
    The Question I originally raised about how to enumerate tables/columns that reference a particular SBLOB would seem to have an answer.

    IBM Tech Support directed me to this thread in the forum:
    https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer/viewthread?GroupId=4147&MessageKey=9560395b-149e-485b-b203-033d203e84fb&CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=digestviewer

    The final post by Milan Refaj has the answer.  He posted a script that generates a query that will display the table, column, and rowid of all tables that reference a particular sblob in a particular sbspace.

    DG

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