- 论坛徽章:
- 0
|
目 录
1总体规划... 4
1.1 主机环境... 4
1.2 数据库产品... 4
1.3 磁盘... 4
1.4 实例、用户及数据库... 4
2主机相关环境设置... 5
2.1安装64位JDK. 5
2.2创建相关用户和组... 6
2.2.1实例用户和组... 6
2.2.2隔离用户和组... 6
2.3创建磁盘阵列卷组... 6
2.3.1创建TDdatavg. 6
2.3.2创建HDdatavg. 7
3 DB2安装... 7
3.1 DB2安装... 7
3.1.1 解压缩... 7
3.1.2 安装... 8
3.2 DB2升级... 15
3.3更新实例环境参数... 21
3.4创建数据库... 22
3.5安装数据库许可证... 22
4表空间设计... 23
4.1表空间规划基本原则... 23
4.2表空间规划... 23
4.2.1 TD数据库... 24
4.2.2 HD数据库... 24
4.3创建表空间... 25
4.3.1创建裸设备... 25
4.3.2修改逻辑卷和裸设备属性... 27
4.3.3创建表空间... 27
4.4创建表... 28
5缓冲池规划... 28
5.1缓冲池规划基本原则... 28
5.2缓冲池规划... 29
5.3创建缓冲池... 29
6 DB2配置参数... 29
6.1数据库管理器配置参数... 29
6.2数据库管理参数... 34
1总体规划
1.1 主机环境
两台主机，型号均为IBM P570，主机名分别为**p570a和**p570b（**为地市名简称），安装操作系统 AIX 5L（64位内核），版本V5.3，维护级别升级到5300-04。
1.2 数据库产品
数据库采用DB2 ENTERPRISE SERVER EDITION v8.2，补丁打到fixpak12，补丁下载地址：
http://www-306.ibm.com/software/data/db2/udb/support/downloadv8_aix5.html#r12
1.3 磁盘
主机内置磁盘 2*146.8G，用于安装操作系统及各类软件。磁盘阵列为IBM DS4800一台， 14*146.8G，其中6块硬盘做RAID10，7块硬盘做RAID5，1块硬盘做全局热备盘，每组RAID划成一个LUN。正常运行状态下，RAID10的LUN挂在主机**p570a上，在对应的物理卷上创建卷组TDdatavg；RAID5的LUN挂在主机**p570b上，在对应的物理卷上创建卷组CRMdatavg。
1.4 实例、用户及数据库
每台主机上各创建一个64位实例db2inst1，实例用户为db2inst1，所属组db2grp1，用户目录/home/db2inst1，隔离用户为db2fenc1，所属组db2ifenc1，用户目录/home/db2fadm1。在挂有卷组TDdatavg的主机实例db2inst1下创建数据库**data，用来存放分销系统数据；在挂有卷组CRMdatavg的主机实例db2inst1下创建数据库**crm，用来存放CRM系统数据。
2主机相关环境设置
2.1安装64位JDK
把DB2软件光盘包中的Developer Kit, Java Technology Edition (64-bit) for AIX 5L放入光驱中，按如下步骤安装：
# mount /cdrom
# smitty install 回车进入操作菜单（蓝色为选择或输入内容，回车进入下一层）
Install and Update Software
List Software and Related Information
Software Maintenance and Utilities
Software Service Management
……
Install Software
Update Installed Software to Latest Level (Update All)
Install Software Bundle
Update Software by Fix (APAR)
Install and Update from ALL Available Software
* INPUT device / directory for software [/cdrom/aix]
* INPUT device / directory for software /cdrom/aix
* SOFTWARE to install [_all_latest]
PREVIEW only? (install operation will NOT occur) no
COMMIT software updates? yes
SAVE replaced files? no
AUTOMATICALLY install requisite software? yes
EXTEND file systems if space needed? yes
OVERWRITE same or newer versions? no
VERIFY install and check file sizes? no
Include corresponding LANGUAGE filesets? yes
DETAILED output? no
Process multiple volumes? yes
ACCEPT new license agreements? yes
Preview new LICENSE agreements? no
回车即开始安装，显示OK表明安装成功
2.2创建相关用户和组
2.2.1实例用户和组
使用命令：mkgroup id=301 db2grp1
mkuser id=301 pgrp=db2grp1 db2inst1
2.2.2隔离用户和组
使用命令：mkgroup id=302 db2fadm1
mkuser id=302 pgrp=db2fadm1 db2fenc1
注：以后做ha时注意各主机用户和组的id保持一致。
2.3创建磁盘阵列卷组
2.3.1创建TDdatavg
# smitty vg回车进入操作菜单（蓝色为选择或输入内容，回车进入下一层）
List All Volume Groups
Add a Volume Group
Set Characteristics of a Volume Group
List Contents of a Volume Group
……
Add an Original Volume Group
Add a Big Volume Group
Add a Scalable Volume Group
VOLUME GROUP name [TDdatavg]
Physical partition SIZE in megabytes 256
* PHYSICAL VOLUME names [hdisk2]
Force the creation of a volume group? no
Activate volume group AUTOMATICALLY yes
at system restart?
Volume Group MAJOR NUMBER []
Create VG Concurrent Capable? no
回车执行操作，显示OK表明创建完成。
2.3.2创建CRMdatavg
步骤同创建TDdatavg，其中VOLUME GROUP name为CRMdatavg。
3 DB2安装
3.1 DB2安装
3.1.1 解压缩
把DB2 V8.2 光盘放入光驱，可看到目录下有一个ese.dbcs.tar.gz文件，解压此文件，进入存放解压文件的目录，执行命令：
gzip –c –d ese.dbcs.tar.gz | tar –xvf –
解压完成即生成DB2安装目录ese.dbcs。
3.1.2 安装
安装DB2有两种方式：
1）db2install
进入目录ese.dbcs，执行命令./db2install，出现提示后，输入 DB2.ESE(区分大小写)，执行安装。此种方式不创建das（DB2管理服务器）和实例，需要在安装完成后手工创建das和实例。
2）db2setup
进入目录ese.dbcs，执行命令：# ./db2setup，进入图形安装界面
安装完成，点击Finish，结束安装
3.2 DB2升级
下载fixpak12补丁，解压生成升级目录，进入目录执行命令installFixPak -y，即可完成所有包的升级工作。升级完成后需手工升级das，root用户执行/usr/opt/db2_08_01/instance/dasupdt。
3.3创建64位实例
root用户执行/usr/opt/db2_08_01/instance/db2isetup，启动创建实例程序，首先欢迎界面
安装完成，点击Finish，结束安装
实例创建完成，切换到db2inst1用户，可执行DB2命令。
执行命令db2level，显示如下信息：
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.112", "s060120", "U805924", and FixPak
"12".
Product is installed at "/usr/opt/db2_08_01".
可显示实例名称、位数、版本等基本信息。
3.4更新实例环境参数
用db2inst1用户登陆系统，执行以下命令：
db2set db2comm=tcpip
db2set db2codepage=1386
db2set db2territory=CN
3.5安装数据库许可证
使用db2inst1用户登陆系统运行如下步骤：
1、使用命令（db2licm -l）可以查看当前数据库的许可情况；
2、使用命令（db2licm –a ）可以为数据库添加数据库许可，许可证文件一般位于数据库产品介质的/ese.dbcs/db2/license/目录中，如：/ese.dbcs/db2/license/db2ese.lic；
3、使用命令（db2licm –a ）添加数据库许可后一般为只添加了一个CPU的许可，可以使用命令（db2licm –n DB2ESE ）添加更多的CPU许可，如在本系统中使用（db2licm –n DB2ESE 4）添加4个CPU的许可。
3.6创建数据库
数据库要创建在磁盘阵列上，在主机**p570a的TDdatavg上创建文件系统/tdhome，作为分销数据库的存放目录，在主机**p570b的CRMdatavg上创建文件系统/crmhome，作为CRM数据库的存放目录。
创建分销数据库，db2inst1用户登录**p570a，执行命令：
db2 create database **data on /tdhome using codeset GBK territoty CN
创建CRM数据库，db2inst1用户登录**p570b，执行命令：
db2 create database **crm on /crmhome using codeset GBK territoty CN
为了WebSphere数据源连接使用，还需要把本地数据库编目成远程数据库。
编目分销数据库，db2inst1用户登录**p570a，执行命令：
db2 catalog tcpip node **data remote ip server 50000
db2 catalog db **data as **data at node **data
编目CRM数据库，db2inst1用户登录**p570b，执行命令：
db2 catalog tcpip node **crm remote ip server 50000
db2 catalog db **crm as **crma at node **crm
4表空间设计
4.1表空间规划基本原则
1、系统表按系统默认存放于各系统表空间，采用系统管理方式。
2、应用新建表全部存放于新建的表空间。
3、新建表空间容器采用裸设备，以提高数据读写能力。
4、索引和数据存放于不同的表空间。
5、大型对象数据存放于单独的表空间。
6、静态表和动态表存放于不同的表空间。
7、大数据量、访问频繁的表可存放于单独表空间。
8、根据表行的长度和数据量确定表空间的页宽。
9、数据交换量比较大的表空间配置单独的缓冲池。
10、为保证表的重建及数据迁移的准确性，各地市保留原表空间名称、类型和管理方式等，空间适当增大。
4.2表空间规划
4.2.1导出原数据库信息
用db2look导出原数据库完整信息，根据导出的表空间信息，规划新建表空间，注意保留原表空间名称。db2look命令可参考如下格式：
db2look –d dbname –e –o Fname –l –f –I userID –w password –wrapper WrapperName
名称
类型
管理方式
容器
容量
页宽
描述
JX_CO
常规
数据库管理
/dev/rtdc1
16G（每容器4G）
4K
存放订单、配送单等数据交换较频繁的表
/dev/rtdc2
/dev/rtdc3
/dev/rtdc4
JX_DATA
常规
数据库管理
/dev/rtdd1
20G（每容器5G）
4K
存放基础数据等其他数据的表
/dev/rtdd2
/dev/rtdd3
/dev/rtdd4
JX_BLOB
大型
数据库管理
/dev/rtdb1
2G（每容器1G）
4K
存放大型对象数据表
/dev/rtdb2
JX_MIDDLE
常规
数据库管理
/dev/rtdm1
2G（每容器1G）
4K
存放中间数据表
/dev/rtdm2
JX_INDEX
常规
数据库管理
/dev/rtdi1
20G（每容器5G）
4K
存放索引表
/dev/rtdi2
/dev/rtdi3
/dev/rtdi4
JX_TMP
用户临时
系统管理
文件系统
自动分配
4K
存放临时表
WRAPPER1
用户临时
系统管理
文件系统
自动分配
4K
存放临时表
STPAGE32
系统临时
系统管理
文件系统
自动分配
32K
存放临时表
4.2.2 分销数据库
4.2.2 CRM数据库
名称
类型
管理方式
容器
容量
页宽
描述
CRMDSPACE
常规
数据库管理
/dev/rcrmd1
32G（每容器8G）
4K
存放销售数据表
/dev/rcrmd2
/dev/rcrmd3
/dev/rcrmd4
CRMBSPACE
常规
数据库管理
/dev/rcrmb1
4G（每容器2G）
4K
存放基础数据表
/dev/rcrmb2
CRMISPACE
常规
数据库管理
/dev/rcrmi1
24G（每容器6G）
4K
存放索引
/dev/rcrmi2
/dev/rcrmi3
/dev/rcrmi4
CRMTSPACE
用户临时
系统管理
文件系统
自动分配
4K
存放临时表
TEMPSPACE2
系统临时
系统管理
文件系统
自动分配
32K
存放临时表
4.3创建表空间
4.3.1创建裸设备
创建表空间之前需要先创建表空间使用的容器，为了提高性能，表空间容器统一使用裸设备，上表中列出的容器即为系统中的各裸设备。AIX中裸设备通过创建逻辑卷获得，裸设备名称即对应逻辑卷名称前加字母”r”。
以rtdd1为例，创建裸设备步骤如下：
# smitty lv
List All Logical Volumes by Volume Group
Add a Logical Volume
Set Characteristic of a Logical Volume
Show Characteristics of a Logical Volume
Remove a Logical Volume
Copy a Logical Volume
VOLUME GROUP name [TDdatavg]
Logical volume NAME [tdd1]
*VOLUME GROUP name TDdatavg
* Number of LOGICAL PARTITIONS [10]
PHYSICAL VOLUME names [hdisk2]
Logical volume TYPE [jfs2]
…… （其余选项按默认即可）
回车执行，显示OK创建成功
其余裸设备根据此步骤建立，各裸设备的参数如下表所示
卷组
物理卷
PP SIZE
逻辑卷
类型
裸设备
容量
LVs
TDdatavg
hdisk2
512M
tdc1
jfs2
rtdc1
4G
8
tdc2
jfs2
rtdc2
4G
8
tdc3
jfs2
rtdc3
4G
8
tdc4
jfs2
rtdc4
4G
8
tdd1
jfs2
rtdd1
5G
10
tdd2
jfs2
rtdd2
5G
10
tdd3
jfs2
rtdd3
5G
10
tdd4
jfs2
rtdd4
5G
10
tdb1
jfs2
rtdb1
1G
2
tdb2
jfs2
rtdb2
1G
2
tdi1
jfs2
rtdi1
5G
10
tdi2
jfs2
rtdi2
5G
10
tdi3
jfs2
rtdi3
5G
10
tdi4
jfs2
rtdi4
5G
10
tdm1
jfs2
rtdm1
1G
2
tdm2
jfs2
rtdm2
1G
2
CRMdatavg
hdisk3
1024M
crmd1
jfs2
rcrmd1
8G
8
crmd2
jfs2
rcrmd2
8G
8
crmd3
jfs2
rcrmd3
8G
8
crmd4
jfs2
rcrmd4
8G
8
crmb1
jfs2
rcrmb1
2G
2
crmb2
jfs2
rcrmb2
2G
2
crmi1
jfs2
rcrmi1
6G
6
crmi2
jfs2
rcrmi2
6G
6
crmi3
jfs2
rcrmi3
6G
6
crmi4
jfs2
rcrmi4
6G
6
注：表中的LVs是根据PP SIZE计算的，如果PP SIZE不是如表中设置，需要根据实际情况计算。
4.3.2修改逻辑卷和裸设备属性
为了可进行数据库操作，逻辑卷和裸设备所属用户和组均应为db2inst1、db2grp1。进入目录/dev，执行如下命令修改裸设备属性（以rtdd1为例）：
chown db2inst1:db2grp1 tdd1
chown db2inst1:db2grp1 rtdd1
其余逻辑卷和裸设备属性均按此命令修改。
4.3.3创建表空间
创建表空间需要指定对应的缓冲池，因此创建表空间前需要创建相应的缓冲池（创建缓冲池见第5章节）。创建表空间使用db2inst1用户，根据表空间类型和管理方式不同，建表语句如下：
常规类型、数据库管理（以JX_CO为例）
CREATE REGULAR TABLESPACE JX_CO PAGESIZE 4 K MANAGED BY DATABASE USING ( DEVICE '/dev/rtdc1' 1048576, DEVICE '/dev/rtdc2' 1048576,DEVICE '/dev/rtdc3' 1048576,DEVICE '/dev/rtdc4' 1048576 ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL CO DROPPED TABLE RECOVERY ON;
大对象类型、数据库管理（以JX_BLOB为例）
CREATE LARGE TABLESPACE JX_BLOB PAGESIZE 4 K MANAGED BY DATABASE USING ( DEVICE '/dev/rtdb1' 262144, DEVICE '/dev/rtdb2' 262144 ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL BLOB;
用户临时类型、系统管理（以JX_TMP为例）
CREATE USER TEMPORARY TABLESPACE JX_TMP PAGESIZE 4 K MANAGED BY SYSTEM USING ('/tdhome/db2inst1/NODE0000/SQL00001/SQLT0003.0' ) EXTENTSIZE 16 OVERHEAD 12.67 PREFETCHSIZE 16 TRANSFERRATE 0.18 BUFFERPOOL IBMDEFAULTBP ;
系统临时类型、系统管理（以STPAGE32为例）
CREATE SYSTEM TEMPORARY TABLESPACE STPAGE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('/tdhome/db2inst1/NODE0000/SQL00001/SQLT0004.0' ) EXTENTSIZE 16 OVERHEAD 12.67 PREFETCHSIZE 16 TRANSFERRATE 0.18 BUFFERPOOL IBMDEFAULTBP ;
其余表空间按类型区分，使用以上语句创建，注意表空间的名称、页宽、容器名称、容器大小、对应缓冲池名称等。
表空间也可通过DB2的控制中心创建。
4.4创建表
执行导出的原数据库的建表语句，由于表空间名称没有改变，不需要更改表和表空间的对应。
5缓冲池规划
5.1缓冲池规划基本原则
1、每个缓冲池对应单独的表空间。
2、根据表空间所需页宽确定缓冲池的页宽。
3、根据数据量和访问量确定缓冲池大小。
5.2缓冲池规划（以吉安为例）
数据库
缓冲池
页宽
页数
对应表空间
jadata
CO
4K
100000
JX_CO
DATA
4K
10000
JX_DATA
MIDDLE
4K
25000
JX_MIDDL
INDEX
4K
100000
JX_INDEX
BLOB
4K
1000
JX_BLOB
TDBP32
32K
1000
STPAGE32
jacrm
CRMD
4K
50000
CRMDSPACE
CRMI
4K
100000
CRMISPACE
CRMT
4K
50000
CRMTSPACE
CRMBP32
32K
1250
TEMPSPACE2
注：缓冲池的容量的设置要充分参考原数据库的设置，还要在实际使用过程中对缓冲池不断的监控调整，以达到最好性能。
5.3创建缓冲池
缓冲池要在表空间之前创建，以创建CO为例，使用db2inst1用户登陆系统，执行命令：
CREATE BUFFERPOOL CO IMMEDIATE SIZE 75000 PAGESIZE 4 K;
其余缓冲池均使用此命令创建，注意修改缓冲池的名称、大小、页宽等。
缓冲池也可通过DB2的控制中心创建。
6 DB2配置参数
6.1数据库管理器配置参数
数据库管理器配置参数在实例级影响系统资源，不受该实例下某一数据库的影响。
查看参数列表：
db2 get dbm cfg
修改某一参数：
db2 update dbm cfg using
dbm配置参数列表
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x0a00
CPU speed (millisec/instruction) (CPUSPEED) = 4.959615e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) = /usr/java14_64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = ON
SYSADM group name (SYSADM_GROUP) = DB2IADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /home/db2inst1
Database monitor heap size (4KB) (MON_HEAP_SZ) = 90
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 32768
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 60
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 4000
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 400
Agent pool size (NUM_POOLAGENTS) = 200(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time and redo index build (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = czycp550
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) = db2c_db2inst1
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4096
Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC
Node connection elapse time (sec) (CONN_ELAPSE) = 10
Max number of node connection retries (MAX_CONNRETRIES) = 5
Max time difference between nodes (min) (MAX_TIME_DIFF) = 60
db2start/db2stop timeout (min) (START_STOP_TIME) = 10
6.2数据库管理参数
数据库配置参数针对单个数据库，其参数指定分配给该数据库的资源量以及其他事项。
查看参数列表：
db2 get db cfg for dbname
修改某一参数：
db2 update db cfg for dbname using
TD数据库配置参数列表
Database Configuration for Database czv3td
Database configuration release level = 0x0a00
Database release level = 0x0a00
Database territory = CN
Database code page = 1386
Database code set = GBK
Database country/region code = 86
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =
Database page size = 4096
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = YES
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 2400
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 512
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 20000
Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 2048
SQL statement heap (4KB) (STMTHEAP) = 8192
Default application heap (4KB) (APPLHEAPSZ) = 2048
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 40
Lock timeout (sec) (LOCKTIMEOUT) = 30
Changed pages threshold (CHNGPGS_THRESH) = 40
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 8
Number of I/O servers (NUM_IOSERVERS) = 12
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 10000
Number of primary log files (LOGPRIMARY) = 12
Number of secondary log files (LOGSECOND) = 8
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = 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
Vendor options (VENDOROPT) =
Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF
HD数据库参数列表
Database Configuration for Database czv3hd
Database configuration release level = 0x0a00
Database release level = 0x0a00
Database territory = CN
Database code page = 1386
Database code set = GBK
Database country/region code = 86
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =
Database page size = 4096
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = YES
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 2400
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 512
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 20000
Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 2048
SQL statement heap (4KB) (STMTHEAP) = 8192
Default application heap (4KB) (APPLHEAPSZ) = 2048
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 40
Lock timeout (sec) (LOCKTIMEOUT) = 60
Changed pages threshold (CHNGPGS_THRESH) = 40
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 8
Number of I/O servers (NUM_IOSERVERS) = 12
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 10000
Number of primary log files (LOGPRIMARY) = 24
Number of secondary log files (LOGSECOND) = 16
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = 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
Vendor options (VENDOROPT) =
Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF
注：数据库的参数设置要充分参考原数据库的配置，还要在实际使用过程中对数据库不断的监控调整，以达到最好性能。
7创建联合数据库对象
7.1联合数据库对象概述
V3系统使用两个数据库TD和HD，为了查询方便，需要把两个数据库进行相互联合，使其成为逻辑上的一个数据库，因此需要分别在TD和HD上把对方创建为联合数据库对象。
联合体数据库及昵称等的创建也可通过db2look导出的原数据库的脚本执行完成。
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/24018/showart_176723.html |
|
免费注册
发表于 2006-09-26 13:48