A useful list of IBM DB2 commands

About

When I start to use a new Database Relational System like IBM DB2, first thing I think is "I need to learn how to do this...", so, here is a set of commands I hope you find very useful. If you want to obtain a more complete list of commands, please visit: How to list DB2 commands.

Managing databases

Creating databases

create database <database-name> using codeset UTF-8 territory en
 
// For example (remember DB2 has a restrictions to 8 characters for databases' name):
create database MYDBNAME using codeset UTF-8 territory en

Changing number of concurrent databases

If you receive and error like this:

SQL1041N  The maximum number of concurrent databases have already been started. SQLSTATE=57032

Run following command:

db2 update dbm cfg using NUMDB <number-of-concurrent-databases>
 
// For example:
db2 update dbm cfg using NUMDB 20
 
// You should see something like this:
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W  One or more of the parameters submitted for immediate modification were not changed dynamically. Client changes will not be effective until the next time the application is started or the TERMINATE command has been issued. Server changes will not be effective until the next DB2START command.

Starting and stopping DB2

Before restarting any IBM DB2 server, I would recommend to display all applications and users that are connected to the specific database that you want to stop. To ensure that no vital or critical applications are running.

Following command list all applications

db2 list applications

If you change any configuration parameter, you will probably need to restart, so, stop database server with:

db2stop
 
// Once IBM DB2 is stopped you will see this message
SQL1064N  DB2STOP processing was successful.

Now, for starting IBM DB2 again, run following command:

db2start
 
// If everything was ok, this message would appear
SQL1063N  DB2START processing was successful.

Working with schemas

Creating a new schema

CREATE SCHEMA <schema-name>
 
// For example:
CREATE SCHEMA CSUSER
 
// After running the command a message will be displayed:
DB20000I  The SQL command completed successfully.

Obtaining users

If you want to list schema users, just run following command:

SELECT SCHEMANAME FROM syscat.schemata

As you can see part of the information about schemas and users is stored in syscat.schemata.

Grant privileges

GRANT ALL ON <schema-name>.<table-name> TO USER <user>
 
// For example:
GRANT ALL ON LCUSER.WIKI_PAGES TO USER LCUSER

Getting information about databases and tables

Connect to the database

db2 connect to <database-name>
 
// For example
db2 connect to MYDB

List information about tables

List all tables:

db2 list tables for all

To list all tables in selected schema, use:

db2 list tables for schema <schema-name>
 
// For example:
db2 list tables for schema CSUSER

To describe a table, type:

db2 describe table <table-schema.table-name>
 
// For example:
db2 describe table CSUSER.WIKI_PAGES

List information about databases

List databases:

db2 list database directory show detail | grep -B6 -i indirect | grep "Database name"

If you just want the database names, without the titles, use:

db2 list database directory show detail | grep -B6 -i indirect | grep "Database name" | sed "s/.*= //"

About the author

Enterprise Web Developer

Alex is an experienced technology professional. His background includes:

  • Web Technologies: Semantic Web (RDF, OWL), MVC Frameworks (CakePHP, CodeIgniter) Content Management Systems (Drupal, Joomla, WordPress), JavaScript libraries (jQuery, Mootools), CSS3, HTML5, mobile web (Sencha, jQuery mobile), Operating Systems, and,
  • Research: Complex Systems (Cellular Automata in specific), Artificial Intelligence, Computer Graphics and Computer Simulation to create computer models of living.