Hello, Db2 World! – How to Connect to a Db2 (LUW) Database

Connecting to a database is required before most actions can be taken on the database. A Db2 connection is made at the database level, and not at the instance or server level. This entry focuses on how to establish a connection to work with a Db2 database. It applies to Db2 for Linux, UNIX, or Windows.

What is a Connection?

When you connect to a Db2 database, Db2 allocates certain resources. Often you are assigned a coordinating agent (thread), which will perform work for you in the database. That coordinating agent also has some small memory areas associated with it that will be enlarged as needed. Connecting also performs authentication using the LDAP or operating system methods available. If defined within a database, connecting may also execute the CONNECT_PROC stored procedure that can be customized per enviornment.

Once you are connected to a database, if you have permissions, you can take actions like:

• Execute queries (SQL) to retreive data
• Make changes to some database configuration areas
• INSERT, UPDATE, and DELETE table data
• Create, alter, and drop tables, indexes, views, stored procedures and other database objects
• Grant or revoke database or object-level permissions and authorities

Where to Connect From

If you have the proper ID, you can ssh or rdp to a server and connect to the database locally on the server.

If you have an application server that is able to access the database server, you can connect from the application server. Some connections will require a Db2 client for this to work. Others may require you have the right application drivers.

Depending on firewall rules, you may even be able to connect from your laptop or desktop to a database server, though this will likely require a VPN connection or being on the right network. Database servers should generally not be accessible from the open internet.

Command Line Connection

Local Command Line Connection

The simplest way to establish a connection is entirely dependent on your background and experience. For me, and most DBAs, the simplest connection is from a command line. For a local command line connection on a Linux or UNIX server, I ssh to the server, and make sure I have sourced the db2profile of the instance in question. This is often done in my .profile, .bash_profile, or .bashrc. Sourcing the db2profile can also be done at the command line like this:

. $INSTHOME/sqllib/db2profile

In the above, $INSTHOME is the instance owner’s home directory. Each database is associated with one and only one instance. Each instance has an id associated with it called the instance owner. The instance owner’s home directory contains a file structure critical to the functioning of db2 in a directory called sqllib. The home directory of the instance owner cannot easily be changed after instance creation.

On Windows, to get to a command line you can run “DB2 Command Window” as an administrator from the IBM DB2 menu for that Db2 copy.

Once you are at a properly configured command line, connect to a database like this:

db2 connect to sample

   Database Connection Information

 Database server        = DB2/NT64 11.1.3.3
 SQL authorization ID   = ECROOKS
 Local database alias   = SAMPLE

“sample” in this case is the database name. This connection syntax only works if you are logged in to the server with an id that has the connect privilege on the database. If you need to use a different id, you have to specify the id name like this:

$ db2 connect to sample user ecrooks
Enter current password for ecrooks:

   Database Connection Information

 Database server        = DB2/NT64 11.1.3.3
 SQL authorization ID   = ECROOKS
 Local database alias   = SAMPLE

In this case “ecrooks” is my user id. Db2 will then prompt me for my password. Note that in the first example, I did not have to provide a password. This is because Db2 relies entirely on the OS or an external authentication facility like LDAP for authentication. When you’re logged into the server, you’re already authenticated, and Db2 recognizes that.

Please never store database passwords in plain text. They tend to be very powerful ids if anyone else can find the password.

Remote Command Line Connection

If you cannot ssh or rdp to the database server, if you need to test or establish connectivity from another server, or if you want to connect from any other computer, you need to establish a remote connection. For this to work, you need to have at least a Db2 client (IBM Data Server Client) installed on the system you are connecting from. This smaller piece of software is free and readily downloadable from IBM based on the version, fix pack, and operating system.

Db2 clients are available for the same operating systems Db2 is supported on. There is some inter-connectablity between versions. The general rule of thumb is that you should be able to connect from a client to a version of Db2 that is two versions newer than your client version, or one version older. Often it will work further out than this, but there are specific exceptions, so it is generally easiest if you install the same version of client as the server. Clients have to be upgraded over time.

If you’re connecting from one Db2 server to another, you do not have to install a client. The client functionality is included in every Db2 server. Between Linux, UNIX, and Windows, it doesn’t matter which operating systems you’re connecting from and to. Connecting to Db2 on z/OS may require some additional steps.

Once you have a client, you also have to catalog the database you wish to connect to. Once that is done, you can connect to the database using syntax like this:

$ db2 connect to sample user ecrooks
Enter current password for ecrooks:

   Database Connection Information

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

Once the connection is established, you can run queries and take other actions:

$ db2 "select * from hello_world"

C1
-------------
Hello, World!

  1 record(s) selected.

Connecting from an Application

Each application and programming language has its own details and gotchas. This entry is the first in a series I plan to write sharing ways to connect from various programming languages. Keep an eye out for entries in the Hello, Db2 World series.

I’ll also be sharing code on github for this in a repository called ecrooks/db2_hello_world

• 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