News
Abstract
Database Package and Perspectives
Content
Here are the perspectives available in the Database package:
Not all perspectives will be displayable with all collections because the required database files and fields might not be available on that release. When the perspective and collection are not compatible, the Display button will not be active. Also note that the view(s) may be different depending on the collection that is being displayed. See single source packages for more information.
Viewing SQL Plan Cache Snapshot, SQL Plan Cache Event Monitor and SQL Performance Monitor database files
Also, from the Database SQL Plan Cache Snapshot, SQL Plan Cache Event Monitor, or SQL Performance Monitor views within the Database task, there is an Action venue item to launch into Investigate Performance Data to view them graphically using PDI's SQL Overview perspective.
Selecting these collections and the perspectives in the SQL Performance Data folder will allow you to view the data graphically using either the SQL Overview or SQL Attribute Mix perspectives.
The “SQL Overview” perspective for both SQL Plan Cache and SQL Performance Monitor database performance files has 11 views, providing pie or bar charts as follows:
- Query Time Summary
- Open Summary
- Open Type Summary
- Statement Usage Summary
- Index Used Summary
- Index Create Summary
- Index Advised
- Statistics Advised
- MQT Use
- Access Plan Use
- Parallel Degree Usage
Here are the first two views as snapshots, a user would display the perspective and be able to scroll down through the views:
The “Attribute Mix” perspective has these views:
- Statement Summary
- Statement Type Summary
- Isolation Level Summary
- Allow Copy Data Summary
- Sort Sequence Summary
- Close Cursor Summary
- Naming Summary
- Optimization Goal
- Blocking Summary
Snapshots of samples of the first two views follow:
Job Level SQL Metrics - *JOBMI
SQL performance data can be collected as part of the *JOBMI collection category.
The following metrics are collected for the job performance data *JOBMI category of Collection Services.
- SQL clock time (total time in SQ and below) per thread (microseconds)
- SQL unscaled CPU per thread (microseconds)
- SQL scaled CPU per thread (microseconds)
- SQL synchronous database reads per thread
- SQL synchronous nondatabase reads per thread
- SQL synchronous database writes per thread
- SQL synchronous nondatabase writes per thread
- SQL asynchronous database reads per thread
- SQL asynchronous nondatabase reads per thread
- SQL asynchronous database writes per thread
- SQL asynchronous nondatabase writes per thread
- Number of high level SQL statements per thread
Troubleshooting Perspective Errors:
For the Integration with Database support in Performance Data Investigator (PDI) there are requirements for other PTFs to be installed on the same system where the queries are being run. If the system you are running Navigator from i as well as the system you are targetting are not configured correctly, you might see the following errors:
- Perspective error and several Chart error messages
- Chart error - Chart is blank due to an SQL query error
Perspective error
When launching into Investigate Data from Database or selecting the SQL Performance Data collections, the underlying Database PTFs need to exist to avoid errors.
If you do not have the required Database PTF installed, when Displaying a SQL Performance Data collection with either the SQL Overview or SQL Attribute Mix perspective, you will see errors similar to the following: Perspective Error - Error displaying selected perspective
followed by a Chart error for each of the views in the perspective.
This will also occur if you have the required Database PTFs on the system from which you are running the Navigator for i, and you have set the Target System to another system which does not have the required Database PTFs installed on that system.
Perspective Error may also occur for valid reasons such as an empty result set. To validate, select Action-> Show as Table. If the table shows "None" then the query result set is empty.
When displaying perspectives based on updated Collection Services database files, the user needs to have the updated Collection Services PTFs to be able to generate or convert their collections to the format where that data will be usable. If the collection is not updated to display the new perspectives, the following error will be shown:
Chart is blank due to an SQL query error. [SQL0206] Column or global variable XXX not found. To add the missing field to the collection, update the collection library files to the most recent version.
Refer to the Navigator for i PTF page for the required Performance Tools Group PTF to install and instructions for updating the collection library files to the most recent version under Performance Collection Tools Converting a Collection Services File for New Metrics.
Follow the special instructions for converting (above) or rename the configured collection library and recreate a new, empty library to have everything from that point on creating new data with updated template files.
Data gathered by Collection Services:
If the format for a database file is changed with new metrics, follow the Special Instructions link to convert your collections.
Update JOBMI file instructions:
To update the QAPMJOBMI file in libraries where an existing version of
the file already exists, follow these directions after applying or
removing the PTF that modified the file:
For each existing library you wish to update:
1) If the collection library you are updating is your current
collection library, the CRTPFRDTA job must be ended.
ENDPFRCOL FRCCOLEND(*YES)
2) Move each of the following files, if it exists, to a backup library.
Note: migrating logical file members will not work if you just rename.
MOVOBJ OBJ(col-lib/QAPMJOBMI) OBJTYPE(*FILE) TOLIB(your-backup)
MOVOBJ OBJ(col-lib/QAPMJOBL) OBJTYPE(*FILE) TOLIB(your-backup)
MOVOBJ OBJ(col-lib/QAPMJOB2) OBJTYPE(*FILE) TOLIB(your-backup)
MOVOBJ OBJ(col-lib/QAPMJMIX1) OBJTYPE(*FILE) TOLIB(your-backup)
MOVOBJ OBJ(col-lib/QAPMJMIX2) OBJTYPE(*FILE) TOLIB(your-backup)
3) Create a copy of the new QAPMJOBMI file in the collection library.
CRTDUPOBJ OBJ(QAPMJOBMI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(col-lib)
4) Copy all existing members from the original QAPMJOBMI file in your
backup library to the new file in the collection library.
CPYF FROMFILE(your-backup/QAPMJOBMI) TOFILE(col-lib/QAPMJOBMI) FROMMBR(*ALL) TOMBR(*FROMMBR) MBROPT(*REPLACE) FMTOPT(*MAP *DROP)
5) Create a copy of each of the following QAPMJOBL, QAPMJOB2,
QAPMJMIX1, and QAPMJMIX2 logical files that you moved in step 2 in your
collection library. This will remap the based-on physical file to
QAPMJOBMI in the collection library and copy all the logical file
members.
CRTDUPOBJ OBJ(QAPMJOBL) FROMLIB(your-backup) OBJTYPE(*FILE) TOLIB(col-lib) DATA(*YES)
CRTDUPOBJ OBJ(QAPMJOB2) FROMLIB(your-backup) OBJTYPE(*FILE) TOLIB(col-lib) DATA(*YES)
CRTDUPOBJ OBJ(QAPMJMIX1) FROMLIB(your-backup) OBJTYPE(*FILE) TOLIB(col-lib) DATA(*YES)
CRTDUPOBJ OBJ(QAPMJMIX2) FROMLIB(your-backup) OBJTYPE(*FILE) TOLIB(col-lib) DATA(*YES)
Note: you should delete your saved logical files in the backup library.
These logical files are still based on the QAPMJOBOS file in your
collection library and will prevent normal deletion of existing
collections.
6) Restart performance data collection if you ended it in step 1 above.
STRPFRCOL
The instructions above are based on only those files and file
relationships shipped with the operating system. It is your
responsibility to handle any additional files, tables, and access paths
that are dependent on files replaced by this PTF. You can determine if
any additional dependencies exist by examining the output of the
following command:
DSPDBR FILE(your-lib/QAPMJOBMI)
Was this topic helpful?
Document Information
Modified date:
30 March 2020
UID
ibm11128315