Candle-lite DB2 manual Moving Interactively between !DB/EXPLAIN and Omegamon, Modes of operation

Moving Interactively between !DB/EXPLAIN and OMEGAMON II

Moving Interactively between !DB/EXPLAIN and OMEGAMON II

This unit tells you how to move interactively between OMEGAMON II for DB2 and !DB/EXPLAIN using the copy-and-paste facility to review and EXPLAIN poorly performing SQL. It also describes how you can use the full functionality of !DB/EXPLAIN.

What functions can you use?

When using OMEGAMON II and !DB/EXPLAIN together, you can use the following OMEGAMON II for DB2 functions:

■ATF or thread detail displays to isolate poorly performing SQL statements

■Copy-and-paste facility to move poorly performing SQL identified in OMEGAMON II to !DB/EXPLAIN for detailed analysis and recommendations

The cut SQL text is pasted in a correctly formatted form so that it can be EXPLAINed without further editing.

■ATF to view actual access paths chosen by the DB2 optimizer and compare these with the access path information predicted by !DB/EXPLAIN

■CUA/TSO toggle function key to access the full range of !DB/EXPLAIN functions

■Historical reports to review accounting performance information about application plans and packages that are most frequently used, consume the greatest amount of resources, or have the biggest negative impact on performance

Modes of operation

When using !DB/EXPLAIN and OMEGAMON II for DB2 together, you can operate in the following modes:

1.You can move back and forth between OMEGAMON II and !DB/EXPLAIN in real time.

–Access OMEGAMON II by means of its CUA interface.

–Use the copy-and-paste facilities in OMEGAMON II to isolate poorly performing SQL statements and paste them to an ISPF member. !DB/EXPLAIN can then analyze the SQL using the ISPF edit macro KTEXPL or a CLIST such as KTEON.

2.You can access the full range of !DB/EXPLAIN functions from within OMEGAMON II for DB2.

–Use the CUA/TSO toggle to jump to a TSO session in which you have started or can start !DB/EXPLAIN.

–Use !DB/EXPLAIN as you would normally.

3.You can access OMEGAMON II Historical Reporter from !DB/EXPLAIN to compare actual access path information with the predicted access path information.

–The direction of the access is only one-way, from !DB/EXPLAIN to OMEGAMON II for DB2.

–You access OMEGAMON II Historical Reporter from the !DB/EXPLAIN Plans panel using the A or P Select.

102 OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540