DB2 9.5 SQL Procedure Developer Certification Examination 735 Preparation, part 6th: DB2 Development tools

Before you start

About this series

Are you considering participating in the DB2 SQL Procedure Developer certification exam (exam 735)? If so, you come to the right place. These six DB2 certifications prepare tutorials to discuss all the basics of the topics you need to know before taking an exam. Even if you're not going to take the certification exams right away, these tutorials are also good materials to help you get a complete picture of DB2 V9.5 database development.

About this tutorial

In this tutorial, you will learn about the most common IBM tools for developing database code, view the overview of Visual Explain (from IBM DB2 Control Center and Command Editor, and IBM Data Studio), and learn about IBM data The profiling feature in Studio and see how to run the explain command DB2EXPLN and db2exfmt from the command line. This tutorial is part sixth of a series of tutorials that you can use to help prepare the DB2 9.5 SQL Procedure Developer certification exam 735.

Prerequisite conditions

This tutorial is written for primary and intermediate linux®, unix®, or windows® database developers or administrators. You should have a basic understanding of how UNIX or Windows command-line shells are used, and you should know the basics of DB2 and SQL commands.

System Requirements

The examples in this tutorial apply to DB2 9.5, which runs on the Windows operating system. The concepts and information provided apply to DB2 that run on any distributed platform.

You do not need a copy of DB2 9.5 to learn this tutorial. However, if you download the free trial version of IBM DB2 9.5 and practice in this tutorial, you'll get a bigger payoff.

Brief introduction

IBM provides several tools for developing database applications, one of which is IBM Data Studio, an eclipse based development environment that facilitates the design, development, deployment, and debugging of SQL and java® stored procedures. Command line Processor can also be used to create and run SQL procedures.

DB2 provides a comprehensive interpretation feature that displays information about the query access plan selected for the SQL statement, enabling developers and database administrators to analyze and optimize the SQL code. This tutorial provides an overview of Visual Explain (from DB2 Control Center and command Editor and IBM Data Studio), a profiling feature provided by IBM Data Studio, and describes how to run exp from the command line Lain command DB2EXPLN and DB2EXFMT.

Using IBM Data Studio

IBM Data Studio provides an easy-to-use development environment that you can use to create, build, debug, test, deploy, and analyze stored procedures. The foundation of Data Studio is the Open Standard Eclipse Framework that is used in Java development. It provides a range of different perspective windows to support rapid application development, including SQL and Java stored procedure development. With IBM Data Studio, you can perform the following tasks:

Connect to a database and study database objects

Create a new routine

Building routines on local and remote DB2 servers

Modify and reconstruct an existing routine

Test and debug the execution of an installed routine

Here are the steps needed to develop, debug, and test a simple stored procedure using IBM Data Studio:

Step 1: Connect to the database in the data perspective's DB Explorer.

Figure 1. To connect to the SAMPLE database by using Database Explorer

Using Database Explorer, you can view and obtain information about all of the object's objects. For example, you can view the column properties and sample data for any given table. You can even update the data in the table by selecting Edit in the Drop-down menu displayed by right-clicking the table name.

Figure 2. View table data in database Explorer