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 <dbname>

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

Ver também