Changing the Logging Mode

Hello ! IDS 11.10.FC2W1 on solaris 10. I am use ontape (no onbar) for back up. Q1)I have all my databases in one of the instance in LOGGING mode. for performing a very hectic parallel insert activity on one of my database whre logging is not that important for me i can take a level 0 backup of the instance and change that database to no logging mode % ontape -s -L 0 -N "database"then do my activity and again do % ontape -s -L 0 -B "database" to bring that database to buffered logging.OR using LTAPEDEV /dev/null is a better approach. I just want to know is there some another approach to do so... or do i have to follow the same procedure. I also want to know that if the activity was started with the database in nologging mode and if for some reasons it is interrupted then what will happen, will the database still go in to fast recovery and roll back the transaction or .....?? Q2)For importing a huge database i can avoid using the -l option which will then create the database with nologging, this import (i guess) should be faster than creating a database with logging, but then again i have to do a level 0 backup to change its logging to buffer. Can i only use ontape to change the loggin mode (which i am doing right now)? Regards vikas

Q1: You could just alter the table you are loading into RAW mode before the load and then back to normal afterwards. You will still want to take an archive before and immediately after the load, however, since the load will not have been logged and so is not recoverable and cannot be rolled back. If there is a server failure or even a log app failure during the load you will have to restore the pre-load archive and start all over, unless you have a way to continue the load from where it left off after determining what made it to disk and what data did not. Note that any rows that were inserted after the last checkpoint before the crash (if the server crashes) will be wiped out by the physical log slapdown during startup before fast recovery starts. Q2: ondblog can be used to mark the DB for a logging mode change but the ontape is needed to implement the change anyway. Art On Thu, Jun 26, 2008 at 11:40 AM, VIKAS HIVARKAR <vikas.hivarkar@tcs.com> wrote: > Hello ! > > IDS 11.10.FC2W1 on solaris 10. > > I am use ontape (no onbar) for back up. > > Q1)I have all my databases in one of the instance in LOGGING mode. > for performing a very hectic parallel insert activity on one of my database > whre logging is not that important for me i can take a level 0 backup of > the > instance and change that database to no logging mode > % ontape -s -L 0 -N "database"> then do my activity and again do > % ontape -s -L 0 -B "database" to bring that database to buffered logging.> OR using LTAPEDEV /dev/null is a better approach. > > I just want to know is there some another approach to do so... > or do i have to follow the same procedure. > > I also want to know that if the activity was started with the database in > nologging mode and if for some reasons it is interrupted then what will > happen, > will the database still go in to fast recovery and roll back the > transaction > or .....?? > > Q2)For importing a huge database i can avoid using the -l option which will > then create the database with nologging, this import (i guess) should be > faster than creating a database with logging, but then again i have to do a > level 0 backup to change its logging to buffer. > > Can i only use ontape to change the loggin mode (which i am doing right > now)? > > Regards > vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Vikas, You can use ondblog to change database logging mode - more info - http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=3D= /com.ibm.adref.doc/adref351.htm Also take a look at RAW Tables - http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=3D= /com.ibm.admin.doc/admin330.htm Regards, Nilesh ids-bounces@iiug.org wrote on 06/26/2008 10:40:23 AM: > Hello ! > > IDS 11.10.FC2W1 on solaris 10. > > I am use ontape (no onbar) for back up. > > Q1)I have all my databases in one of the instance in LOGGING mode. > for performing a very hectic parallel insert activity on one of my database > whre logging is not that important for me i can take a level 0 backup= of the > instance and change that database to no logging mode > % ontape -s -L 0 -N "database"> then do my activity and again do > % ontape -s -L 0 -B "database" to bring that database to buffered logging.> OR using LTAPEDEV /dev/null is a better approach. > > I just want to know is there some another approach to do so... > or do i have to follow the same procedure. > > I also want to know that if the activity was started with the databas= e in > nologging mode and if for some reasons it is interrupted then what wi= ll > happen, > will the database still go in to fast recovery and roll back the transaction > or .....?? > > Q2)For importing a huge database i can avoid using the -l option whic= h will > then create the database with nologging, this import (i guess) should= be > faster than creating a database with logging, but then again i have t= o do a > level 0 backup to change its logging to buffer. > > Can i only use ontape to change the loggin mode (which i am doing rig= ht now)? > > Regards > vikas > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

Thank you ! i think its better for me to do the activity with database in logging mode. -rw-rw---- 1 informix informix 12288000000 Jun 26 16:50 logdbs i have 586 total logical logs each of 20M. now if my single transaction fill this 586 logs ( which i backup with logful.sh) using directory feature. now if this single transactions spans more than these 586 logs and then before completing the transaction the server goes down or the transaction is aborted in between, then the fast recovery will start and rollback of this transaction will be carried out from logdbs ( correct me if i am wrong here ). now will the recovery be carried out with all the data from the logdbs only or will it ask for some logs those are backed up earlier as they can be a part of this transaction and no more in logdbs. Sorry if i have lost it completely!, but want to clear this doubt.

yes to avoid any such wage situation i can use explicit commit's in between the transaction.but if thats not the case?

Oops! i got the point, in case of Roll back of the transaction it will read from the physdbs and bring the database to a point when the transaction was started. so does that means if one single transaction (with out commit) spans over the size of physdbs then the transaction will give error and roll back. Mr Kagel Pls correct me if i am worng here. Regards, vikas

No, transaction rollback is performed on using the logical log. The physical log is used ONLY at engine startup to restore the server to a known state before logical log are processed. At the beginning of startup the engine checks the contents of the physical log. If the server was shutdown normally, then there will have been a checkpoint taken immediately after all users were disconnected from the server which will have emptied the physical log (see below). So, if the physical log is not empty, that means that the engine crashed or the machine crashed out from under it, or for some other reason a normal shutdown was not possible. If this happens the pre-image pages contained in the physical log are slapped back onto disk to create a clean-slate for the logical log rollforward and rollback operations that constitute the remainder of fast recovery restoring the disk to its state as of the last known checkpoint. So, yes the physical log is written back to disk, but it really has nothing to do with the rollback of individual transactions. That is done by/using the logical logs. The log records since the last checkpoint are rolled forward (ie replayed) until the end of the last logical log. When the end is reached any open transactions at the time of the checkpoint (and those begun since the checkpoint) which were not committed during the rollforward are then rolled back. Then a new checkpoint record is written noting that the server is in a consistent state and that all of these changes have been committed to disk, and the engine moves to an operational state. On the other comment, no, the physical log is ONLY needed until the checkpoint guarantees that all logical log records are safely on disk along with the checkpoint record. The physical log is cleared at the end of every checkpoint. HOWEVER, the physical log cannot be allowed to fill up between checkpoints. This means that if the physical log reaches 75% full the engine will force a checkpoint immediately - even if one isn't scheduled yet. If the physical log reaches 100%, known as a physical log overflow, all activity on the server is blocked until the checkpoint completes. This almost never happens, it would require an extremely small physical log and a very active highly volatile server. On Fri, Jun 27, 2008 at 1:42 AM, VIKAS HIVARKAR <vikas.hivarkar@tcs.com> wrote: > Oops! i got the point, in case of Roll back of the transaction it will read > from the physdbs and bring the database to a point when the transaction was > started. > > so does that means if one single transaction (with out commit) spans over > the > size of physdbs then the transaction will give error and roll back. > > Mr Kagel Pls correct me if i am worng here. > > Regards, > vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Thank you Mr Kagel ! i got caught between Fast Recovery and Roll back of transaction. so the bottom line is Roll back of a transaction is carried out by Logical logs. but then what will happen if i have max of 10 logical logs which are filled during a single uncommited transaction, i backup those logs continuesly and make them available for informix to reuse and the transaction continues using the logs and then the transaction abortes resulting in a roll back of the transaction. the roll back will be carried out using the logical logs but will this roll back to completed require the 10 logical logs which were backed up? because if nothing was commited then every thing should be rolled backed and the database should be at the point just before the transaction was started.

Correct. If the percentage of logical logs fill to LTXHWM percentage full new transactions will be blocked and the transaction that's oldest in the logs will begin to rollback - the engine does not wait for the logs to be completely full. If during the rollback the logs fill to LTXEHWM percent full all other update activity on the server is blocked until the logs finally fill or the transaction finally rollsback releasing the oldest logs for reuse. (If that rollback wasn't enough to free enough logs to get under the high water mark, then the next oldest transaction is similarly rolled back. The engine will not reuse a log that has an open transaction in it, even if it has been backed up. Otherwise rollback would not be possible. Art On Fri, Jun 27, 2008 at 2:19 AM, VIKAS HIVARKAR <vikas.hivarkar@tcs.com> wrote: > Thank you Mr Kagel ! > > i got caught between Fast Recovery and Roll back of transaction. > so the bottom line is Roll back of a transaction is carried out by Logical > logs. > > but then what will happen if i have max of 10 logical logs which are filled > during a single uncommited transaction, i backup those logs continuesly and > make them available for informix to reuse and the transaction continues > using > the logs and then the transaction abortes resulting in a roll back of the > transaction. > the roll back will be carried out using the logical logs but will this roll > back to completed require the 10 logical logs which were backed up? > because if nothing was commited then every thing should be rolled backed > and > the database should be at the point just before the transaction was > started. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Thank you Mr Kagel !