Contents
DB2 Instance
# Create an instance: db2icrt db2inst1 # Drop an instance: db2idrop db2inst1 # List all instances: db2ilist # Start an instance: db2start # Stop an instance: db2stop # Restart an instance db2 force applications all sleep 10 db2 stop sleep 10 ps -ef|grep db2inst1 db2 start
Env variables
* Source env variables:
/home/db2inst1/sqllib/db2profile # source db2cshrc from c shell instead.
* DB2INSTANCE
echo $DB2INSTANCE db2inst1
* db2 profile registry
-global registry
-instance registry
# View all registry db2set -lr # Set global env db2set DB2INSTPROF=<val> -g # Set instance env db2set DB2INSTPROF=<val> -i MyInst
* dbm cfg (database mgr cfg file)
# get dbm cfg db2 get dbm cfg db2 qet db cfg for <dbname> # update dbm cfg db2 update dbm cfg using <parm> <val> db2 update db cfg for <db> using <parm> <val>
Update Parameters
db2 update db cfg for itimdb using database_memory 40000 db2 alter bufferpool IBMDEFAULTBP size automatic db2set DB2COMM
Database
Connect
* System db directory
Contains all dbs you can connect to from your db2 system. Stored at instance level.
db2 list db directory
Local: indirect
Remote: remote
* Add db directory entry
db2 catalog db <db_name> as <alias> at node <node_name>
* Local db directory
Contains info about local dbs.
db2 list db directory on <path>
* Node directory
Contains all connectivity info for remote databases. Supports only tcpip in ver8.
db2 list node directory
db2 catalog tcpip node mynode remote 9.26.138.35 server 60000* DCS directory
Contains connectivity info for host dbs on zSeries or iSeries machines.
db2 list dcs directory
db2 catalog dcs db as db1gDatabases
* Create database
db2 create database temporal db2 connect to mydb2 user peenv7 db2 list tables db2 list tablespaces
Tablespace
• Catalog (SYSCATSPACE)
• Temp (TEMPSPACE1)
• User space (USERSPACE1)
Example
db2ilist db2 get instance set DB2INSTANCE=MyInstance db2 list db directory db2 create database temporal db2 connect to mydb2 user peenv7 db2 list tables db2 list tablespaces // backup instance db2 qet dbm cfq > dbmcfq.bak db2set -all > db2set.bak db2 list db directory > systemdbdir.bak db2 list node directory > nodedir.bak db2 list dcs directory > dcs.bak //end backup db2 drop database temporal
CLP
* Verify installation
select count(*) from syscat.tables select count(*) from sysibm.tables select count(*) from sysibm.sysdummy1 describe table syscat.tables
Help
db2 ? sql0100 | more
Scripting
select text from syscat.views select text,tabname from syscat.checks select text from syscat.procedures select text from syscat.triggers