注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

金生丽水,雨润轩德

 
 
 

日志

 
 

IBM p570小机上对DB2数据库中数据的迁移案例(2)  

2008-04-18 11:33:35|  分类: 数据库优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
BTW:还是CU上的blog好啊,空间大,没有想到163这么小气,复制人家的文章还要分割,比较痛苦。

6 DB2配置参数

6.1数据库管理器配置参数

     数据库管理器配置参数在实例级影响系统资源,不受该实例下某一数据库的影响。

     查看参数列表:

     db2 get dbm cfg

     修改某一参数:

     db2 update dbm cfg using <parameter-name> <value>

 

     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 <parameter-name> <value>

 

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系统使用两个数据库TDHD,为了查询方便,需要把两个数据库进行相互联合,使其成为逻辑上的一个数据库,因此需要分别在TDHD上把对方创建为联合数据库对象。

 
联合体数据库及昵称等的创建也可通过db2look导出的原数据库的脚本执行完成。

  评论这张
 
阅读(1848)| 评论(0)
推荐

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018