Log mode for the Informix database

Today, when operating the database for bulk DML operations, reported a previously unseen error, Java.sql.SQLException:No Transaction isolation on non-logging db ' s, I hereby write a record of my own resolution process.

First of all, I see from the above error that the general is that the library does not log logs, but before our transaction operations are bound to have a log. So the following search is expanded.

1. View the log mode:

Onmonitor--Logical-logs-->databases see Log Status for each library

Look, the state is indeed N. In this case, the transaction is not supported. So the error will be.

2, modify the mode:

Ontape-s-B dbname

I executed the statement error,error changing logging status-' BMT '. Iserrno 107. Program over. The reason seems to be that when the command is executed, the database server is taken offline, it is not possible to perform any additions or deletions to the operation.

Ontape–s–l 0–n dbname Change the database dbname from-u,-b,-a one mode to-n mode to create a level 0 backup

Ontape–s–l 0–u cem2 Change the database "CEM2" from-N to-u mode to create a level 0 backup

Ontape–s–l 0–b cem2 Change Database "cem2" from-N to-B mode, create level 0 backup

Ontape–s–l 0–a cem2 Change the database "CEM2" from-N to-a mode to create a level 0 backup

# # # #ontape工具在-u,-b,-a three modes in any one of the changes to another do not need to create a system backup; To completely change from log mode to log mode, or vice versa, to create a level 0 backup;

Other than that

Create Database dbname with log

Create DATABASE state is U

Create database dbname with buffered log

Create DATABASE state is B
Both of these have logs that support transactions.

Here is a general list of the meanings of each pattern:

-N No Logging no logs

-U unbuffered Logging non-buffered log

-B Buffered Logging buffer log

-a unbuffered Logging, mode ANSI ANSI modes

No Loggin has no log mode: Only minimal information is logged to the logical log, only DDL statements are executed, transactions are not supported, that is, DML statements cannot be executed. A database environment without logs can have a high throughput rate, but is not capable of rebuilding changes to the database in the event of a serious instance failure.

unbuffered Logging non-buffered log mode: Whenever a transaction commits, the physical and logical log buffers containing the transaction information are flushed to disk. In this case, data integrity and consistency can be guaranteed at the transaction level, even if serious instance errors occur. However, because each commit transaction causes the buffer to be flushed to disk, disk I/O is increased. Also, because refreshes write the entire buffer contents to the logical log according to the progress of the current transaction, there is a lot of unused data in the logical log pages. The log fills up quickly, but the "real" data contained in it is much less than the buffer log database environment.

Buffered Logging Buffered Log mode: The environment will retain these transaction information in the logical and physical log buffers until the buffer fills up, or a checkpoint operation occurs, or the user connection that generated the transaction is closed before the transaction has been written. In the buffered logs database environment, disk I/O is greatly reduced for each transaction, so instances run faster, but because transaction information is stored in shared memory, serious instance errors can be dangerous and when the shared memory of the instance is freed, transaction information that has not yet been written to disk is lost.

##### non-buffered log mode and buffered log mode operate exactly the same way, depending on when the log records are written to disk.

unbuffered Logging, mode ANSI ANSI mode: ANSI Mode operates as a non-buffered log, but it also enforces the same way as ANSI transactions.  ANSI conformance includes features and rules such as the unique owner name of the reference table, the different default values for table-level permissions, the ability to read and update cursors, and the difference between the character and decimal data types over the data type or how the definition statement reacts.

# # # #OnLine dymanic server does not strictly enforce compliance with all ANSI standards in an ANSI database environment, and if you execute a non-ANSI SQL statement, the instance generates a warning message but still handles it down. Unless the operating environment requires the use of ANSI standards, there is no benefit to using ANSI mode.

Attach several statements:

command to find a process: Onstat-g SQL | grep dbname

command to delete a process: onmode-z process number

Remove the command for all processes: Onmode-u

This article is from the "bilingling" blog, make sure to keep this source http://bilingling.blog.51cto.com/11578217/1795678

Log mode for the Informix database