db2数据库配置文件在哪 db2搭建数据库环境

转载

mob6454cc6e8f43 2024-05-11 20:35:40

文章标签 db2数据库配置文件在哪 DB2 数据库 2d hive 文章分类 数据仓库大数据

目的

部署两套DB2数据库作为源和目标。

服务器环境配置如下

服务器1	服务器2
主机名	db2db01	db2db02
IP地址	192.169.114.27	192.169.114.28
CPU	intel Xeon 4核 2.6G Hz	intel Xeon 4核 2.6G Hz
内存	32G	32G
磁盘	/opt 16G 用作DB2的安装目录 /home 20G 用于DB2实例目录 /dev/vdb 500G 未分区、未挂载 /dev/vdc 50G 未分区、未挂载	/opt 16G 用作DB2的安装目录 /home 20G 用于DB2实例目录 /dev/vdb 500G 未分区、未挂载 /dev/vdc 50G 未分区、未挂载

DB2数据库规划

数据库1	数据库2
DB2软件版本	DB2 v10.5.0.6	DB2 v10.5.0.6
DB2安装目录	/opt/ibm/db2/V10.5	/opt/ibm/db2/V10.5
数据库实例名	db2inst1	db2inst1
数据库名	DB2TEST	DB2TEST
数据库目录	/db2data 500G	/db2data 500G
事务日志目录	/db2log 50G	/db2log 50G

安装前准备

一、配置/etc/hosts文件，在文件中添加以下内容，两台服务器均做相同操作

`192.169.114.27 db2db01 192.169.114.28 db2db02`

二、配置/etc/services文件，添加以下内容，为DB2实例分配TCPIP端口。两台服务器均做相同操作

`DB2c_db2inst1 50000/tcp`

三、新建数据库所需用户及用户组并设置用户密码

`groupadd -g 600 db2iadm groupadd -g 601 db2fadm useradd -u 1000 -g 600 -d /home/db2inst1 db2inst1 useradd -u 1001 -g 601 -d /home/db2fenc1 db2fenc1 passwd db2inst1 passwd db2fenc1`

用户‘db2inst1、db2fenc1’密码均为:123qwe!@#

四、挂载磁盘，分别新建两个目录‘/db2data’、‘/db2log’。将500G磁盘分为一个分区并格式化为ext4文件系统并挂载到‘/db2data’目录下。将50G磁盘分为一个分区并格式化为ext4文件系统并挂载到‘/db2log’目录下。更改目录所属用户及用户组

`chown -R db2inst1:db2iadm /db2data chown -R db2inst1:db2iadm /db2log`

安装DB2软件

一、在root目录中新建‘db2’目录用于保存DB2安装包及license文件。

二、将DB2安装压缩文件及license文件上传至服务器/root/db2目录中并解压

三、进入安装目录‘cd server_t’，采用字符界面安装，过程如下

./db2_install Requirement not matched for DB2 database "Server" . Version: "10.5.0.6". Summary of prerequisites that are not met on the current system: DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so". DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6". DBI1324W Support of the db2_install command is deprecated. Default directory for installation of products - /opt/ibm/db2/V10.5 ******************************************************** Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no] yes Specify one of the following keywords to install DB2 products. SERVER CONSV EXP CLIENT RTCL Enter "help" to redisplay product names. Enter "quit" to exit. ******************************************************* SERVER ********************************************************* Do you want to install the DB2 pureScale Feature? [yes/no] no Requirement not matched for DB2 database "Server" . Version: "10.5.0.6". Summary of prerequisites that are not met on the current system: DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*". DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6". DB2 installation is being initialized . . . . The execution has been successfully completed. For more information, see the DB2 installation logs on "/ TMP/db2_install.log. 24239".

./db2_install
Requirement not matched for DB2 database "Server" . Version: "10.5.0.6".
 
Summary of prerequisites that are not met on the current system:
 
   DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".
 
 
DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".
 
 
DBI1324W Support of the db2_install command is deprecated.
 
 
 
Default directory for installation of products - /opt/ibm/db2/V10.5
 
***********************************************************
Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no]
yes
Specify one of the following keywords to install DB2 products.
 
  SERVER
  CONSV
  EXP
  CLIENT
  RTCL
 
Enter "help" to redisplay product names.
 
Enter "quit" to exit.
***********************************************************
SERVER
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no]
no
Requirement not matched for DB2 database "Server" . Version: "10.5.0.6".
 
Summary of prerequisites that are not met on the current system:
 
   DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".
 
 
DBT3514W  The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".
 
 
DB2 installation is being initialized
.
.
.
.
The execution has been successfully completed.
For more information, see the DB2 installation logs on "/ TMP/db2_install.log. 24239".

安装完成，安装license

`/opt/ibm/db2/V10.5/adm/licm -a /root/db2/ db2aese_c.lic LIC1402I License added successfully`

创建DB2实例

创建DB2实例，实例用户为‘db2inst1’，受防护用户为‘db2fenc1’

`/opt/ibm/db2/V10.5/instance/db2icrt -u db2fenc1 db2inst1 DBI1446I The db2icrt command is running. DB2 installation is being initialized. . . . The execution completed successfully. For more information see the DB2 installation log at "/tmp/db2icrt.log.6460". DBI1070I Program db2icrt completed successfully`

/opt/ibm/db2/V10.5/instance/db2icrt -u db2fenc1 db2inst1
DBI1446I The db2icrt command is running.
 
DB2 installation is being initialized.
.
.
.
The execution completed successfully.
 
For more information see the DB2 installation log at "/tmp/db2icrt.log.6460".
DBI1070I Program db2icrt completed successfully

实例创建完成

切换至DB2实例用户，并修改相关注册变量、实例参数

`su – db2inst1 db2start 08/20/2017 10:26:21 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. db2set DB2COMM=TCPIP db2 update dbm cfg using SVCENAME DB2c_db2inst1 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.`

su – db2inst1
db2start
08/20/2017 10:26:21    0  0  SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
db2set DB2COMM=TCPIP
db2 update dbm cfg using SVCENAME DB2c_db2inst1
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

新建数据库

在实例db2ins1中新建数据库

`db2 "CREATE DATABASE DB2TEST ON '/db2data' DBPATH ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY CN DB20000I The CREATE DATABASE command completed successfully.`

根据提示数据库创建完成。

修改数据库配置参数

`db2 update db cfg for db2test using NEWLOGPATH /db2log db2 update db cfg for db2test using LOGFILSIZ 78600 db2 update db cfg for db2test using LOGPRIMARY 100 db2 update db cfg for db2test using LOGSECOND 30`

db2 update db cfg for db2test using NEWLOGPATH /db2log
db2 update db cfg for db2test using LOGFILSIZ 78600
db2 update db cfg for db2test using LOGPRIMARY 100
db2 update db cfg for db2test using LOGSECOND 30

修改完成后重启实例

`[db2inst1@localhost ~]$ db2stop force 2017-08-23 16:40:40 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst1@localhost ~]$ db2start 08/23/2017 16:40:44 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.`

[db2inst1@localhost ~]$ db2stop force
2017-08-23 16:40:40    0  0  SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@localhost ~]$ db2start
08/23/2017 16:40:44    0  0  SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

检查相关参数

`[db2inst1@localhost ~]$ db2set -all [i] DB2COMM=TCPIP [g] DB2SYSTEM=localhost.localdomain [g] DB2INSTDEF=db2inst1`

`[db2inst1@localhost ~]$ db2 get dbm cfg \|grep -i SVCENAME TCP/IP Service name (SVCENAME) = DB2c_db2inst1 SSL service name (SSL_SVCENAME) =`

[db2inst1@localhost ~]$ db2 get db cfg for db2test\|grep -i log Log retain for recovery status = NO User exit for logging status = NO Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300 Log buffer size (4KB) (LOGBUFSZ) = 2149 Log file size (4KB) (LOGFILSIZ) = 78600 Number of primary log files (LOGPRIMARY) = 100 Number of secondary log files (LOGSECOND) = 30 Changed path to log files (NEWLOGPATH) = Path to log files = /db2log/NODE0000/LOGSTREAM0000/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = 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) = 0 HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(0) HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0 First log archive method (LOGARCHMETH1) = OFF Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Log pages during index build (LOGINDEXBUILD) = OFF Log DDL Statements (LOG_DDL_STMTS) = NO Log Application Information (LOG_APPL_INFO) = NO

[db2inst1@localhost ~]$ db2 get db cfg for db2test|grep -i log
 Log retain for recovery status                         = NO
 User exit for logging status                           = NO
 Catalog cache size (4KB)             (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                       (LOGBUFSZ) = 2149
 Log file size (4KB)                        (LOGFILSIZ) = 78600
 Number of primary log files               (LOGPRIMARY) = 100
 Number of secondary log files              (LOGSECOND) = 30
 Changed path to log files                 (NEWLOGPATH) = 
 Path to log files                                      = /db2log/NODE0000/LOGSTREAM0000/
 Overflow log path                    (OVERFLOWLOGPATH) = 
 Mirror log path                        (MIRRORLOGPATH) = 
 First active log file                                  =
 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) = 0
 HADR log write synchronization mode    (HADR_SYNCMODE) = NEARSYNC
 HADR spool log data limit (4KB)     (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
 HADR log replay delay (seconds)    (HADR_REPLAY_DELAY) = 0
 First log archive method                (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1   (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                 (LOGARCHOPT1) = 
 Second log archive method               (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2   (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                 (LOGARCHOPT2) = 
 Failover log archive path               (FAILARCHPATH) = 
 Number of log archive retries on error  (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)        (ARCHRETRYDELAY) = 20
 Log pages during index build           (LOGINDEXBUILD) = OFF
 Log DDL Statements                     (LOG_DDL_STMTS) = NO
 Log Application Information            (LOG_APPL_INFO) = NO

说明参数修改完成

由于DB2数据库中的表结构、表数据来源于oracle数据库，需要将oracle源数据库中的DDL语句及数据迁移到DB2数据中，计划使用IBM Data Movement Tool 工具进行迁移，详细迁移步骤在另外文档中说明。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。