|
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 |
db2val |
|
DB2 level (version etc.) |
db2level |
|
Licenses |
db2licm -l |
|
Creating a new instance |
db2icrt instance name |
|
List instances |
db2ilist |
|
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) |
|
Example |
db2set DB2COMM=tcpip |
|
TABLESPACES |
Logical unit between Tables and disk space |
|
BUFFERPOOL |
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 |
|
Example |
db2 alter bufferpool ibmdefaultbp size 500 |
|
Page |
Is a smallest unit to manipulate data. Sizes( 4k, 8k, 16k, 32k) |
|
Extent |
A bunch of pages in same table (Tables do not share extents) |