Running DB2 Queries Part 2 -- CLPPlus
Burt Vialpando and Dean Compher
21 January 2011
With the release of DB2 v9.7 you have a new command line interface called the or “CLPPlus”. This is available on all DB2 v9.7 clients and database servers. It gives the heritage DB2 person a new report generation tool. It gives the person who knows the Oracle SQL*Plus interface a way to query and do some database administration in a syntax they already know. This is especially good for those in a shop that has both DB2 and Oracle because it provides a common query and basic reporting interface for both databases! In this article I'll show you how to get started using the CLPPlus interface and provide several examples.
The bulk of this article is taken directly from material created by Burt Vialpando in his IBM DB2 9.7 Administration for the Experienced Oracle DBA Proof of Technology. The figures shown are also lifted directly from his presentation. If your local IBM database sales representative can arrange it, Burt can come to your city and deliver this Proof of Technology to you in person. I have also posted a number of scrips written by Burt in the “Files” section on my developerWorks community page. Please feel free to download them as they are a great way to get started with this tool.
We'll start out by giving some simple examples of using CLPPlus. The first thing that you need to do is to start the CLPPlus and connect either from a shell window or from the GUI menu. For more details about connecting, please see my previous article. Here are two quick examples connecting to the DB2 sample database:
$ clpplus MyUserID/MyPasswd@localhost:50000/sample
- or -
Start | All Programs | IBM DB2 | DB2COPY1(Defalut) | Command Line Tools | Command Line Processor Plus
SQL> connect MyUserID:50000/sample
The sample database comes with every version of the DB2 database server. If you want to try these examples yourself, just log in to any test database server instance and execute the (NO “e”) command and it will create the sample database for you complete with data. The following examples use tables and other objects in that database, so your results should be similar. Please note that CLPPlus works with all editions of DB2/LUW except the Express-C version.
Once you are connected you can run commands or queries. Here are some examples that you may recognize if you are used to using Oracle:
The describe employee command which is similar to the traditional DB2 describe table employee command is shown here:
To run operating system (OS) commands from the SQL> prompt you just prefix them with the HOST command, or starting in fp3a you can use the “!” as shown in the following two examples:
SQL> host dir
SQL> ! more db2diag.log
From within the CLPPlus tool you can do several things with your SQL including running SQL from a file, view previously executed SQL, show the SQL and edit the SQL. These commands and the ones like HOST that I showed above are all described in more detail by clicking the appropriate links on the page. Here are a few of the commands that you can use for queries with examples after that:
• Use the START command to execute SQL already in a file or the GET command to just load the SQL file into the buffer without executing it.
• Show the last SQL command executed using the LIST command.
• Edit SQL using the CHANGE command or the EDIT command
• Execute SQL using the RUN Command
• Save the query to a file using the SAVE command
• Tell CLPPlus to send the output of a query to a file using the SPOOL command or tell it to show the elapsed time for query execution
In the following example a simple query in the file called CLPPlus01.SQL will be executed from the SQL> prompt with the output shown afterwards:
Use the LIST command to display the last SQL command executed or in the buffer:
You can edit the SQL in the buffer by doing simple substitutions with the CHANGE command, for example, “SQL> change /salary DESC/salary ASC/” or by executing the EDIT command to edit the file in the default editor like Notepad or vi. You must save the file using the editor's save commands as you normally would to have the contents of the buffer changed. Here is an example of using the EDIT commands
In this case you would select File|Save to save the changes and then exit Notepad to get back to your CLPPLUS where your updated copy of the SQL will be automatically shown. To execute the query, just execute the RUN command at the SQL> prompt.
Now that you've verified the results of the query in the CLPPlus window, you can run the query again, saving the output to a file called employee_report.txt using the following commands:
SQL> SPOOL employee_report.txt
SQL> SPOOL OFF
In this case the RUN command just runs the query already in the buffer. You don't want forget to turn off the spooling or the results of subsequent work will also be saved to this file. To see the status of things like spooling you can use the show command as follows:
SQL> show spool
It is often important to measure the speed of your queries. You can do this by turning on timing. When this feature is enabled, CLPPlus will show you how much time it took to run your query. When you've spent a significant amount of time getting a query just right you will want to save it to a file too. Assuming that our query is still in the buffer you can time the execution and save the query in this example:
SQL> set timing on
SQL> save updated_query.sql
This just provides a few examples of running commands and queries in CLPPlus. As I noted earlier, you can see the complete set of commands on the page and get a description of each command by clicking on it.
Reporting and Running CLPPlus from Scripts
So far we've discussed running commands and queries interactively, but it is often useful to be able to run existing queries from the Operating System (OS) command line or from a script. This is especially true for reporting, so I also briefly discuss reporting features here.
You can execute an SQL script file using the CLPPlus command, denoting the input file using the “@” symbol. For example:
$ clpplus myUser/myPass@serverName:50000/dbName @fileName.sql
Here is a diagram that Burt provided showing what is in each segment:
Just as with the regular CLP, you can have several SQL queries and commands in a file separated by semicolons.
A frequent use of scripted queries is for reporting. CLPPlus adds a lot of reporting capabilities to DB2 that are easy and fast to implement when you need to create a good looking report in a hurry. It is also great if you need to create professional reports, but don't have any other reporting tools. It uses the same formatting commands as SQL*PLUS so if you create software that needs to run on both Oracle and DB2 you can create one report that runs on both. If you are familiar with creating reports for Oracle databases and now need to create them for DB2/LUW then there is not much new for you to learn.
To see all of the many report formatting capabilities of SQL plus such as defining column headings, defining lines per page, or defining report column lengths visit the command page. Burt also created some great examples of report formatting that you can copy from my developerWorks community page and use for the basis for your first reports. All of these reports operate on the sample database that comes with DB2, so you can run them right away to see what they do. You may need to change ports, hosts, userids or passwords, but they should otherwise work. If you find them useful, please also leave a note on the Message Board section thanking Burt! Here are the files on my community page that I recommend most for reporting examples:
• CLPPlus03.SQL - Called by the CLPPlus02.CMD DOS shell script
• CLPPlus04.SQL - Run interactively/Uses substitution variables
The CLPPlus02.CMD file is a DOS batch shell script that provides a good example of configuring the environment for CLPPlus and then executing an SQL file from within the script. You can execute the 04.SQL script from the command line as follows:
C:\> clpplus myUser/myPass@serverName:50000/sample @CLPPlus04.SQL
You will then be prompted for two values. This example shows what you will see and what you should enter:
Unless you change it this script will create a file called CLPPlus04_OUTPUT_REPORT.txt because that is what is defined in the “spool” command in the script. You can review that file for the report created.
This article is the second installment of an occasional series that I am writing about how to query your DB2 database. There are several more things that you can do with CLPPlus that are beyond the scope of these articles. However, I have included Burt's examples on my community page. They are well documented, so you can review them and see what they are doing. They primarily show examples of using traditional Oracle query and command syntax on DB2. Examples include using the DECODE and ROWNUM commands, running PL/SQL scripts, Anonymous Block, and DBMS package use, plus other interesting features. You will need to set the registry variable correctly to use a number of these features.
If you want to try out these examples, but don't have a database server you can experiment with, you can just download a free trial version of DB2 from the page and put it on your Windows or Linux workstation or small server. Another alternative is to get a very inexpensive server in the cloud. See how to do this on my article. Once you have a DB2 v9.7 instance, you can create the sample database and start running these examples! Regardless of the method you use of getting access to a DB2 v9.7 database, make sure that you are not using the “Express-C” edition .
A very few database administration commands can be run in CLPPLUS, but for most you will need to use the CLP for administering your database.
More and more collateral is being added to the web all of the time about CLPPlus so keep looking. You can even see a how-to video. It is only 6 minutes, but has lots of great information for getting started. Stay tuned for part 3 of my series of client articles where I'll compare and contrast the various free query and administration tools to help you pick the right one for your purpose. Finally don't forget to visit my developerWorks community page for CLPPlus examples and to share insights you have about using the CLPPlus.
Part 3 of this sears describes that actual free tools that you can download to query DB2 Databases.