Database performance optimization tasks

 

This table shows how you can access various tools in iSeries™ Navigator to optimize your database performance.

Table 1. Optimizing database performance
Tasks Objects whose context menu supports the task Available from object context menus in the Web interface iSeries Navigator utilities
View the SQL Plan Cache SQL plan cache snapshot objects1 Yes N/A
Display indexes advised by the system

  • A database folder

  • A schema object

  • A table object
Yes

  • Visual Explain

  • An SQL performance monitor object

  • An SQL plan cache snapshot object
Show the picture of a query execution using Visual Explain

  • A job that contains SQL statements in the Work Management folder1

  • SQL performance monitor objects (analyze or compare)

  • SQL plan cache snapshot objects (analyze or compare)
No

  • The Show statements window

  • The Run SQL Scripts window

  • The Current SQL for a Job window

  • The Plan Cache Viewer
Create an SQL performance monitor The SQL Performance Monitors folder Yes

  • Taskpad

  • The Run SQL Scripts window

  • Visual Explain
Analyze monitor data An SQL performance monitor object Yes The Run SQL Scripts window
Display SQL statements collected by a performance monitor An SQL performance monitor object Yes N/A
Compare data collected by performance monitors SQL performance monitor objects Yes N/A
Create a subset of an existing SQL performance monitor for easier analysis SQL performance monitor objects (analyze or show statements) Yes Visual Explain
Create an SQL plan cache snapshot

  • The SQL Plan Cache Snapshots folder

  • An SQL plan cache snapshot object
Yes N/A
Analyze an SQL plan cache snapshot An SQL plan cache snapshot object Yes N/A
Display SQL statements collected by an SQL plan cache snapshot An SQL plan cache snapshot object Yes N/A
Compare data collected by SQL plan cache snapshots SQL plan cache snapshot objects Yes N/A
Display SQL information for an object

  • A package object

  • A trigger object

  • A procedure object

  • A function object

  • A program object that contains precompiled SQL information in the Integrated File System folder
Yes N/A
Change query attributes for a job A database folder Yes The Run SQL Scripts window
Manage the background statistics process The Databases folder No N/A
Manage statistics data

  • A table object

  • A table partition
No

  • A database navigator map

  • Visual Explain
Start the statistics advisor N/A No

  • Visual Explain

  • An SQL performance monitor analysis report

  • An SQL plan cache snapshot analysis report
Show an SQL statement

  • A detailed SQL performance monitor object

  • An SQL plan cache snapshot object

  • A job that contains SQL statements in the Work Management folder2
Yes

  • The Current SQL for a Job window

  • The Compare SQL Performance Data window
Display the most recent SQL statement for a job

  • The Databases folder

  • A job that contains SQL statements in the Work Management folder2
Yes N/A
Notes:

1 To view the SQL Plan Cache, right-click the SQL Plan Cache Snapshots folder and select SQL Plan Cache > Show statements.

2 To show the SQL statement that a job contains from the Work Management folder, right-click a job in the Work Management folder and select Details > Last SQL Statement.

 

Parent topic:

iSeries Navigator database tasks

 

Related tasks


Viewing the plan cahce with iSeries Navigator
Viewing implementation of your queries using Visual Explain
Displaying index advisor information
Collecting statistics with the Statistics Manager
Memory Resident Database Monitor: DDS

 

Related reference


iSeries Navigator URL parameters and available Web tasks