Re: Are BLOBs logged?

Bill Lubanovic wrote: > > I have a naive and simple question: are the full contents of BLOBs > logged? I've heard contradictory assertions on this, and I want to find > out why our transaction logs are filling up so fast. Bill, I have read the answers by Jay A. and Bill E. and they are literally correct but misleading. Here's the story: 1. Blobs in you tablespace are logged. This means that if you defined the blob column simply as "text" or "byte" and did not specify a blobspace ofr their storage, the blob data is stored on pages in the same tablespace that contains the data of your table. I would guess this is your case because the logs are filling up so fast. 2. Blobs in a blobspace are indirectly logged. What in *&^# does that mean? You have defined the blob column as text (or byte) IN <someblobspace>; the blob data will be stored in a separate special dbspace called a BLOBspace, which the engine parcels out with different allocation rules. When you make a change in such a blob column, the original data remains untouched in its place on the disk. The updated version of the blob data is written to a new page (or pages) elsewhere in the BLOBspace. In the record itself, the locater to that blob data is updated to point to the new location. This update *IS* logged. The old version of the BLOB data is temporarily frozen - nobody can use it! The allocation maps (the have horrid names: free map, free-map-free-page) are updated to indicate that the pages withthe updated blob data are no longer free pages. This change is also logged. More information after this rhetorical argument: Question: Why does the Informix engine keep the old copy of the BLOB? Let it just do the bloody update in place! Answer: Because by keeping the old copy, you can roll back a transaction and restore the old copy of the blob data, unfreezing it by simply rolling back the changes to the free map (allocation) pages. Even after you commit, if you restore from an archive, the archive process can still roll back the blob data to its previous value (should you decide not to restore the latest log tapes). Effectively, the old-frozen/new BLOB pages are an extension of the logical log, except that the BLOB data is not physically in the logical log itself. This is the reason for the wierd algorithm. Back to the raw info: When you run a log backup, the ontape (or onarchive) is intelligent enough to notice that the log record references blob-space free-maps and locaters. The backup program will then copy the BLOB data - both the frozen image and the new image - as the before/after information in the UPDATE log record. Question: Waitaminit! Are you telling us that the old blob data hangs around forever?! That's a security risk as well as a waste of space! Answer: I *did* say "temporarily" frozen. As we all know, eventually a log file is freed up for re-use as we cycle around the limited pool of log files. I also explained that the frozen and updated blob pages are extensions of the update record in the logical log. Let's combine these two factoids: When a log file is freed up for re-use, all of its extensions in the blob space are also freed. This means the frozen blob pages are now officially unfrozen, since the log file that referenced them is now free and the log records that pointed to the frozen pages are now gone. Bottom line: Yes, ALL BLOB DATA IS LOGGED! This whole shebang has been extremely well thought out. Anyone who believes that Informix does not log BLOB data is under the influence of the Ellison's Gate cult and needs to be kept away from applesauce until reprogrammed! >;) If this has answered the question conclusively, will someone with the capability (at iiug?) please include this in the FAQ? I have been unable to find any reference to BLOBs in the FAQ, though I have only clicked on the more likely headings. > Opinions expressed herein are my own and may not represent those of my employer. Why not? Are you contractually obligated to disagree with your employer? ;-) -- -- Jake (In persuit of undomesticated aquatic avians) +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+