SQL Visual Explain

page d'accueil Boite à outils

Cette page a été mise à jour le 21 janvier 2009.  Contact

Welcome page Tools box

Versionning 

• 2006-01-10 first publication*

See also

• Sql tips and tricks
• Visual Explain
• Exec SQL to run an SQL stack, such as RUNSQLSTM but with parameters
• Execute SQL to run an SQL statement from a CLP program
• User Defined table Function
• SQL table function

 

j'ai trouvé l'outil d'analyse et d'optimisation de SQL400. Il est dans iseries navigator (demarrer / programmes / IBM iSeries Access for Windows / iSeries Navigator). Et la fenêtre "Run SQL Script" est au bout de mes connexions / (ma machine) / Bases de données / qtemp ; clic droit : execution de script

I found the tool for analysis and optimization of SQL400. It is in iseries navigator (start / programs / IBM Access iSeries / iSeries Navigator). And the window " Run SQL Script " is at the end of my connections / (my machine) / data bases / qtemp; right click: script execution

Starting Visual Explain

There are two ways to invoke the Visual Explain tool. The first, and most common, is through iSeries™ Navigator. The second is through the Visual Explain API, QQQVEXPL. You can start Visual Explain from any of the following windows in iSeries Navigator:

• Enter an SQL statement in the Run SQL Scripts window. Select the statement and choose Explain... from the context menu, or select Run and Explain... from the Visual Explain pull-down menu.
• Expand the list of available SQL Performance Monitors. Right-click a detailed SQL Performance Monitor and choose the List explainable statements option. This opens the Explainable statements for SQL performance monitor window. Highlight (left click) on the SQL statement that you want to explain and click Run Visual Explain. You can also start an SQL Performance Monitor from Run SQL Scripts. Select Start SQL Performance monitor from the Monitor menu.
• Start the Current® SQL for a Job function by right-clicking Databases and select Current SQL for a Job. Select a job from the list and click SQL Statement. When the SQL is displayed in the lower pane, you can start Visual Explain by clicking Run Visual Explain.

In addition, a database monitor table that was not created as a result of using iSeries Navigator can be explained through iSeries Navigator. First you must import the database monitor table into iSeries Navigator. To do this, right-click the SQL Performance Monitors and choose the Import option. Specify a name for the performance monitor (name it will be known by within iSeries Navigator) and the qualified name of the database monitor table. Be sure to select Detailed as the type of monitor. Detailed represents the file-based (STRDBMON) monitor while Summary represents the memory-resident monitor (which is not supported by Visual Explain). Once the monitor has been imported, follow the steps to start Visual Explain from within iSeries Navigator.

Note:
When using the Explain only option of Visual Explain from Run SQL Scripts in iSeries Navigator, some queries receive an error code 93 stating that they are too complex for displaying in Visual Explain. You can circumvent this by selecting the "Run and Explain" option.

You can save your Visual Explain information as an SQL Performance monitor, which can be useful if you started the query from Run SQL Scripts and want to save the information for later comparison. Select Save as Performance monitor from the File menu.