Informix

 View Only
Expand all | Collapse all

In-table Simple Large Objects

  • 1.  In-table Simple Large Objects

    IBM Champion
    Posted Thu May 05, 2022 08:17 PM
    Informix 12.10.FC14
    Solaris 10  1/13

    I have in-table simple large objects (both TEXT and BYTE).  I want to move them to a distinct blob space.
    I started with a small table that contains only a few TEXT objects.  Here is excerpt from oncheck -pT:


    TBLspace Report for doc:wmoriart.cmpct_invst

    Physical Address 1:3324768
    Creation date 05/19/2010 14:34:03
    TBLspace Flags c01 Page Locking
    TBLspace contains TBLspace BLOBs
    .
    .
    .
    Type                   Pages           Empty         Semi-Full           Full            Very-Full
    ----------------     ----------         ----------         ----------         ----------         ----------
    Free                          5
    Bit-Map                      1
    Index                          0
    Data (Home)            10
    TBLspace BLOBs    16                0                     1                  7                      8
                               ----------
    Total Pages              32



    I ran an 'ALTER TABLE <table> MODIFY(<column> TEXT IN<blobspace>);' command.
    It seems to have worked.  The blob space was newly created and empty. After the ALTER command, 'oncheck -pe' shows it is no longer empty (although the number of pages doesn't match the number of BLOB pages that oncheck -pT showed before I did the ALTER.

    Also, oncheck -pT for the source table now shows no entry for "TBLspace BLOBs" (previously was 16, see above).  But, the heading information  still reports:

    "TBLspace contains TBLspace BLOBs"

    Why is this?  I think all the TEXT objects formerly in the same dbspace as the table are now in a new blob space.  If they are not, I would also ask "why", and what do I need to do to move them to the blob space.

    Thank you.

    DG

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

    #Informix


  • 2.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Thu May 05, 2022 08:34 PM
    Try running the REPACK SHRINK API function.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Fri May 06, 2022 01:49 PM
    Thank you, Art.

    As user Informix, I executed: "EXECUTE FUNCTION task("table repack shrink", "cmpct_invst", "doc");"
    It reported: "Succeeded: table repack shrink doc:wmoriart.cmpct_invst"

    I reran: "oncheck -pT doc:cmpct_invst|more"
    I observed no change from the previous execution of oncheck.  The line "TBLspace BLOBs    16 . . ." was still omitted, indicating to me that there are no BLOBs.  But, the heading continues to report: "TBLspace contains BLOBspace BLOBs".

    I want to believe that there are zero BLOBs, yet oncheck reports in-partition BLOBs are present.

    Is there a way to explain the apparent discrepancy and/or to prove whichever case is true (either in-table BLOBs are present, or they are not)?

    Thank you.

    DG


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



  • 4.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Fri May 06, 2022 03:40 PM
    Well, then there is only one other possibility. Altering a TEXT or BYTE column that resides IN TABLE to reside in a blobspace is an in-place alter. If so, updating every row or running the "table update_ipa" API function should resolve the issue. 

    I didn't go there initially because I moved a client's TEXT and BYTE columns from blobspace to IN TABLE to support replication on their server and I don't remember that it was an in-place alter going in that direction.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Fri May 06, 2022 04:05 PM
    Thank you once again, Art, for the suggestion.

    I executed: "UPDATE cmpct_invst SET ofndr_num = ofndr_num WHERE 1 = 1;".

    ("cmpct_invst" is the table name.  "ofndr_num" is just an INT column in the table.  Figured maybe any column might trigger the resolution of the IPA for that row.)

    Then, having second thoughts, just to be sure, I executed: "UPDATE cmpct_invst SET cmpct_invst_smry = cmpct_invst_smry WHERE 1 = 1;".

    ("cmpct_invst_smry" is the TEXT column.)

    Then I ran 'oncheck -pT'.  Exactly the same result.  The "TBLspace BLOBs    16... " line continuess to be omitted, but the header still includes "TBLspace contains TBLspace BLOBs".

    Could this be a bug, or am I just not understanding correctly how Informix works with SImple Large Objects?

    I wonder if it could be related to Tom Girsch's thread Repack/Shrink With In-Table BYTE Columns ?

    DG

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



  • 6.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Fri May 06, 2022 04:10 PM
    Another thought... I'm thinking (perhaps erroneously) that if my ALTER statement attempt to move the Simple Large Objects were actually effected by means of IPAs, then I would not expect 'oncheck -pe' show that the (newly created and empty) blobspace now contains data from the table that I just ALTERED... right?

    DG

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



  • 7.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Fri May 06, 2022 04:13 PM
    I truly appreciate your time, Art.  You are very generous with that commodity.

    But, if you don't have any more "low-cost" ideas, maybe it's time for me to open a tech support case.

    :)

    DG

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



  • 8.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Fri May 06, 2022 04:15 PM
    Now that you mention it, it does indeed sound exactly like Tom's issue.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 9.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Tue May 10, 2022 07:07 AM
    Tried this, not seeing same behavior, i.e. "TBLspace contains TBLspace BLOBs" neatly changing into "TBLspace contains BLOBspace BLOBs" (and back again when running the reverse alter).

    Didn't you mention "both TEXT and BYTE", so two blob columns, or was that for your production table only?

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



  • 10.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Tue May 10, 2022 07:16 AM
    Btw.: such alter is a slow alter, so your entire table will be recreated and re-populated.
    You might want to drop all indices/constraints first, and you might want to change the table into type raw before the actual blob alter (and back to standard afterwards), so a lot of logical logging, incl. risk of LTX, would be avoided and the actual alter would run a lot faster.

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



  • 11.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Wed May 11, 2022 01:26 PM
    Hi Andreas,

    And, thank you for the suggestion to avoid the unnecessary logging of all that traffic.

    DG

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



  • 12.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Wed May 11, 2022 07:28 PM
    Good ideas.

    I'm curious... Other than, "it's just the way it is" or "it just wasn't considered", might there be some intentional, design-based reason why the ALTER TABLE command, when modifying the location of BLOBs, doesn't either:

    1) automatically do COMMITs
    or
    2) have a parameter to specify COMMITs every N rows

    to preclude long transactions?

    DG

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



  • 13.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Wed May 11, 2022 01:25 PM
    Hello Andreas,

    MY SINCERE APOLOGIES TO YOU and ART, and whoever else may have wasted their valuable time responding to this thread, which I initiated in total error.

    Turns out that Andreas had the exact answer that there was no problem.  Even I, in my earlier post #3 above, had copied and pasted the answer directly into the thread.

    I was simply pereiving "TBLspace contains BLOBspace BLOBs" as "TBLspace contains TBLspace BLOBs".

    IOW, 'oncheck -pT' was reporting the correct results, and I was just not seeing it.  I must have seen "TBLspace contains ... BLOBS" and just not noticed the change from "TBLspace" to "BLOBspace".

    I am completely embarrassed and regret the waste of time.

    Thank you for your help.

    DG


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



  • 14.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Wed May 11, 2022 01:27 PM
    Forgiven.  B^)

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 15.  RE: In-table Simple Large Objects

    IBM Champion
    Posted Thu May 12, 2022 03:07 AM
    Absolutely, had been playing with blobspace blobs anyway, so hardly any exta effort ;-)

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