Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Chapter 10. DB2 Development Tools 243

code up to Line 11. When Line 11 is reached, the following command can be issued in the

Console window to display the computed shipping rate:

EVAL SP:RATECALC

8. To complete execution of the SHIP_IT stored procedure, click the green resume arrow on

the tool bar.

For more information on the Graphical iSeries System Debugger, look at the debugger tool

help by pressing F1. The iSeries InfoCenter also contains information on the debugger, just

search the InfoCenter Web site:

http://www.ibm.com/eserver/iseries/infocenter for “graphical debugger”

For more information, see the white paper, Graphical Debugger makes Procedural SQL

Debug Even Easier, at:

http://www.ibm.com/servers/enable/site/education/abstracts/sqldebug_abs.html

10.3 OS/400 utilities

There are some additional tools that OS/400 provides. We briefly cover them in this section.

DB2 SMP (Symmetric Multiprocessing)

DB2 SMP is a licensed program that can also be used to boost the performance of SQL

applications. By activating DB2 SMP, the query optimizer will try to speed the performance of

the SQL SELECT operation by enabling a single operation to be split in multiple operations

running across multiple CPUs concurrently.

PRTSQLINF

PRTSQLINF is a CL command to print the information, which includes the SQL statements

and the access plans used during the execution of the statements. For SQL embedded in a

program and package objects, this command extracts the optimizer access method

information out of the objects and places that information in a spooled file. The spooled file

contents can then be analyzed to determine if any changes are needed to improve

performance.

STRDBMON

STRDBMON is a CL command that is used to collect the database performance data for a

specified job or all jobs on the iSeries server. The Database Monitor gathers query execution

statistics from the iSeries server jobs and records them in a database file. This database file

is then analyzed to provide performance information to help tune the query or the database.

You can use Visual Explain to analyze the collected performance data and give the

recommendations.

10.4 DB2 Development Center

The DB2 Development Center (formerly known as the DB2 Stored Procedure Builder) is a

rapid iterative development environment for developing stored procedures, user defined

functions, and much more. This client-based development tool supports the entire DB2 UDB

family of servers, therefore, it is an especially useful tool if you are developing and deploying

procedures on multiple DB2 UDB servers.