DB2日志(2) 用C语言扩展实现DB2日志管理及主备同步
还好DB2提供了相关的USEREXIT程序(又名用户出口程序)可以使我们管理日志文件并有了扩展功能的可能性.在这一点上DB2明显要比Orac
还好DB2提供了相关的USEREXIT程序(又名用户出口程序)可以使我们管理日志文件并有了扩展功能的可能性.在这一点上DB2明显要比Oracle开放些.
实现
1.首先将USEREXIT接口打开,让数据库支持USEREXIT程序
--查看当前状态(Windows环境下)
db2 get db cfg for XCLDB2 |find /I "userexit"
--打开userexit
db2 update db cfg for XCLDB2 using userexit on
--查看当前状态(Windows环境下)
db2 get db cfg for XCLDB2 |find /I "userexit"
2. 去DB2安装目录下找到例子程序
Unix下: sqllib/samples/c
Windows下: C:\Program Files\IBM\SQLLIB\samples\c
例子文件:
Db2uext2.cadsm — 对 Tivoli Storage Manager 的支持,也称为 ADSM
Db2uext2.cdisk — 对磁盘的支持
Db2uext2.ctape — 对本地磁带的支持,仅可用于 UNIX 系统
Db2uext2.cxbsa — 对 XBSA Draft 0.8 客户机的支持
这些样本程序中的每个都只需要稍作修改(如 buffer_size 、 audit_log_activation 、 audit_log_path 、 error_log_activation 和 error_log_path )
除了上面这些,还可以用db2uext2.ctsm来实现.它是新的替代版.
3.以Windows环境为例演示如何编译扩展.
3.1 用Visual Studio新建一个属性为空项目的控制台应用程序工程.注意,工程名为"Db2uext2",
工程目录为"c:\DB2C"
3.2 将 Db2uext2.cdisk 复制到 C:\DB2C\db2uext2 目录下,将其更名为Db2uext2.c.
然后在Visual Studio工程中将文件导入.
导入成功后打开文件. 查找并替换 "c:\\mylogs\\" 为自己所要的目录如 "c:\\xcllogs\\"
其实主要是更改的这几个参数,具体含义在C程序的代码注释中很详细:
#define ARCHIVE_PATH "c:\\xcllogs\\"
#define RETRIEVE_PATH "c:\\xcllogs\\"
#define AUDIT_ACTIVE 1 /* enable audit trail logging */
#define ERROR_ACTIVE 1 /* enable error trail logging */
#define AUDIT_ERROR_PATH "c:\\xcllogs\\" /* path must end with a slash */
#define AUDIT_ERROR_ATTR "a" /* append to text file */
#define BUFFER_SIZE 32 /* # of 4K pages for output buffer */
3.3将编译好的Reselse版本的db2uext2.exe文件放在DB2管理程序能找到的目录下.
编译后的db2uext2.exe文件存放目录:
Windows下:
C:\Program Files\IBM\SQLLIB\BIN
Unix下:
/sqllib/adm
3.4 测试扩展程序是否生效
3.4.1 在DB2 CLP下手工强制归档一份日志
db2 archive log for db XCLDB2
3.4.2 应当可以在"C:\xcllogs\XCLDB2\NODE0000"目录下查到新产生的日志
C:\>tree C:\xcllogs
卷 WINXP 的文件夹 PATH 列表
卷序列号为 88F1-6579
C:\XCLLOGS
└─XCLDB2
└─NODE0000
c:\>dir C:\xcllogs\XCLDB2\NODE0000
上面举的是Windows下的,在Unix下时,一定要注意,需先mkdir目录并授权(如777),
并chown -R 目录给DB2的用户.
引申:
上面只是举了个最简单的例子,但如果把这个例子扩展下.甚至可以通过日志文件的同步
或异步复制到异机或远程存储来实现数据库的容灾.
简单的说一个主备数据库同步的实现方法 :
1. 首先在备份数据库服务器上,用主库的备份恢复出一个完整的DB2数据库.
这个备份服务器硬件什么的可以不一致,就是文件系统划分不一样时,需要用重定向恢复来生成备库.
2. 将备份服务器的活动日志目录,设为主库服务器有读写权限.
3. 参考上面的例子,实现一个扩展.用于将日志传一份至备份服务器的活动日志目录.
4. 在备份服务器上弄一个定时作业,定时前滚 "db2 rollforward to end of logs"
哈哈,这样两边就同步了.
这个扩展还可以加上自动定期整理日志的功能.也可以利用Db2uext2.ctape做些磁带库方面的扩展.
总之,看个人的创意了. ,

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.

In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.

MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.

MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.
