Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 10.1 fundamentals certification exam 610 prep, Part 1: Planning

Norberto Gasparotto Filho
Norberto Gasparotto Filho is a database specialist with more than eight years of experience with database administration. He was the winner of the first edition of "DB2's Got Talent" contest in 2011. He also worked as programmer using a variety of technologies, and has certifications in both programming and database administration areas. In his blog ("Insights on DB2 LUW database admin, programming and more"), Norberto shares lessons learned in the day-to-day database administration work, tips and knowledge. During his spare time, Norberto likes to run, ride a bike and have fun with his kids and wife. Learn more in Norberto's profile in the developerWorks community.

Summary:  This tutorial introduces you to the basics of the IBM® DB2® 10.1 product editions, functionalities and tools, along with underlying concepts that describe different types of data applications such as OLTP, data warehousing / OLAP, non-relational concepts and more. It will briefly introduce you to many of the concepts you'll see in the other tutorials in this series, helping you to prepare for the DB2 10.1 Fundamentals certification test 610.

View more content in this series

Date:  18 Oct 2012
Level:  Introductory PDF:  A4 and Letter (2292 KB | 50 pages)Get Adobe® Reader®


DB2 tools

DB2 comes equipped with a set of tools that enable you to access, query, operate, and manage your DB2 database environment, either locally or remotely. Many of these tools are stand-alone with graphical user interfaces (like Data Studio and Replication Center) while others are command line tools; we’ll look at both types in more detail in this section.

Command line tools

DB2 Command Line Processor (CLP)

The Command Line Processor (CLP) is a command-line interface that can be used to interact with DB2 instances and databases. You’ll find it with every DB2 edition, as well as with each DB2 client (but not with driver packages).

The DB2 CLP can be run in interactive mode (by executing the command db2 once) or in non-interactive mode (by prefixing commands run from a system command prompt with the keyword "db2").

For example, in interactive mode, a DB2 command would be executed like this:

db2 => list applications

In non-interactive mode, however, the same command would be executed like this:

db2 list applications

When using non-interactive mode, you can execute operating system’s commands whenever you want. But if you need to execute an operating system command while running in interactive mode, you must prefix the command with an exclaimation mark (!). For example:

db2 => !dir

On Windows, to enable use of DB2 CLP in interactive and non-interactive modes, you must click on the DB2 Command Window icon or run the db2cmd command. When the db2cmd command is invoked from another command terminal, Windows will create a new window for DB2 CLP non-interactive use. Figure 6 illustrates how the DB2 CLP is run in interactive mode.

Figure 6. Running the DB2 CLP in interactive mode
Calling DB2 CLP in interactive mode from command line

You must also use a Command Window to run CLP in interactive mode. When DB2 is installed on a Windows server, a menu item named “DB2 Command Line Processor" is created and when selected, this menu item launches CLP in interactive mode. Figure 7 shows the menu item used to launch the CLP in interactive mode.

Figure 7. The menu item used to start the DB2 Command Line Processor
Figure showing the operating system's menu with the DB2 icons

Running CLP on Linux and UNIX

On Linux and UNIX, you have to "source" the DB2 profile before you can use CLP. Sourcing the DB2 profile is done automatically when you log on with the DB2 instance owner user-id. If you are not an instance owner, you must do the following before you will be allowed to use the DB2 CLP:

  1. Locate the home directory for the instance owner user (also called instance-home).
  2. Within the instance home directory, you’ll find a subdirectory called sqllib. Inside that subdirectory you will find a file named db2profile.
  3. Execute the commands in the db2profile file as follows: (assuming the DB2 instance home directory is /home/db2inst1)
$ . /home/db2inst1/sqllib/db2profile

Don’t forget the space between the point and the file location — otherwise the command won’t work. The execution won’t return any outputs and right after executing it, you will be in DB2 CLP non-interactive mode. Try it! You can add that command to the user’s profile (usually a hidden file called .profile or .bashrc within your user’s home directory) so that it will be executed automatically whenever you log on.

Now that you know how to access DB2 CLP, let’s look at some of its basic functionality. I’ll be showing commands using non-interactive mode, but you can use DB2 CLP in interactive or non-interactive mode as you wish. Want to switch from non-interactive mode to interactive mode? Just issue the command db2 at the command prompt. To get help at any time, enter db2 “?" (non-interactive mode) or ? (interactive mode). Figure 8 shows the output that is generated when help is invoked.

Figure 8. The DB2 ? command
Screenshott showing the help given when invoking the ? command.

To get more help on a specific command, run the command "? <DB2_Command>". For example:

db2 “? list utilities"

Measuring your query execution times

On Linux and UNIX, you can take advantage of the time utility to measure the execution time of a given statement or command (when in non-interactive mode). For example:

$ time db2 "select count(*)
> from department"


  1 record(s) selected.

real    0m0.033s
user    0m0.013s
sys     0m0.015s	

You may have noticed that the DB2 command provided in this example is enclosed in double quotes. On UNIX platforms, you must enclose DB2 commands and SQL statements in double quotes any time they contain special characters — * ( ) \ & | < > ? ! — if you want to execute those commands/statements using DB2 CLP in non-interactive mode. That’s because way in which the UNIX operating system interprets such characters can produce unexpected results.

The DB2 CLP uses DB2’s directory files to establish database connectivity, which means that if you want to work with a remote database, its server has to be catalogued locally as a node, and the database itself must be catalogued in the system database directory. (You’ll learn more about DB2’s directory files in the third tutorial in this series: Working with Databases and Database Objects). Local databases are catalogued automatically, so you can easily access them through CLP.

It’s possible to issue any SQL query against a DB2 database using CLP. However, when updating data or changing the definition of objects in a database, CLP will automatically commit the changes after each statement is executed, by default. This behavior, as well as several other default behaviors of CLP can be changed. To check what options can be changed in CLP, run the following command:

$ db2 list command options

When this command is executed, you should see output that looks like this:

Listing 1. Command options
$ db2 list command options

     Command Line Processor Option Settings

 Backend process wait time (seconds)        (DB2BQTIME) = 1
 No. of retries to connect to backend        (DB2BQTRY) = 60
 Request queue wait time (seconds)          (DB2RQTIME) = 5
 Input queue wait time (seconds)            (DB2IQTIME) = 5
 Command options                           (DB2OPTIONS) =

 Option  Description                               Current Setting
 ------  ----------------------------------------  ---------------
   -a    Display SQLCA                             OFF
   -c    Auto-Commit                               ON
   -d    Retrieve and display XML declarations     OFF
   -e    Display SQLCODE/SQLSTATE                  OFF
   -f    Read from input file                      OFF
   -i    Display XML data with indentation         OFF
   -l    Log commands in history file              OFF
   -m    Display the number of rows affected       OFF
   -n    Remove new line character                 OFF
   -o    Display output                            ON
   -p    Display interactive input prompt          ON
   -q    Preserve whitespaces & linefeeds          OFF
   -r    Save output to report file                OFF
   -s    Stop execution on command error           OFF
   -t    Set statement termination character       OFF
   -v    Echo current command                      OFF
   -w    Display FETCH/SELECT warning messages     ON
   -x    Suppress printing of column headings      OFF
   -z    Save all output to output file            OFF

You can change any of the options available for each command you execute in non-interactive mode. In interactive mode, changing an option affects all subsequent commands executed in the CLP session.

The plus (+) and minus (-) characters can be used to turn an option ON or OFF. For instance, with the auto-commit option:

-c switches auto-commit to ON (which is the default) while +c or -c- (both do the same) do the opposite, that is, they switch the auto-commit option to OFF.

The following example shows several statements in which CLP options are modified as part of the statement execution.

To issue administrative commands like db2start (instance start) or db2stop (instance stop) on a Windows server, you have to open a DB2 Command Window in administrative mode (refer to Figure 6).

Listing 2. CLP options modified as part of statement execution
$ db2 "select sum(salary) from employee"


  1 record(s) selected.

$ db2 +c "update employee set salary = salary*1.5"
DB20000I  The SQL command completed successfully.
$ db2 +c "select sum(salary) from employee"


  1 record(s) selected.

$ db2 rollback
DB20000I  The SQL command completed successfully.
$ db2 "select sum(salary) from employee"


  1 record(s) selected.

To issue administrative commands like db2start (instance start) or db2stop (instance stop) on a Windows server, you have to open a DB2 Command Window in administrative mode (refer to Figure 6).


Like DB2 CLP, CLPPlus is a lightweight command line processor that is present on DB2 Data Server Driver packages and Clients. CLPPlus extends some features of DB2 CLP by adding the following functionality:

  • Support for connection to databases given only the database name, port, user ID and password (no DB2 directory files needed)
  • A text buffer that stores scripts, script fragments, SQL statements, SQL PL statements and PL/SQL statements
  • Multiple options for formatting the output of scripts and queries

CLPPlus also provides a customizable way of showing query results — data is presented in a more friendly fashion on query executions and the way it is presented can be customized. It is possible, for example, to format numeric results, change column headers, and so on.

CLPPlus is also a great way to welcome users coming from other database management products. With CLPPlus, most of the options and functionality individuals coming from other RDMS products are used to remains available — coupled with the power of IBM DB2.

You can access CLPPlus from the operating system menu, or by typing the command clpplus in the command line terminal. It's also possible to specify a database connection when invoking CLPPlus from a command line terminal by entering a command that looks like this:

$ clpplus userid@hostname:port_number/dbname

For example, if you know you can access a database named SAMPLE through port 50005 and your user ID is idngf01, you can start CLPPlus and establish a connection to the SAMPLE database by executing a command that looks like this:

$ clpplus idngf01@localhost:50005/sample

Figure 9 shows the type of output that is generated if a database connection is successfully established when CLPPlus is invoked.

Figure 9. Starting CLPPlus with database connection information
Figure showing CLPPlus successfully connected to a database.

Once CLPPlus has been started, you can use the following command to get an index of help topics that are available:

SQL> help index

Figure 10 shows the output that is generated when the help index is displayed.

Figure 10. Output from the CLPPlus HELP INDEX command
Figure showing some of the commands possible to be used in CLPPlus, as          output of HELP INDEX

To get specific help on any of the commands available, simply type HELP, followed by the command.
For example:

SQL> help set

By changing parameters with the SET command, it is possible to change the way in which data is displayed when queries are executed. It is also possible to edit your commands prior to executing them (or after receiving an error message) through the EDIT command. After the EDIT command is called, a default editor is opened (Notepad on Windows, vi on Linux) and you are expected to create and save a script. After the script is edited and saved, an the editor is closed, you can run the edited command through the CLPPlus RUN command.

Commands can have multiple lines — the forward-slash symbol ("/") provides a completion indicator to the editor — and thanks to the CLPPlus buffer, you can navigate through all of the commands that have been entered (since CLPPlus was started), by pressing the up and down arrow keys on your keyboard.

db2pd — Monitor and troubleshoot tool

The DB2 Problem Determination Tool (or db2pd) is a powerful tool with a command-line interface that can help you monitor and troubleshoot your databases with very little system interaction. (db2pd attaches directly to DB2 shared memory sets to retrieve system and event monitor information; consequently, it does not acquiring latches or consume system resources.) The db2pd tool can be run in either interactive or non-interactive mode.

Figure 11 shows the use of db2pd to retrieve information about DB2 memory usage in interactive mode.

Figure 11. Running db2pd in interactive mode
Figure showing db2pd in action, in interactive mode.

To run db2pd non-interactive mode, just issue the command db2pd, followed by the appropriate option.

For example: db2pd -dbptnmem

Data replication tools

DB2 provides two different solutions you can use to replicate data to and from relational databases (including some non-IBM databases): SQL replication and Q Replication. Both solutions can be configured and maintained by both the Replication Center (a stand-alone GUI for replication setup and maintenance) and the replication configuration command-line processor (ASNCLP). Such tools are not available on DB2 10 Express-C and Express editions.

While SQL replication is a bit simpler to configure and doesn't depend on additional products for homogeneous replication, Q replication tends to be used most often when big portions of a database need to be replicated, or when significant amounts of data need to be transferred to data marts/data warehouses. Q replication requires installation of InfoSphere Replication server as well as WebSphere® MQ (both are priced separately). As it divides a workload across these two products, Q Replication is able to replicate large volumes of data with a low level of latency.

Other tools

Other useful tools that can be found with almost any DB2 product edition are:

  • Visual Explain — Provides a visual depiction of the access plan that was chosen for a quey (either SQL or XQuery), thereby helping you solve query performance issues more easily. Visual Explain is part of IBM Data Studio — which will be covered a little later. Two additional tools that can be used to format and display Explain data are db2expln and db2explnfmt.
  • db2look — Use this tool to generate DDL statements for an entire database, for specific schemas, or for a list of tables. db2look is a command-line tool; however, its functionality is also present with IBM Data Studio, through the option "Generate DDL", which is available for most objects that can be accessed in with this tool.
  • db2level — Use this tool to display information related to the current level (version and release) of the DB2 product you have installed.
  • db2licm — Use this tool to determine whether to apply a license or to display licensing information for your installed products.
  • db2cfexp / db2cfimp — With these utilities, you can export and import connectivity information as well as configuration information for databases, database manager instances, and servers, all in one shot.
  • db2advis — With this extremely useful utility, you can improve performance, by having DB2 advise you on which indexes and MQTs to create (or drop).
  • db2top — Use this tool to monitor your database in real-time (not available in GUI / Windows).
  • db2diag — Use this diagnostics tool to understand what’s happening right now with your DB2 environment.

There are many other tools that you can learn and take advantage of. I encourage you to learn more about them from the Information Center for DB2 10.1.

4 of 10 | Previous | Next


Zone=Information Management
TutorialTitle=DB2 10.1 fundamentals certification exam 610 prep, Part 1: Planning