Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Ops Monitor for Db2 13.1 image-2024-5-19_8-5-1.png .

Testing traces

There are many different scenarios for testing. This topic lists the most common tests, and gives tips on running the tests.

  • Testing your new application during development

    1. Qualify the trace by your own AUTHID.

      Db2 trace overhead is only incurred for the tests of your application. Depending on the number of users of the BMC AMI Ops Monitor for Db2 trace in your test Db2 system, you might be able to keep your trace request active through several test iterations, consolidating them on one trace log. (Only four detail traces can be active at one time.)

    2. BIND your program after the trace is started.

      The text and EXPLAIN data of all static SQL statements is captured in your trace output for reference when analyzing performance. EXPLAIN=YES is not required.

    3. If you do not set an automatic stop time when requesting your trace, don’t forget to stop it when you have completed testing. You can stop a trace by using the Z (Stop) line command on the CT view.

      If you have logged the trace data, you should also purge the trace when complete. You can still browse and print the data from the log (HT view). Otherwise, analyze the data from the online buffers and purge the trace when you are done.

  • Analyzing the performance of an existing application
    1. Set automatic start and stop times to cover a time span in which you expect sufficient activity.
    2. If the application usually runs well with only a few occurrences of poor performance, specify exception filters to save only those that you need to analyze. For example, select only those with high elapsed or CPU times, or with many GETPAGE requests or I/Os.
  • Improving the performance of a bad SQL statement

    1. You might have identified a poorly performing SQL statement and want to try out several different variations in the syntax to determine which is best.

      Using Common Explain or a similar tool, you can modify and execute the statement directly from the source while in ISPF edit. Otherwise, make the statement executable from SPUFI.

    2. Start the trace qualified by your AUTHID.

    3. Modify and execute each variation as dynamic SQL.

      You want to force the thread to terminate so an accounting record will be produced. With SPUFI, you must exit each time to terminate the thread.

    4. The trace automatically captures the SQL text, EXPLAIN data (with cost factor), and the accounting record and SQL performance statistics.

      You now have all variations captured together in one trace for easy comparison.

  • Comparing test to production

    Save the detail trace log from the last test run. Run a short detail trace in production after cutover to compare performance. You can either print batch reports or browse both trace logs in split screen mode.

Related topic

Tuning-an-application-with-trace

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Ops Monitor for Db2 12.2