DB2验证数据库

精华

小牛编辑

213浏览

2023-03-14

运行create database 命令后，可以使用以下命令检查是否已创建数据库:

db2 list db directory

验证数据库是否具有限制性

通过使用上述命令，只能检查数据库列表。无法确定数据库是限制性的还是非限制性的。

使用以下命令检查数据库是否具有限制性。

语法:

db2 get db cfg for <db_name> | grep -i restrict

示例:

假设要检查 XYZ 数据库是否是限制性数据库:

db2 get db cfg for XYZ | grep -i restrict

得到类似以下结果：

$ db2 get db cfg for XYZ | grep -i restrict
 Restrict access                                         = NO

可以看到它不是限制性的。现在检查XYZ2是否是限制性数据库:

db2 get db cfg for XYZ2 | grep -i restrict

得到类似以下结果：

$ db2 get db cfg for XYZ | grep -i restrict
 Restrict access                                         = YES

可以看到XYZ2 是限制性数据库。

配置数据库管理器和数据库

实例也称为数据库管理器。实例配置(数据库管理器配置)存储在名为db2system 的文件中，与数据库相关的配置存储在名称为“SQLDBCON”的文件中。无法直接编辑这些文件。这些文件只能使用调用API的工具进行编辑。

可以使用以下命令配置数据库管理器。

要获取实例数据库管理器的信息:

语法:

db2 get database manager configuration     

## 或者 

db2 get dbm cfg

得到类似下面的结果：

$ db2 get dbm cfg

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x1400

 CPU speed (millisec/instruction)             (CPUSPEED) = 1.180861e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 Max number of concurrently active databases     (NUMDB) = 32
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = /home/db2inst1/sqllib/db2dump/ $m
 Current member resolved DIAGPATH                        = /home/db2inst1/sqllib/db2dump/DIAG0000/
 Alternate diagnostic data directory path (ALT_DIAGPATH) =
 Current member resolved ALT_DIAGPATH                    =
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 0

 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) = OFF

 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
 Cluster manager                                         =

 Database manager authentication        (AUTHENTICATION) = SERVER
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 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) = AUTOMATIC(90)
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Global instance memory (% or 4KB)     (INSTANCE_MEMORY) = AUTOMATIC(519452)
 Member instance memory (% or 4KB)                       = GLOBAL
 Agent stack size                       (AGENT_STACK_SZ) = 1024
 Sort heap threshold (4KB)                  (SHEAPTHRES) = 0

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 65535
 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

 Priority of agents                           (AGENTPRI) = SYSTEM
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC(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) = yiibai
 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

 SSL server keydb file                   (SSL_SVR_KEYDB) =
 SSL server stash file                   (SSL_SVR_STASH) =
 SSL server certificate label            (SSL_SVR_LABEL) =
 SSL service name                         (SSL_SVCENAME) =
 SSL cipher specs                      (SSL_CIPHERSPECS) =
 SSL versions                             (SSL_VERSIONS) =
 SSL client keydb file                  (SSL_CLNT_KEYDB) =
 SSL client stash file                  (SSL_CLNT_STASH) =

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0

 Number of FCM buffers                 (FCM_NUM_BUFFERS) = AUTOMATIC(4096)
 FCM buffer size                       (FCM_BUFFER_SIZE) = 32768
 Number of FCM channels               (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
 FCM parallelism                       (FCM_PARALLELISM) = AUTOMATIC(4)
 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

 WLM dispatcher enabled                 (WLM_DISPATCHER) = NO
 WLM dispatcher concurrency            (WLM_DISP_CONCUR) = COMPUTED
 WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO
 WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5

 Communication buffer exit library list (COMM_EXIT_LIST) =
 Current effective arch level         (CUR_EFF_ARCH_LVL) = V:11 R:1 M:3 F:3 I:0 SB:0
 Current effective code level         (CUR_EFF_CODE_LVL) = V:11 R:1 M:3 F:3 I:0 SB:0

 Keystore type                           (KEYSTORE_TYPE) = NONE
 Keystore location                   (KEYSTORE_LOCATION) =

$

更新实例数据库管理器
语法:

db2 update database manager configuration     

## 或者 

db2 update dbm cfg

重置以前的配置
语法:

db2 reset database manager configuration    

## 或者 

db2 reset dbm cfg

数据库配置参数

获取数据库的信息
语法:

db2 get database configuration     

## 或者  

db2 get db cfg

更新数据库配置
语法:

db2 update database configuration      

## 或者

db2 update db cfg

在数据库配置中重置先前配置的值
语法:

db2 reset database configuration       

## 或者

db2 reset db cfg

检查当前活动数据库的大小
语法:

db2 "call get_dbsize_info(?,?,?,-1)"