Db2 Basics: Setting up a Command Line to Work with Db2

I’m a command line gal, and probably always will be. I doubt a GUI will tempt me away from the command line, though I do find myself enjoying using things like Jupyter Notebook to also access the databases I support.

Does Your Database Know Who You Are?

There are a number of environmental variables that are set when you log in to the Db2 instance owner id. Some of these variables are required for working with a Db2 environment. In sandbox environments, we may do most things as the Db2 instance owner, but in any shared environment, it is critical to security to use your own ID. There are a few exceptions to this – if you’re a member of a team administering Db2, you want to use the instance ID for scheduling any scripts. No one wants to go spelunking through crontabs across the system to figure out who is running backups and things along those lines. It is also best to use the instance owner to start and stop Db2. Most other work, even that done by DBAs, can be done from their own properly privileged IDs.

Working as a consultant, I see an astonishing variety of security practices. At one end of the spectrum, I’m handed root the second I get access to the system when all I really needed was SYSMON. At the other end of a spectrum, I have actually worked on systems where I was not allowed to directly access the system – I could only tell someone else what to type via screen sharing. That is a slow and frustrating way to work.

The focus of this article is not that broad, though. The focus here is how to set up a basic Linux/UNIX command line for one ID so it can execute db2 commands and SQL.

Error Messages

There are a couple of common error messages that are immediately associated with a command line environment that is not properly set up. The first is:

db2: command not found

This indicates that minimally your PATH variable is not set up properly, but also that there are other environment variables that should be set up as well. Sometimes a developer or system administrator tries to be clever when they get that error message, and they locate the basic db2 executable directly. However, there are still missing componnents, and even executing db2 from a location where it exists leads to this error message:

SQL10007N Message "-1390" could not be retreived.  Reason code: "3".

Setting Up A Command Line to Run Db2 Commands

Linux or UNIX – Sourcing the Db2 Profile

IBM provides a profile (actually two of them) that contains all the information needed to connect and run any Db2 commands you might need. This profile is stored in the sqllib directory. This directory is created in the home directory of the instance owner when the Db2 instance is created. The following lines are added by Db2 to the .profile, .bash_profile, or .bashrc when the instance is created to source the db2profile:

# The following three lines have been added by UDB DB2.
if [ -f /db2home/db2inst1/sqllib/db2profile ]; then
    . /db2home/db2inst1/sqllib/db2profile
fi

The path specified in these lines is going to vary depending on where your instance owner’s home directory is. Generally, your instance owner’s home directory should not be on / or on /home. Hopefully you’ve placed it somewhere else.

If you don’t know where the instance owner’s home directory is, there are several ways to find it. For any of them, you have to have access to the server in some way and know the name of the instance owner. If you don’t have this, ask your system admin or database administrator. There can easily be many Db2 instances on a server. In these examples, the instance name is represented by db2inst1. You’ll want to replace that with your instance name.

• If you have access to the instance owning id, login and issue:

  echo $INSTHOME

• On the server, try this:

  cat /etc/passwd |grep db2inst1

• Look at the instance owner's .profile, .bash_profile, and .bashrc to find the lines automatically added by Db2 on installation

if [ -f /db2home/db2inst1/sqllib/db2profile ]; then
    . /db2home/db2inst1/sqllib/db2profile
fi

Changes to the db2profile

It is a bit advanced for this blog article, but should you need to change anything in the db2profile, do not make the changes directly, as the db2profile is likely to be overwritten on upgrade or patching. There's another file in the same directory called userprofile that can be used for any changes you want to make.

Windows

This is one area where Db2 on Windows is significantly different from Db2 on UNIX and Linux.

Default Command Window

I believe there's a special place in hell for batch, so I avoid using it as much as possible, and this includes the default Windows command line. If you must use it, the only way to set it up is to find the executable for it installed when db2 is installed on Windows, and open the command line through it.

Even when I've written batch scripts (shudder), they have to pass things to this.

PowerShell

I am big fan of administering Db2 from a PowerShell command line on Windows. Setting up PowerShell to run Db2 commands is usually just a one-liner either just executed or added to the PowerShell $profile:

set-item -path env:DB2CLP -value "**$$**"

This assumes you're working with the default Db2 instance and copy.

Interacting with Db2 on Linux and UNIX

Once you have the basic command line environment set up, there are still several ways to interact with Db2. My favorite is the first - using the standard bash/ksh command line.

Using the Standard Bash/Ksh Command Line

The top reason that I like this method is that I use vi as my command line editor, and it is incredibly easy to view and to search your command history with it. I can't tell you how many times having commands saved in the history saved me time or allowed me to understand what happened. Most experienced DBAs I have worked with use this method as well. This approach makes it easy to also use bash/ksh to script simple loops and to pipe things to grep or more to make viewing easier. This method takes the form of simply prefixing all db2 commands and queries with "db2", usually enclosing them in double quotes to avoid trouble with shell expansion.

db2 "command"
db2 "query"

A simple example:

$ 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             = /db2data1/SAMPLE
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

$ db2 connect to SAMPLE

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = ECROOKS
 Local database alias   = SAMPLE


$ db2 "select substr(bpname,1,12) as bpname from syscat.bufferpools"

BPNAME
------------
BUFF16K
BUFF32K
BUFF8K
IBMDEFAULTBP

  4 record(s) selected.

The disadvantage to this method tends to be the formatting of query output. It's highly manual, and often done with substr and other functions in the SQL.

Using the Interactive Db2 Command Line

By simply typing 'db2' and hitting enter, you're entered into an interactive Db2 command line.

$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.8

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 =>

I'm not the best person to extol the virtues of this method because I don't use it.

CLPPLUS

The other method I have played with is the CLPPLUS environment. I believe this is Db2's answer to Oracle's SQLPLUS. On several occasions, I've used this environment to get output formatted nicely for a blog entry or presentation. But it's still not my go-to choice. Check out my blog entry exploring CLPPLUS.

A Few Tips for Multi-Instance Environments

There are a few things you can do to make working with environments where you switch instances on the same server easier.

Aliases for Switching Instances

The first is to define aliases at the bash level for switching instances. One of these that I use looks like this:

alias test='db2 terminate && . /db2home/db2inst1/sqllib/db2profile && cd ~/test && db2 connect to testdb'
alias qa='db2 terminate && . /db2home/db2inst2/sqllib/db2profile && cd ~/qa && db2 connect to qadb'

These alias make it so I type one short string and the following things happen:

1. Any existing db2 command line is terminated
2. The proper db2profile is sourced
3. The current directory is changed to one I have specific to that instance
4. A connection is established to the database

Technically only #1 and #2 are required, the other two are just niceties.

Command Line Prompt

To make sure I always know what instance I'm working with, I also set my command line prompt in my .bash_profile using this code:

STARTFGCOLOR='\e[0;31m';
STARTFGCOLOR2='\e[0;32m';
ENDCOLOR="\e[0m"
export PS1="[\u@${STARTFGCOLOR2}DEV_QA1$ENDCOLOR $STARTFGCOLOR\${DB2INSTANCE}$ENDCOLOR \W]\n\$ "

What this gives me at the command line is this:

In the example above, the instance name is VDBA, and it stands out so that I can quickly verify which instance I'm working with.

Summary

The command line is not the only way to work with Db2. I'm regularly using scripts in KSH/Bash, Perl, PowerShell, Python, and my recent favorite - Jupyter Notebook (Python Kernel) to interact with Db2. Scripting is a powerful way to automate the things a DBA does, easily schedule them, and ensure a greater level of consistency.

Edited on 2019-04-30 to add two missed punctuation marks, change "environment parameters" to "environment variables", and spell .bashrc correctly.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home