Tag: DB2 Cheat Sheet

Listing instances

db2ilist 

Instance environment commands

Get instance

db2 get instance 

Set instance

set db2instance=<instance_name> 

db2start

db2start 

db2stop

db2stop 

Creating an instance

Before create a new instance, you need to log in with root. Instance id is not a root id or a root name.

Step1: Create an operating system user for instance.

useradd -u <ID> -g <group name> -m -d <user location> <user name> 
-p <password>  

Step2: Go to the DB2 instance directory in root user for create new instance.

cd /opt/ibm/db2/v10.1/instance  

Step3: Create instance using the syntax below:

./db2icrt -s ese -u <inst id> <instance name>

Arranging communication port and host for an instance

db2c_<inst name> <inst_port>/tcp

Updating an instance

Update an instance in normal mode

db2iupdt <inst_name> 

Update an instance in debugging mode

db2iupdt -D <inst_name> 

Upgrading an instance

db2iupgrade -d -k -u <inst_username> <inst_name>  

Dropping an instance

db2idrop -u <inst_username> <inst_name> 

Using other commands with instance

Check the current instance activated by database manager

db2 get instance  

See the current instance with operating bits and release version]

db2pd -inst | head -2  

Check the name of currently working instance]

db2 select inst_name from sysibmadm.env_inst_info    

Creating non-restrictive database

db2 create database <database name>

Creating restrictive database

db2 create database <db_name> restrictive 

Creating database with different user defined location

db2 create database '<db_name>' on '<data location>' dbpath on '<db_path_location>'  

Viewing local or system database directory files

db2 list database directory 

Activating database

db2 activate db <db_name> 

Deactivating database

db2 deactivate db <db_name>

Connecting to database

db2 connect to <database name> 

Verifying if database is restrictive

db2 get db cfg for <db_name> | grep -i restrict 

Configuring the database manager and the database

• Database Manager Configuration Parameters

Get the information of Instance Database manager

db2 get database manager configuration   

Update instance database manager

db2 update database manager configuration   

Reset previous configurations

db2 reset database manager configuration  

• Database Configuration Parameters

Get the information of Database

db2 get database configuration   

Update the database configuration

db2 update database configuration    

Reset the previously configured values in database configuration

db2 reset database configuration     

Dropping Database

db2 drop database <db_name>

Listing the available bufferpools in the current database directory

db2 select * from syscat.bufferpools   

Creating the bufferpool

db2 create bufferpool <bp_name> pagesize <size> 

Dropping the bufferpool

drop bufferpool <bp_name> 

Listing storagegroups

db2 select * from syscat.stogroups

Creating a storagegroup

db2 create stogroup  on ‘path’

Creating tablespace with stogroup

db2 create tablespace <tablespace_name>  using stogroup <stogroup_name> 

Altering a storagegroup

db2 alter stogroup  add ‘location’, ‘location’ 

Dropping folder path of storagegroup

db2 alter stogroup  drop ‘/path’ 

Rebalancing a tablespace

db2 alter tablspace <ts_name> rebalance   

Renaming a storagegroup

db2 rename stogroup <old_stg_name> to <new_stg_name>   

Dropping a storage group

Step 1: Before dropping any storagegroup, you can assign some different storagegroup for tablespaces.

db2 alter tablspace <ts_name> using stogroup <another sto_group_name>  

Step 2: Drop the existing stogroup

db2 drop stogorup <stogroup_name>   

Getting currently active schema

db2 get schema  

Setting another schema to current environment

db2 set schema=<schema_name>  

Creating a new Schema

db2 create schema <schema_name> authroization <inst_user> 

Creating Tables

db2 create table <schema_name>.<table_name>
(column_name column_type....) in <tablespace_name> 

Listing table details

db2 select tabname, tabschema, tbspace from syscat.tables    

Listing columns in a table

db2 describe table <table_name>    

Creating table with hidden column

db2 create table <tab_name> (col1 datatype,col2 datatype 
implicitly hidden)   

Inserting data values in table

db2 insert into <tab_name>(col1,col2,...)
 values(val1,val2,..)   

Retrieving values from table

db2 select * from <tab_name>    

Retrieving values from a table including hidden columns

db2 select col1,col2,col3 from <tab_name>    

See the data in the hidden columns

db2 describe table <table_name> show detail   

Altering the type of table columns

db2 alter table <tab_name> alter column <col_name> set data type <data_type>  

Altering column name

db2 alter table <tab_name> rename column <old_name> to <new_name>     

Dropping the tables

db2 drop table <tab_name>     

Delete the entire hierarchy of the table (including triggers and relation)

db2 drop table hierarchy <tab_name>

Creating database object aliases

db2 create alias <alias_name> for <table_name>    

Retrieving values using alias name of the table

db2 select * from <alias_name>    

Explanation of each constraint

• NOT NULL

db2 create table <table_name>(col_name col_type not null,..)  

• Unique constraints

db2 create table <tab_name>(<col> <col_type> not null unique, ...) 

• Primary key

db2 create table <tab_name>( ,.., primary
key ()) 

• Foreign key

db2 create table <tab_name>(<col> <col_type>,constraint 
<const_name> foreign key (<col_name>)  
                  reference <ref_table> (<ref_col>)  

• Checking constraint

db2 create table                                                      
 (  
  primary key (),                                                       
  constraint  check (condition or condition)  
 )

Dropping the constraint

Dropping UNIQUE constraint

db2 alter table <tab_name> drop unique <const_name>

Dropping primary key

db2 alter table <tab_name> drop primary key 

Dropping check constraint

db2 alter table <tab_name> drop check <check_const_name>  

Dropping foreign key

db2 alter table <tab_name> drop foreigh key <foreign_key_name>  

Creating indexes

db2 create unique index <index_name> on 
<table_name>(<unique_column>) include (<column_names..>)

Dropping indexes

db2 drop unique index <index_name> on 
<table_name>(<unique_column>) include (<column_names..>) 

Creating a BEFORE trigger

db2 create sequence <seq_name> 

Retrieving values from table

db2 select * from <tablename>

Creating an AFTER trigger

db2 create trigger <trigger_name> no cascade before insert on 
<table_name> referencing new as <table_object> for each row set
 <table_object>.<col_name>=nextval for <sequence_name> 

Dropping a trigger

db2 drop trigger <trigger_name> 

Creating a sequence

db2 create sequence <seq_name> 

Viewing the sequences

db2 value <previous/next> value for <seq_name>

Dropping the sequence

db2 drop sequence <seq_name>>

Creating a view

db2 create view <view_name> (<col_name>,
<col_name1...) as select <cols>.. 
from <table_name> 

Modifying a view

db2 alter view <view_name> alter <col_name> 
add scope <table_or_view_name> 

Dropping the view

db2 drop view <view_name> 

Creating a database and table for storing XML data

Create a database by issuing

db2 create database xmldb 

Create a well-formed XML file and create a table with the data type of the column as ‘XML’.

db2 “create table <schema>.<table>(col <datatype>, 
col <xml datatype>)” 

Insert xml values into table, well-formed XML documents are inserted into XML type column using SQL statement ‘INSERT’.

db2 “insert into <table_name> values(value1, value2)” 

Updating XML data in a table

db2 “update <table_name> set <column>=<value> where 
<column>=<value>” 

Backup

• Offline backup

List the active applications/databases

db2 list application  

Terminate Database Connection

db2 terminate 

Deactivate Database

db2 deactivate database one   

Take the backup file

db2 backup database <db_name> to <location>   

• Online backup

Check if the database is using circular or archive logging

db2 get db cfg for one | grep LOGARCH   

Updating logarchmeth1 with required archive directory

Make directories

mkdir backup 
mkdir backup/ArchiveDest   

Provide user permissions for folder

chown db2inst1:db2iadm1 backup/ArchiveDest 

Update configuration LOGARCHMETH1

db2 update database configuration for one using LOGARCHMETH1 
'DISK:/home/db2inst1/backup/ArchiveDest'

Take online backup

db2 backup database one online to 
/home/db2inst1/onlinebackup/ compress include logs  

Verify Backup

db2ckbkp <location/backup file>   

Listing the history of backup files

db2 list history backup all for one    

Restoring the database from backup

Restore the database from backup file

db2 restore database <db_name> from <location> 
taken at <timestamp>    

Roll forward all the logs located in the log directory, including the latest changes just before the disk drives failure.

db2 rollforward db <db_name> to end of logs and stop 

Creating and granting membership in roles

db2 create role <role_name> 

Granting role from DBADM to a particular table

db2 grant select on table <table_name> to role <role_name> 

Add users to a role

db2 grant role <role_name> to user <username> 

Role hierarchies

db2 grant role <roll_name> to role <role_name>