AIX小机上建立DB2数据库.docx

1总体规划1.1 主机环境两台主机，型号均为IBM P570，主机名分别为*p570a和*p570b（*为地市名简称），安装操作系统 AIX 5L（64位内核），版本V5.3，维护级别升级到5300-04。1.2 数据库产品数据库采用DB2 ENTERPRISE SERVER EDITION v8.2，补丁打到fixpak12，补丁下载地址：/software/data/db2/udb/support/downloadv8_aix5.html#r121.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 db2inst12.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 SQL08024with level identifier 03050106.Informational tokens are DB2 v12, s060120, U805924, and FixPak12.Product is installed at /usr/opt/db2_08_01.可显示实例名称、位数、版本等基本信息。3.4更新实例环境参数用db2inst1用户登陆系统，执行以下命令：db2set db2comm=tcpipdb2set db2codepage=1386db2set db2territory=CN3.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 50000db2 catalog db *data as *data at node *data编目CRM数据库，db2inst1用户登录*p570b，执行命令：db2 catalog tcpip node *crm remote ip server 50000db2 catalog db *crm as *crma at node *crm4表空间设计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 WrapperNameCRM数据库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 VolumeVOLUME 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创建成功 其余裸设备根据此步骤建立，各裸设备的参数如下表所示注：表中的LVs是根据PP SIZE计算的，如果PP SIZE不是如表中设置，需要根据实际情况计算。4.3.2修改逻辑卷和裸设备属性为了可进行数据库操作，逻辑卷和裸设备所属用户和组均应为db2inst1、db2grp1。进入目录/dev，执行如下命令修改裸设备属性（以rtdd1为例）：chown db2inst1:db2grp1 tdd1chown 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缓冲池规划（以吉安为例）注：缓冲池的容量的设置要充分参考原数据库的设置，还要在实际使用过程中对缓冲池不断的监控调整，以达到最好性能。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) = 106.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 (4