DB2: Comandos Uteis

db2level
Obtendo a versão do DB2

[db2admin@db2server ~]$ db2level DB21085I Instance "db2admin" uses "64" bits and DB2 code release "SQL09070" with level identifier "08010107". Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix Pack "0". Product is installed at "/opt/IBM/DB2".

db2lic
Obtendo as informações de licença

[db2admin@db2server ~]$ db2licm -l Product name:                    "DB2 Workgroup Server Edition" License type:                    "Restricted" Expiry date:                     "Permanent" Product identifier:              "db2wse" Version information:             "9.7" Max number of Value Units:       "480" Max amount of memory (GB):       "4"

db2set
Mostra o valor das variáveis do registro

[db2admin@db2server ~]$ db2set -all [i] DB2COMM=tcpip [g] DB2SYSTEM=db2server.ebasso.net [g] DB2INSTDEF=db2admin [g] DB2ADMINSERVER=dasusr1

db2 get dbm cfg
Mostra o valor das variáveis do Database Manager

[db2admin@db2server ~]$ db2 get dbm cfg Database Manager Configuration Node type = Database Server with local and remote clients Database manager configuration release level           = 0x0d00 CPU speed (millisec/instruction)            (CPUSPEED) = 1.968101e-07 Max number of concurrently active databases    (NUMDB) = 8 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/db2admin/sqllib/java/jdk64 Diagnostic error capture level             (DIAGLEVEL) = 3 Notify Level                             (NOTIFYLEVEL) = 3 Diagnostic data directory path              (DIAGPATH) = /home/db2admin/sqllib/db2dump 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) = ON SYSADM group name                        (SYSADM_GROUP) = DB2ADMIN 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                          (CLUSTER_MGR) = 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/db2admin Database monitor heap size (4KB)         (MON_HEAP_SZ) = 1024 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(1048576) Backup buffer default size (4KB)           (BACKBUFSZ) = 1024 Restore buffer default size (4KB)          (RESTBUFSZ) = 1024 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) = 32767 Query heap size (4KB)                  (QUERY_HEAP_SZ) = 1000 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) = 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_db2admin 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 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024) No. of int. communication channels  (FCM_NUM_CHANNELS) = AUTOMATIC(512) db2start/db2stop timeout (min)       (START_STOP_TIME) = 10

db2 get db cfg for dbname
Mostra o valor das variáveis para uma Database

[db2admin@db2server ~]$ db2 get db cfg for  [db2admin@db2server ~]$ db2 get db cfg for STSC Database Configuration for Database STSC Database configuration release level                   = 0x0d00 Database release level                                 = 0x0d00 Database territory                                     = US Database code page                                      = 1208 Database code set                                      = UTF-8 Database country/region code                           = 1 Database collating sequence                            = IDENTITY Alternate collating sequence             (ALT_COLLATE) = Number compatibility                                   = OFF Varchar2 compatibility                                 = OFF Date compatibility                                     = OFF Database page size                                     = 4096 Dynamic SQL Query management          (DYN_QUERY_MGMT) = DISABLE Statement concentrator                     (STMT_CONC) = OFF Discovery support for this database      (DISCOVER_DB) = ENABLE Restrict access                                        = NO 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 Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN Backup pending                                         = NO All committed transactions have been written to disk    = 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 Self tuning memory                    (SELF_TUNING_MEM) = ON Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(331200) Database memory threshold              (DB_MEM_THRESH) = 10 Max storage for lock list (4KB)             (LOCKLIST) = AUTOMATIC(6200) Percent. of lock lists per application      (MAXLOCKS) = AUTOMATIC(60) Package cache size (4KB)                  (PCKCACHESZ) = AUTOMATIC(1656) Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(21535) Sort list heap (4KB)                        (SORTHEAP) = AUTOMATIC(4307) Database heap (4KB)                           (DBHEAP) = AUTOMATIC(2460) Catalog cache size (4KB)             (CATALOGCACHE_SZ) = 300 Log buffer size (4KB)                       (LOGBUFSZ) = 256 Utilities heap size (4KB)               (UTIL_HEAP_SZ) = 51481 Buffer pool size (pages)                    (BUFFPAGE) = 1000 SQL statement heap (4KB)                    (STMTHEAP) = AUTOMATIC(4096) Default application heap (4KB)            (APPLHEAPSZ) = AUTOMATIC(256) Application Memory Size (4KB)            (APPL_MEMORY) = AUTOMATIC(40000) Statistics heap size (4KB)              (STAT_HEAP_SZ) = AUTOMATIC(4384) Interval for checking deadlock (ms)        (DLCHKTIME) = 10000 Lock timeout (sec)                       (LOCKTIMEOUT) = -1 Changed pages threshold               (CHNGPGS_THRESH) = 80 Number of asynchronous page cleaners  (NUM_IOCLEANERS) = AUTOMATIC(3) Number of I/O servers                  (NUM_IOSERVERS) = AUTOMATIC(3) 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(40) Average number of active applications      (AVG_APPLS) = AUTOMATIC(1) Max DB files open per application           (MAXFILOP) = 61440 Log file size (4KB)                        (LOGFILSIZ) = 1024 Number of primary log files               (LOGPRIMARY) = 13 Number of secondary log files              (LOGSECOND) = 4 Changed path to log files                 (NEWLOGPATH) = Path to log files                                      = /home/db2admin/db2admin/NODE0000/SQL00001/SQLOGDIR/ 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 Group commit count                         (MINCOMMIT) = 1 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 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 HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0 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 Auto deletion of recovery objects   (AUTO_DEL_REC_OBJ) = OFF TSM management class                   (TSM_MGMTCLASS) = TSM node name                           (TSM_NODENAME) = TSM owner                                  (TSM_OWNER) = TSM password                            (TSM_PASSWORD) = Automatic maintenance                     (AUTO_MAINT) = ON   Automatic database backup            (AUTO_DB_BACKUP) = OFF Automatic table maintenance         (AUTO_TBL_MAINT) = ON     Automatic runstats                  (AUTO_RUNSTATS) = ON       Automatic statement statistics  (AUTO_STMT_STATS) = ON     Automatic statistics profiling    (AUTO_STATS_PROF) = OFF Automatic profile updates        (AUTO_PROF_UPD) = OFF Automatic reorganization              (AUTO_REORG) = OFF Auto-Revalidation                         (AUTO_REVAL) = DEFERRED Currently Committed                       (CUR_COMMIT) = ON CHAR output with DECIMAL input        (DEC_TO_CHAR_FMT) = NEW Enable XML Character operations       (ENABLE_XMLCHAR) = YES WLM Collection Interval (minutes)    (WLM_COLLECT_INT) = 0 Monitor Collect Settings Request metrics                      (MON_REQ_METRICS) = BASE Activity metrics                     (MON_ACT_METRICS) = BASE Object metrics                       (MON_OBJ_METRICS) = BASE Unit of work events                     (MON_UOW_DATA) = NONE Lock timeout events                  (MON_LOCKTIMEOUT) = NONE Deadlock events                         (MON_DEADLOCK) = WITHOUT_HIST Lock wait events                        (MON_LOCKWAIT) = NONE Lock wait event threshold              (MON_LW_THRESH) = 5000000 SMTP Server                              (SMTP_SERVER) =