Common commands in the DB2 command line processor (CLP)

Brief introduction

The following is a quick reference to the most commonly used DB2 CLP commands. Although not a complete reference, this document can prove to be a valuable reference material for any user who has just contacted DB2. Feedback can be sent to ypaul@ca.ibm.com.

The arguments in square brackets are optional arguments, and the contents in angle brackets represent variables. For example, the syntax for CONNECT is:

connect to <dbname>
[ [user <userid>] using <pwd>]

This means that by using the Connect command to use the password as User1 mypass a connection to a database sample can have the following forms:

Connect to sample
Connect to sample user user1
Connect to sample user user1 using mypass

Organizes all commands according to the scope of the command. For any CLP command, you can enter a prefix question mark ("?"). Keyword to view its full syntax.

Instance

CLP command	Describe
Db2start	Start the Database Manager instance.
Db2stop	Stops the Database Manager instance.
Get dbm CFG	Returns the Database Manager configuration settings.
Get dbm CFG Show detail	Displays the current and deferred values of the Database Manager parameters (from V8).
1update dbm CFG using <p> <v>	Database Manager configuration Parameters <p> Update to value <v>.
Get instance	Returns the value of the DB2INSTANCE environment variable.
List Active databases	Lists the number of active databases and connections.
List application [Show detail]	Returns information about the currently connected application.
Force application (H1 [, H2,.., HN])	Disconnects a particular application based on the handle number.
Force application All	Disconnect all applications from the database.
Attach to <node> user <userid> using <pwd>	Connect by user <userid> by using a password <pwd> with a remote instance identified as <node>.

Database

Create DATABASE <dbname>	Create a database named <dbname>.
Activate database <dbname>	explicitly activates the database.
Deactivate database <dbname>	Explicitly invalidates the database.
Connect to <dbname> [[user <userid>] using <pwd>]	Explicitly connect user <userid> and password <pwd> to database <dbname> as needed.
1update dbm CFG using <p> <v>	Database Manager configuration Parameters <p> Update to value <v>.
Connect Reset	Disconnect from the current database.
Get DB cfg Show detail	Displays the current and deferred values for the database configuration parameters (V8 only).
Get DB CFG for <dbname>	Returns the database configuration settings for database <dbname>.
Update db CFG for <dbname> using <p> <v>	Update database <dbname> Database configuration parameters <p> updates to values <v>.
List Tables[for {user | all | system | Schema <schemaname>}][show detail]	Lists the tables in the database. If you do not specify any parameters, the default is to list the current user's table.
Describe table <tablename>	Displays column information for a table or view.
List tablespaces [Show detail]	Displays the identity, name, type, content, and state of the table space.
List tablespace containers for <tablespace_id> [show detail]	Displays container information for the table space specified with <tablespace_id>.
Quiesce tablespaces for table <tablename> Reset	Resets the table space state to normal.

Connectivity of

Catalog [admin] <protocol> node ...	Create an entry in the node directory for protocol <protocol>.
list [admin] node directory	Returns the contents of the node directory.
Catalog Database <dbname> ....	Create an entry for database <dbname> in the database directory.
List database directory [on <PATH>]	Returns the contents of the database directory.

Performance

Get Monitor Switches	Returns the status of the session monitor switch.
Update monitor switches using <monitor> <on|off>	Set the status of the session monitor switch for <monitor>.
Reset Monitor All	Resets the performance Monitor value.
Get snapshot for dbm	Returns performance information at the instance level.
Get snapshot in <dbname>	Returns all performance information for the database <dbname> at the database level.
Get snapshot for dynamic SQL on <dbname>	Returns the contents of the dynamic SQL cache.
Runstats on table <tbschema>.<tbname>	Collect statistics for tables <tbname>. The table name must be fully qualified with <dbschema>.
Reorgchk on table All	Determine if the table needs to be reorganized. This is useful for automating runstats for all tables.
reorg Table <tablename>	The table is reorganized by refactoring rows to eliminate "fragmented" data and to compress information.

Management

Export	Extracts the database data into a flat file.
Import	Import data into a database by using the Import utility.
Load query Table <tbname> [To Local-message-file] [NoSummary | summaryonly] [Showdelta]	Returns the progress of the LOAD utility.
Backup Database <dbname> [to <path>]	Perform a database backup.
Restore Database <dbname> [from <path>]	Perform database recovery.
Get Health snapshot for dbm	Returns the normal snapshot information for the instance (V8 only).
Get health Snapshot to <dbname>	Returns all normal snapshots of the database <dbname> (for V8 only).

Managing servers

Get admin cfg	Returns the configuration settings for the Management server.
Update admin cfg using <p> <v>	Manage server configuration Parameters <p> Update to value <v>.

Application development

Get routine to <filename> from [specific] procedure <routine-name>[hide body]	Extract the SQL procedure into a binary file.
Put routine from <filename> [owner <newowner>[use Registers]]	Deploy the SQL process from a binary file.