Common DB2 Commands

 

+

Search Tips   |   Advanced Search

 

  1. Set environment
  2. db2top
  3. Cycle DB2
  4. Find all databases for a certain instance
  5. runstats and reorg
  6. Connect to database (local)
  7. Attach to Node
  8. List tables
  9. View table space information
  10. Attach to db
  11. Find DB2 version
  12. Check for SVCENAME from database manager configuration
  13. Update SVCENAME if needed
  14. Set TCPIP communication protocol
  15. Check registry to TCPIP changes
  16. Stop Instance
  17. Stop (FORCE) Instance
  18. Start Instance
  19. Create Instance
  20. Delete Instance
  21. Check for active databases
  22. Create database
  23. Drop database
  24. Start/Activate database
  25. Deactivate database
  26. Delete tables
  27. Find table structure
  28. Catalog remote node
  29. Catalog remote database
  30. List catalog for db
  31. Delete db catalog
  32. Delete node catalog
  33. Enable a user account that has been disabled.
  34. DB2 Configuration Information
  35. Enable an event monitor for deadlocks
  36. Enable a statement event monitor to capture timeouts
  37. While the database is experiencing problems, take snapshots
  38. DB2 Configuration Files
  39. Memory

See also:

  1. IBM WebSphere Portal Web Content Manager and DB2 Tuning Guide

 

Set environment

if [ -f /data/db2inst1/sqllib/db2profile ]; then
. /data/db2inst1/sqllib/db2profile
fi

### List instances
db2ilist

### List current instance
db2 get instance

 

Cycle DB2

Log on as user db2inst1 and run...

dbstop
dbstart

 

db2top

If DB2 V9.1 FP6 is installed, you can run db2top to monitor the system. Typical usage:

db2top -d jcrdb

 

Find all databases for a certain instance

$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 6
 Database 1 entry:

 Database alias                       = CUSTDB
 Database name                        = CUSTDB
 Local database directory             = /data/db2inst1
 Database release level               = b.00
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Database 2 entry:

 Database alias                       = FDBKDB
 Database name                        = FDBKDB
 Local database directory             = /data/db2inst1
 Database release level               = b.00
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Database 3 entry:

 Database alias                       = JCRDB
 Database name                        = JCRDB
 Local database directory             = /data/db2inst1
 Database release level               = b.00
 Directory entry type                 = Indirect
 Catalog database partition number    = 0

 Database 4 entry:

 Database alias                       = COMMDB
 Database name                        = COMMDB
 Local database directory             = /data/db2inst1
 Database release level               = b.00
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Database 5 entry:

 Database alias                       = RELDB
 Database name                        = RELDB
 Local database directory             = /data/db2inst1
 Database release level               = b.00
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Database 6 entry:

 Database alias                       = LMDB
 Database name                        = LMDB
 Local database directory             = /data/db2inst1
 Database release level               = b.00
 Directory entry type                 = Indirect
 Catalog database partition number    = 0

 

runstats and reorg

Two of the database attributes which DB2 relies upon to perform optimally are the database catalog statistics and the physical organization of the data in the tables. Catalog statistics should be recomputed periodically during the life of the database, particularly after periods of heavy data modifications (inserts, updates, and deletes) such as a population phase. Due to the heavy contention of computing these statistics, it is best to perform this maintenance during off hours, periods of low demand, or when the portal is off-line. The DB2 runstats command is used to count and record the statistical details about tables, indexes and columns. I have used two techniques in our environment to compute these statistics. The form I recommend is:

db2 "runstats on table tableschema.tablename on all columns with distribution on all columns and sampled detailed indexes all allow write access"

These options allow the optimizer to determine optimal access plans for complex SQL. A simpler, more convenient technique for computing catalog statistics is:

db2 reorgchk update statistics on table all

For example...

db2 connect to CUSTDB user db2inst1 using db2inst1
db2 reorgchk update statistics on table all > fdbkdbout
db2rbind CUSTDB -l CUSTDBdb2rbind.out -u db2inst1 -p db2inst1

db2 connect to FDBKDB user db2inst1 using db2inst1
db2 reorgchk update statistics on table all > fdbkdbout
db2rbind FDBKDB -l FDBKDBdb2rbind.out -u db2inst1 -p db2inst1

db2 connect to JCRDB user db2inst1 using db2inst1
db2 reorgchk update statistics on table all > jcrdbout
db2rbind JCRDB -l JCRDBdb2rbind.out -u db2inst1 -p db2inst1

db2 connect to COMMDB user db2inst1 using db2inst1
db2 reorgchk update statistics on table all > commdbout
db2rbind COMMDB -l COMMDBdb2rbind.out -u db2inst1 -p db2inst1

db2 connect to RELDB user db2inst1 using db2inst1
db2 reorgchk update statistics on table all > reldbout
db2rbind RELDB -l RELDBdb2rbind.out -u db2inst1 -p db2inst1

db2 connect to LMDB user db2inst1 using db2inst1
db2 reorgchk update statistics on table all > lmdbout
db2rbind LMDB -l LMDBdb2rbind.out -u db2inst1 -p db2inst1

Not only does this command count and record some of the same catalog statistics, it also produces a report that can be reviewed to identify table organization issues. However, I have found instances where this produces insufficient information for the optimizer to select an efficient access plan for complex SQL, particularly for queries of the JCR database. If you want a technique that has the same convenience of the reorgchk command and provides the detailed statistics preferred by the optimizer, use this command:

db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table', concat(rtrim(tabSchema), concat('.',concat(rtrim(tabname), ' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'"

db2 -v -f "runstats.db2"

Reorganizing all the tables would be over-kill in a production environment. To determine which tables might benefit from reorganization, use the command:

db2 reorgchk current statistics on table all > "reorgchk.txt"

The tables that need reorganization are indicated by a * in at least one of the three columns next to the table name. For those tables that require reorganization, use the following command:

db2 reorg table tableschema.tablename

 

Connect to database (local)

db2 connect to db [user username using user-pw]

 

Attach to Node

db2 attach to MYNODE user wasuser using wasuser1

 

List tables

db2 list tables [for {user | all | system | schema schema-name}] [show detail]

 

View table space information

db2 list tablespaces [show detail

 

Attach to db

db2 attach to MYDB user wasuser using wasuser1

 

Find DB2 version

db2level

db2licm l

 

Check for SVCENAME from database manager configuration

db2 get dbm cfg

 

Update SVCENAME if needed (require restart of db2 instance)

db2 update dbm cfg using SVCENAME DB2_svr20aix

 

Set TCPIP communication protocol (require restart of db2 instance)

db2set DB2COMM=tcpip

 

Check registry to TCPIP changes

db2set -a

db2set -lr

 

Stop Instance

db2stop

 

Stop (FORCE) Instance

db2stop force

 

Start Instance

db2start

 

Create Instance

db2icrt u

 

Delete Instance

db2idrop

 

Check for active databases

db2 list active databases

 

Create database

db2 create db

 

Drop database

db2 drop db

 

Start/Activate database

db2 activate db

 

Deactivate database

db2 deactivate db

 

Delete tables

db2 drop table

 

Find table structure

db2 describe table

 

Catalog remote node

db2 catalog tcpip node remote server

 

Catalog remote database (node must be calatalog first)

db2 catalog db at node

 

List catalog for db

db2 list db directory show detail

 

Delete db catalog

db2 uncatalog db dbname

 

Delete node catalog

db2 uncatalog node nodename

 

Enable a user account that has been disabled.

db2 connect to hall_stg user wasuser using wasuser1
db2 "update userreg set status=1 where LOGONID='wcsadmin'"

 

DB2 Configuration Information

db2level > tempfolder/db2level.txt
db2 get dbm cfg > tempfolder/db2dbmcfg.txt
db2 get db cfg for dbname > tempfolder/db2cfg.txt

Connect to the database and issue the following commands:

db2 "export to tempfolder/packages.csv of del select * from syscat.packages order by last_bind_time asc"
db2 "export to tempfolder/tables.csv of del select * from syscat.tables order by stats_time asc"
db2 "export to tempfolder/indexes.csv of del select * from syscat.indexes order by stats_time asc"
db2 "reorgchk current statistics" > tempfolder/reorgchk.txt
db2 "select * from syscat.bufferpools" > bufferpools.txt
db2 "select tbspace,bufferpoolid from syscat.tablespaces" > buffpoolmap.txt
db2 list tablespaces show detail > tblspaces.txt

 

Enable an event monitor for deadlocks

Enable an event monitor to capture information about deadlocks ( SQL0911N Reason code 2 ). Only enable this monitor when requested by the support analyst or if the following error is printed to SystemOut.log:

[IBM][CLI Driver][DB2/LINUXPPC] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001

Given that the impact on performance by enabling this monitor is negligible, IBM recommends that you enable the monitor and leave it enabled until the problem reoccurs. If the deadlocks occur intermittently during the day, let the event monitor run for period of 24 hours.

Complete the following steps to enable an event monitor for deadlocks:

  1. Create a temporary directory where the event monitor files will be created, for example mkdir tempfolder/deadlock

  2. Enable timestamp monitor switch to collect start/end and execution times:

    db2 update monitor switches using timestamp on

  3. Create an event monitor

    db2 "create event monitor wcdeadlck for deadlocks with details history values write to file 'tempfolder/deadlock'"

  4. Enable the event monitor...

    db2 "set event monitor wcdeadlck STATE=1"

  5. After capturing the error, the event monitor needs to be disabled and the output formatted to a file: Disable the event monitor...

    db2 "set event monitor wcdeadlck STATE=0"

  6. Format the event monitor logs...

    db2evmon -db dbname -evm wcdeadlck > tempfolder/evdeadlock.txt

 

Enable a statement event monitor to capture timeouts

Use a statement event monitor to capture timeouts ( SQL0911N Reason code 68 ). Only enable this monitor when requested by the support analyst or if the following error is printed to SystemOut.log:

[IBM][CLI Driver][DB2/LINUXPPC] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

A statement event monitor will log all the statements being run on the database. As there will be some performance degradation, you should only enable this monitor on a production system while the database is experiencing timeout problems or when requested by the support analyst. You should disable the event monitor as soon as the data concerning the problem has been collected.

Also note that as all the statements are logged, the size of the file will grow rapidly. Depending on the number of statements, on a typical production database, the file can grow at a pace of one gigabyte every ten minutes. Ensure that there is enough space on the file system to contain the event monitor. Alternatively, the File Options of the CREATE EVENT MONITOR Statement can be used to specify a maximum file size.

Complete the following steps to enable an event monitor for statements:

  1. Create a temporary directory where the event monitor files will be created, for example mkdir tempfolder/timeout

  2. Enable timestamp monitor switch to collect start/end and execution times:

    db2 update monitor switches using timestamp on

  3. Create the event monitor...

    db2 "create event monitor wctimeout for transactions, statements write to file 'tempfolder/timeout'"

  4. Enable the event monitor...

    db2 "set event monitor wctimeout STATE=1"

After capturing the error, the event monitor needs to be disabled and the output formatted to a file:

  1. Disable the event monitor...

    db2 "set event monitor wctimeout STATE=0"

  2. Format the event monitor logs...

    db2evmon -db dbname -evm wctimeout > tempfolder/evtimeout.txt

 

While the database is experiencing problems, take snapshots

  1. db2 update monitor switches using lock on statement on bufferpool on uow on sort on table on

  2. db2 reset monitor all

  3. db2 get snapshot for all on dbname > tempfolder/db2snap.yyyymmddhhmm.txt (repeat this step in two-minute intervals for about ten minutes)

  4. db2 update monitor switches using lock off statement off bufferpool off uow off sort off table off

 

DB2 Configuration Files

 

Memory

See: The DB2 UDB memory model 

### Available
 db2 attach to db2inst1  db2 get dbm cfg show detail > dbm_cfg.txt

### Current Usage
$ db2mtrk -i -v Tracking Memory on: 2009/03/19 at 11:27:21
 Memory for instance

   Database Monitor Heap is of size 851968 bytes
   Other Memory is of size 8781824 bytes
   Total: 9633792 bytes

On db2-01, we have 66MB of shared memory: 4KB * (16448)

To find out how much memory is allocated to main buffer pools, issue: 

$ db2 "SELECT * FROM SYSCAT.BUFFERPOOLS"
 BPNAME            BUFFERPOOLID DBPGNAME  NPAGES  PAGESIZE  ESTORE NUMBLOCKPAGES BLOCKSIZE    IBMDEFAULTBP      1            -          -2     8192      N      0             0   ICMLSFREQBP4      2            -          1000   4096      N      0             0  ICMLSVOLATILEBP4  3            -          8000   4096      N      0             0 ICMLSMAINBP32     4            -          8000   32768     N      0             0 CMBMAIN4          5            -          1000   4096      N      0             0

While the sizes of most of the memory pools are pre-determined by their configuration settings, the sizes for the following two memory pools are dynamic by default:

Package Cache: pckcachesz = maxappls * 8
Catalog Cache: catalogcache_sz = maxappls * 4
Maximum number of active applications: maxappls = AUTOMATIC

Setting maxappls to AUTOMATIC has the effect of allowing any number of connected applications. DB2 will dynamically allocate the resources it needs to support new applications. Therefore, the sizes for package cache and catalog can vary depending on the value of maxappls.

In addition to the above parameters, there is another parameter which also affects the amount of database shared memory. It is the database_memory parameter. The default value for this parameter is AUTOMATIC. This means DB2 will calculate the amount of database memory needed for the current configuration, based on the sizes of the various memory pools listed above. In addition, DB2 will also allocate some additional memory for overflow buffer. The overflow buffer is used to satisfy peak memory requirements for any heap in the database shared memory region whenever a heap exceeds its configured size.

If database_memory is set to a number, then the larger of database_memory or the sum of the individual memory pools is used.

If database_memory is set to AUTOMATIC, use the following commands to display its value:

db2 connect to dbname user userid using pwd
db2 get db cfg for dbname show detail

for example...

db2 connect to JCRDB user db2inst1 using db2inst1
db2 get db cfg for JCRDB show detail > jcrdb_cfg.txt

Use the db2mtrk tool to display the amount of memory that is currently being used:

db2mtrk -i -d -v > db2mtrk.txt