Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

234 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

Figure 10-11 SQL Builder

In addition, the DB Servers view in the Data perspective also includes a tool that is likely to

reverse engineer. This tool can generate the SQL DDL from the database information that

was collected at the creation of the new database connection.

10.2 iSeries Navigator

iSeries Navigator is a graphical tool for database administration, and it can help in

development activities. It includes the Databases component that is useful for SQL

application development. The following are the features within the Databases component that

benefit your development environment.

10.2.1 Database Navigator

Use Database Navigator to easily view and work with database objects and their related

objects (for example, easily view indexes built over a table).

You can graphically work with DB2, including:

򐂰 Create work and administer database objects such as tables, views, alias, procedures,

and so on. For example, the definition of the tables can be changed, such as add/remove

columns.

򐂰 Define Referential Integrity rules/constraints.

򐂰 Reverse engineer SQL DDL from objects created with DDS interfaces.

򐂰 Manage database logging (journaling).

򐂰 Analyze SQL/Query job performance through Visual Explain.

Chapter 10. DB2 Development Tools 235

The pop-up menu on the database objects in the navigator tree provides the editing and

viewing of database objects.

10.2.2 Run SQL Scripts

The Run SQL Scripts allows you to execute SQL statements against the DB2 UDB for iSeries

server and also view the results in the results window. You can store scripts of the SQL

statements in files on PC or IFS. When you need to re-run those scripts, you simply open and

run in this Run SQL Scripts.

SQL Assist has statement wizards to provide programmers with step-by-step processing of

coding an SQL Select, Insert, Update, and Delete. This is especially useful for native

programmers who are still learning SQL syntax. You can test your SQL statements to see the

result and launch the Visual Explain to understand the optimizer’s implementation of the

query.

You can find an example of using this feature in the next section.

10.2.3 Visual Explain

Visual Explain provides a graphical way of identifying and analyzing database performance. It

illustrates the decisions made by the query optimizer. It also recommends ways to improve

query performance by building indexes or collecting columns statistics. Visual Explain is a

very useful tool for database developers to analyze and tune the SQL performance.

Before we bring you to our Visual Explain example, we would like to introduce you to the

Statistics Manager. It is good to know what the Statistics Manager is and how it relates to the

column statistics.

Statistics Manager

OS/400 is an object-based operating system. Tables and indexes are objects. Like all

objects, information about the object’s structure, size, and attributes is contained within the

table and index objects. In addition, tables and indexes contain statistical information about

the number of distinct values in a column and the distribution of those values in the table. The

DB2 UDB for iSeries optimizer uses this information to determine how to best access the

requested data for a given query request.

Starting with V5R2 of OS/400, DB2 UDB for iSeries has a new SQL query engine (SQE). As

part of this new SQL query engine, a statistics manager component is responsible for

generating, maintaining, and providing statistics to the SQE optimizer. As mentioned earlier,

sources for statistics within DB2 UDB for iSeries come from default values and/or indexes.

With SQE, the optimizer has another source, namely

column statistics stored within the table

object.

The column statistics will be generated by a low-priority background job, with the goal of

having the column statistics available for future executions of this query. This automatic

collection of statistics allows the SQE optimizer to benefit from columns statistics, without

requiring an administrator to be responsible for the collection and management of statistics,

as is true for other RDBMS products. Even though it is not required, statistics can also be

manually requested for iSeries users who want to take on the task of statistics collection and

management, without waiting for the statistics manager to recognize the need for a column

statistic. The column statistics that are generated are only used by the new SQL query

engine. The original Classic Query Engine (CQE) continues to use only default values and

indexes for statistics.