If you've used the V5R4 set of data-base tools in System i Navigator, you'll enjoy the additional database functionality in the V6R1 version, renamed Navigator for i5/OS. The On Demand Performance Center is one area that has grown with another release of enhancements. New features such as Visual Explain While Running, overview comparison of multiple SQL performance monitors, and the ability to save analysis reports make Navigator more useful than ever. So let's walk through these and other enhancements.

On Demand Performance Center

Let's start with the additions to the On Demand Performance Center set of tools. The SQL Performance Monitors and SQL Plan Cache Snapshots now let you customize the columns shown in each analysis report, which gives you the flexibility to see only the columns that are important to you, and in any order that you specify. What's more, the customization is saved for you, so the next time you view that report, Navigator knows how you like it. Figure 1 is an example of the columns customization. Do note that you can return to a default setting.

The next significant enhancement to the analysis reports is saving the output from these reports. While viewing a report, go to the File menu and select the Save Results menu option. Figure 2 shows the dialog box that appears. You can choose to save the report in comma separated value (.csv), text (.txt), or spreadsheet format. Having the spreadsheet capability helps you produce charts and graphs based on your report's data, including trending charts. This save capability also works in conjunction with the column customization so the saved output contains only the columns that you selected for that particular report.

Another nice addition to the reports is that you can choose to have the statement variables filled in when you select the new Work with SQL Statement and Variables menu option, which Figure 3 shows. This action takes you to the Run SQL Scripts window with the parameter markers already filled in with the values for that specific run of the statement. Now, you can run the statement and use Visual Explain to see the graph representing the statement's access plan. In V5R4, you have to fill in the parameter markers yourself.

A typical use of SQL Performance Monitors or SQL Plan Cache Snapshots is to compare query statements between a well-performing environment and a slower one. New in V6R1 is the ability to compare two or more monitors at a summary level, so you can determine whether something needs to be investigated. For instance, you may have a weekly SQL Performance Monitor and want to make sure that there are no problems between weeks. Figure 4 illustrates the new high-level comparison dialog box. The highlighted row is the Total Runtime. As you can see, quite a difference is apparent between these two monitors, signaling the possibility of a problem. This feature can also be run with SQL Plan Cache Snapshots.

As Figure 5 shows, the SQL Performance Monitor wizard has some new options that allow for even more granularity when starting a SQL performance monitor.

If you've found the SQL Plan Cache useful, many new enhancements to the Show Statements function are sure to interest you. First, we redesigned the dialog box, as Figure 6 demonstrates, leaving more room on the right side for more statements. We also modified the dialog box to reveal only the first 500 characters of the SQL statements, greatly improving its performance. We also added new functions, such as the ability to view a list of users that have run a specific SQL statement. Plus, you can view any currently active jobs that are running a specific SQL statement and then view the attributes for those jobs.

If you've used the stored procedure interface to capture SQL Plan Cache Snapshots, you'll be glad to know that in V6R1 those snapshots are registered automatically in a control table on the system. The snapshots are then viewable from the SQL Plan Cache Snapshots folder without having to import them first.

One of the things that can happen with the SQL Plan Cache is that when the plan cache is full, plans can be removed from it to make room for another plan. However, in V6R1 you can start an SQL Plan Cache event monitor, as Figure 7 shows, and capture plans as they are removed and store them in a table with the same format as an SQL Plan Cache Snapshot. Thus you use the same analysis capabilities with the event monitors as you do with snapshots. Figure 8 demonstrates that you have many of the same filter features as you have with capturing an SQL Plan Cache Snapshot, so you can capture only the plans you want.

Visual Explain is a tool for making an SQL statement into a visual graph of what the optimizer is doing. An exciting new enhancement is the Explain While Running feature. This attribute lets you run Visual Explain against a long-running query statement and then refresh the Visual Explain picture. After the refresh, the actual runtime information is updated and highlighted in the attributes section, as Figure 9 shows. The nodes that have the updated information are also highlighted. Plus, you can continue to refresh the picture and see the actual numbers change as the statement runs.

Index Management

Index management is another key part of the On Demand Performance Center. The Index Advisor was introduced in V5R4 to provide a place to see which indexes the database was advising while running queries. In V6R1, the Index Advisor shows the SQL Create Index statement needed to generate the advised index. You can use this option on one or more advised indexes by right-clicking them and selecting Show SQL. Figure 10 displays the output as it is placed into the Run SQL Scripts dialog box. The index names are generated for you and are a combination of a table name plus the constant INDEX plus a suffix starting from 00001. This suffix increments when there are multiple indexes for the same table. Now you have a way to record the Create statements and can save them for later use in your change management system.

Another component of index management is the Show Indexes function. Previously, you were limited to showing indexes on a table-by-table basis. V6R1 provides the ability to Show Indexes for all the tables in a schema. Simply navigate to a particular schema, right-click the Tables folder, and select Show Indexes.

One of the main reasons for the Show Indexes function is to view the usage statistics for the indexes, such as the last time it was used for a query or the last time it was used for statistics One of the most common ways to use these statistics is in conjunction with the Index Advisor. You start by analyzing your existing indexes, comparing those with the advised indexes from the Index Advisor, and then deciding which advised indexes to create. After the indexes are created, you check whether your application is still using the existing indexes or whether it's using only the new indexes. New in V6R1 is the ability to reset these statistics before running your application. To do so, find the table you are interested in, right-click it, and select Reset Usage Counts.

In V6R1, you can view directly from the indexes list the SQL statements that are using or have used a certain index. Simply right-click the index that you're interested in and select Show Statements, which displays the Show Statements dialog box for the SQL Plan Cache in Figure 11.

As Figure 12 illustrates, additional information, such as the owning index information, has been added for each index in the Index List. This information helps you see which indexes share an access path and which ones do not. For example, when a primary key is created on a table, it is a maintained access path (index). Someone else might create an index using the same column as the primary key used. The database does not create two separate maintained access paths for such a case. Rather, it uses the original access path that the primary key was created over, which perhaps makes the new index redundant. The highlighted row in Figure 12 demonstrates this scenario. You can use this information to help you determine whether you have redundant indexes.

More Database Management Functions

When using Materialized Query Tables (MQTs) in your applications, you can now view all the MQTs for all the tables in a schema. First, right-click the Tables folder in a particular schema, then select Show Materialized Query Tables. Similarly, you can right-click the Views folder and select this option to see the MQTs that are based on all the views in a schema.

Another new menu option, Generate SQL, has been added to each database object folder in a schema so you can generate the SQL for all the objects of that folder type. Also in each object folder, you can customize the columns that you see, as well as provide the columns to be used when sorting the objects in a folder.

One common green-screen command is WRKOBJLCK, which is useful for finding lock holders for a specified object. In V6R1, we added this function to Navigator for i5/OS. To use it, you first find the table or index that you are interested in, right-click it, and select Lock Holders. Figure 13 shows this function's output for the STAFF table.

Another useful capability is the Comments option added to many of the database objects. When you select this option by right-clicking the database object and clicking Comments, you get a dialog box that lets you enter up to 2,000 characters of text to describe that object.

SQL Details for a Job

In V6R1, the function formerly called Current SQL for a Job gets a face-lift and a new name — SQL Details for Job. Figure 14 shows the new dialog box. Notice that the function now has a filter capability, including common job names to search for database jobs. After you right-click a job and select SQL Details, you see that, in V6R1, the information is categorized and that a given job has several new attributes, including new client information. When you have the details for that job, you can do things such as show the SQL statement in Run SQL Scripts or run Visual Explain directly.

Health Center

The Health Center was introduced in V5R4 to help you understand the condition of certain database objects on your system. V6R1 provides two new tabs of health information: the Environmental Limits tab and the Activity tab. Figure 15 depicts the Environmental Limits tab, whose purpose is to show you the jobs that have been or are currently consuming the most of some key SQL resources and what the limits, if any, are for those resources. The list shows up to 10 jobs for each SQL resource. If a limit exists for that resource, it is also shown.

In addition, if the icon to the left of the job name shows that the job is still active on the system, you can perform additional actions on that job. The set of actions includes Start an SQL Performance Monitor, see the SQL details for the job, show the SQL statements in the SQL plan cache for statements run in this job, and work with the job details. Also, if you want to reset the counts because you've released a new version of an application, you can use the Clear button. You'll also notice that some client interface information is included for each job.

The Activity tab shows the common types of access to database tables. Unlike the other tabs, this tab has no limits, but it does have a count that you can watch, as Figure 16 shows. To do this, you can use the Change Status Threshold button, which displays the Change Threshold dialog box. This threshold is not a percentage as it is on the limits tabs, but it is a count. For example, you could set the warning and critical threshold to indicate when the number of inserts into a table goes over a specified number. When these thresholds are reached, the Status column indicates so (Figure 16).

In addition to the Health Center's two new tabs, we added Journal and Journal Receiver counts to the Overview tab. Also, the Size Limits tab has totals for the number of SQL statements in a program, service program, or module.

Run SQL Statements

The ability to run SQL statements and CL commands in the Run SQL Scripts window is likely the most-used function in Navigator for i5/OS. So naturally, V6R1 offers several enhancements in this area. The most-requested capability is to save the results from queries, so V6R1 has an option to do this. To enable it, first select Allow Save Results from the Options menu. Then right-click the result set window and save the results. A dialog box similar to the one in Figure 2 will appear, and you can then save the results to a .csv, .txt, or spreadsheet format, including the column headings.

Speaking of column headings, another usability enhancement is that you can now display the column headings instead of the column names in the result set window, which is useful when you are used to seeing the columns represented by their headings, such as in the STRSQL interactive query command tool. To set up this functionality, go to the Connection menu and select JDBC Settings. Then click the Other tab and select Show column label, as Figure 17 illustrates. You can now undock the result set window from the tab and open it in a separate window. And if you usually have the result set window come up in a separate window, you can save the window size and location for the next time you launch one. The Font option (located in the View pull-down menu) applies to the result set window.

Another key enhancement is the ability to save and open an SQL script file as a source physical file member in the operating system. By saving SQL scripts on the server, you can share the scripts more easily. In addition, the RUNSQLSTM CL command is expanded in V6R1 to run statements that contain CL-prefixed commands, so scripts are portable between the two environments.

A new choice in the Options menu, Check SQL Portability, lets you see whether your SQL statement is portable to run as International Standards Organization (ISO)-standard syntax on other databases. Also, the View menu has a new option, Job Details, which lets you get to all the same functionality you would have if you found the job in the Work Management folder's Active Jobs list.

For users who like to work with the IFS folders in Navigator, now the .sql file type has been registered, so you can launch the file directly to Run SQL Scripts while looking at the list of files in the IFS folders.

A Significant Release

As you can see, V6R1 includes many enhancements to the database tools introduced in V5R4. The On Demand Performance Center set of tools continues to gain new capabilities with Visual Explain While Running, overview comparison of multiple SQL performance monitors, and the ability to save the analysis reports. Also, the enhancements to Run SQL Scripts, the Health Center, and SQL Details for a Job, along with many others, make this another significant release. With all these features, you'll want to get started using V6R1.

Jim Flanagan is a senior software engineer at IBM in database development for IBM DB2 for i in Rochester, Minnesota. He is the team leader of Navigator for i5/OS — database.