DB2 : some basic commands

DB2 : some basic commands

http://www.ibm.com/developerworks/data/library/techarticle/dm-0503melnyk/

Syntax of the db2 command

db2 command

CLP option flags

Option	Description (default)
-a	Display SQLCA data. (OFF)
-c	Commit SQL statements automatically. (ON)
-e{c|s}	Write SQLCODE or SQLSTATE data to standard output. (OFF)
-ffilename	Accept input from the specified file (command script). If other options are specified in combination with the -f option, the -f option must be specified last. (OFF)
-lfilename	Write log records to the specified file. This file contains a history of executed commands and statements. If the file already exists, new information is appended. If other options are specified in combination with the -l option, the -l option must be specified last. (OFF)
-n	Preserve any trailing blank space characters that precede a newline character within a delimited token. For example: create table t1 (c1 varchar(30)); insert into t1 values ('123789'); -- three blanks spaces follow '123 update command options using n on; insert into t1 values ('123789'); select * from t1;   C1 ------------------------------ 123 789 123 789   2 record(s) selected.</span> The -n option must be used with the -t option. (OFF)
-o	Write both data and messages to standard output. (ON)
-p	Display a prompt when in CLP interactive mode. (ON)
-rfilename	Redirect output (excluding messages and error codes) to the specified file. If the file already exists, new information is appended. If the -a option is specified in combination with the -r option, SQLCA data is also written to the file. The -r option does not affect the -e option. If the -e option is specified, SQLCODE or SQLSTATE data is written to standard output, not to the file. (OFF)
-s	Stop processing if errors occur while executing a command script or when in CLP interactive mode; write error messages to standard output. If this option is disabled, error messages are displayed, and execution of the remaining commands or statements continues; execution only stops if a system error occurs. (OFF)
-t	Use a semicolon (;) as the statement termination character. This option disables the backslash (\) line continuation character. (OFF)
-tdx	Use x as the statement termination character. (OFF)
-v	Echo input text to standard output. (OFF)
-w	Display SQL statement warning messages. (ON)
-x	Return data without any headers, including column names. (OFF)
-zfilename	Redirect all output (including any messages or error codes) to the specified file. If the file already exists, new information is appended. If the -a option is specified in combination with the -z option, SQLCA data is also written to the file. The -z option does not affect the -e option. If the -e option is specified, SQLCODE or SQLSTATE data is written to standard output, not to the file. (OFF)

$ setup -u <response_file>
This will automatically pick all the values from the response file

Verify the DB2 version:
db2level

Display all DB2 products with available licenses installed.
db2licm –l 

to list the installed DB2 on system
db2swtch -l -- Displays a list of DB2 database product installations on the system.
db2swtch -d installation-name --- Sets the default DB2 copy.


For launching the Db2 first step
$ db2fs

for DB2 control center
$ db2cc

Create the DB instance
db2icrt <instance_name>

Create the DB instance [In Linux]
/home/db2inst1/instance> ./db2icrt -u db2fenc2 db2inst2
db2fenc2 and db2inst2 must exist before hand.

to list the instance list
db2ilist

to get the current active instance
db2 get instance

to set the instance
set db2instance=<instance_name>

Linux (syntax depends on shell)
export DB2INSTANCE=<instance_name>


for start the db instance after setting the instance
db2start

to create database
create db <db_name> [As dbAlias ON drive <drive_name>]
create database <db_name> [As dbAlias ON drive <drive_name>]

to create database [In Linux]
create db <db_name> [As dbAlias ON path <drive_name>]
create database <db_name> [As dbAlias ON drive <drive_name>]

Connect to a DB
connect to <dbname> user <username> using <userpswd>

to chk the connection
db2 connect

to drop a connection with databse
connect reset

to drop a database
drop db <db_name>

to stop the instance current active instance
db2stop [force]

to drop instance
db2idrop <instance_name>
db2idrop -f <instance_name> -- forcefully delete instance

to drop instance [In Linux]
db2stop (as the instance owner, eg: db2inst1)
/opt/ibm/db2/V9.5/instance> ./db2idrop db2inst1 (as root)

• Size of your database:
  • =>db2 'call get_dbsize_info(?,?,?,0)'

• Stop the db2 instance
  • =>db2stop

• Stop an instance that has current connections
  • =>db2 force applications all
  • =>db2 deactivate db <dbname>
  • =>db2 terminate
  • =>db2stop force
  • =>ipclean

• Start the db2 instance
  • =>db2start

• Kill a hung instance (last resort)
  • =>db2_kill -all

• List the databases in an instance
  • =>db2 list db directory

• List the cataloged nodes
  • =>db2 list node directory

• Show the database manager configuration settings
  • =>db2 get dbm cfg

• Show the database level configuration settings
  • =>db2 get db cfg for <dbname>

• Activate a database
  • =>db2 activate db <dbname>

• Deactivate a database
  • =>db2 deactivate db <dbname>

• View the DB2 License
  • =>db2licm -l

• To switch between partitions
  • =>db2 terminate; export DB2NODE=<new-node-num>