Shulou(Shulou.com)06/01 Report--

Today, when operating the database for batch DML operations, I reported an error I had never seen before, java.sql.SQLException: No Transaction Isolation on non-logging db's. I would like to write down my solution process.

First of all, I can see from the above error that this library does not log, but there must be a log for our transaction operations in the past. So the search was carried out as follows.

1. View the log mode:

Onmonitor-- > Logical-logs-- > Databases look at the Log Status of each library

At a glance, the state is really N. In this case, transactions are not supported. So it will be wrong.

2. Modify the mode:

Ontape-s-B dbname

I got an error when I executed the statement, Error changing logging status-'bmt'. Iserrno 107. Program over. The reason seems to be that when you execute this command, you need the database server to be offline, but you can't perform any operations to add, delete, modify and check.

Ontape-s-L 0-N dbname changes the database dbname from one of the modes of-Umai BLMI to-N to create a level 0 backup.

Ontape-s-L 0-U cem2 changes the database "cem2" from-N to-U mode to create a level 0 backup

Ontape-s-L 0-B cem2 changes the database "cem2" from-N to-B mode to create a level 0 backup

Ontape-s-L 0-A cem2 changes the database "cem2" from-N to-A mode to create a level 0 backup

# the ontape tool does not need to create a system backup to change any of the three modes from-Umam to the other; to change completely from no log mode to a log mode, or vice versa, you need to create a level 0 backup

In addition,

Create database dbname with log

The creation database status is U

Create database dbname with buffered log

The creation database status is B.

Both come with logs and support transactions.

Here's a general list of what each pattern means:

-N No Logging has no log

-U Unbuffered Logging unbuffered log

-B Buffered Logging buffer log

-A Unbuffered Logging, Mode ANSI ANSI mode

No Loggin does not have a logging mode: very little information is recorded in the logical log, only DDL statements can be executed, and transactions are not supported, that is, DML statements cannot be executed. A database environment without logs can have high throughput, but does not have the ability to rebuild changes to the database in the event of a serious instance failure.

Unbuffered Logging unbuffered log mode: whenever a transaction commits, the physical log and logical log buffers containing the transaction information are flushed to disk. In this way, 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 Ihand O has been added. In addition, because the refresh writes the entire buffer contents to the logical log according to the progress of the current transaction, there will be a lot of useless data in the page of the logical log. The log populates quickly, but it contains much less "real" data than the buffered log database environment.

Buffered Logging buffered log mode: the environment retains this transaction information in the logical and physical log buffers until the buffer fills, or a checkpoint occurs, or the user connection that created the transaction is closed before the transaction is written to the log. In the "buffer log" database environment, the disk I / O caused by each transaction is greatly reduced, so the instance runs faster, but because the transaction information is stored in shared memory, serious instance errors can be dangerous. when the shared memory of the instance is freed, the transaction information that has not been written to disk is lost.

# unbuffered log mode and buffered log mode operate in exactly the same way, the difference is when the log records are written to disk.

Unbuffered Logging, Mode ANSI ANSI mode: ANSI mode operates the same as unbuffered logs, but it also enforces consistency with ANSI transactions. ANSI consistency includes features and rules such as unique owner naming of referenced tables, different default values of table-level permissions, differences in cursor reading and update capabilities, and differences in how character and decimal data types react to data types crossing boundaries or defining statements.

# OnLine Dymanic Server does not strictly enforce compliance with all ANSI standards in the ANSI database environment. If you execute a non-ANSI SQL statement, the instance will generate a warning message, but still proceed to process. Unless the operating environment requires the use of the ANSI standard, there is no benefit from using the ANSI mode.

If you want to change from N mode to B mode, the steps are as follows:

Onmode-u clear the connection session and enter the quiescent mode

Ontape-s-B spdb modifies the log mode of spdb to B

Onmode-m switches back to online mode

Onmonitor-- > Logical-Logs-- > Databases check the log mode of spdb again. It has been changed back to B, and the application is restarted successfully.

Several additional commands are attached:

Command to find a process: onstat-g sql | grep dbname

Command to delete a process: onmode-z process number

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.