Most Db2 DBAs administer Db2 at the command line. The command line provides a rich and powerful interface with Db2, and has a lot of options. Many other users may wish to access Db2 at the command line as well. Developers and system administrators are just a few of the people who might find the Db2 command line useful.
Command Line on Server or Client
The first thing to decide when accessing Db2 from the command line is whether that command line will be on the database server or on a client. A client is any machine with a network connection to the database server and the Db2 client software installed. Db2 client software is available for free on the Db2 Download Db2 Fix Packs by version page. On that page, select the most recent fix pack for the version you want, and on the next page, select the operating system you wish to install it on. Finally, select the “Data Server Client” download.
If you are connecting on the server directly, there is some setup to do for your ID. If you are connecting from a client, you will first have to catalog the node and the database.
Types of Command Line Available
The type of command line you choose to use can vary based on the operating system and choices you make. On AIX and Linux, a Bash or KSH command line is the main focus. On Windows, you can use a Db2 command window or a PowerShell command line. Db2 also offers an interactive Command Line Processor and a CLPPlus that offers some additional features and Oracle SQLPlus compatability.
Using a Bash or KSH Command Line
On a Linux or Unix server, you should be able to use your current shell. Bash and KSH are really identical here in terms of how you access Db2. There are several pieces of information that you’ll need to be successful:
- The location of the db2profile for the Db2 instance
- The name of the Db2 database
The first part of setting up command line access from Bash or KSH is to source the proper db2profile for the instance. The location for this is $INSTHOME/sqllib/db2profile. $INSTHOME is the home directory of the Db2 instance owner. If you know the name of the Db2 instance, you can look this up by finding the entry for the db2instance owner in /etc/passwd:
$ cat /etc/passwd |grep db2inst1
db2inst1:x:999:999::/db2home/db2inst1:In this example, the instance owner’s home directory is /db2home/db2inst1/.
Once you have the location, sourcing the db2profile is a simple, one-line command:
. /db2home/db2inst1/sqllib/db2profileIf there is only one instance on the server or client that you will be accessing, then this line can be added to your .profile, .bash_profile, or .bashrc file so that it is automatically sourced any time you log in.
Once you have sourced the db2profile, you can list the databases available for connection in the database directory:
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /db2home/db2inst1
Database release level = 14.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =If the database you are looking for is not listed here, you cannot connect to it. You may have to catalog it if you are connecting from a client, or you may have the wrong instance.
If you are working on a server with multiple Db2 instances and need to switch from one Db2 instance to another, you need to source the db2profile of the other Db2 instance you wish to access. Only one db2profile can be sourced at any one time, and therefore only one Db2 instance can be accessed at any one time. It is best to terminate your Db2 sessions before sourcing a different instance’s db2profile. If you do not, you may get an SQL1013N error when trying to connect to a database that exists in the database directory for the new instance.
Using a PowerShell Command Line
My favorite command line on a Windows Db2 server is the PowerShell command line. I like it because it is more similar to a Linux/UNIX shell than the standard Windows command line. Like Bash or KSH, there are a couple of steps you have to perform to set things up here.
First, for full functionality, you’ll need to run powershell as an administrator. You may be able to run some commands without this, but there are some odd failures that you’ll run into.
To be able to execute Db2 commands on a Windows server with a single copy and a single instance, execute:
set-item -path env:Db2CLP -value "**$**"This can also be added to your $profile so it is automatically in place when you start PowerShell.
Once you have set this environment variable, you can list the databases available for connection in the database directory, exactly like at a Bash or KSH prompt:
PS C:\Windows\system32> db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = C:
Database release level = 14.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =If the database you are looking for is not listed here, you cannot connect to it. You may have to catalog it if you are connecting from a client, or you may have the wrong instance.
Using the Db2 Command Line Processor(CLP) in Interactive Mode
Some people enjoy using the Db2 CLP in interactive mode. I’m not a huge fan of this because I think command history is easier from a standard command line, I like to pipe output to OS commands, and I like to use other OS-level commands without prefixing them with a bang (!). Interactive mode does keep you from having to enter “db2′ before every command.
To access Db2 in this way, simply start up one of the command lines described above and enter “db2” and then hit enter. On Windows, this is also accessible as an option in the IBM Db2 file on the programs menu to start a “Command Line Processor”.
Once in this interactive mode, you use the same commands you do at a Bash/KSH/PowerShell command line, but without the “db2” prefix. To list the databases available for connection:
db2 => list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /db2home/db2inst1
Database release level = 14.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
db2 =>Using Db2’s CLPPlus
There are things to love about CLPPlus and frustrating things as well. It behaves more like Oracle’s SQLPlus than other Db2 command line interfaces. You can run SQLPlus scripts with it. The use of type 4 JDBC drivers and connection strings can be a bit frustrating for those of us used to local connections. Formatting output is easier with CLPPlus, but there are some difficulties running certain utilities.
To use CLPPlus, you must establish a database connection from an existing command line session. Also, TCPIP must be enabled (in the Db2COMM registry variable), and the SVCENAME DBM cfg parameter must be set to a valid value. Once all that is done, establishing a connection looks like this:
> clpplus -nw ecrooks@localhost:50000/sample
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.
Enter password: **********
Database Connection Information :
---------------------------------
Hostname = localhost
Database server = Db2/AIX64 SQL10057
SQL authorization ID = ecrooks
Local database alias = SAMPLE
Port = 50000
SQL>The -nw keeps Db2 from launching clpplus as a separate program requiring X11 forwarding on Linux and UNIX, and instead launches it in the existing window.
Navigating this command line environment takes a bit more practice and work. A good video on the basics is available from IBM. Several other good resources for clplus are available online:
- http://www.db2dean.com/Previous/clpPlus.html
- http://www.databasejournal.com/features/db2/article.php/3868406/CLPPLUS-IBM-Db2s-New-Command-Line-Processor.htm
- http://www.idug.org/p/bl/et/blogaid=334
Summary
At XTIVIA, our Db2 DBAs are command line geeks. We prefer the command line to GUIs for administering and working with Db2. The command line is a simple and reliable interface that makes scripting and automation for Db2 easy. Db2 command line environments are simple to set up in just a few steps.