Using the Product in a DB2 Environment › Background DB2 Catalog Extraction › Synchronous Data Gatherer › DB2 Harvester and Data Collection Settings

DB2 Harvester and Data Collection Settings

Following lists the DB2 Harvester and data collection settings in TUNSSP00 that are used by CA Mainframe Application Tuner.

DB2HRVST

Synchronous Data Gatherer activation.

During initialization of the Server, this parameter prepares the environment for the Harvester to accept START and STOP exit commands.

When the DB2 Harvesters are in place, absolute DB2 counts, CPU time, and elapsed times are gathered.

The following are valid responses:

• YES activates the DB2 Harvester facility and allows CA Mainframe Application Tuner to gather data.
• NO prevents the activation of the DB2 Harvester facility and CA Mainframe Application Tuner will not gather data.

The default is DB2HRVST=NO (for no DB2 support).

Recommendation: When customizing for DB2 support, set this value to YES.

DB2EXPL

Explain data collection.

Request that information regarding DB2 access path selection be obtained from DB2 SQL statements by issuing the EXPLAIN command and externalizing the data.

The call for Explain data is made while the address space is being measured.

Explain will be performed for all static SQL found in the DBRM or package, as bound into the DB2 catalog. When DB2CTSQL=NO (do not access the DB2 catalog for SQL) is specified, the statement that is explained is derived from internal DB2 objects.

Dynamic SQL is always explained from SQL derived from internal DB2 objects.

The following are valid responses:

• YES indicates that Explain data will be collected for each dynamic SQL statement and all SQL statements in a DBRM or package.
• NO indicates no DB2 Explain data is to be gathered.
• BATCH indicates DB2 Explain data is collected only for batch jobs and not for online systems, such as CICS.

The default is DB2EXPL=NO (for no DB2 support).

Recommendation: When customizing for DB2 support, set this value to YES.

DB2HEXPL

Explain data from harvested SQL.

Request that information regarding DB2 access path selection be obtained from DB2 SQL statements that were extracted using the Synchronous Data Gatherer.

This statement is only valid when DB2HVSQL=YES (harvest all SQL) or DB2HVSQL=NO and DB2HVDYN=YES (harvest only dynamic SQL) are specified.

The following are valid responses:

• YES indicates that Explain data will be collected for each dynamic SQL statement and all SQL statements in a DBRM or package that are seen by the Synchronous Data Gatherer.
  • DB2EXPL=YES must be specified in conjunction with this option.
  • DB2HVSQL=YES or DB2HVDYN=YES is required with this option.
• NO indicates that no DB2 Explain of the harvested SQL is to be performed.

The default is DB2HEXPL=NO (for no DB2 support).

Recommendation: When customizing for DB2 support, set this value to YES.

DB2CTSQL

SQL from DB2 catalog.

DB2CTSQL is used to determine whether or not CA Mainframe Application Tuner should extract the plan or package statements from the DB2 catalog or extract them directly from DB2 control blocks when monitoring a DB2 application.

DB2HVSQL=YES and DB2HRVST=YES should be used in conjunction with DB2CTSQL=NO since the Data Harvester collects information in a synchronous manner.

Specify whether or not to collect the SQL statement from the DB2 catalog. The following are valid responses:

• YES will collect the SQL data for each plan or package sampled or harvested from the DB2 catalog.
• NO will not collect the SQL statement from the DB2 catalog.

It is recommend that DB2CTSQL=YES be used.

The default is DB2CTSQL=YES.

DB2SPSQL

Collect the SQL statement during the sample process.

A data record is written with the SQL found in internal DB2 control blocks for the address space being monitored.

The following are valid responses:

• YES will collect the SQL statement during the sample process.
• NO will not collect the SQL statement during sampling.

The default is DB2SPSQL=YES.

DB2SPDYN

Collect dynamic SQL statement during sampling if DB2SPSQL=NO

A data record is written with the dynamic SQL statement found in internal DB2 control blocks for the address space being monitored. YES will produce the dynamic SQL statement each time a call to DB2 is sampled and the SQL statement is determined to be dynamic.

Note: When DB2SPSQL=YES is specified, DB2SPDYN= is ignored.

The following are valid responses:

• YES will collect the dynamic SQL statement during the sample process.
• NO will not collect the dynamic SQL statement during the sample process.

The default is DB2SPDYN=NO.

DB2HVSQL

Collect harvested SQL data.

A data record is written with the SQL found in internal DB2 control blocks for the address space being monitored. YES will produce the SQL data record each time a call to DB2 is harvested.

The following are valid responses:

• YES will harvest the SQL statement.
• NO will not harvest the SQL statement.

It is recommend that DB2HVSQL=YES be used.

The default is DB2HVSQL=YES.

DB2HVDYN

Collect dynamic SQL data if DB2HVSQL=NO.

A data record is written with the dynamic SQL found in the internal DB2 control blocks for the address space being monitored.

The following are valid responses:

• YES will collect the dynamic SQL data records.
• NO will not collect the dynamic SQL data records.

The default is DB2HVDYN =NO.

DB2HVEXT

Exit name.

Specify the name of the exit to be used when a Harvester is started during a monitor session.

The following are valid values:

• DB2E1 — harvest non-COMMIT SQL calls to DB2
  (see also DB2HVSE1 below)
• DB2E2 — harvest only COMMIT calls to DB2
  (see also DB2HVSE2 below)
• DB2E3 — harvest both COMMIT and other SQL calls to DB2
  (see also DB2HVSE3 below)

Recommendation:

Use the default parameter, DB2E3, to collect the most complete data.

When using the default, you should also review DB2HVSE3 to define the DB2 subsystems where you would like CA Mainframe Application Tuner to implant the DB2 Harvester during startup processing.

The default is DB2HVEXT=DB2E3.

DB2HVSE1

Plant DB2 Harvester exit DB2E1 at server start.

Use this parameter to avoid losing some data related to the first call to DB2. This parameter allows the Server initialization process to implement the Harvester exit during startup.

Specify a list up to 12 DB2 subsystems that a Harvester START command can issue for the DB2E1 exit.

The format is DB2HVSE1=ssid,ssid,ssid,...

This parameter should only be used when DB2HVEXT=DB2E1 is specified.

There is no default.

DB2HVSE2

Plant DB2 Harvester exit DB2E2 at server start.

Use this parameter to avoid losing some data related to the first call to DB2. This parameter allows the Server initialization process to implement the Harvester exit during startup.

Specify a list up to 12 DB2 subsystems that a Harvester START command can issue for the DB2E2 exit.

The format is DB2HVSE2=ssid,ssid,ssid,...

This parameter should only be used when DB2HVEXT=DB2E2 is specified.

There is no default.

DB2HVSE3

Plant DB2 Harvester exit DB2E3 at server start.

Use this parameter to avoid losing some data related to the first call to DB2. This parameter allows the Server initialization process to implement the Harvester exit during startup.

Specify a list up to 12 DB2 subsystems that a Harvester START command can issue for the DB2E3 exit.

The format is DB2HVSE3=ssid,ssid,ssid,...

This parameter should only be used when DB2HVEXT=DB2E3 is specified.
Note: DB2HVEXT=DB2E3 is the default.

There is no default.

DB2HVLOC

Harvest the requesting location .

The requesting location name is generally of importance for distributed DB2 threads. It can help to identify where the SQL originated from.

Most other threads will originate from the local location.

The following are valid responses:

• YES - Use LOCATION as part of the key to gather information.
• NO - Do not add LOCATION to the key.

The default is DB2HVLOC=YES.

DB2HVCOR

Use correlation ID as part of the key.

Collect the DB2 correlation ID for the DB2 data in both the Harvester and the sampling process.

This option adds a higher level of granularity to the DB2 data being collected.

• For IMS, the correlation ID is the PSB name.
• For CICS, the correlation ID is the transaction name.
• For Batch type threads, the correlation ID is the job name.
• For remote and RRSAF threads, the value in this field is dependent on the requesting application.

The following are valid responses:

• YES - Use the DB2 correlation ID in building the key.
• NO - Do not add correlation ID to the key.

The default is DB2HVCOR=YES.

DB2HVIID

Use operator ID as part of the key.

Collect the DB2 primary operator ID for the DB2 data in both the Harvester and the sampling process.

This option adds a higher level of granularity to the DB2 data being collected.

The following are valid responses:

• YES - Use the DB2 operator ID in building the key.
• NO - Do not add operator ID to the key.

The default is DB2HVIID=YES.

DB2HVTHD

Use thread address as part of the key.

Add the DB2 Thread Agent Control Element address to the data being collected by the Harvester and the sampling process.

The following are valid responses:

• YES - Use the DB2 thread address in building the key.
• NO - Do not add the DB2 thread address to the key.

The default is DB2HVTHD=YES.

DB2HVSUM

Allow for the construction of an internal table that will be used to summarize repetitive harvested DB2 data.

The numeric value specified represents the number of data rows that will be stored internally. Each time the Harvester is activated, this table will be searched sequentially to determine if there is a like row present. If the key values of the row are equal, then the data is added to the existing row. If no matching row is found, then a new row is added to the table. When the table is full, the row at the bottom of the table is sent to the monitor file and the new row is placed into the table at that location.

The number of slots is an arbitrary value that should be derived by an estimation of the looping process within a DB2 application and the number of times that application will execute during the monitor cycle. If the largest program executes 25 calls to DB2 before looping back to execute the 25 calls again and is executed many times in a row, then a value of DB2HVSUM=25 would insure that the calls for the application that were made during the monitor session would be summarized before being written.

Part of the summarization key is the SQL statement itself, so that uniquely dynamic calls will not benefit from summarization.

When SQL records are requested along with summarization, the SQL is only written once to reduce the amount of data written.

The value range that can be entered for DB2HVSUM is 0 through 9999.

The default is DB2HVSUM=0.

DB2HVMXA

Harvester abends.

Designate the number of Harvester abends that will be allowed before shutting down the collection of data.

When an error occurs, the Harvester writes diagnostic information to LOGREC and displays the following message on the server JES log:

TN3438W Harvester Abend cccccccc aaaaaaaa dddddddd tttttttt ssssssss pppppppp jjjjjjjj

TN3439I Harvester processing continues. pppppppp jjjjjjjj

where

• cccccccc is the abend code.
• aaaaaaaa is the next instruction address.
• dddddddd is displacement of instruction.
• tttttttt is the count of abends.
• ssssssss is the intercept sequence number.
• pppppppp is the profile name.
• jjjjjjjj is the job name being monitored.

Harvesting processing continues until the maximum abend count specified by DB2HVMXA is exceeded. At this time, the following message displays:

TN3440W Max Harvester ABEND count has been exceeded. Harvester processing ended for pppppppp jjjjjjjj

where

• pppppppp is the profile name.
• jjjjjjjj is the job name being monitored.

When the value specified for ABEND count is reached, the Harvester will stop collecting data. If DB2HVMXA is zero, no limit is recognized.

The default is DB2HVMXA=1000.

DB2HVMXM

Abend messages.

Designate the number of abend messages that will be displayed in the server JES log if the Harvester encounters an error.

To limit the number of messages displayed in the JES log and LOGREC, use DB2HVMXM to stop the display of Harvester abend messages.

When the message count exceeds the value specified for DB2HVMXM, the following message displays:

TN3441W Max Harvester ABEND message count exceeded for pppppppp jjjjjjjj

where

• pppppppp is the profile name.
• jjjjjjjj is the job name being monitored.

The default is DB2HVMXM=100.

DB2TCSID

UNICODE translation code page.

Translate data from UNICODE code page 1208 into the code page specified.

Supported code page values are -1, 37, 273, 274, 277, 278, 280, 281, 284, 285, 297, 500, 871, and 1047.

The default is DB2TCSID=-1. (-1 is equivalent to code page 37.)

CAFSTALL

DB2 CAF interface timeout value.

Specify a timeout value in seconds.

When CA Mainframe Application Tuner issues calls to DB2 to obtain Explain data, CA Mainframe Application Tuner will set a timer. If the timer expires before the Explain data is returned, CA Mainframe Application Tuner assumes the DB2 region is stalled. For large DB2 systems, the default of 90 seconds might not be sufficient to return DB2 Explain data. For these cases, CAFSTALL allows the site to specify the timer amount in seconds.

Default is 90.

DB2LIBS

DB2 parameter member suffix.

Specify a TUNDB2xx member with this keyword.

The CAF Interface loads DB2 interface modules to obtain the SQL statement text from the DB2 catalog. For each version of DB2 you have installed at your site, you need to specify a load library where DSNHLI is located. These load libraries are in the parameter library member of TUNDB2xx, where xx is a 2-character suffix or &SYSCLONE.

Default is xx, where xx is the release number of CA MAT.

DB2PACK

DB2 package name.

Specify the name of the DB2 package assigned during the DB2 BIND job during customization.

This job must be run against every DB2 subsystem against which monitors will be run.

The default is MATnn, where nn is the value specified for the DB2LIBS keyword (normally the release number of CA MAT).

DB2PLAN

DB2 plan name.

Specify the name of the DB2 plan assigned during the DB2 BIND job during customization.

This job must be run against every DB2 subsystem against which monitors will be run.

The default is MATnnDB2, where nn is the value specified for the DB2LIBS keyword (normally the release number of CA MAT).