DB2 administration

List DB2 databases:

db2 list db directory

Checking connection to DB2:

db2 connect to testdb

Linking a catalog after installing a new version

db2 catalog db dbname

Disconnect existing connections to DB

db2 terminate

Create a DB

db2 create db dbname

Deleting DB

db2 drop db dbname

List all tables

db2 list tables for all

Lista all tables for schema

db2  list tables for schema testschema

Validation of DB2 installation


DB2 level (version etc.)



db2licm -l

Creating a new instance

db2icrt instance name

List instances


Choosing an active instance (Windows)

set db2instance=instance name  

Choosing an active instance (Unix/Linux)

change user to instance owner: sudo su – db2inst1

Shows an active instance

db2 get instance

Deleting an instance

db2idrop instance name

Creating a tablespace using default bufferpool

db2 crate tablespace name managed by database using (file ‘\path\filename’ 1000) bufferpool ibmdefaultbp

Create a table with columns in tablespace

db2 create table NAME <col1 int, col2 int> in TABLESPACE NAME

Create a table with index

db2 create table NAME <col1 int, col2 int> in TABLESPACE NAME index in IndexTABLESPACE NAME

DB Manager configuration (not command)

dbm cfg

Get DB manager configuration

db2 get dbm cfg

Change Config Manager parameter

db2 update dbm cfg using PARAMETER option ( like INTRA_PARALLEL no )

DB configuration file (not command)

db cfg

Get configuration file for Database

db2 get db cfg for testdb

Change Config file parameter

db2  update db cfg for TESTDB using PARAMETER option ( like LOGFILSIZ 1234 )

STMM (self-tuning memory manager)

db2  update db cfg for TESTDB using SELF_TUNING_MEM ON/OFF

DB2 Profile registry settings (To all instances)

db2set -all

List all parameters can be set

db2set -lr

Changing  Profile registry settings parameters

db2set PARAMETER=option (like DB2_CODEPAGE=1208)


db2set DB2COMM=tcpip


Logical unit between Tables and disk space


Memory cache

Executing a script

db2 -tvf script.sql                 t-terminate, v-verbose, f-file

Executing a script using !-statement terminator

db2 -td! -vf script.sql         ( CONNECT TO DB! )

Snapshot Bufferpools

db2 get snapshot for all bufferpools

DB Manager Memory information for instance

db2mtrk -i -v

Help of DBM memory monitoring tool

db2mtrk -h

Memory of databese

db2mtrk -d -v

select * from syscat.bufferpools

Alter bufferpool size

db2 alter bufferpool bufferpoolname size pages


db2 alter bufferpool ibmdefaultbp size 500


Is a smallest unit to manipulate data. Sizes( 4k, 8k, 16k, 32k)


A bunch of pages in same table (Tables do not share extents)