Re: Several questions ...

> Hi, community > > Are you having a good day? You probably don't really want to know. > I've several question about Informix: > > 1. Anyone know where I can find some information about #users and #licenses > of Informix database product line(IDS, IDS with AD&XP options) vs. Oracle, > DB2, MS SQL Server database products. In other words, what is the market > position of Informix engine? > > 2. This question is about Informix instance's fast recovery mechanism. I > know for each update operation, the affecting pages' before images are > copied to physical log. But > > a. what is happening when I delete a table? > > Does Informix copy all the pages in that table to physical log as "before > images"? I know this is not possible because when I execute the following > lines: > > begin work; > > delete table xyz;(let's say it has 1 million rows) > > Table is deleted immediately!!! > > run $onstat -d and I see the increased size of dbsapce free space > > rollback work; > > The table is recoverd within seconds!!! So I'm sure it's not a real rollback > row by row from logical log. What is happening then? The DROP TABLE statement is kind of like the MS-DOS delete command, in that neither deletes the data, they just remove the directory (catalog) information that points to the data. They do it for the same reason, too: speed. In the case of Informix, the data and index pages are left alone. The information in systables, syscolumns, sysindexes, sysfragments, and others are deleted. The physical log would have a before-image of each of these entries. Also, the TBLSPACE TBLSPACE page for the affected table and/or detached indexes are cleared, the chunk free list page is updated to reflect the pages freed (which is why your 'onstat -d' shows increased free space), and before-images of these are also put in the physical log. Of course, for a logged database, the updates to the catalog and other pages are logged in the logical logs. In the case of a ROLLBACK WORK, there are just a few changes that have to be undone. > > b. What is happening when I insert a new row to a table? > For an INSERT operation is there a 'before image'? If there is what is it? For a single INSERT, probably yes. It is in the physical log. The reason I say probably is this - a before-image is written to the physical log the FIRST time a page is modified. If there are many INSERT/UPDATE/DELETE statements that all affect the same page, there is only one before-image in the physical log. The physical log is cleared at each checkpoint, so if there is more update activity against that page, a new before-image is written to the physical log whenever the first update (after the checkpoint) occurs. If your second question is not a typo, then "what is it?" is the same as any other before-image - it's whatever the page looked like before you modified it, with an exception. If the page is a brand-new, never-before updated page, it will have an invalid page address. In that event, Informix will not bother logging a before-image. For more information on invalide page addresses, physical log reduction, page allocation and page initialization, consider the IDS Internal Architecture and Advanced Administration class from Informix. > c. Is SELECT statement(single or cursor, inside or outside a transaction) > ever involved in the fast recovery mechanism? Only update activity is logged. SELECTs do not update, so they are not logged. Possible exception - SELECT ... INTO TEMP might log if you did not specify the WITH NO LOG option. If so, and assuming that we're dealing with a logged database, the SELECT might be involved in fast recovery. If that were the case, I would expect the temp table to be created during the roll-forward of the logical logs, but it would be dropped during the roll-back of uncommitted transactions. Mark Collins mcollins@us.dhl.com Originality is the art of concealing your sources.