Running DB2 Queries – Part 3
21 March 2011
Updated 2 January 2013
There are a variety of different tools that you get complementary with DB2 on Linux, UNIX and Windows (DB2/LUW) that will allow you to query your database and run commands, but how do you choose which one to use? Now that Optim Database Administrator and Optim Development Studio come complementary with DB2/LUW, there are even more choices. In this article, I'll give a brief summary of these tools and give my thoughts on when to use each of them. In some cases this will just be your preference. In some of my earlier articles, I've discussed the packages that contain these tools and where to get them and have even gone more in depth about some of the individual tools and where to get them 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 DB Queries Part 2- CLPPlus – Describes the new CLPPlus interface
• Data Studio Compares and contrasts the GUI Control Center and Data Studio and describes Data Studio
I want to take a moment to thank César Inacio Martins who wrote to me suggesting this article. If you have ideas about what you want see in future articles, please e-mail me or add a suggestion to the Message Board on my db2Dean and Friends developerWorks community page.
The tools that I'll compare in this article are:
• Data Studio (GUI)
• Optim Development Studio (GUI)
• Optim Database Administrator (GUI)
• Control Center/Command Editor (GUI, Deprecated 9.7, Discontinued 10.1)
• Command Line Processor – CLP (Command Line)
• CLPPLus (Command Line)
The complimentary GUI interfaces for running Queries on DB2 are IBM Data Studio, Optim Development Studio, Optim Database Administrator, and the DB2 Control Center. Data Studio, ODA and ODS are the up and coming GUI Clients, but some functions such as the control of Replication are not there. The Control Center has been deprecated, but currently has more functions, especially at the instance level. For more information about these clients see my previous Data Studio article.
The IBM Data Studio allows you to connect to DB2 on ALL platforms (LUW, Z, I) without DB2 Connect and Informix too. It has a helpful query editor and provides a great way to organize and recall your SQL scripts in the Data Project Explorer. Check out this developerWorks article about the Data Studio Query Editor. Among other things the Data Project Explorer provides an expandable menu like Windows Explorer that lets you organize your scripts in a hierarchy or groups and view/edit them by just double clicking. You can also create and debug stored procedures with Data Studio. Data studio allows you to do many database level administration functions like alter table, export, runstats object creation, etc., by right clicking the appropriate object or heading in the Database Explorer. When you execute those administration functions you will be led through a set of menus to choose parameters. At anytime you can view the command being created. Data studio only runs on Windows and Linux and makes a JDBC Type-4 connection to the database. Anyone can download Data Studio from the web. Two versions are available as described on the download page. If you think that you might want to use Data Sudio with other products later, then I recommend getting the “IDE” version. The only advantage to the stand alone version is that it takes less space on your hard drive. One other download link available on this download page is the Data Studio Health Monitor. It pretty much does the same thing that the Health Center does in Control Control Center.
Data Studio is unable to do some instance level administrative tasks like starting and stopping instances. Data Studio does not talk to the Database Administrative Server (DAS). The DAS is a separate process on the database server that can control database instances. Since Optim Development Studio and Optim Database Administrator have everything in Data Studio and are now free for customers with up-to-date maintenance, I recommend using one of them instead of Data Studio, unless you just need a lightweight query tool. All of these three tools also allow you to create XQuery XML queries. Please also see the IBM Data Studio Overview where you can drill down into more detailed information.
Optim Development Studio and Optim Database Administrator have now been consolidated in to the free IBM Data Studio 3.1.
Optim Development Studio (ODS) has everything in IBM Data Studio described above and several additional features, but with the same instance administration limitations. ODS is now free with DB2/LUW as long as your maintenance is up to date, but requires a separate download from Passport Advantage, the same place you would download DB2 and other licensed products. Please note that ODS and its Activation Kit are imbedded in the set of products under the main DB2 collection and only under the ones for Linux and Windows. It also has the same look and feel of Data Studio and other OPTIM products because they all use the same views like Data Project Explorer and Data Source Explorer, and uses the same Query Editor. While Development Studio does many things useful to the Java developer. Like generating methods for several tables at once, those things are beyond the scope of this article. One of the best parts of ODS is that it better facilitates collaboration between developers and DBAs during Java development projects. For the DBA it does all of the database administration tasks described above under Data Studio, plus it has other useful features. For example, you can run a Java application in ODS and see performance metrics about each query issued during the run or set of runs. In reality, the Java developers will typically run the application under ODS on her own workstation capturing the correct information, export the project and send it to the DBA where he can review the queries. Once the DBA has the ODS/pureQuery output the queries can be sorted by metrics like longest elapsed time or most CPU time and each interesting query can be explained right in the tool. Further, the ODS user can often see the Java method and line number from which the SQL statement originates. How to do these and other functions is explained in the 5-part Optim Development Studio article. While the title of this article starts with, “What's New and Cool...” it really gives a nice overview of the product. Make sure to click through to all 5 parts. Please also see the IBM Optim Development Studio Overview where you can drill down into more detailed information.
Optim Development Studio and Optim Database Administrator have now been consolidated in to the free IBM Data Studio 3.1.
Optim Database Administrator (ODA) has everything in IBM Data Studio described above and several additional features, but with the same instance administration limitations. ODS is now free with DB2/LUW as long as your maintenance is up to date, but requires a separate download from Passport Advantage, the same place you would download DB2 and other licensed products. Please note that ODA and its Activation Kit are imbedded in the set of products under the main DB2 collection and only under the ones for Linux and Windows. It also has the same look and feel of Data Studio and other OPTIM products because they all use the same views like Data Project Explorer and Data Source Explorer, and uses the same Query Editor. ODA is a great tool for the DBA who needs to change databases, while preserving the data, permissions, indexes and other dependent objects even when tables need to be dropped and recreated. For more information on ODA, please visit the ODA Information Center. A good place to start is the “Learn More” link.
ODA allows you to reverse engineer the database into the tool, make changes to the objects using easy to use object editors and then when you are done it will generate a script to implement those changes in your database. When run, the script will capture all objects and permissions in the current database, keep a copy of data in the form that you dictate like a shadow tables or export/import/load processes, and drop/create or alter objects as needed. For a large complex change, this can save weeks of work and increase the quality of the change on implementation night. ODA will also generate a script to back out all changes in case something goes wrong. In addition to implementing changes that you input, ODA can also compare two databases, and generate a script to make the structure of one database like the other, preserving all data in the database being changed. This is great where you have a months-long development project where you are making several changes to the test database over time. At the end of the project, you can compare test to production, and tell ODA to generate a script to make the production database schema look just like the test database, while preserving the production data. ODA can also be used to generate scripts to copy databases or portions of them. Finally if you have Infosphere Data Architect, our data modeling tool, you can compare a model to a database and have ODA generate the change script to make the database look like the model.
You can have both Optim Database Administrator and Optim Development Studio on the same workstation. If you install them such that you shell share them, then you can do all functions provided by the combination of tools from one window. Adding one after the other is installed just adds expands the tools available in the same window. This is all part of IBM's Integrated Tools approach. Data Studio, ODA and ODS only run on Linux and Windows and are primarily designed to run on a workstation as opposed to installing directly on a server.
Starting with DB2 10.1, the Control Center and Command Center are no longer included in any clients or servers. However, some GUI tools are still included such as the Replication Center. The IBM Data Studio is now the GUI tool that should be used for most administration purposes. While you can use the v9.7 Control Center, you can not use new features like the INGEST utility from it. Most SQL queries should work from the v9.7 Command Center, but at some future server version, the v9.7 client will stop working.
The Control Center and its component Command Center have been around a long time, but have been deprecated with the release of DB2 v9.7. This means that they are no longer being improved, and at some point they will not work with new versions of DB2. Command Editor is a basic GUI SQL editor and is a component of Control Center that can be invoked from within Control Center or from the command line or GUI menu. Control Center allows you to perform all database and instance level administration tasks. You need to have the DAS running on your database servers to allow it to stop and start your instances and some other instance level administration tasks, but database object commands can be executed without the DAS. You can perform all object related administration tasks like alter table, export, runstats object creation, etc., by right clicking the appropriate object or heading in left pane of the window that shows databases and objects under them. Using the Command Editor you can run any SQL statement and some DB2 commands.
You can install the Control Center on your Windows or Linux server from the “Data Server Client” as described in DB2 Clients – Connect to your Database or optionally when installing DB2 Connect or a DB2 Database Server. Starting in DB2 v9.1, Control Center no longer installs on any flavor of UNIX. Unless you need one of the functions that are not in Data Studio, ODA or ODS, I recommend that you use one of those tools instead since Control Center will go away sometime in the future. You can start the Control Center either from the Linux or Windows GUI menus, or from an OS shell window using the db2cc command as long as your environment is configured.
The CLP is a command line interface that is always installed with all DB2 servers, clients, and even with DB2 Connect. It can be executed locally on a server through an SSH or Telnet window, or you can execute it on your workstation and then connect to the database server you desire. You would typically catalog the database before connecting to it from a client (i.e., the CLP on your workstation). I consider the CLP the workhorse for database administration. It is typically run by just logging into a Linux or UNIX server through something like Telnet or SSH, or on Windows by taking remote control of the server and then opening a “Command Window”. These CLP windows are really just a regular shell window on the OS with DB2 specific environment variables set such as the path to the DB2 libraries. You just need to inherit the <instance_home>/sqllib/db2profile to set these. Once you have this and a login to the database with appropriate authorities you can run all queries, DB2 Commands (db2 list applications) and system commands (db2level).
While it is true that I am a line command nut, I can objectively say that the CLP is hands-down the best tool for administration. It always works when you can get a login to the database server and it is fast. I visit a lot of customers, and knowing the CLP means that I can help with the database regardless of whether anyone has installed a GUI client, has xWindows working or can make a client connection to the database. Also I've created SQL scripts for many activities and find it less time consuming to just run them from the CLP than trekking through a series of GUI menus to do what I need to do. Further, in an attempt to make things easier for you by making certain assumptions, GUI's often make troubleshooting more difficult. First, you don't always know what the GUI client is really executing against the database, so you don't always know what you are actually troubleshooting. Secondly, GUI's sometimes give some generic error message that they generate and hide or truncate the real error codes and messages actually coming from the database. When you use the CLP, you know exactly what is being executed and get the full, raw messages and codes from the database. Please do not construe this to mean that I don't use GUI tools -- on the contrary, I find them very useful and they can save a lot of work. The ones that have a button to show you the SQL or other commands being generated are great because they save you the trouble of getting the syntax exactly right, and you can then paste it into your script that can be executed in the CLP over and over.
The CLP is always invoked by the command “db2” and can be run in a line command mode or in interactive mode as described in Running Queries- Part 1. On Windows or a Linux GUI you can directly enter the CLP interactive mode by choosing the “Command Line Processor” from the DB2 submenu (under the Start button on Windows), but this just starts a regular DOS window or Linux shell window that runs the “db2” command for you to get you into the CLP interactive mode. You run the line command by issuing the “db2” command followed by a query, command, or the -f option and providing an input file that contains one or more commands or queries. I generally prefer using the line command mode, just because I like combining shell utilities like the pipe “|”, file redirect “>” or “more” with my queries.
CLPPlus or “Command Line Processor Plus” is similar to the CLP and lets you run any queries you like. I have described it in a previous articled called, . Running DB2 Queries Part 2 – CLPPlus. CLPPlus allows those familiar with Oracle's SQL*Plus syntax to generate queries and run PL/SQL in a way that they already know and has great reporting features that allow you to quickly and easily format query output into a report. The CLPPlus does not run most system commands like db2level or db2ilist and does not support most CLP commands like “list tablespaces show detail”.
Another way to query the DB2/LUW database and perform administrative tasks is through programming languages such as C, C++, REXX or COBOL. There are Application Programming Interfaces already built for most of the commands you would execute from the command line or script. I don't know much about this, but there are good explanations and lots of examples of using them in the Administrative API section of the Information Center. Please make sure to scroll all the way down when you get tho this page for lots of links that show information about many facets of this interface.
I hope that this article has been useful for determining which complimentary tool is right for you and when you should use each. Please add feedback to my db2Dean and Friends developerWorks community page with additional insights that you believe may help others who read this article. Also if you are on Facebook, please “Like” my db2Dean page.