INTERVAL parameters

If you did not specify an interval, the product uses the default interval that is defined in the report.

If you specify an interval without values, the results depend on the data source:

Data Collector—Output includes the snapshot of Db2 activity that was taken while the report was active.
Data set—Output includes all of the intervals that are present in the data set.

Tip

The product cuts records at the SQL statistics collection interval that the DOMPLEX option set specifies. If you specify START and END times that span intervals, you might not get all of the data that you expect. To avoid this, run the Select Analysis Interval report against the data set to see the actual times to use.

The following figure shows the syntax of the INTERVAL statement and parameter:

INTERVAL parameters_ INTERVAL statement - Copy.png

If you are generating the Batch SQL Comparison Analysis report (SQMBDIFF), you can specify multiple intervals. The following figures display an example of the syntax using multiple intervals:

INTERVALparameters for SQMBDIFF.png

INTERVAL(DAY1 (START(-1,12:00) FOR(02:00:00))-
DAY2 (START(-2,12:00) FOR(02:00:00))
DAY3 (START(-3,12:00) FOR(02:00:00)))

In the previous example, the Batch SQL Comparison Analysis report (SQMBDIFF) generates performance data for each of the included statements. The report groups the data by interval label (in this example, DAYn). The maximum length of a label is 8 characters.

The following sections describe the keywords and the permissible options for each keyword:

START keyword

The START keyword (optional) specifies the beginning date and time for the data included in the reports. You must specify this keyword if you specify FOR or END. The START date and time must be prior to the date and time when the job is run.

If no START keyword is specified and the data source is a data set, the start time defaults to the timestamp of the first record in the data set.

If no START keyword is specified and the data source is a Data Collector, the start time defaults to the date and time of report activation.

If multiple Db2s that do not share the same interval are being reported, the start time for each Db2 can be different.

Note

If you are generating the Batch SQL Comparison Analysis report (SQMBDIFF), you can include multiple START/END/FOR groupings. A label that specifies a named time period must precede each grouping.

Example

Two Db2s being reported:

Db2A has an interval of 24 hours, starting at midnight
Db2B has an interval of 8 hours (intervals starting at midnight, 8:00 A.M. and 4:00 P.M.)

The report is activated at 10:00 A.M. The interval for Db2A starting at midnight is active, so midnight is the start time for Db2A. The interval for Db2B starting at 8:00 A.M. is active, so 8:00 A.M. is the start time for Db2B.

Valid abbreviation: S

START keyword values

Value	Description of value
date	Beginning date of the interval¹
-nn	Number of days prior to the current date to be used for the beginning date for data in the reports -nn Valid values include any number from -0 through -60. Information Example If -5 is specified on 03/06/2011 (USA format), the start date is 03/01/2011. Use -0 to indicate the current date.¹
time	Beginning time for the data to be included in the reports Valid values are any time in the range 00:00:00–23:59:59 or 00:00–23:59. When specified without a date, the default differs, depending on the data source: Data Collector—If the specified time has already passed on the current date, the date defaults to the current date. If the specified time is later than the current time, the date defaults to the previous day. Data set—The date defaults to the first date upon which that time occurs in the source data set. Information Example If a time of 8:00:00 is specified and the timestamp of the first record in the data set is 01/01/2011 10:00:00 (USA format), the date defaults to 01/02/2011.
date,time	When both a date and time are specified, the reports begin with the first record having a timestamp that is equal to or greater than the specified date and time.
-nn,time	When both a number of days and a time are specified, the date that is represented by -nn is calculated. The report begins with the first record with a timestamp that is equal to or greater than the calculated date and specified time.

^{1 When specified without a time, the time defaults to midnight (12:00 A.M.) on the specified date (midnight = beginning of day).}

END keyword

The END keyword (optional) specifies the ending date and time for the data to be included in reports.

Note

END and FOR are two different ways of specifying the ending time of reports. If both keywords are specified, END is ignored.

If you do not specify the END or FOR keyword and the data source is a data set, the end time defaults to the timestamp of the last record in the data set. If you do not specify the END or FOR keyword and the data source is a Data Collector, the end time defaults to the time of report deactivation.

The END date and time should be prior to the date and time that the job is run. If both the START and END dates and times are later than the date and time that the job is run, an error results and no report is produced. If only the END time is later than the data and time that the job is run, the report is produced, but the date-time combination is flagged with a dollar sign ($) in the text of the control statements and the BMC24189 warning message is produced. This message states that the end time is later than the run time.

Note

If you are generating the Batch SQL Comparison Analysis report (SQMBDIFF) and you have applied PTF BQU0737, you can include multiple START/END/FOR groupings. A label that specifies a named period must precede each grouping.

Valid abbreviation: E

END keyword values

Value	Description of value
date	Ending date of the interval¹
-nn	Number of days prior to the current date to be used for the ending date for data in the reports Valid values are any number in the range -0 through -60. Information Example If -5 is specified on 03/06/2011 (USA format), the end date is 03/01/2011. Use -0 to indicate the current date.¹
time	Ending time for the data to be included in the reports Valid values are any time in the range 00:00:00–23:59:59 or 00:00–23:59. When specified without a date, the end date defaults to the start date. For this reason, an end time without a date is valid only if the specified time is later than the start time. Information Example If the start date and time are 01/01/2011 8:00:00 (USA format) and you specify an end time without a date, the default date is 01/01/2011 and the specified time must be later than 8:00:00.
date,time	When both a date and time are specified, the report ends with the last record (in the archived data set when a data set is the source; in the active data set when a Data Collector is the source) having a timestamp equal to or less than the specified date and time.
-nn,time	When both a number of days and a time are specified, the date represented by -nn is calculated and the report ends with the last record having a timestamp equal to or less than the calculated date and specified time.

^{1 When specified without a time, the time defaults to midnight (12:00 A.M.) on the specified date (midnight = beginning of day).}

FOR keyword

The FOR keyword (optional) specifies the duration of the interval (as an alternative to specifying an END time).

Note

END and FOR are two different ways to specify the ending time of reports. If you specify both keywords, END is ignored.

When the data source is a data set:
- If you do not specify an END or FOR keyword, the end time defaults to the timestamp of the last record in the data set.
- If you do not specify any of the START, END, or FOR keywords, the product reports the entire contents of the data set.
When the data source is a Data Collector:
- If you do not specify an END or FOR keyword, the end time defaults to the time of report deactivation.
- If you do not specify a START or an END, the product reports only the current interval at report activation or deactivation.

Note

If you are generating the Batch SQL Comparison Analysis report (SQMBDIFF), you can include multiple START/END/FOR groupings. A label that specifies a named period must precede each grouping.

Valid abbreviation: F

FOR keyword values

Value	Description of value
nnn	Duration of the interval stated as a number of days Valid values are any number in the range 1–364.
time	Duration of the interval stated hours, minutes, and seconds (hh:mm:ss). Valid values are any time in the range 00:00:00–23:59:59 or 00:00–23:59.
nnn,time	Duration of the interval stated as a number of days and hours For example, a specification of 30,6:00 means that the interval is reported in periods of 30 days and 6 hours. Information Example INTERVAL(START(02/22/2011,6:00:00) - FOR(6:00:00)) This INTERVAL statement sets a global default interval for all reports, which includes data generated on 02/22/2011 (USA format) between 6:00 a.m. and 12:00 noon. Reporting begins with the first record (in the archived data set when data set is the source; in the active data set when a Data Collector is the source) with a timestamp equal to or having an interval that includes the start time and ends with the last record with a timestamp equal to or less than the end date and time. If the data source is a Data Collector and the end time falls within the current interval at report activation, no data from that interval is reported. This INTERVAL statement applies to all subsequent REPORT statements in the same job stream unless they contain an INTERVAL parameter or until another INTERVAL statement is encountered.

INTERVAL parameters

START keyword

END keyword

FOR keyword

BMC AMI Apptune for Db2 12.1

On this page