DB2的归档模式设置方法-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

DB2的归档模式设置方法

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:33 PM

db2odatabase

DB2的日志使用通常有两种方式，既称之为循环日志，也就是不采用归档日志模式，另外一种就是归档日志模式，默认是循环日志模式，也

DB2的日志使用通常有两种方式，既称之为循环日志，也就是不采用归档日志模式，另外一种就是归档日志模式，默认是循环日志模式，也就是无法进行日志归档或在线备份。通常来说决定DB2日志配置的几个database级别的参数如下：
当前的数据库版本为：
[db2inst1@localhost ]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09075"
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23291", and Fix Pack
"5".
Product is installed at "/opt/ibm/db2/V9.7".

[db2inst1@localhost ]$ db2 get db cfg|grep -i log
Log retain for recovery status = NO
User exit for logging status = YES
Log buffer size (4KB) (LOGBUFSZ) = 256
Log file size (4KB) (LOGFILSIZ) = 2000
Number of primary log files (LOGPRIMARY) = 20
Number of secondary log files (LOGSECOND) = 30
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000001.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method (LOGARCHMETH1) = DISK:/db2src/TS/backup/
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =

如上常用的参数介绍如下：
LOGPRIMARY+LOGSECOND是日志文件的个数，而每个日志文件的大小由LOGFILSIZ参数设定，如上当前的日志文件大小为2000 pages,而page大小为4K，所以每个日志文件为8M，总共的日志空间为400M，NEWLOGPATH参数决定了日志文件存放目录路径，修改后可以动态生效。
First active log file就是指DB当前使用的活动日志文件名称
MAX_LOG 指定单个事物可以使用主日志空间的百分比，当达到这个百分比后，这个事物就得被回滚，默认设置为0，也就是单个事务日志空间使用不受限制，可以使用主附日志的所有空间。
SOFTMAX 指定软检查点频率和范围，单位为一个日志文件大小的百分比，100也就是一个日志文件，当发生数据库崩溃恢复时，需要保持一致状态，就会发生崩溃恢复，需要使用指定日志文件数来做恢复，当然日志量越小恢复就越快，通常保持默认值。
LOGARCHMETH1 这个即设置归档的方式，可以指定DISK、TSM等参数。
上面大概介绍了参数设置，那么如何配置DB2归档呢？
其实DB2的归档配置比较简单，只需要需要修改LOGARCHMETH1参数即可如下：
[db2inst1@localhost backup]$ db2 update db cfg using LOGARCHMETH1 'disk:/db2src/TS/backup'
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.

但是修改了这个参数还是无法生效的，需要执行重启数据库这里直接重启实例：
[db2inst1@localhost backup]$ db2stop force
11/25/2013 22:17:02 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@localhost backup]$ db2start
11/25/2013 22:17:07 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@localhost backup]$ db2 connect to sample
SQL1116N A connection to or activation of database "SAMPLE" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
发现重启后还是不能连接，这时数据库状态为BACKUP PENDING,需要做一个备份，，记住是离线备份。

更多详情见请继续阅读下一页的精彩内容：

推荐阅读：

DB2数据库性能调整和优化(第1、2版) PDF

DB2数据库性能优化介绍

linux

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What are some tools you can use to monitor MySQL performance?Apr 23, 2025 am 12:21 AM

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

How does MySQL differ from SQL Server?Apr 23, 2025 am 12:20 AM

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In what scenarios might you choose SQL Server over MySQL?Apr 23, 2025 am 12:20 AM

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

How does MySQL handle character sets and collations?Apr 23, 2025 am 12:19 AM

MySQLmanagescharactersetsandcollationsbyusingUTF-8asthedefault,allowingconfigurationatdatabase,table,andcolumnlevels,andrequiringcarefulalignmenttoavoidmismatches.1)Setdefaultcharactersetandcollationforadatabase.2)Configurecharactersetandcollationfor

What are triggers in MySQL?Apr 23, 2025 am 12:11 AM

A MySQL trigger is an automatically executed stored procedure associated with a table that is used to perform a series of operations when a specific data operation is performed. 1) Trigger definition and function: used for data verification, logging, etc. 2) Working principle: It is divided into BEFORE and AFTER, and supports row-level triggering. 3) Example of use: Can be used to record salary changes or update inventory. 4) Debugging skills: Use SHOWTRIGGERS and SHOWCREATETRIGGER commands. 5) Performance optimization: Avoid complex operations, use indexes, and manage transactions.

How do you create and manage user accounts in MySQL?Apr 22, 2025 pm 06:05 PM

The steps to create and manage user accounts in MySQL are as follows: 1. Create a user: Use CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password'; 2. Assign permissions: Use GRANTSELECT, INSERT, UPDATEONmydatabase.TO'newuser'@'localhost'; 3. Fix permission error: Use REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost'; then reassign permissions; 4. Optimization permissions: Use SHOWGRA

How does MySQL differ from Oracle?Apr 22, 2025 pm 05:57 PM

MySQL is suitable for rapid development and small and medium-sized applications, while Oracle is suitable for large enterprises and high availability needs. 1) MySQL is open source and easy to use, suitable for web applications and small and medium-sized enterprises. 2) Oracle is powerful and suitable for large enterprises and government agencies. 3) MySQL supports a variety of storage engines, and Oracle provides rich enterprise-level functions.

What are the disadvantages of using MySQL compared to other relational databases?Apr 22, 2025 pm 05:49 PM

The disadvantages of MySQL compared to other relational databases include: 1. Performance issues: You may encounter bottlenecks when processing large-scale data, and PostgreSQL performs better in complex queries and big data processing. 2. Scalability: The horizontal scaling ability is not as good as Google Spanner and Amazon Aurora. 3. Functional limitations: Not as good as PostgreSQL and Oracle in advanced functions, some functions require more custom code and maintenance.

See all articles