Running DB2 Queries – Part 4

Dean Compher

29 April 2011

Updated 25 November 2011


There are a lot of useful things that you can do with the query tools that come with DB2 on Linux, UNIX and Windows.  With these tools you can execute queries directly on the server or the tools can run on your workstation and act as a client.  There can be subtle differences in the responses you get depending on where the tool is actually running.  In this article I'll discuss those differences and conclude with some interesting examples of how you can use the facilities including some scripting examples.  I like scripts, because once I've saved them, I can use them over and over without having to figure out a GUI menu every time.  In other articles I've described other aspects of the client that may be helpful as you read this article including:


       DB2 Clients – Connect to your Database  - Describes client and driver packages that contain the command line and some GUI Tools

       Running DB2 Queries- Part 1 – Describes how to use the CLP and CLPPlus tools

       Running DB2 Queries Part 2- CLPPlus – Describes the new CLPPlus interface

       Running DB2 Queries – Part 3   -- Compares the various DB2 client tools with each other. 

       Data Studio Compares and contrasts the GUI Control Center and Data Studio and describes Data Studio


It is important to note that there are three categories of things that you can execute in DB2:


       Queries - “select * from table_a”

       DB2 Commands - “list node directory” or “get dbm cfg” that are run from CLP window or other interface that typically also allows queries.

       DB2 System Commands - “db2ilist” or “db2support” that are like any another command you can issue at the OS command line like a “dir” or “ls” command.  Since these are executable files and not something processed by the DB2 client, you generally can't type them into a GUI query tool.  Instead, they can be invoked by clicking on the correct menu options in the GUI tools. 


Before I get into the discussing your options it is important to note that there are some differences between running these commands locally on the database server and running them as a client from your workstation making a client connection to the database.   These differences primarily occur for commands that get information from- or make changes to- the instance.  For example, queries will always behave the same because you connect to a database that executes the query.  However, a number of DB2 Commands and System Commands operate on the instance that is running your current CLP.  So for example if you execute the CLP on your workstation and connect to a database on a server, and your run the command “db2 list node directory” you will see the nodes that have been cataloged on on your workstation, even if you previously connected to a remote database (db2 connect to remote_db).  The same is true for system commands.  For example if I run the db2ilist command on my workstation, I will only see instances created on my workstation and not any of the instances on the server to which I am connected.  There are exceptions though, because some commands are created to be run on a client and connect to a database.  In general these commands will have a parameter to specify a database name or will not work unless you already have an active connection to a database.  Examples include the “db2 export ...” command that will only work if you have connected to a database,  and the db2expln command that has the “-d” option to explain a query against a particular database. 


Considering that almost no one can get their hands on a server console anymore, I want to clarify what I mean by server vs. client in the preceding paragraph.  By server vs. client I mean where the library exists from which the command is executed.  So if you have the Data Server Client installed on your workstation and you execute the “db2 connect to my_db” command in a CLP window to connect to a database on a remote server then you are making a client connection to the database, but the “db2” command is actually executing from a library on your workstation.  In this case you are executing a a command such as “db2” or “db2ilist” on your local machine.  However, many DBA's can access a remote database server directly.  For Linux and UNIX database servers this is often done using a Telnet or SSH window.  In these cases you are actually typing commands like “db2” or “db2ilist” on your own keyboard, but the commands are actually executing objects in the libraries that live on the remote server like they would for the “ls” or “pwd” command.  Therefore, if I open a CLP line command window on my laptop and also an SSH window where I connect to a remote server, and I connect to the same database on both, when I issue the “db2ilist” command on both I will see different results!


This is a good place to talk about the “db2cc” DB2 system command that starts the DB2 Control Center.  Most people starting the Control Center on a Windows workstation will start it from the menus under the Windows “Start” button, but you can also issue the db2cc command from a CLP window to start the CC.  As of DB2 v9.1 it no longer executes directly any UNIX systems.  Many prefer to execute the GUI on their workstation and connect to a database.  However, you can still use this command and the others like the ones for Task Center and Replication Center executing directly on Linux and Windows database servers.  If you want to actually have db2cc executing on your server and see it on your workstation, then you would connect to the server using your favorite line command window like SSH and then issue the “db2cc” command.  For this to work you need to to have xWindows working on the server and an appropriate environment configuration and an X client like Hummingbird on your workstation to see the Control Center GUI.  The same considerations apply about server vs. client execution apply for running the db2cc command on your workstation vs. the server.


It has been my experience that everyone who administers DB2 directly on a Windows server either does all of their administration from the client installed or their workstation or takes remote control of the server and sees the desktop of that server.  Windows servers also allow the db2cc command to be executed.  However, most people that have access to a UNIX or Linux server typically get a login and use SSH or Telnet to connect and get only a character based interface.  If you are logging on as the instance owner, then your environment will generally be ready to go to execute DB2 commands because the “db2profile” command gets executed when you logged in.  However, if you log in to the server as a different user id, you can query and administer the database without becoming the instance owner though su or sudo.  You just need to inherit the db2profile environment configuration with a command such as:


.  /home/db2inst1/sqllib/db2profile


assuming that the instance home is /home/db2inst1.  Also don't forget the “dot space” in front of the path so that you actually inherit the environment.  Once you have this environment configured from your personal account on the server, you are ready to issue commands like “db2 connect to db_name” without becoming the instance owner.  Even if you su or sudo to the instance owner you may still find that you need to configure the profile.


When you are logged into a server as the DB2 instance owner on that server, you can connect to the databases in that instance with out specifying a user id.  However, whenever you connect to a database from any client or from a different local user on the client then you need to specify the userid and password.  In the CLP on your workstation you can connect to a database on a server using the connect command as follows:


C:\> db2 connect to db_name user user_id


In this case I did not specify the password, so I would be prompted for it.  When you are running the CLP on your workstation making a connection to a remote database, then you need to “CATALOG” the node and database on your server or add a db2dsdriver.cfg file.  Cataloging just means that you have defined all of the information needed to connect to the database on your client, and gave that definition a name.  In the example above that name is “db_name”.  Most people use the actual name of the database, but you can make it anything you want.   You can run the catalog commands in the CLP or you or you can use the Configuration Assistant.  Just remember though, that the entries will be made on the machine actually executing the CLP or CA code as described in earlier paragraphs. 


You can update the db2dsdriver.cfg file with your favorite editor to add database connection information.  It is an XML file that can also be used for several other client configurations as well including pureScale.  It allows you to create connections for your client and e-mail it to your users instead of cataloging databases on each client.  Here are some important points:

•           V9.5 fp3 introduced it for the DS Driver

•           V9.7 fp3a introduced it for CLI clients (e.g. Control Center)

•           V9.7 fp4 introduced it for .NET clients. 

•           Can be used for OCBC, CLI, .NET, OLE DB, and open source (PHP or Ruby) connections

·                     Find a sample of the file for V9.7 fp3 and later in a file called db2dsdriver.cfg.sample after you install your Data Server (Runtime) Client or Data Server Driver.  I’ve also included some samples of the file in the “Files” section at the bottom of my db2Dean and Friends Community  page.  

•           To validate your file use the db2cli tool to see if you have errors. 


It is important to note that some tools like IBM Data Studio, Optim Database Administrator and Optim Development Studio and other Eclipse based clients use a JDBC type-4 connection and the database does not need to be cataloged in the database directory.  In these tools you would create the connection by right-clicking the “Database Connections” heading in the “Data Source Explorer” view, choosing “New” from the menu and following the wizard to create the connection:



The eclipse based tools can also be run on Windows and Linux, but not UNIX.  They can also be run locally on a server like control center, but are more typically run on a workstation client.  For more information on Data Studio, ODA and ODS, please see Part 3 of this series.


So now I'll actually get into discussion of running queries, DB2 Commands and DB2 System Commands using the CLP.  The preceding discussion was needed to show options that can be used instead of the CLP and the differences between executing a command on a server vs. a client of the database such as your workstation. 


The good news about queries is that they always behave the same running on the server as they do on a client.  They always operate on the database to which you are connecting.  One nice thing about executing queries through the CLP is that you can easily take control of what it is doing through options that you choose.  You should note that a minus (-) sign in front of an option turns it on and a plus (+) turns it off.   For a complete list of options and their use, see the CLP Options page.  Some of the more commonly used options are:


f filename

Use filename as input of command(s)/query(s) instead of looking for a query or going into interactive mode


Often used with -f to say that the semicolon (;) is separates commands


Verbose – Echo command text to standard out.


Autocommit.  Useful if you want to hold a lock.  On by default.

-l filename

Log commands into a history file.  Most useful in an interactive session.


For example, the following command:


db2 +c -tvf my_file.sql

Would execute the queries in the file my_file.sql (-f), expecting those commands to be separated by semicolons (-t), echoing the commands in my_file.sql to the output (-v), and continuing to hold locks after query completion (+c).  In this case, you could test to see what happens in a locking scenario by opening a second CLP window and executing commands that try to update those same rows.  This is useful to see what happens in a lock timeout situation or to test lock monitoring in your database monitoring tool like Optim Performance Manager.  Locks will continue to be held until you explicitly issue a COMMIT or ROLLBACK command. 


Scripting is great because it saves you lots of time, by having commands and queries already written that you use a lot like taking snapshots and is useful in automating maintenance functions like backups.  Another thing that is great about scripting is that you can generate other scripts based on dynamic data in the database.  For example, I once needed to script the RUNSTATS command to run weekly for a database system where tables were frequently added and I didn't want to have to remember to modify the RUNSTATS script each time a table was added.  As you may recall, RUNSTATS takes a table name as input.  I wrote the following query to generate the RUNSTATS commands that I would then execute as part of the script.  Here it is:




        || '.'

        || NAME


   from sysibm.SYSTABLES







I said I incorporated this query (in a file called runstats.sql) into a shell script that then ran the output of this query.  One nice thing about shell scripts is that you can check the return code from from your query and report problems.  This is especially important for commands executed on a database because sometimes there can be locking or other problems.  The CLP is no different and you can see all of its return code meanings on the CLP Return Codes page.  Here is a portion of the script where I call the RUNSTATS query (runstats.sql) above and then execute its output:




db2 -tf /db2inst1/runstats.sql | grep RUNSTATS > /db2inst1/runstats2.sql


    if [[ ${rc?} -ne 0 ]] then

       echo "Return code = ${rc?}"

       mailx -s "${SUBJF1?}"  "${TOLIST?}" < /db2inst1/runstats.log

       db2 terminate

       exit -1


    db2 -tvf ${BASEDIR?}/bin/runstats2.sql


I posted both the runstats.sql and the full korn shell “” that calls it on my db2Dean and Friends community in the “Files” section.  Please feel free to download and use them.  This type of scripting where you generate scripts with a query that combines command literals and database data has a lot of utility.  If you have any Windows/DOS batch scripts that call DB2, please add them for others who may find them useful.




I hope that you found this discussion of db2 clients useful.   Please “like” my Facebook db2Dean page at and add any feedback about what you liked or what I can improve.  You don't have to be a member of Facebook to add comments.  I can only make things better with your help!


HOME | Search