ANLCNTL configuration parameters

Descriptions of ANLCNTL parameters

The ANLCNTL parameters are not normally modified by Db2 SQL Performance Analyzer users.

The following parameters define a target host configuration. You can have multiple configurations (PDS members) in batch using the ANLCNTL DD statement to point to the hiqual.SANLDATA PDS member, and in TSO by having multiple members in the hiqual.SANLPARM PDS. For each member (subsystem, LPAR, or configuration), configure the following parameters of ANLCNTL:

AUTHIDO

The Db2 user ID is used to customize SQL PA. This ID owns the SQL PA objects that are created for the customization and must have SYSADM authority. The default value is ANLUSER0.

BUFF08K nnnnnnnn

Set the BUFF08K system parameter to the total number of 8 KB buffers in the 8 KB buffer pool of the dominant application. This value is used to set the number of pages per block for sequential prefetch and other I/O calculations. Use the most prominent 8 KB buffer pool here, even if several are used.

The default value is 00000500 buffers.

BUFF16K nnnnnnnn

Set the BUFF16K system parameter to the total number of 16 KB buffers in the 16 KB buffer pool of the dominant application. This value is used to set the number of pages per block for sequential prefetch and other I/O calculations. Use the most prominent 16 KB buffer pool here, even if several are used.

The BUFF16K default value is 00000250 buffers.

BUFF32K nnnnnnnn

Set the BUFF32K system parameter to the total number of 32 KB buffer s in the 32 KB buffer pool of the dominant application. This value is used to set the number of pages per block for sequential prefetch and other I/O calculations. Use the most prominent 32 KB buffer pool here, even if several are used.

The default value is 00000100 buffers.

BUFFERS nnnnnnnn

Set the BUFFERS system parameter to the total number of 4 KB buffers in the 4 KB buffer pool of the dominant application. This value is used to set the number of pages per block for sequential prefetch and other I/O calculations. Specify the most prominent 4 KB buffer pool here, even if several are used.

The default value is 00002000 buffers.

COMMENT remarks

You can insert a comment anywhere in a parameter file. All remarks are transferred to the output report. To use this parameter, type COMMENT followed by your remarks.

COSTING nnnnnnnn

The COSTING system parameter indicates the maximum monetary value, in your currency, that is permitted before the SQL statement is flagged as exceeding the monetary limit. The maximum value is 999999999. A setting of 0 results in no monetary limit.

The default value is 15.

COSTQUN nnnnnnnn

The COSTQUN system parameter indicates the maximum number of QUNITS that are permitted before the SQL statement is flagged as exceeding the QUNIT limit. The maximum value is 999999999. A setting of 0 results in no QUNIT limit.

The default is 200.

Reminder: CPUTIME, COSTING, and COSTQUN are limits for these resources. SQL PA flags all activity that exceeds the limits set by these parameters. If no parameters are specified, SQL PA assumes that there is no limit for that category.

CPUCOST nnnn.nnn

Set the CPUCOST system parameter to the cost of one hour of CPU time, in the national currency, for this configuration. This parameter is used by SQL PA for charge back and financial cost determination. SQL PA

The default value is 500 per hour for processor time.

CPUTIME nnnnn

The CPUTIME system parameter indicates the maximum CPU time, in seconds, that is permitted before the SQL statement is flagged as exceeding the time limit. The minimum value is 0.001. The maximum value is 86400, which is equivalent to 24 hours. A setting of 0 results in unlimited CPU time.

The default value is 5.

CURRSYM a

Set the CURRSYM system parameter to the single character symbol that represents the national currency that corresponds to the MONEYIS parameter.

CPUCOST and TIMCOST are used to calculate the total monetary value of a transaction, based on your charge back costing guidelines. Setting one or more of these parameters to zero, or leaving out, changes the cost algorithm accordingly. For example, if your installation does not charge for connect time, the costing would reflect only processor and I/O charges.

The default value is the dollar sign, $.

DATAGRP nnnnnnnn

The DATAGRP system parameter indicates the name of the user group authorized to view user table data. If the value of DATAGRP is set to +OFF+, the feature is disabled and all users will be able to view the user table data.

The default value is +OFF+.

FASTTBL YES | NO

Use this parameter to indicate whether to bypass the explain plan processing. When FASTTBL is NO and the TABLES function is chosen, EXPLAIN is run on the statements. When FASTTBL is YES, and the TABLES function is chosen, processing bypasses the EXPLAIN, and uses the catalog queries to gather the needed information.

MONEYIS aaaaaaaa

Set the MONEYIS system parameter to the national currency, in descriptive form, such as DOLLARS, POUNDS, STERLING, DRACHMA, or KRONA.

The default value is DOLLARS.

SETPLAN NOTALLOW | ALLOW | GENERIC

Note: Prior to Version 5.1, the only values accepted were YES and NO, with a default of NO. These values will still be processed if already specified as a parameter in a previous version of SQL PA.

• If the SETPLAN value is set to ALLOW (or YES prior to V5.1), the SETPLAN system parameter authorizes the use of the USEPLAN user parameter to indicate the qualifier of the plan table.
• If SETPLAN is set to NOTALLOW (or NO prior to V5.1), the value of USEPLAN should be +OFF+. The plan table is qualified by a generic ID or the user's TSO ID if no generic ID is available.
• If SETPLAN is set to GENERIC, only a generic ID will be used to qualify the plan table.

The default value for SETPLAN is NOTALLOW. The default value for USEPLAN is +OFF+. That value (or blanks) also negates the effects of USEPLAN, regardless of the SETPLAN setting. SETPLAN is an optional solution intended for sites which restrict PLAN_TABLE access. When possible, use the generic ANLUSER1–n tables for processing.

The parameter is set in the data set identified by the ANLCNTL DD statement in batch and by the system parameters data set identified on the "Parameter Data Sets" panel for TSO. The default USEPLAN value is +OFF+, and it is not activated unless the SETPLAN ALLOW (or YES prior to V5.1) parameter is also included in the system parameters data set member. If you implement this option for all TSO users, then the SANLPARM members, which constitute the Db2 Target Host selections, must be modified to include SETPLAN ALLOW (or YES prior to V5.1).

Note:

1. SETPLAN is an optional solution for those sites that need it. When possible, use the generic ANLUSER1-n tables for their processing.

   To comply with your security guidelines for SQL PA generic IDs, you might want to create an ALIAS for the generic ANLUSER1-n tables.

2. The default value for USEPLAN is +OFF+. That value (or blanks) also negates USEPLAN's effects, regardless of the SETPLAN value.

The default value is NOTALLOW.

SUBSYST aaaaaaaa

This parameter specifies the Db2 subsystem name, up to eight characters long, on this machine. Normally, Db2 is called DSN on most processors, but it is possible to run several copies of Db2 on the same machine. If you run more than one copy of Db2, you must define a separate configuration file for each Db2 subsystem. It is not necessary to specify SUBSYST if your Db2 subsystem's name is DSN.

The default value is DSN.

SUBVERS aaaaaaaa

This parameter represents the Db2 version that you are using at your installation. Acceptable values are V8COM, V8NFM, V9COM, and V9NFM. V#COM and V#NFM differentiate between conversion mode and new function mode. This parameter is primarily used in the TSO Target Hosts library's members, hiqual.SANLPARM, to allow the ANLALL CLIST a way to select alternate Db2 subsystems or program modules for a single TSO installation that accesses multiple Db2 subsystems at different Db2 levels.

It is also used to select the appropriate catalog access host variables for optimum performance and must always be specified.

TIMCOST nnnn.nnn

Set the TIMCOST system parameter to the cost of one hour of connect time, in the national currency, for this configuration. This parameter is used by SQL PA for charge back and financial cost determination. The processor time provided by the optimizer is used for cost assessment.

The default value is 10 per connect hour.

USRPARM YES | NO

The USRPARM system parameter authorizes the use of the user-level user parameters.

The default value is NO.