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
򐂰 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
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
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.

Get Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.