Running DB2 Queries – Part 1
30 October 2010
Updated 2 January 2013
So you need to run a query against your DB2 database, but what software do you need to do that and how do you point that software to your database? There are actually quite a few options that range from simple command line tools to basic GUI (Graphical User Interface) tools to sophisticated reporting writer tools like Cognos. In this article I'll cover command line and basic GUI tools that are free from IBM with DB2. With these tools you can quickly start running queries.
Many people will run these queries from their workstation, but if you have direct access to your database server with tools like telnet or ssh you can run the same command line tools there. By default, at least the command line tools get installed with your DB2 server software.
To access your databases with these tools from your workstation you will need to download and install either the IBM Data Studio, IBM Data Server Runtime Client or the IBM Data Server Client. You can read about these clients and get download links from my page. You can see short descriptions here:
• The IBM Data Server Runtime Client allows you to have command line access where you can type your queries command line or execute queries you have saved in a file.
• The IBM Data Server Client gives you a GUI interface called the Command Center and the same command line tools as the runtime client.
• The IBM Data Studio gives you a GUI interface that is very easy to use.
January 2011 UPDATE:
Development Studio and Optim
Database Administrator are now available for free for customers who own any
paid edition of DB2/LUW and have current maintenance
. They are
available on your Passport Advantage site where you get DB2. These
are additional Eclipse based GUI tools that provide all of the features of IBM
Data Studio plus several more. Optim Development Studio is especially
good at helping you generate queries.
2 January 2013 UPDATES:
If you are not very familiar with installing and configuring database connections then this is the tool for you. It is a stand-alone tool that allows you to connect to a database and see the tables you want to query and helps you write your queries. You can also easily save your queries into files and easily retrieve them. This tool has many other features for more advanced users such as wrapping queries in web services and a stored procedure builder, but I will leave those topics to a later article. In addition to being very easy to use for the beginner, this application allows you to connect to DB2/zOS, DB2/i (AS400), DB2 on Linux, Unix and Windows (LUW) and Informix. Even the connections to DB2/zOS and DB2/i are free and do not need DB2 Connect! It installs its own JCC driver and makes JDBC type-4 connections to the database. This driver can not be used to connect other applications to the databases. For more information about how to run queries in Data Studio please see this article: Data Studio Update Part 5: Query Building and Editing.
The Data Server Client is also good for querying the database. Important: See bullet 2 in the 2 January 2013 updates above! It provides both the GUI Control Center/Command Editor that allows you to query DB2/LUW databases and provides all of the command line tools as well. It also comes with the that is a GUI tool for configuring the database connections and creating ODBC sources when you create those connections. In addition to creating queries in the Command Editor, you can also connect applications like Excel spread sheets to the database using the ODBC source. Many other reporting and query tools can use the connections and ODBC sources you create with the Configuration Assistant. You can invoke the Command Editor either from the Control Center start it by itself from the Windows Start Menu. The Control Center and its components like the Command Editor are being deprecated, meaning that they may not be found in upcoming versions. Only the GUI tools are being deprecated the client is not. Further you should note that the Control Center and Command Editor are not available on UNIX and may not be available on some Windows and Linux server editions since these are strictly meant to be client tools starting in DB2 v9.1. To query DB2/zOS or DB2/i you must be licensed for and either have it installed or have an available DB2 Connect Gateway.
IBM Data Server Runtime Client
The runtime client is the same as the IBM Data Server Client, except it does not have any of the GUI tools like the Command Editor. However It does have the command line tools that allow you to query the database and the libraries that allow you to connect applications through the database connections and ODBC sources. You just have to configure these items through the command line.
Command Line Tools
All of these tools are available through the Data Server Client and the Data Server Runtime Client. They are:
Command Line Processor
These tools take as input queries or certain types of commands and return the results to the standard output or screen. These are just regular shell windows with the environment set to find DB2 libraries. On Windows these are Command Windows (DOS windows) and regular terminal shell windows on Linux and UNIX. From the Command Line Processor (CLP) in either mode, you can execute many types of commands, but I will focus mainly on database queries.
The CLP in “Command Line Mode” is just a DOS Window (C:\>) or Linux/UNIX terminal window ($). At these commands you can not only execute regular shell commands like “dir” or “ls”, but you can also execute queries with the “db2” command. For instance you can say:
$ db2 “select * from table_xyz”
C:\> db2 “select * from table_xyz”
and get back all of the rows from that table. From the CLP “Interactive Mode” you are already inside of a db2 command shell (db2 => ) so you would just execute the query without typing the “db2” part. From the CLP Command Line Mode, you can also execute system commands like “db2pd” that you can't execute from Interactive Mode or the GUI Command Editor. With both modes you can execute sets of queries that are in a file.
Before you can execute a query, you must have the database cataloged (defined to your client) as described by the or other means. When the database is cataloged on your system you give it a name that is often the actual name of the database but you can give it an alias as part of the command. In any case with both CLP modes you must also connect to the database before issuing queries. Therefore, a client with a database defined with the name “mydb” you would execute the following set of commands if you wanted the results of the two queries in a Linux CLP Command Line Mode window:
$ db2 “connect to MyDb user MyUserID”
$ db2 “select * from dba_tab where name = 'db2Dean'”
$ db2 “select count(*) from syscat.tables”
If you entered the CLP in interactive mode, you also need to connect as follows:
db2 => connect to MyDb user MyUserID
Command Line Processor Plus (CLPPlus)
If you are used to running queries and reports in Oracle using SQL*Plus then you will be right at home using the CLPPlus. This tool allows you to run your queries just as you would under SQL*Plus and has all of the formatting utilities to which you have become accustomed. The are also the same as the ones you know and love. For queries, it is your choice whether to run queries under CLP or CLPlus. One is as efficient as the other so you should use the one you like best for queries. CLPlus even allows you to run a subset of CLP commands such and IMPORT and LOAD. To enter clpplus from a DOS Window, you can enter the following command:
C:\> clpplus MyUserID@localhost:50000/MyDb
If you start CLPPlus from the windows Start menu then you will need to issue the connect command before you can enter queries:
SQL> connect MyUserID:50000/MyDB
Once you enter the “clpplus” command you are in an interactive mode and you will enter your queries at the SLQ> prompt. Don't forget to end each query with the semicolon. Here is an example of connecting with CLPlus and then entering a query:
$ clpplus :50000/MyDb
Connected to MYDB v 9.7 AS MyUserID
SQL > select * from dba_tab where name = 'db2Dean';
The EXIT breaks you out of the CLPlus. Since you supply the connect string for the database with this command you don't need to catalog the database on your client, so you will not need tools like the Configuration Assistant. If you need to create reports from the command line, then CLPlus is a much better choice than the CLP because CLPlus has a much richer set of formatting commands.
Invoking the Command Line Tools
Although the CLP can be invoked from a graphical menu on Linux platforms and from the command line in Windows, I usually see folks start it from a terminal window on UNIX and Linux and from the “Start Button” if running Windows.
Under windows you typically push the “Start” button and then choose “Programs” or “All Programs” and you can then choose the mode that you want as shown in this diagram:
This is a screen shot of a Windows XP system with the DB2 v9.7 Data Server Client installed. Different editions of DB2 and different editions of Windows may not look exactly like this, but will be similar. In this diagram you can choose any of the items in the box on the far right to invoke an editor:
What you SEE
What you GET
GUI Command Editor. Can also be selected from the Control Center once it is started using:
General Administration Tools | Control Center
Command Line Processor
CLP Interactive Mode
Command Line Processor Plus
CLP Command Line Mode
You can use a similar GUI menu to start the command line tools under some Linux installations and there the DB2 menus will look similar to Windows. However, since people often just use them from a terminal window, I'll go more in depth here on that. Once you have a terminal window, you have to inherit certain DB2 environment variables to be able to run DB2 commands. You do this be executing the “db2profile” file in the “sqllib” directory that is under the DB2 instance on the machine. You need to do this whether you are running from the instance owner id or from your own personal user id. For example you would execute:
You would substitute “/home/db2inst1” for the actual directory where your DB2 server or client instance lives on the local workstation or server. Don't forget that DB2 database servers always get at least the runtime client, and the Control Center can also be optionally installed on some Windows and Linux server operating systems, but not on any UNIX systems. Also remember to use the “dot space” syntax used above to inherit the correct profile settings.
Once you've set your profile, you can now run the CLP in either mode or run the CLPlus to run queries.
Once you are have set the profile under Linux or UNIX or have started the “Command Window” as shown in the diagram above for Windows, you are ready to start running CLP Command Line Mode commands as shown in the “Command Line Processor” section above, for example you can run:
db2 “connect to MyDb user MyUserID”
Further, to run queries already in a file you can run the command line mode using the -f option to do so:
db2 -tvf MyFile.sql
Invoke the CLP Interactive Mode by just typing the command, “db2” and hitting enter from the Command Line Mode or Choose the “Command Line Process on Windows. The CLP has many other options including ones to turn auto commit on and off. You can see the complete list at .
Once you have set the profile under Linux or UNIX or have started the “Command Line Processor Plus” as shown in the diagram above for Windows, you are ready to start running CLPPlus commands as shown in the “Command Line Processor Plus (CLPPlus) section above, for example you can run:
and then start entering queries. To run a query contained in a file execute this:
clpplus MyUserID/MyPassWord@localhost:50000/MyDb @MyFile.sql
This article should get you started if you need to review your options for running queries. However, there are many more things that you can do with these tools and I will discuss them in later articles. I am considering topics in those articles that including scripting with the command line tools, cataloging tables from the command line, connecting to DB2 on i and z, and much, much more. Stay Tuned!