Menu
Task launchers and tutorials. Use a Task Launcher and view the tutorials to help you get started in IBM Data Studio. Task Launchers: Use the Task Launchers to.
Posted Feb 5, 2008
By Paul Zikopoulos
So far in this series about the IBM Data Studio integrated development environment (IDE) that’s available with DB2 Version 9.5 (DB2 9.5), I’ve shown you how to set up database connection objects and the actions that you can perform on them. In addition, I introduced you (in Part 2) to some of the features available in the Database Explorer view, and Part 3 and Part 4 gave you a full tour of overview diagrams. In Part 5, I showed you how to point-and-click your way to OLE DB functions that can integrate data from external data sources that have an OLE DB provider. In this part, I’m going to show you how easy it is to leverage the SQL editor in IBM Data Studio to quickly and easily build an SQL statement.
Things you have to do to follow the examples in this article...
I recommend that you start with Part 1 because I tend to build on the concepts and objects created in this series sequentially. For this article, I assume that you’ve at least created the SAMPLE database (using the db2sampl –xml –sql command). Of course, I’m assuming that the SAMPLE database appears in the Database Explorer view and that you have a data development project created. You can use the DatabaseJournalProject that I quickly showed you how to create in Part 5, or create a new one, as shown below:
You can see in the previous figure the OLE DB function that we created in Part 5 of this series.
Building an SQL Statement using the IBM Data Studio
IBM Data Studio comes with a built-in tool called the SQL Builder for creating SQL statements. This tool can be invoked from different parts of the integrated development environment (IDE); for example, it’s available in various wizards for building routines, and it can be started on its own to build standalone SQL statements.
The steps that follow outline how to build the following query using the SQL Builder:
This query returns a predefined number of attributes that are joined from the EMPLOYEE and DEPARTMENT tables for all female employees registered for a fictitious company.
To build an SQL statement using the IBM Data Studio SQL Builder, perform the following steps:
1. Select the SQL Scripts folder from your project, right-click, and select New->SQL Statement.
2. The New SQL Statement window opens. Select a project from the Project name drop-down list that you want to associate this SQL statement with; for our example, use the DatabaseJournalProject project. In addition, enter FEMALEPERSONNEL as the name for this SQL statement in the Statement name field, and select the SQL builder radio button. The default SELECT option in the Statement template field is fine since we are building a SELECT SQL statement. Now click Finish.
In the previous figure, you can see that you can use the Project name drop-down list to select any existing data development project that you’ve created in IBM Data Studio. If you want to create a new one, or don’t have one, click New and this will launch the New Data Development Project wizard.
IBM Data Studio provides templates for multiple SQL statements. The default template creates a SELECT statement, but as you can see in the previous figure, you can use the Statement template drop-down list to select one of several templates provided.
Also, note in the previous figure that you have the option to create your new SQL statement using the SQL Editor as opposed to the SQL Builder. While the SQL Editor lets you build your SQL statement, this option doesn’t give you all of the assistance features associated with the SQL Builder. For this article, we’ll stick with the SQL Builder. As you get more experienced with IBM Data Studio, you can use SQL Editor to build other SQL statements. In the end, you’ll decide just how much ‘hand holding’ you want; either facility will give you a productivity boost.
3. Depending on the way you configured your database connection object, you may be prompted to enter a valid set of credentials to connect to the database for which the database connection object was created. If this is the case, enter in the credentials for a user account with access to the data server, and click OK.
If the connection is successful, IBM Data Studio will create a template for the type of SQL statement you are trying to create, as shown below:
4. Right-click in the To add a table… pane and select Add Table.
5. The Add Table window pop-up appears. Select the EMPLOYEE and DEPEARTMENT tables, and then click OK. (Use the CTRL key to select multiple tables from the Add Table window - or add them one at a time.)
You may have noticed that the Add Table window has a Table Alias field. As its name implies, you can use this field to give a table an alternative name; however, when you add multiple tables to the SQL Builder at the same time, this field becomes inactive.
If you are dealing with complex table names, or simply want to refer to your tables using an alias name, you need to select them one at a time and assign each an alias name.
For this example, you can optionally remove the DEPARTMENT table that you’ve already added (simply click to select it and press Delete), and add the DEPARTMENT table again using an alias of DEPARTMENTALPERSONNEL.
The SQL Builder should now look like this:
You can see that the SQL Builder provides visual cues to certain relationships in each column. For example, a primary key is represented by and a foreign key relationship by . You can also hover over any column in the SQL Builder and hover help will let you know the underlying data types (especially useful when creating SQL statements that contain a join):
Home > Articles > Data > DB2
␡- IBM Data Studio
< BackPage 2 of 10Next >
This chapter is from the book DB2 Essentials: Understanding DB2 in a Big Data World, 3rd Edition
This chapter is from the book
This chapter is from the book
DB2 Essentials: Understanding DB2 in a Big Data World, 3rd Edition
IBM Data Studio
IBM Data Studio is included in every DB2 edition. IBM Data Studio provides a single integrated environment for database administration and application development. You can perform tasks that are related to database modeling and design, developing database applications, administering and managing databases, tuning SQL performance, and monitoring databases all in one single tool. It is an ideal tool that can greatly benefit a team environment with different roles and responsibilities.
IBM Data Studio comes in three favors: full client, administration client, and web console.
The full client includes both the database administrative and the application development capabilities. The development environment is Eclipse-based. This offers a collaborative development environment by integrating with other advanced Eclipse-based tools such as InfoSphere Data Architect and InfoSphere Optim pureQuery Runtime. Note that some of the advanced InfoSphere tools are only included in the DB2 Advanced editions and the DB2 Developer Edition. You can also separately purchase the advanced tools.
The administration client is a subset of the full client. It still provides a wide range of database administrative functionality such as DB2 instance management, object management, data management, and query tuning. Basic application development tasks such as SQL Builder, query formatting, visual explain, debugging, editing, and running DB2 routines are supported. Use the full client for advanced application development features.
The web console, as the name implies, it is a web-based browser interface that provides health monitoring, job management, and connection management.
IBM Data Studio Workspace and the Task Launcher
When you have successfully installed the IBM Data Studio, you are asked to provide a workspace name. A workspace is a folder that saves your work and projects. It refers to the desktop development environment, which is an Eclipse-based concept.
Task Launcher is displayed, which highlights the following category of tasks:
- Design
- Develop
- Administer
- Tune
- Monitor
Each category is described in more detail in its own tab. Click any tab, and you see the key and primary tasks listed in the box on the left. See Figure 4.26 to get an idea on how to navigate the Task Launcher.
As an example, the figure shows you the Develop tasks. You can find the key development tasks on the left. On the top right, it lists more tasks related to development. On the bottom right, IBM Data Studio provides a few documentation links where you can learn more about development. Where appropriate, it also suggests the advanced tools available in the InfoSphere Optim portfolio that apply to the task you have selected.
Connection Profiles
Every task you were to perform against a database requires to first establish a database connection. To connect to a database from IBM Data Studio, open the Database Administration perspective. On the top right corner, click the Open Perspective icon and select Database Administration.
On the Administration Explorer, right-click the white space or under the New menu, select New Connection to a database. From the New Connection window, you see that you can use the IBM Data Studio to connect to different IBM data sources, as well as non-IBM data sources. Select the database manager and enter the necessary connection parameters. Figure 4.28 shows an example.
Figure 4.27 Open the Database Administration perspective
Pull down the JDBC driver drop-down menu, and you can select the type of JDBC driver to use. JDBC type 4 driver is used by default.
Use the Test Connection button to ensure the connection information you enter is valid. Click Finish.
At this point, you have created a connection profile. Connection profiles contain information about how to connect to a database such as indicating the type of authentication to be used when connecting the database, specifying default schema, and configuring tracing options. Other team members can import the connection profiles to their own IBM Data Studio and be able to deploy a set of consistent connection settings.
To update the connection profile, right-click the database and select Properties. Properties for the database are displayed as shown in Figure 4.29.
Figure 4.29 Updating the connection profile
General Database Administration Tools
There are few other useful administration tasks available in the menu illustrated in Figure 4.29.
The Manage Connection function enables you to rename the connection profile, delete the connection profile, change the user ID and password, and duplicate the profile. The Back Up and Restore function enables you to setup a database or table space backups. In the appropriate editor, you can specify the type of backup, location of the backup images, and performance options for the backup. Database backup and recovery is discussed in Chapter 10, “Maintaining, Backing Up, and Recovering Data.”
The Set Up and Configure function enables you to configure the database. Database configuration and this IBM Data Studio function are covered in detail in Chapter 5. Notice from the menu, you can launch the Configure Automatic Maintenance editor. DB2 provides automatic maintenance capabilities for performing database backups, reorganizing tables and indexes, and updating the database statistics as necessary. The editor enables you customize the automatic maintenance policy (see Figure 4.30).
Figure 4.30 Select the Automatic Maintenance policy options
The Manage Database function enables you to start and stop the database. In DB2, that means activating and deactivating the database. Activating a database allocates all the necessary database memory and services or processes required. Deactivating a database releases the memory and stops DB2 services and processes.
The Monitor function launches the IBM Data Studio Web Console. Refer to the section, “IBM Data Studio Web Console,” for introduction of the tool.
The Generate DDL function uses the DB2 command-based tool db2look to extract the Data Definition Language (DDL) statements for the identified database objects or the entire database. This function and tool come handy when you want to mimic a database, a set of database objects, or the database statistics to another database. As a result of the Generate DDL function in IBM Data Studio or the DB2 command db2look, you receive a DDL script. The script contains statements to re-create the database objects you have selected. See Figure 4.31 for a reference of the types of statements you can generate using the IBM Data Studio.
Figure 4.31 Generate DDL function in the IBM Data Studio
For complete options for the DB2 command db2look, refer to the DB2 Information Center.
The Start Tuning function configures the database to enable query tuning. You might receive a warning indicating that you need to activate the InfoSphere Optim Query Workload Tuner (OQWT) license for advanced tuning capability. Note that IBM DB2 Advanced Enterprise Server Edition comes with OQWT. Follow the instructions to apply the product license or click Yes to configure the database server for tuning with the features complementary in the IBM Data Studio.
When the database is configured to use the tuning advisors and tools, you are presented with the Query Tuner Workflow Assistant, as shown in Figure 4.32.
From the Query Tuner Workflow Assistant, you can obtain a statement from various sources and tune the statement. In the Capture view, it gives you a list of sources where you can capture the statements. Figure 4.33 shows an example on capturing the SQL statements from the Package Cache. This example captures over 100 statements. Right-click the statement in which you are interested and select Show SQL statement or Run Single-Query Advisors and Tools on the Selected Statement.
Figure 4.33 Capturing SQL statements for tuning
Run the query advisors and tools on the selected statement. You can now enter the Invoke view. The tool collects information and statistics and generates a data access plan (see Figure 4.34).
When the query tuning activities are complete, you are brought to the Review view. It presents you the analysis results and an advisor recommendation, such as the one shown in Figure 4.35. The tool documentation recommends gathering and re-collecting all of relevant statistics of the query.
Figure 4.35 Review the advisor recommendation
You can also review the access plan graph generated by the DB2 explain function (see Figure 4.36 for an example). Remember to save the analysis for future references and compare them if needed.
The Manage Privileges function allows you to grant database privileges to the users. Refer to Chapter 8, “Implementing Security,” for details about privileges and database access controls.
General Database Development Tools
IBM Data Studio consolidates the database administration and database development capabilities. From the Task Launcher – Develop, you find a list of key development tasks such as creating and running SQL statements, debugging stored procedures, and user-defined functions (UDFs). Each task brings you to a tool that helps you accomplish it.
SQL and XQuery Editor
The SQL and XQuery editor helps you create and run SQL scripts that contain more than one SQL and XQuery statements. To launch the editor, open the Data Project Explorer; under SQL Scripts select New > SQL or XQuery Script. As shown in Figure 4.37, a sample SQL script is entered. You can configure the run options for the script.
Figure 4.37 SQL and XQuery editor
The editor formats the SQL statements nicely and provides syntax highlights for easier reading as you enter the SQL statements. The functionality content assist is also very useful. It lists all the existing schemas in the database so that you can just select one from the drop-down menu. The editor also parses the statement and validates the statement syntax. You can validate the syntax in scripts with multiple database parsers and run scripts against multiple database connections.
SQL Query Builder
The SQL Query Builder enables you to create a single SQL statement, but it does not support XQuery. As the name implies, the tool helps you build an SQL statement. It helps you look at the underlying database schema or build an expression, as shown in Figure 4.38.
Database Routines Editor and Debugger
Stored procedures and user-defined functions (UDFs) are database application objects that encapsulate application logic at the database server rather than in application-level code. Use of application objects help reduce overhead of SQL statements and the results that are passed through the network. Stored procedures and UDFs are also called routines. IBM Data Studio supports routines development and debugging.
From the Data Project Explorer, create a new Data Development Project. In the project, you can create various types of database application objects such as stored procedures and UDFs (see Figure 4.39). To debug a routine, right-click the routine and select Debug.
Figure 4.39 Creating a stored procedure
Related Resources
- Book $31.99
- eBook (Watermarked) $25.59
- eBook (Watermarked) $57.59