Exception Advisor parameters


Input parameters control various aspects reported on by the Exception Advisor.

The following table describes these input parameters. The parameters are also explained in the comments of the Exception Advisor job.

Note

The Exception Advisor parameters have two default value behaviors. One value is the default if the parameter is not specified (omitted). The other default behavior is used when the parameter is specified without a value (NULL).

The Exception Advisor parameters have two default behaviors. One behavior is the default if you omit the parameter. The other behavior is the default when you specify the parameter without a value (NULL).

  • Omitted: To use the omitted value for a parameter, leave the option out by removing or commenting out the line for the parameter.
  • NULL: To use the null value for a parameter, include the parameter, but specify nothing to the right of the equal sign.

 

Parameter

Description

Omitted value

Null value

exception.baseline

Table that contains baseline statement statistics

Exception Advisor uses this value to compare performance characteristics of an exceptional statement execution with statistics for the same statement in a baseline.

BASELINE_STMT_STATISTICS

No baseline reporting

exception.daily

Table that contains daily statement statistics

Exception Advisor uses this value to compare performance characteristics of an exceptional statement execution with statistics for the same statement in a daily table.

DAILY_STMT_STATISTICS

No daily reporting

exception.evnttab

Table that holds basic information about exceptions if updateresults is 1

XADVISOR_EVENTS

XADVISOR_EVENTS

exception.filter

Value that limits the statements to be evaluated

Specify a WHERE predicate to be applied to the exception table. Enclose the predicate in double quotation marks. Use a plus sign (+) for continuation if needed.

Example
exception.filter="WHERE PROGRAM='PROGRAM1' + AND PLAN='PLAN1'"

You can use any columns in the STATS_EXCEPTION table for filtering.

No filtering

No filtering

exception.hisetab

Table that holds statement history entries If updateresults is 11

XADVISOR_HISTORY_ENTRIES

XADVISOR_HISTORY_ENTRIES

exception.histtab

Table that holds statement history header information for statement history If updateresults is 11

XADVISOR_HISTORY_TITLES

XADVISOR_HISTORY_TITLES

exception.hvtab

Table that contains host variable value information 1, 2

STMT_EXCEPTIONS_HV

No host variable reporting

exception.interval

Table that contains interval statement statistics

Exception Advisor uses this value to compare performance characteristics of an exceptional statement execution with statistics for the same statement in an interval table.

No interval reporting

No interval reporting

exception.level

Value that determines whether to evaluate multiple exceptions individually or together as one:

  • SUMMARY evaluates all exceptions of same type from the same statement as a single exception.
  • DETAIL evaluates each exception individually.

DETAIL

DETAIL

exception.monthly

Table that contains monthly statement statistics

Exception Advisor uses this value to compare performance characteristics of an exceptional statement execution with statistics for the same statement in a monthly table.

MONTHLY_STMT_STATISTICS

No monthly reporting

exception.nondml

Value that determines whether to process statements other than SELECT, OPEN, FETCH, CLOSE, INSERT, UPDATE, and DELETE

The following values are supported:

  • 0 = Do not report exceptions for these statements.
  • 1 = Report exceptions but do not report history.
  • 2 = Report exceptions and history.

1

1

exception.obtab

Table that contains exception object information 1, 2

STMT_EXCEPTIONS_OB

No object reporting

exception.printreports

Value that determines whether to process reports as online Exception Advisor table output

  • 0 = Reports are not printed as the advisor runs.
  • 1 = Reports are generated as advisor output.

The exception.updateresults parameter controls sending results to Db2 tables for online viewing.

1

1

exception.purgeresults

If updateresults is in effect, the age at which the product purges existing rows in the XADVISOR results tables

CURRENT_DATE - 7 DAYS

No rows deleted

exception.ruletab

Table that holds advice related to defined rules (from //RULES DD) if updateresults is 11

XADVISOR_RULES

XADVISOR_RULES

exception.runtab

Table that holds information pertaining to this and other executions of the advisor, such as number and time range of exceptions reported. 1

XADVISOR_RUNS

XADVISOR_RUNS

exception.startdate

The first date for which exceptions are evaluated

Use format yyyy-mm-dd or supported SQL expressions such as CURRENT_DATE - 1 DAY.

Blank specifies no limit.

CURRENT_DATE - 1 DAY

Unlimited start

exception.starthist

Date at which to start reporting historical data

Use the same format as the exception start date.

No limit

No limit

exception.stopdate

Last date for which exceptions are evaluated

Use format yyyy-mm-dd or supported SQL expressions such as CURRENT_DATE - 1 DAY.

Blank specifies date of most recent record in statement exception table.

CURRENT_DATE - 1 DAY

Date of most recent record in statement exception table

exception.tab

Table that contains statement exception information

This table must exist and be populated with data from the DMDBMERG utility.1

STMT_EXCEPTIONS

STMT_EXCEPTIONS

exception.tabcreator

Default table owner or creator if subsequent table specifications do not include the owner or creator

BMCSFTWR

BMCSFTWR

exception.texttab

Table that contains SQL text 1, 2

STMT_TEXT

No text reporting

exception.updateresults

Value that determines whether to update the ADVISOR tables with the results of this run for subsequent online viewing

  • 0 = Do not update tables with results.
  • 1 = Update tables with results.

If 1 is specified, exception.level must be set to DETAIL.

0

1

exception.valuetab

Table that holds exception values that exceeded thresholds if updateresults is 11

XADVISOR_VALUES

XADVISOR_VALUES

exception.weekly

Table that contains weekly statement statistics

Exception Advisor uses this value to compare performance characteristics of an exceptional statement execution with statistics for the same statement in a weekly table.

WEEKLY_STMT_STATISTICS

No weekly reporting

1 If specified without an owner, exception.tabcreator is the owner.

2 This table is optional. If present, it must be populated with data from the DMDBMERG utility.

 

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