Re: Several questions ...

Dong Xiao wrote: > > Hi, community > > Are you having a good day? Just lovely thank you. > 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? Meaning what exactly? Informix is willing to license you from 1 to unlimited users. > 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? A "DROP TABLE..." is a single atomic operation and the only pages that need a preimage for recovery are the pages containing the systables and other system catalog rows related to the table, the tablespace tablespace page for the table, and the dbspace's freelist. The pages that belonged to the table and its indexes are just placed on the free list. If they are reused then those pre-images will be logged then. > 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? Again the few pages needed to identify the table and the disk space that belongs to it are simply recovered from the logical log. BTW, the physical log page images are ONLY used to provide a blank slate for the logical logs rollforward/rollback in case of a physical engine or server machine crash in case some log records were still in the logical log buffers at the time of the crash. Otherwise the logical logs contain sufficient information for ALL rollback operations. > 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? Every page that is modified has its before image copied to the physical log if it is not already there (ie the page has not been modified since the last checkpoint). However the images used for rollback and for recovery rollforward/rollback are in the logical log and are record images NOT page images. In the logical log EVERY modification to any row is recorded. An insert records ONLY the post-image, delete ONLY records the pre-image, and updates record both pre-image and post-image of the affected rows. > c. Is SELECT statement(single or cursor, inside or outside a transaction) > ever involved in the fast recovery mechanism? No, only transactions that modify rows in tables and other disk structures are logged in the logical log. FYI all SQL statements are treated as singleton transactions if there is not an explicit transaction in effect, except for ANSI mode databases where the first statement not contained in a transaction begins an implicit transaction that exists until committed or rolled back. Since there is no record in the logical log that a SELECT occurred there is not rollforward/rollback involved related to it during fast recovery. The exception to the rule is a SELECT that creates a TEMP table in a dbspace that is not a TEMP dbspace. The creation of, insertion to, and destruction of the temp table are logged. Art S. Kagel