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®


IBM Data Studio

Having powerful and productive command line tools to administer database servers is something indispensable for every experienced DBA. But what about newcomers?
Graphical user interface (GUI) tools are great for users who are just starting with any product — especially when it comes to database management systems. Usually, GUI tools are of great help even for the most skilled professionals — it’s just not possible to memorize all the commands you need!

IBM Data Studio is the new standard GUI tool for DB2 databases administration, management and development. Through an integrated and modular environment, Data Studio provides collaborative database development tools for DB2 for Linux, UNIX, and Windows, DB2 for z/OS, DB2 for i, Informix and other non-IBM database products, with support for several programming languages.

You can download Data Studio at no-charge, or can you can get it together with DB2 when downloading the product (by selecting Data Studio as well through use of Download Director). It is also possible to download Data Studio when installing DB2 through the DB2 installation launchpad. The version of Data Studio that is currently available (at the time this tutorial was written) is 3.1.1.

Figure 12 shows what the start-up panel for this version looks like.

Figure 12. The start-up panel for Data Studio 3.1.1
Figure showing the Data Studio splash-screen (program start-up)

Data Studio components

There are 3 different components to choose from when downloading IBM Data Studio (all are available at no-charge):

  • IBM Data Studio Administration client
  • IBM Data Studio Full client
  • IBM Data Studio Web console

The Administration client is a lightweight tool for administering databases and meets most of the basic development needs for DB2 for Linux, UNIX, and Windows and DB2 for z/OS. The Full client expands the functionality of the administration client to support development of Java, SQL PL and PL/SQL routines, XML, and other technologies. You must decide which version (administration or full client) will better meet your needs. Both full and administration client components can be installed on Linux and Windows platforms.

Web console complements Data Studio by adding monitoring and job management, both of which can be accessed through a web browser (you’ll see more about this product later).

Getting started with IBM Data Studio

If you are already used to Eclipse-based applications, you'll very likely feel comfortable with the Data Studio interface. After spending some time with the tool, users coming from the DB2 Control Center will find Data Studio very intuitive and easy to use. Figure 13 shows the basic interface for IBM Data Studio.

Figure 13. The basic interface for IBM Data Studio
Figure showing the Data Studio tree and other areas.

One key concept in Data Studio is that almost all functionality is accessed through context menus, which are presented whenever you right-click on the object you want to work with. To find out what options are available for any object, simply highlight that object and put your mouse’s right button to work!

When you first start Data Studio, you are presented with the Task Launcher view, which welcomes you to Data Studio and provides you with a list of some of the tasks you can perform. The Task Launcher view can be seen in Figure 14.

Figure 14. The Task Launcher
Figure showing the Data Studio Task Launcher.

To connect to a database from the Task Launcher, select Administer, followed by Connect and browse a database. Then, when the Select Connection dialog is presented, click New. (You can do the same thing by choosing the Administration Explorer view and clicking the button New. Figure 15 depicts the menu items that must be selected to establish a new connection in this manner.)

Figure 15. Establishing a new database connection
Figure showing a path to create a new connection to a database.

When the New Connection dialog appears, select DB2 for Linux, UNIX and Windows in the left-most box (to connect to a DB2 for Linux, UNIX, and Windows database), and enter the appropriate information such as database name, host name, port, user ID, and password. Figure 16 shows a New Connection dialog whose input filed have been populated with this type of information.

Figure 16. The New Connection dialog
The dialog shown when attemting to connect to a new database.

(Before you can connect to any database, the database must first be created; DB2 ships with a SAMPLE database that can be created at any time by executing the command db2sampl,or by making the appropriate selection from the First Steps utility.)

DB2 Control Center (deprecated)

In versions prior to DB2 10.1, there was a GUI tool called the Control Center that offered Wizards that could be used to guide database administrators through the steps needed to complete specific tasks (such as backing up a Db2 database). The Control Center was deprecated in DB2 9.7 and was discontinued in DB2 10.1.

IBM Data Studio and the Data Studio web console replace (and in some cases, extend) most of the functionality that was available with the Control Center; both offer task assistants in place of the Control Center's Wizards, to help you to perform database administration/development tasks (and to help you better understand what’s required to do each task). For a complete mapping of features and functionality between the Control Center and Data Studio, refer to this developerWorks article: Migrating from DB2 Control Center to IBM Data Studio

The Test Connection button on the New Connection dialog can be used to check the validity of the data entered; when this button is pressed an attempt is made to connect to the database specified using the credentials and connectivity information provided.

It is important to note that you can use most of Data Studio’s functionality without having to install a DB2 client/driver package. That’s because Data Studio comes equipped with drivers that offer connectivity to several database products (usually through JDBC). Therefore, if you plan to administer DB2 remotely, or you plan to develop using a remote database server, having only IBM Data Studio installed locally should be enough.

Now that you know how to establish a database connection in Data Studio, let's take a look at Data Studio’s interface.

As an Eclipse-based application, IBM Data Studio makes it possible for you to choose between different perspectives, making its use more directed to what you intend to do. For example, when you choose the Database administration perspective, Data Studio’s interface changes to show you the view elements that are relevant to database administration like the Administration Explorer, the Job Manager, and so on. Figure 17 illustrates how you can select the Database Administration, Data and IBM Query Tuning perspectives.

Figure 17. Changing between perspectives
Figure showing the perspectives in Data Studio.

Perspectives, as the name implies, influence only the way in which you see what you are doing in Data Studio: when you switch between perspectives, nothing is lost — only the way in which you view what you have done is affected. A variety of perspectives are available. Figure 18 shows all the perspectives that exist in Data Studio full client.

Figure 18. The different perspectives that are available with Data Studio full client
A list of icons and their descriptions describing the perspectives available in Data Studio

Now, let’s turn our attention to some of the basic functionality that’s available with Data Studio.

Accessing objects and data

Usually, the one thing users want to see in a database management tool is the ability to examine a database’s tables and views.

To view the tables within a database you can navigate through Administration Explorer view (under Database Administration perspective) and after successfully connecting to a database, the Tables folder will be shown in the tree that’s presented on the left-hand side of the screen. By clicking the Tables folder, you will be able to get a list of all the tables available in the Editors area.
Accessing tables through the Data perspective has a different path: you must go through the Data Source Explorer view and open the Schemas folder to obtain a list of the tables that have been defined within the schema chosen.

Figure 19 shows how to access tables in the SAMPLE database using the Database Administration perspective.

Figure 19. Accessing tables with the Database Administration perspective
The tables and their contents in Data Studio

As you can see, when using the Database Administration perspective, every folder in the Administration Explorer view shows you all existing objects of a particular type (for example, all tables, all schemas, all sequences, and so forth). But when you switch to the Data perspective, navigation follows a top-down hierarchy. Within a database, you can see the schemas, and inside each schemas, you can see tables, views, sequences and so on that have been defined within that schema). You get to choose the way you want to navigate.

Right-clicking on a table will show the context menu with all the options that can be performed on that table. And by using this context menu, you can operate the table specified as you wish.

Data Studio also has a diagram editor, which can be used to better visualize a database’s objects and the relationships between them. Figure 20 shows you the menu items that must be selected to invoke the diagram editor.

Figure 20. Invoking the diagram editor
Figure showing the menu used to open the diagram editor

You’ll be prompted to choose additional tables as you wish, and then Data Studio will present the tables you have selected, arranged in a diagram as requested. Figure 21 illustrates how a diagram produced by the diagram editor might look.

Figure 21. The diagram editor interface
Table objects shown in a diagram.

Using a diagram like the one produced by the diagram editor makes it is easier to understand structures and object relationships.

Changing data

Using context menus, it’s also possible to browse and edit data. (Simply right click on a selected a table and choose the Browse Data or Edit Data menu option). You can edit data in line and when you save, Data Studio will show you the SQL statement(s) that were used to make the changes.
Data Studio highlights changes in yellow; Figure 22 illustrates an example where the data for a row and column has been changed. Figure 23 shows the SQL that was used to make the change.

Figure 22. How modified values are presented
Changed data is highlighted in yellow.

Figure 23. The SQL that was used to make the change shown in Figure 22
Data Studio shows you the SQL it used to apply your changes

Issuing queries against a database

Data Studio assists you when writing SQL or XQuery queries. To create a new script (that is, a script that does not contain any statements), simply highlight a database object, right-click on it to bring up a context menu, and select the New SQL Script option. (The same thing can be done by right-clicking on an SQL Scripts folder in any project you select under Data Project Explorer — the context menu will be shown as "New SQL or XQuery Script").
All SQL or XQuery scripts you create will be shown on the Data Project Explorer, within the project you chose, when it is saved.

As with other Integrated Development Environments (IDEs), Data Studio can assist you as you enter queries in a script, by auto-completing object names. This functionality is provided via the Content Assist context menu item. Figure 24 illustrates how this menu item looks.

Figure 24. Using Content Assist
Data Studio helps you with Content Assist to have your commands auto completed.

Data Studio can also help you format any unformatted SQL. Figure 25 illustrates how the "Format SQL" menu item can be used to apply the proper formatting to an unformatted SELECT statement.

Figure 25. Using Format SQL
Data Studio shows can format your SQL for an improved visualization.

There’s also the Content Tip function, which assists you with syntax tips as you write your scripts. Figure 26 illustrates how Content Tips are presented.

Figure 26. Content Tips
How Content Tips are presented.

Once you are done and are ready to submit your script/query to DB2 for execution, you simply just press the Run SQL button (the green circle with a triangle in it that looks like a Play button). This can also be done by pressing the F5 key. And if your script contains multiple queries, you can execute an individual query by selecting just the text for the query that you want executed, before pressing the Run SQL button or the F5 key. Figure 27 illustrates how the Run SQL button looks (on the Data Studio tool bar).

Figure 27. The Run SQL button
How the Run SQL button looks (on the Data Studio tool bar).

With this toolbar, it is also possible to show the access plan for a query (Open Visual Explain button), tune a query (Start tuning button), import or export the SQL script (In-Box and Out-Box buttons), or schedule a query to be executed later (Clock button).

Retrieving results

When you execute queries, Data Studio shows the results in either text form or in a grid. Figure 28 illustrates how query results are returned in a grid; Figure 29 shows query results being returned as text.

Figure 28. Query results shown in grid mode
How query results are returned in a grid.

Figure 29. Query results shown in text mode
Query results being returned as text.

Tuning queries inside Data Studio

In a perfect world, DB2 would always return the results for a particular query “in the blink of an eye." But in the real world, you are required to create appropriate indexes and materialized query tables (MQTs), keep statistics up to date, and eliminate any fragmentation in your data (through reorgs) before DB2 can deliver the best query response times possible. The DB2 Optimizer does an exceptional job in choosing the access plans that are used to retrieve the data that is needed to resolve a query (and is being continually improved as new versions are released), but there are times when some queries simply do not behave as expected. For these types of queries, Data Studio can help you identify problems and solve performance issues by advising on the appropriate action(s) to take.

Data Studio offers basic tuning features (Statistics advisor, Query formatting, Access Plan graph and Reports) free of charge and many of these features can be extended with the purchase of InfoSphere Optim Query Tuner for Linux, UNIX and Windows.

As with most of the Data Studio functionality, it is possible for you to invoke the query tuner from many places. One possible way of calling it is through the SQL script editor (by pressing the Start tuning graphical button.) After pressing this button and interacting with the query tuner assistant, you should get a screen like the one that’s shown in Figure 30.

Figure 30. The Query Tuner Workflow Assistant
The screen showing the access plan for the query and the additional options available for tuning.

If you look closely at Figure 30, you will see a data access plan that is being displayed for a particular query. By analyzing access plans, you can determine whether or not a query is using indexes efficiently. Data Studio can also suggest running statistics on tables that a query frequently accesses. You’ll have more tuning alternatives available when you use InfoSphere Optim Query Workload Tuner (paid-separately), or alternatively, the DB2 Advisor, which can be invoked by executing the db2advis command.

Database administration with Data Studio

Earlier, we saw how to switch to the Database Administration perspective to facilitate database administration work inside Data Studio. After changing to this perspective, you’ll be presented with the Administration Explorer view.

Inside the Administration Explorer view, there are a lot of objects shown that you can choose to operate with. It is possible to change objects such as tables, columns, indexes, and for every change you make, Data Studio creates a change plan that analyses impacts and can be used to implement your changes — or schedule them to be done later. Change plans are saved in the “Change Plans" folder on the Administration Explorer view. And objects that have a pending change plan have their icons changed to a delta sign (like a triangle) to visually indicate that they have changes that are pending.

Figure 31 illustrates how to change a column in a table, and the menu items that must be selected to review the resulting change plan that gets generated.

Figure 31. Altering a table's structure, and reviewing it through a Change Plan
The table's structure was changed by changing a column, and Data Studio created a Change Plan for it.

As you can see in Figure 31, there’s also an option called Review Undo Script which you can use to revert all changes, if necessary. When the Review and Deploy menu item is selected, the change plan that was generated will be displayed in the Review and Deploy dialog and you will be given the opportunity to run the plan immediately or schedule it to be executed at a later date and time. Figure 32 shows what this dialog looks like.

Figure 32. The Review and Deploy dialog
The dialog for change plan review.

If you elect to run the change plan right away, the results of the execution will be displayed in the SQL Results window. Figure 33 shows what this window looks like.

Figure 33. The SQL Results window after changes to an object have been made
After the review, the change plan was deployed.

To execute administrative tasks, Data Studio connects to the database server specified using Secure Shell (SSH). DB2 10.1 includes a lightweight SSH server with Windows installs; on Linux and UNIX servers the SSH server that is available with the operating system is used instead. When connecting to older versions of DB2 on Windows, Data Studio can also make use of the Database Administration Server (DAS) if there’s no SSH support available. (DB2 8.2 and earlier only uses DAS; the DAS was deprecated in DB2 9.7 and may be discontinued in a later version.)

Monitoring and managing jobs through Web Console

Data Studio web console is available as a separate download, and can be used to monitor database health and availability, as well as manage scheduled jobs through a web browser. Data Studio web console contains a web server which can be accessed from anywhere — any device that has a web browser and can connect to the web console can be used to monitor a DB2 database. Thus, you can keep an eye on a database using a smartphone or tablet — you’re not limited to having to use a computer. Data Studio web console is available on Linux, AIX, HP-UX, Solaris, and Windows platforms. Figure 34 shows what the opening screen of the Data Studio web console looks like. Figure 35 shows how the Data Studio web console can be used to monitor the health of a database.

Figure 34. The Data Studio web console
After the review, the change plan was deployed.

Figure 35. The Health Summary panel of the Data Studio web console
How the Health Summary pannel looks like within the Data Studio web console.

5 of 10 | Previous | Next


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