| Additional Information on Supplied Report Definitions for DB2 | |
In this document, you used four supplied report definitions for DB2: XDBSTA1, XDBACCT, XDBSTA0, and XDBTATB. These are fairly new supplied report definitions. Before they were available as supplied report definitions, they needed to be created as user-defined report definitions. The following text is the description of the steps for creating them. The original text has been retained in this release because it contains a large amount of supplementary information for interpreting these four reports. In another release, this text may move elsewhere in the product.
Original text, in which the steps are no longer necessary but the discussions of the data are still useful:
This task walks you through the steps necessary for the generation of graphics and tabular reports on the DB2 data from your site.
In this task, you will save the report definitions and reports in three different ways:
Note: This task discusses one method for customizing reports. For more, see MVS Appendix 2: Tips for Reports. Also, additional methods will be covered later in this file.
Note: You will need write access to the PDB for some tasks in the section after this one and for production work. You will need write access to the SITELIB for one task in the section after this one and occasionally thereafter (for instance, to make site-level updates to the holiday list).
If you do not have both of the above, exit IT Service Vision (without exiting SAS) and restart it using the DISP=OLD parameter for write access to the PDB and the SITEACC=OLD parameter for write access to the SITELIB. For example:
cpe pdb='your.DB2test.pdb' siteacc=old
cpe pdb='your.DB2test.pdb' disp=old
cpe pdb='your.DB2test.pdb' disp=old siteacc=old
If this command does not fit on your command line, you can obtain the same results by modifying the CMAUTOEX member of the CPMISC PDS (and changing it back later if necessary). Or you can invoke the IT Service Vision server by typing these lines starting at the first line in the body of the PROGRAM EDITOR window
%cpstart(pdb='your.DB2test.pdb',
disp=old,
siteacc=old);
Then, if your PROGRAM EDITOR window has pulldown menus, select Locals and Submit or, if your PROGRAM EDITOR window has a command line, type SUBMIT on the command line and press ENTER or RETURN.
Note: For more information about command lines and pulldown menus, see Shared Appendix 2: Navigating SAS Windows.
DB2 generates three different SMF record types, depending on what was specified at your site when the subsystem was initialized.
The System Services statistics record (SMF type 100) gives information about the DB2 subsystem as a whole during a specific interval, such as
The data in these records have accumulated starting from the initialization of the DB2 subsystem. So that the numbers will represent just one interval, IT Service Vision subtracts the values in the previous interval from the current interval.
IT Service Vision table XDBSTAB stores data (buffer pool detail statistics) from type 100 subtype 1 records. IT Service Vision table XDBSTA0 stores data (stats, part of MXG DB2STATS) from type 100 subtype 0 records. IT Service Vision table XDBSTA1 stores data (stats, part of MXG DB2STATS) from type 100 subtype 1 records.
The accounting or transaction record (SMF type 101) contains data for a single thread, from the time the thread was created until it terminates or, if the thread is re-used, until the authorization id changes. The record includes:
The IT Service Vision table XDBACCT stores data (accounting) from type 101 subtype 0 records.
For situations that require extremely detailed information, the performance or trace or system parameters record (SMF type 102) traces individual events in the DB2 subsystem, such as start or end of: an SQL statement execution, lock or latch, COMMIT or ABORT, etc.
Note: There are over 150 different subtypes of this record. This record is usually used only to track specific problems and is usually collected only for limited periods of time.
IT Service Vision does not supply tables for the type 102 records because there are so many subtypes and they are so rarely used. You can run the IT Service Vision %CPDDUTL macro against a control statement file that contains the GENERATE SOURCE control statement. The run generates CPDDUTL control statements for creating the table and variable definitions. Next, check the CPDDUTL control statements, edit them as necessary, and run the %CPDDUTL macro against the control statements to create the table and variable definitions in your PDB's data dictionary. Finally, to process data into the table, add the table name to the tablist positional parameter of a %CMPROCES invocation with COLLECTR=SMF and TOOLNM=MXG and other keyword parameters as appropriate.
For more details on creating the table and variable definitions, see the information on %CPDDUTL and the CPDDUTL control statements in the reference documentation for IT Service Vision. For more details on checking the table and variable definitions, see Generic Collector Appendix 1: Algorithm Used by GENERATE SOURCE.
DB2 SMF/GTF Records are documented in a multitude of DSECTS in the DB2 Macro Library that comes with the DB2 product. You should ask your DB2 representative for the name of the library and then view the members online if you need additional information. This member has a large amount of detailed information, often containing performance discussions for large or small values of particularly critical fields. The three SMF-style records (100, 101, 102) are combinations of individual DSECTS in the IBM Macro Library.
You have some observations in the DB2 specific tables in your new DB2 test PDB.
You can do a check in interactive mode by following this path from the main menu:
PDB Data -> Examine PDB Data
The #Obs field in the Query PDB Data window should have non-zero values for some of the tables in your active PDB.
Select File and then End to go back to the main menu.
Follow this path from the main menu
PDB Admin -> Manage PDBs/Switch PDB
IT Service Vision displays your list of known PDBs. Your list of PDBs is stored in your SASUSER library and contains the names of PDBs that you have defined or that someone else has defined and you have added to your list.
Select Actions and Create/Add. IT Service Vision prompts you for the name of the new PDB. Enter the high-level-qualifiers of the PDB from the CMDB2LD job and then select OK. Notice that your list of PDBs now has the new PDB in it.
Select the new PDB and then follow this path
ItemActions -> Activate -> select WRITE -> OK
Note: This task does not require write access, but the following task does; so getting write access now is a convenience for later.
To return to the main menu, select File and End.
Here is an overview of the steps in this action:
The specific steps are as follows:
Note: So that you can interpret these when you get the report, here is a brief and then longer description of each of these variables. (In the descriptions, the word processed is used in its DB2 sense, not in its IT Service Vision sense.)
This is the number of entry and exit events processed to calculate the elapsed time in DB2 and the processor times, which include
The majority of thread allocation and certain ABEND conditions are not included in these values.
Note: This field is calculated only if accounting class 2 is turned on.
This value represents the time that your program was actually in DB2 and is a subset of the CPU time in DBTCBTM. The difference between the values in DBTCBTM and QWCAJST is the time that your DB2 program was not executing DB2, which should be small in well-designed DB2 programs.
Note: This field is calculated only if accounting class 2 is turned on.
Note: SRB times reflect the SRB time for the entire address space. Because data base access agents are products of the distributed data facility address space, SRB times for data base access agents reflect the SRB time for the entire distributed data facility address space. Likewise, because a single CICS address space may support multiple agents, the SRB times for agents attaching to DB2 from CICS reflect the SRB time of the entire CICS address space, rather than a specific agent.
This is the number of entry-to and exit-from wait events that are processed to calculate the I/O elapsed wait times in QWCAWTI (see below).
Note: This field is calculated only if accounting classes 1 and 3 are on.
This is the number of wait trace events processed for waits for lock/latch, in QWCAWTL (see below).
This is the number of wait trace events processed for waits for read I/O under another thread, in QWCAWTR (see below).
This is the number of wait trace events processed for waits for synchronous execution unit switching to DB2 service tasks, in QWACAWTE (see below).
This is the number of wait trace events processed for waits for write I/O under another thread, in QWCAWTW.
This is the elapsed time that the allied agent waited for locks and latches in DB2. When DB2 makes a lock or latch request that results in a wait for an allied agent, the store clock time is saved, and, when the event completes, the ending time is used to calculate total elapsed wait time. The result is added to the previously saved lock and latch wait time in DB2.
Note: This field is calculated only if accounting classes 1 and 3 are active.
This is the elapsed time that the allied agent waited for synchronous I/O in DB2. When DB2 makes an I/O request for an allied agent, the store clock time is saved, and, when the I/O completes, the ending time is used to calculate the total elapsed I/O wait time. The result is added to the previously saved elapsed I/O wait time in DB2.
Note: This field is calculated only if accounting classes 1 and 3 are active.
This is the accumulated wait time for read I/O that is done under a thread other than this one. It is calculated by MXG software by subtracting the entry to event wait timestamp from the resume-from-the-event timestamp.
This is the accumulated wait time due to synchronous execution unit switch to DB2 services from this thread. It is calculated by MXG software by subtracting the entry timestamp from the resume timestamp. The following DB2 service waits are included in QWACAWTE:
DB2 Accounting information
10:38 Thursday, September 26, 1996
---------------------------------------------
| | MEAN | MAX |
|-----------------+------------+------------|
|Entry/exit event | 1211.66| 19330320.00| (QWCARNA)
|-----------------+------------+------------|
|Wait trace event | 45.72| 168110.00| (QWCARNE)
|-----------------+------------+------------|
|Waits for lock/l | 7.08| 57982.00| (QWCARNL)
|-----------------+------------+------------|
|Waits for read I | 8.19| 15962.00| (QWCARNR)
|-----------------+------------+------------|
|Wait trace event | 1.12| 513.00| (QWCARNS)
|-----------------+------------+------------|
|Waits for write | 0.02| 172.00| (QWCARNW)
|-----------------+------------+------------|
|Accumulated lock | 0.01| 72.20| (QWCAWTL)
|-----------------+------------+------------|
|Accumulated i/o | 0.62| 1580.86| (QWCAWTI)
|-----------------+------------+------------|
|Wait time for | 0.38| 1361.87| (QWCAWTR)
|-----------------+------------+------------|
|Wait time due to | 0.08| 334.70| (QWCAWTE)
---------------------------------------------
In this example, we can see that
After you finish viewing the report(s), select File and End to return to the main report definition window.
Note: If, for any reason, no report is generated, follow this path:
Actions -> Access Report Results
In the Manage Results window, select LOG and click on View Current. If you have an error message, it will be described here.
To cycle back to to the previous window, use PF3 or the PREVWIND command. (The default key for the PREVWIND command is PF7.)
Actions -> Access Report Results
IT Service Vision displays the Manage Results window.
Because the report is a text report, select OUTPUT. Then select Save Current.
IT Service Vision requests a name and description for the report and provides default values from the supplied report. Although you can specify any valid SAS name for the name of the report (a SAS name starts with a letter or underscore and consists of 1-8 characters that are letters, numbers, or underscores), you may want to keep most of the supplied name and just change the final character to identify uniquely this version of the report. Then select OK and OK. IT Service Vision stores the sample report in your SASUSER library.
To return to the main report definition window, select Goback.
Note: When, later, you want to see this sample report, follow this path from the main menu:
Reports -> Report Results
Select Saved Text Reports. IT Service Vision displays a list of the reports that you saved.
To view the report, type B on the underscore in front of the name and press ENTER or RETURN.
To exit, select File and End.
IT Service Vision displays the Save PROGRAM window. The Name and Description are copied from the supplied report definition. If you want to identify this custom report definition uniquely, change at least the name, perhaps by changing the last byte of the name. (If you come back later and make other customizations of this report definition and want to avoid a name conflict, you can see a list of the names of the other custom report definitions by selecting VIEW PROGRAM.) Then select OK and OK.
IT Service Vision saves the report definition to your SASUSER library. Then IT Service Vision returns you to the main report definition screen.
To return to the Reports menu, select File, Quit, File, and End.
Note: Later, when you want to use the custom report definition from the server's interactive interface, select Custom Rpts instead of Supplied Rpts on the Reports menu.
Select Custom Rpts. IT Service Vision displays the list of custom report definitions. Follow this path:
select the report definition -> ItemActions -> Modify Report
IT Service Vision displays the report definition's main window.
Then write out the report definition by following this path from the report definition's main window:
Actions -> Access Report Results -> SOURCE -> View Current
IT Service Vision displays the PREVIEW window. This window contains the report macro invocation (report definition) that generated the report. If you follow the steps that were described above, the macro source code looks similar to this:
/*---------------------------------------------------------------*
| IT Service Vision Design Report
| Report Name:
| Dataset: XDBACCT
| Generated: Thursday, September 26, 1996 at 16:13
*---------------------------------------------------------------*/;
GOPTIONS reset=title reset=footnote DEVICE=IBM3179;
TITLE1 'DB2 Accounting information';
FOOTNOTE1 'Created from the XDBACCT table (DETAIL level).';
%CPTABRPT(XDBACCT
,QWCARNA,%QUOTE(Entry/exit event),
,QWCARNE,%QUOTE(Wait trace event),
,QWCARNL,%QUOTE(Waits for lock/l),
,QWCARNR,%QUOTE(Waits for read I),
,QWCARNS,%QUOTE(Wait trace event),
,QWCARNW,%QUOTE(Waits for write),
,QWCAWTL,%QUOTE(Accumulated lock),
,QWCAWTI,%QUOTE(Accumulated i/o),
,QWCAWTR,%QUOTE(Wait time for),
,QWCAWTE,%QUOTE(Wait time due to),
,STAT=MEAN MAX
,TYPE=TYPE1
,REDLVL=DETAIL
,SEPS=YES
,ORDER=INTERNAL
,STATLAB=MAX
,MISS=NO
,OUTMODE=LP
,STATFMT=. . );
You will probably want to change the values in the label fields so that they are easier to interpret. Labels can be up to 16 characters. For more information on the syntax, see the reference documentation for IT Service Vision.
A useful customization for a daily production report is to add BEGIN= and END= parameters as follows:
,BEGIN=TODAY-1 ,END=TODAY-1
These settings cause the report definition to report on the previous day's data.
IT Service Vision displays the Write to file... window. In the Selection field, type 'high-level-qualifiers.CPMISC(report_name)', where report_name is the name of the member to which the report definition is to be written (probably the same as the name of the report). (Note that the filename is embedded in single quotes.) Select REPLACE (or make no selection because REPLACE is the default). Then select OK.
IT Service Vision writes the report definition in the form that is for batch use (and with the parameters that will direct a report to the printer). A later task describes how to add this to the daily production job.
Output Options -> Output Window -> OK
The overview of steps is the same as in the previous action:
Here are the specific steps:
From the main menu, select Reports. IT Service Vision displays the Reports menu.
Select DesignGraphRpt on the Reports menu, and then select One Y-Axis Plot as the report type. IT Service Vision displays the main report definition window for One Y-Axis Plots.
Select DETAIL from the list of levels.
Click on the the icon in the upper left corner of the window. IT Service Vision displays a list of the tables in your DB2 test PDB. Select the XDBSTA1 table.
Click on the Y Variable(s): button to list all variables in the selected table. You can select a maximum of nine variables to be plotted on the Y axis. If you select more than one analysis (Y) variable, they should have the same unit of measurement because they are plotted on a single axis.
For the sample graphics report, select the following variable::
You may want to use the Find button to search for the variable name or the variable description. When you finish in the Variables window, select OK.
IT Service Vision opens the Variable descriptions window to list the variables currently selected for this report and to list the default label names. You can use the default label or type the label that you want to use on this report. On One Y- Axis Plots, labels can be a maximum of 16 characters. You may want to change it as follows:
Variable Description Label -------- ---------------- --------------------------------------- QIEFREE Free Pages free pages in free chain DATETIME Timestamp Timestamp when record was written
Note: DATETIME is the default variable for the X axis.
Select OK when you finish in the Variable descriptions window.
If you click on the By Variable(s) button, you will be able to select one or more variables to produce a separate graph for each value of the BY variable or for each combination of values if you have multiple BY variables. There will be one line for each combination of By variables, and each line will display on a separate graph.
For instance, you may want to select MACHINE (System id) or QWSSSID (DB2 Subsystem ID).
If you select Class Variable, you can specify a variable by which other variables will be grouped or categorized in this report. There will be one line for each class variable, and all lines will display on the same graph.
Note: You cannot choose a class variable if there are multiple analysis (Y) variables.
This selection indicates how your data should be summarized prior to analysis. ASIS leaves the data as is. All other options combine the data based on the summary option. For example, if you select HOUR, an average of all data points for each hour is plotted instead of plotting all data points for each hour.
The default is ASIS. For this example, select HOUR.
You can choose a summary statistic if you have chosen a Summary Time Period option other than ASIS. The default is MEAN.
Output Options enables you to change the output location. The default for graph reports is Graphics Window (the SAS GRAPH window on your monitor).
Plot Options are additional options that can be used in building your report, such as axis options or vertical reference lines.
By selecting Style you can choose from a list of plot style types that impact the statistical methods used for your report. For this example, use the default, LINE.
To run the current report definition, click on Actions then Run Report.
When you look at the results in your graphics report, keep in mind that the QIEFREE values generally vary indirectly with DB2 activity. You should see higher values off-shift and lower values during periods of peak activity. A low average free page count during periods of low activity may be a sign of a situation needing more detailed analysis.