In-table Simple Large Objects

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

Try running the REPACK SHRINK API function. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

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

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

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

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

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

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

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

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

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

Hi Andreas, And, thank you for the suggestion to avoid the unnecessary logging of all that traffic. DG ------------------------------ David Grove ------------------------------

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

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

Absolutely, had been playing with blobspace blobs anyway, so hardly any exta effort ;-) ------------------------------ Andreas Legner ------------------------------