Step 1 (DPRSMFA)—SMF extract and file creation

This step specifies the name of the Performance Reporter statistics, accounting, and audit file creation program as PGM=DPRDSMF and the region required to run the program.

SMF extract DD statements

The following table describes the SMF extract DD statements:

Related topic

DPRSMFA-job-control-statements

DD statement

Description

STEPLIB DD

Defines the program library containing the DPRDSMF load module

DPRLOG DD

Defines the data set for the program results summary, which indicates input and output record counts

SYSOUT DD

Defines the data set for the program results summary, which indicates input and output record counts

SYSUDUMP DD

Defines the dump data set for problem determination

DPDACCT DD

Contains the accounting records created from the Db2 SMF 101 accounting records for TYPE=ACCT and, optionally, for PKGACCT, DDFACCT, BUFACCT

Tip

For improved performance in STEP3 SUMMMARIZE and STEP4 LOAD processing, BMC recommends that DPDACPKG, DPDACDDF, and DPDACBUF DD statements be used for accounting data other than TYPE=ACCT.

DPDSTOR DD

(Optional)

If present, contains the address space storage records (ASPCSTOR) created from the Db2 SMF 100 statistics records

This file is passed to STEP3 STORDD. If this statement is not present, DPDSTOR records are written to DPDSTAT.

DPDACPKG DD

(Optional)

If present, contains the package accounting records (PKGACCT) created from the Db2 SMF 101 accounting records

This file is passed to STEP3 ACPKGDD and is concatenated to the STEP6 DPDACCT DD statement. If this statement is not present, PKGACCT records are written to DPDACCT.

DPDACDDF DD

(Optional)

If present, contains the DDF accounting records (DDFACCT) created from the Db2 SMF 101 accounting records

This file is passed to STEP3 ACDDFDD and is concatenated to the STEP6 DPDACCT DD statement. If this statement is not present, DDFACCT records are written to DPDACCT.

DPDACBUF DD

(Optional)

If present, contains the buffer accounting records (BUFACCT) created from the Db2 SMF 101 accounting records

This file is passed to STEP3 ACBUFDD. If this statement is not present, BUFACCT records are written to DPDACCT.

DPDACACC DD

(Optional)

If present, contains the accelerator accounting records (ACCA) created from the Db2 SMF 101 accounting records

This file is passed to STEP3 ACCELDD. If this statement is not present, DPDACACC records are written to DPDACCT.

DPDSTAT DD

Contains the statistics records created from the Db2 SMF 100 statistics records

This file is passed to the statistics consolidation step. For more information, see Step-2-DPRSMFA-Statistics-consolidation.

Contains the statistics records created from the Db2 SMF 100 statistics records (Q3ST-QLES)

This file is passed to the statistics consolidation step. For more information, see Step-2A-DPRSMFA-Statistics-consolidation.

Contains the statistics records created from the Db2 SMF 100 statistics records (QLST-QXST)

This file is passed to the statistics consolidation step. For more information, see Step-2B-DPRSMFA-Statistics-consolidation.

DPDAUDIX DD

Contains audit records for record extraction processing

This is an internal DD statement that is used during extraction processing of audit records.

DPDAUDIT DD

Contains the audit records created from the Db2 SMF 102 audit records

This file is passed to the load into Db2 tables step. For more information, see Step-3-DPRSMFA-Job-submission-for-parallel-processing.

DPDDGBP DD

If present, contains the dynamic buffer pool attribute records (IFCID 202) and group buffer pool attribute records (IFCID 230)

The file is passed to the load into Db2 tables step. For more information, see Step-3-DPRSMFA-Job-submission-for-parallel-processing .

SORTIN DD

or

DPRINPUT DD

Defines the data (an SMF data set or DOM archive data set) to be extracted and sorted

The DPRINPUT DD statement is optional. If the SORT=NO control statement is specified, the DPRDSMF program attempts to extract the data from the DPRINPUT DD data set; if the DPRINPUT DD statement is omitted, DPRDSMF extracts the data from the SORTIN DD data set. For more information about SORT=NO, see DPRCNTL SMF extract control statement.

DPRCNTL DD

Defines the Db2 subsystem and the selection of data types by using the following control statements (described in DPRCNTL SMF extract control statement):

  • DATE=
  • EXCLUDEPKG=
  • INCLUDEPKG=
  • SHORTREC=[YES|NO]
  • SORT=[YES|NO|COPY]
  • SSID=
  • TCCSID=
  • TIME=
  • TRANSLATEDBID=[YES|NO]
  • TYPE=
  • TYPE2=
  • TYPE3=
  • TYPE4=

DFSPARM DD

 

Specifies the VLSHRT override option (for DFSORT users only)

VLSHRT—Temporarily overrides the VLSHRT installation option. DFSORT continues processing if a variable-length input record is too short to contain all specified INCLUDE compare fields.

Important

This override parameter is necessary only if you receive the following messages in the job output:

  • Message DPR0104I
  • Return code 16
  • An error message from the sort routine indicating that an SMF record is too short

This method of processing short SMF records provides better performance than using the SHORTREC control statement. However, this option works only with DFSORT Release 11.1 or later. If using any other sort routine, you need to add the SHORTREC control statement.

DPRCNTL SMF extract control statement

The following table describes the control statements for the DPRCNTL SMF extract control statement:

Statement

Description

DATE=

Select records only from specific date ranges:

  • DATE=01JAN2002-01FEB2002 (records for all of JAN 2002)
  • DATE=*_1-* (records from yesterday)
  • DATE=*_3 (records for previous 3 days plus today)

EXCLUDEPKG=

Exclude package accounting records that you explicitly specify

The INCLUDEPKG and EXCLUDEPKG filters are mutually exclusive. If you insert both INCLUDEPKG and EXCLUDEPKG filters, the DPRSMF job generates message DPRO215I and terminates.

The following keywords are valid:

  • PROGRAMNAME (program name—package ID or DBRM)
  • FIRSTPKG (first package or DBRM executed)
  • CORRID (correlation ID value)
  • CONNECTION (connection name)
  • CONNTYPE (connection type)
  • PLANNAME (plan name)
  • AUTHID (primary authorization ID)

You can specify up to eight comma-separated values for each keyword. Enclose keyword values that have an embedded space in single quotation marks, such as 'Db2 CALL'.

Example
EXCLUDEPKG
     PROGRAMNAME=DSNESM68,
      CORRID=(PKGCORR1,PKGCORR2,PKGCORR3XXXX,BOLHHH4),
     CONNECTION=(PKGCONN1,PKGCONN2,PKGCONN3,DB2CALL),
      CONNTYPE=(PKGCONT1,PKGCONT2,PKGCONT3,'DB2 CALL'),
      PLANNAME=(PKGPLAN1,PKGPLAN2,PKGPLAN3,DSNTIA51),
      AUTHID=(PKGAUTH1,PKGAUTH2,PKGAUTH3,BOLCJN2),
      FIRSTPKG=(PKG11,PKG12,PKG13XXXXXXXXXXXXX,TSMCOM2,PKG15,PKG16,PKG17XXXXXXXXXXXXX,TSMCOMX)

Multiple keywords for EXCLUDEPKG imply a logical OR operand.

Example

The following filter excludes any records that have PLANNAME=AUD110I or FIRSTPKG=ADUECAT:

EXCLUDEPKG PLANNAME=(AUD110I),
      FIRSTPKG=(ADUECAT)

If you use the EXCLUDEPKG filter from package accounting (PKGACCT), the only records excluded are those with matching values. To compare values for the CONNTYPE=keyword, see the CONNTYPE column in Accounting-detail-and-summary-tables-DMRACxxx.

INCLUDEPKG=

Include package accounting records that you explicitly specify

The INCLUDEPKG and EXCLUDEPKG filters are mutually exclusive. If you insert both INCLUDEPKG and EXCLUDEPKG filters, the DPRSMF job generates message DPRO215I and terminates.

The following keywords are valid:

  • PROGRAMNAME (program name—package ID or DBRM)
  • FIRSTPKG (first package or DBRM executed)
  • CORRID (correlation ID value)
  • CONNECTION (connection name)
  • CONNTYPE (connection type)
  • PLANNAME (plan name)
  • AUTHID (primary authorization ID)

You can specify up to eight comma-separated values for each keyword. Enclose keyword values with embedded space in single quotation marks, such as 'DB2 CALL'.

Example
INCLUDEPKG
     PROGRAMNAME=DSNESM68,
      CORRID=(PKGCORR1,PKGCORR2,PKGCORR3XXXX,BOLHHH4),
     CONNECTION=(PKGCONN1,PKGCONN2,PKGCONN3,DB2CALL),
      CONNTYPE=(PKGCONT1,PKGCONT2,PKGCONT3,'DB2 CALL'),
      PLANNAME=(PKGPLAN1,PKGPLAN2,PKGPLAN3,DSNTIA51),
      AUTHID=(PKGAUTH1,PKGAUTH2,PKGAUTH3,BOLCJN2),
      FIRSTPKG=(PKG11,PKG12,PKG13XXXXXXXXXXXXX,TSMCOM2,PKG15,PKG16,PKG17XXXXXXXXXXXXX,TSMCOMX)

Multiple keywords for INCLUDEPKG imply a logical OR operand. 

Example

The following filter includes any records that have PLANNAME=AUD110I or FIRSTPKG=ADUECAT:

INCLUDEPKG PLANNAME=(AUD110I),
      FIRSTPKG=(ADUECAT)

If you use the INCLUDEPKG filter from package accounting (PKGACCT), the only records included are those with matching values. To compare values for the CONNTYPE=keyword, see the CONNTYPE column in Accounting-detail-and-summary-tables-DMRACxxx.

SHORTREC=YES|NO

Allows selection by Db2 subsystem ID from SMF data with records shorter than 18 bytes

Important

This control statement is recommended only if you have specified SSIDs and receive message DPR0104I or SYNCSORT message WER250A. You will receive this message if some of the SMF records are shorter than 18 bytes and the VLSHRT option is not applicable to your SORT routine.

If you are using DFSORT Release 11.1 or later, see the description for the DFSPARM DD value.

SORT=YES|NO|COPY

Determines whether input records are sorted

The sort is required to ensure that the input records are in chronological order. However, if the input records are already in chronological order, you can bypass the sort and reduce CPU usage, I/O, elapsed time, and DASD usage.

SORT=YES is the default. You should specify YES when multiple files are being extracted, and the files might not be in chronological order.

You can specify SORT=NO or SORT=COPY in the following situations:

  • The DPRSMF job runs at intervals during the day, each time an MVDB2/DC archive file is created and used as input. We recommend this method because it breaks the processing into smaller, incremental jobs throughout the day and produces chronologically ordered input records.
  • The DPRSMF job runs at intervals during the day by using input SMF files from one LPAR. Processing one file at a time does not require a sort.
  • You can use multiple files (SMF files or MVDB2/DC archive files) as input to DPRSMF, provided they are processed in chronological order. However, often these files are managed as Generation Data Groups (GDGs), and the IBM default order when specifying only a GDG base name is descending (newest first). In this situation, the input files must be ordered chronologically. BMC does not provide a tool to re-order the files, but such tools are available. If the files are from one CDC or LPAR and are in chronological order, the sort is not needed.

SORT=NO and SORT=COPY are functionally equivalent. SORT=NO uses written I/O and comparison routines written by BMC to read and process the input. SORT=COPY uses optimized SORT I/O and comparison routines. In any of the situations previously described either SORT=NO or SORT=COPY should improve processing time. The improvement will vary from environment to environment, and sufficient testing is needed to determine which option provides the best results. We recommend experimenting with various SORT parameters through the use of the DFSPARM (DFSORT) or $ORTPARM (Syncsort) DD statement.

important

Files from multiple LPARs, even within a sysplex, cannot be processed with SORT=NO or SORT=COPY. When data from multiple data sharing members is consolidated into one set of Db2 tables, the DPRSMF job must be run separately for each Db2 or LPAR. The DPRDSMF program verifies that the input is in chronological order when SORT=NO or SORT=COPY is specified.

SSID=

Defines the Db2 subsystem IDs that are desired when you want to select only certain Db2 subsystem IDs from the SMF data as it is read

If this operand is omitted, all subsystems are selected. As many as 10 entries may be entered, enclosed in parentheses and separated by commas.

TCCSID=

Specifies the target CCSID for translation of Unicode data

The default is CCSID 37 (US EBCDIC).

For example:

  • TCCSID=1140 (US EBCDIC with Euro)
  • TCCSID=273 (United Kingdom EBCDIC)
  • TCCSID=1143 (Germany/Austria EBCDIC with Euro)
  • TCCSID=1147 (France EBCDIC with Euro)
  • TCCSID=260 (Canada French 116 character EBCDIC)

z/OS Unicode Conversion Services must be properly configured with the appropriate translation tables (CCSID 1208-target). See the z/OS Support for Unicode: Using Conversion Services manual.

TIME=

Can be used to select records only from specific time ranges:

  • TIME=0800-1700 (first shift records only)
  • TIME=0800 (records from 8 A.M. to midnight)

TRANSLATEDBID=NO

(Optional)

Excludes the translation of the DBNAME for IFCID 142, 143, and 144 records when processing audit records

When TRANSLATEDBID=NO is specified, the database name is reported as the numeric equivalent to the DBID.

You can use TRANSLATEDBID=NO in conjunction with the following parameters:

  • TYPE2=AUDSUM
  • TYPE2=AUDDDL
  • TYPE2=AUDDML

Specifying this parameter can reduce CPU usage when processing audit records, if the SMF data contains a high number of IFCID 105 and 107 records.

TYPE=

Allows selection of only the following of SMF accounting and statistics data:

  • STAT (statistics data, including buffer statistics)
  • (BMC.AMIOPS.SPE2304)STATA (Statistics data Q3ST-QLES)
  • (BMC.AMIOPS.SPE2304)STATB (Statistics data  QLST-QXST)
  • DDFSTAT (DDF statistics data)
  • BUFSTAT (Detail buffer statistics data per pool)
  • DGPASTAT (Dynamic and group buffer pool attribute data)

You can enter these keywords in any combination, enclosed in parentheses and separated by commas. If this entry and TYPE2 are not coded, all accounting and statistics types are selected.

Allows selection of only the following of SMF accounting data:

  • ACCT (Accounting base data)
  • PKGACCT (Package accounting data)
  • DDFACCT (DDF accounting data)
  • BUFACCT (Detail buffer accounting data per pool)

 

TYPE2=

Allows selection of these audit data types:

  • AUDFAIL (authorization failures data)
  • AUDGRV (authorization control - GRANTs/REVOKEs data)
  • AUDDDL (DDL access data)
  • AUDDML (DML access data)
  • AUDDMB (DML at BIND)
  • AUDCHG (authorization ID change data)
  • AUDUTL (utility access data)
  • AUDSUM (summary data)

You can enter these keywords in any combination, enclosed in parentheses and separated by commas. Audit data types are not selected unless TYPE2 is specified.

TYPE3=

Allows selection of these statistics data types:

  • ASPCSTOR (storage address space table data)
  • SYSTSTOR (storage system table data)
  • AUDCMD (audit command table)
  • AUDATH (audit authorization table)
  • AUDTRC (audit traced table)
  • AUDFTB (fast index traversal block table)
  • AUDIFE (incompatible functions executed table)
  • AUDRLS (Remote Location Statistics table) 

You can enter these keywords in any combination, enclosed in parentheses and separated by commas.

TYPE4=

Allows selection of these accelerator or simulated bufferpool data types:

  • ACCA (accelerator data)
  • ACCS (accelerator summary data)
  • SYMB (simulated bufferpool data)

Timezone Adjustment Process

The following keywords refer to the timezone adjustment process. Supply the parameters in TZPARM only when local time is not equal to Greenwich mean time at your shop (a non-zero value is in SYS1.PARMLIB member CLOCKXX). SMF records for Db2 contain Greenwich mean time. TZPARM must contain the correct parameters for the DPRSMF program to convert Greenwich mean time to local time.

Statement

Description

TIMEZONE=

(Optional)

Defines the adjustment amount based on Coordinated Universal Time (UTC)

Sample parameters for the first SMF group are:

  • SMFID=SYSA
  • TZSTART=1991-04-01-02.00.00,TIMEZONE=W.05.00.00
  • TZSTART=1991-10-28-02.00.00,TIMEZONE=W.06.00.00

Sample parameters for the second SMF group are:

  • SMFID=SYSB
  • TZSTART=1991-04-01-01.00.00,TIMEZONE=W.05.00.00
  • TZSTART=1991-10-28-04.00.00,TIMEZONE=W.06.00.00
Examples
  • W.06.00.00 = UTC-06:00 (Central time - US and Canada)
  • E.02.00.00 = UTC+02:00 (Tel Aviv and Madrid)

Syntax rules are as follows:

  • No blank spaces are allowed between parameters within a line.
  • All preceding zeros are required for month, day, and time. For example, specify 04 for the month of April.
  • A maximum of 40 TZSTART/TIMEZONE entries are allowed.
  • Because of the difference between daylight savings and standard timezones, a minimum of two datetime entries is required for each SMF group entry.
  • If the SMFID is not specified, the datetime entries apply to all Db2 SMF records encountered.
  • When the SMFID is specified, the associated datetime entries apply only to the Db2 SMF records with the specified SMFID.

  • The timestamp of the datetime entry indicates the local time when the change of timezone occurs.

    Example

    TZSTART=1991-04-01-02.00.00,TIMEZONE=W.05.00.00

    indicates that the TIMEZONE value was changed to W.05.00.00 at local time 1991-04-01-02.00.00.

SMFID=

Defines the SMF ID for which the following timezone parameters apply

If the timezone parameters apply for all SMF IDs that might be processed, it is not necessary to include SMFID. Do not include some TZSTART/TIMEZONE combinations without a qualifying SMFID, and other TZSTART/TIMEZONE combinations after the qualifier.

TZSTART=

Defines the starting time after which the adjustment is made to any datetime fields

Its data field is in datetime format.

Where to go from here

See Step-2-DPRSMFA-Statistics-consolidation.

 

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