Access path recovery with Runstats Rescue for DB2 z/OS
Quickly reset from the current „bad“ access path from the DB2 Optimizer-relevant Catalog statistics to the previous „better“ access path for both Dynamic & Static SQL.
Walking through the RUNSTATS Rescue application for Dynamic SQL…
- Select the new scenario RUNSTATS Rescue
- Customize the settings
- Generate some JCL
- [Optionally] copy to a GENGROUP dataset
- Insert the EXPLAIN TABLE-CREATOR and QUERYNO, or use timestamp range
- Optional launch of our catalog browser
- Drill down to the Index level
- Last-Time-OK pop-up
- Press PF3 to get the confirm dataset pop-up
- Perform the RUNSTATS Rescue extraction
- Reset the statistics and execute the DSC flushing RUNSTATS
- The „Rescued“ Statistics
How does it looks in real life:
Near the bottom, you can see the new scenario RUNSTATS Rescue.
Select it to get a little pop-up window with the three steps.
The first step must only be done once and then simply be plugged into an existing production job.
I would recommend the first job of the normal DB2 Database Maintenance job stream.
1 Select the new scenario RUNSTATS Rescue in the little pop-up window with five possible steps
2 First validate the setup
We recommend usage of a GDG to simplify all processing.
3 Generate some DB2 Catalog extraction JCL with the first option looking like this
4 [Optional, but highly recommended] Copy to a GENGROUP dataset
At the end, the step to copy to a GENGROUP dataset and register the dataset in the metadata table:
Do the EXPLAIN in SPUFI.
5 Insert the EXPLAIN TABLE-CREATOR and QUERYNO, or use a timestamp range
Selecting the second option, then request the required input data.
Here you can use the QUERYNO, or a timestamp range for multiple EXPLAINS.
6 [Optional, but highly of interest] launch of our catalog browser
Hitting „enter“, launch our catalog browser to enable you to see which objects were being used by that SQL…
7 Drill down to the Index Level
8 Last-time-OK pop-up
Just press enter to let the System find the correct dataset/GDG for your use
9 Press PF3 to get the confirm dataset pop-up
Here you can manually override the System if desired, but just press ENTER to get the JCL.
10 Perform the RUNSTATS Rescue extraction
The next JCL appears that performs the RUNSTATS Rescue extraction, including the optional steps for GENGROUP support, as seen here:
11 Reset the statistics and execute the RUNSTATS
Finally, the fifth option is selected, which actually resets the statistics and executes the RUNSTATS to flush the DSC
12 The “Rescued” statistics:
Now the next time that statement appears, it will use the “rescued” statistics and get back its old Access Path.