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/.*= //"

HANDLING ISSUES WHEN LOAD COMMAND IS IN PENDING STATE

[db2inst1@host1 ~]$ touch test.del
[db2inst1@host1 ~]$ LOAD from 'test.del' OF DEL TERMINATE INTO db2inst1.reporter_status
-bash: LOAD: command not found
[db2inst1@host1 ~]$ db2 LOAD from 'test.del' OF DEL TERMINATE INTO db2inst1.reporter_status
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3110N  The utility has completed processing.  "0" rows were read from the
input file.


Number of rows read         = 0
Number of rows skipped      = 0
Number of rows loaded       = 0
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 0

[db2inst1@host1 ~]$ db2 set integrity for db2inst1.reporter_status immediate checked
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL3600N  The IMMEDIATE CHECKED option of the SET INTEGRITY statement is not
valid since the table "DB2INST1.REPORTER_STATUS" is a user maintained
materialized query table or is not in the Set Integrity Pending state. 
SQLSTATE=51027
[db2inst1@host1 ~]$ db2 "select count(*) from reporter_status"

1         
-----------
     243724

  1 record(s) selected.



db2 LOAD FROM "/tmp/0812.out" OF DEL MODIFIED BY identityignore generatedignore rowchangetimestampignore timestampformat=\"YYYY-MM-DD hh:mm:ss\" implieddecimal  INSERT INTO DB2INST1.REPORTER_STATUS