DMDBMERG utility
The DMDBMERG utility allows you to maintain Performance Advisor tables that are based on
Apptune
data.
DMDBMERG generates control statements for input into a LOAD utility and generates load records representing data for one or more Performance Advisor tables.
The input to DMDBMERG can be data that is:
Retrieved directly from a Data Collector
To retrieve directly from a Data Collector, use the DATASOURCE(COLLECTOR(….)) control statement.
- From archive files containing Apptune trace data
- To request that DMDBMERG retrieve the data from appropriate archives, use the //DOMBARC DD statement to designate an archive directory (also known as COPYDIR) that provides indexing. The indexing permits allocation of correct archives based on the tables and time intervals requested.
- To request that DMDBMERG retrieve the data from specific archives, use the //TRACEIN DD statement.
If you specify any of these sources without specifying //LOADIN DD, the generated rows are appended to the table or tables being updated.
For some tables, you must update existing rows. In these cases, DMDBMERG also requires as input a LOADIN file containing load records generated by the previous DMDBMERG execution for the same table(s). When LOADIN is present, the resulting LOAD control statements cause the referenced table(s) to be entirely replaced by the generated load records.
Typically, a job that executes DMDBMERG in one step would then execute BMC AMI Load for Db2 or a Db2 LOAD utility (such as DSNUTILB) as the next step to maintain Performance Advisor tables.
The following figure shows sample JCL and instructions to run the DMDBMERG utility.
// MSGCLASS=X, <== CHECK
// class="A" <== CHECK
//*
//*Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z
//*
//* DMDBMERG -- GENERATE DATA FOR PERFORMANCE ADVISOR TABLES
//*
//* DMDBMERG PRODUCES LOAD CONTROL STATEMENTS AND LOAD RECORDS, WHICH
//* SERVE AS INPUT TO A LOAD UTILITY TO POPULATE AND MAINTAIN
//* PERFORMANCE ADVISOR TABLES.
//*
//* THE SYNTAX OF THE CONTROL STATEMENTS AND THE USE OF OPTIONAL
//* DD STATEMENTS ARE ALSO DOCUMENTED IN THE SQL PERFORMANCE SOLUTION
//* GUIDE.
//*
//* NOTE - THE DATE EXAMPLES ARE IN USA FORMAT. MODIFY MM/DD/YY
//* TO MATCH THE FORMAT SPECIFIED IN THE GLOBAL OPTIONS PANEL.
//*
//*----> CHANGE: ?BMC-HLQ? = DATA COLLECTOR HIGH-LEVEL QUALIFIER
//*
//*Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z/Z
//*
//TABLES EXEC PGM=DMDBMERG
//*
//STEPLIB DD DISP=SHR,DSN=?BMC-HLQ?.BBLINK
//*
//SYSPRINT DD SYSOUT=* ** MESSAGES **
//*
//DOMCUST DD DISP=SHR,DSN=?BMC-HLQ?.BBCSTM ** CUSTOM DATASET
//DOMPROF DD DISP=SHR,DSN=?BMC-HLQ?.PROFILE ** PROFILE DATASET
//DOMHELP DD DISP=SHR,DSN=?BMC-HLQ?.BBHELP ** HELP DATASET
//DOMAUTH DD DISP=SHR,DSN=?BMC-HLQ?.SECURITY ** AUTHORITY DATASET
//DOMBARC DD DISP=SHR,DSN=?BMC-HLQ?.COPYDIR ** ARCHIVE DIRECTORY
//*
//*TRACEIN DD DISP=SHR,DSN=?TRACEDSN? ** OPTIONAL SPECIFICATION **
//* ** OF INPUT ARCHIVE TRACE **
//* ** DATA. BY DEFAULT THE **
//* ** DATA IS DYNAMICALLY **
//* ** LOCATED, USING DOMBARC. **
//*
//*
//*LOADIN DD DISP=SHR,DSN=?LOADDSN? ** OPTIONAL SPECIFICATION **
//* ** OF INPUT LOAD RECORDS **
//* ** PRODUCED AS DB2LOAD ON **
//* ** PREVIOUS RUN OF DMDBMERG. **
//* ** IF LOADIN IS SPECIFIED, **
//* ** DATA FROM THESE RECORDS **
//* ** IS MERGED WITH ARCHIVE **
//* ** INPUT AND THE RESULT IS **
//* ** USED TO REPLACE THE TABLE.**
//* ** OTHERWISE DATA FROM **
//* ** ARCHIVE INPUT IS ADDED TO **
//* ** THE TABLE. **
//*
//TRACEWRK DD DISP=(NEW,PASS), ** STAGED SORT OUTPUT. IF **
// UNIT=WORK, ** DD DUMMY, TABLE OUTPUT **
// SPACE=(CYL,(5,5),RLSE), ** WILL NOT BE PRODUCED **
// DCB=(RECFM=VBS,LRECL=32760,BLKSIZE=8192)
//*
//* THE DB2LOAD AND LOADCTL STATEMENTS BELOW SPECIFY 'GENERIC'
//* DESTINATIONS FOR LOAD DATA AND LOAD CONTROL OUTPUT.
//* YOU CAN ALSO SPECIFY DEDICATED DD STATEMENTS CORRESPONDING TO
//* INDIVIDUAL TABLES, IN ORDER TO SEGREGATE THE DATA BY TABLE.
//* THIS CAN BE HELPFUL FOR THE SUBSEQUENT LOAD UTILITY STEPS, WHERE
//* EACH LOAD UTILITY CAN REFERENCE A DEDICATED DD STATEMENT IN ORDER
//* TO RECEIVE ONLY THE DATA APPLYING TO THE TABLESPACE BEING LOADED.
//*
//* TABLE DB2LOAD LOADCTL
//* =============================== ======== ========
//* OBJ_STATISTICS LDOBSTAT CTOBSTAT
//* STMT_STATISTICS LDSTSTAT CTSTSTAT
//* STMT_SUMMARY LDSTSUMI CTSTSUMI
//* STMT_STATISTICS_OB LDSTSTOB CTSTSTOB
//* WKLD_STATISTICS LDWKSTAT CTWKSTAT
//* DAILY_OBJ_STATISTICS LDOBSTAD CTOBSTAD
//* DAILY_STMT_STATISTICS LDSTSTAD CTSTSTAD
//* DAILY_STMT_SUMMARY LDSTSUMD CTSTSUMD
//* DAILY_WKLD_STATISTICS LDWKSTAD CTWKSTAD
//* WEEKLY_OBJ_STATISTICS LDOBSTAW CTOBSTAW
//* WEEKLY_STMT_STATISTICS LDSTSTAW CTSTSTAW
//* WEEKLY_STMT_SUMMARY LDSTSUMW CTSTSUMW
//* WEEKLY_WKLD_STATISTICS LDWKSTAW CTWKSTAW
//* MONTHLY_OBJ_STATISTICS LDOBSTAM CTOBSTAM
//* MONTHLY_STMT_STATISTICS LDSTSTAM CTSTSTAM
//* MONTHLY_STMT_SUMMARY LDSTSUMM CTSTSUMM
//* MONTHLY_WKLD_STATISTICS LDWKSTAM CTWKSTAM
//* BASELINE_OBJ_STATISTICS LDOBSTAB CTOBSTAB
//* BASELINE_STMT_STATISTICS LDSTSTAB CTSTSTAB
//* BASELINE_STMT_SUMMARY LDSTSUMB CTSTSUMB
//* BASELINE_WKLD_STATISTICS LDWKSTAB CTWKSTAB
//* STMT_TEXT LDSTTEXT CTSTTEXT
//* STMT_ERRORS LDSTERRS CTSTERRS
//* STMT_EXCEPTIONS LDSTEXCP CTSTEXCP
//* STMT_EXCEPTIONS_HV LDSTEXHV CTSTEXHV
//* STMT_EXCEPTIONS_OB LDSTEXOB CTSTEXOB
//*
//DB2LOAD DD DISP=(NEW,PASS), ** DB2 LOAD-FORMAT DATA TO BE **
// UNIT=WORK,DSN=&&LOAD, ** PASSED AS INPUT TO A DB2 **
// SPACE=(CYL,(5,5),RLSE), ** LOAD UTILITY **
// DCB=(RECFM=VB,LRECL=32756,BLKSIZE=32760) **
//LOADCTL DD DISP=(NEW,PASS), ** DB2 LOAD UTILITY CONTROL **
// UNIT=WORK,DSN=&&CNTL, ** STATEMENTS **
// SPACE=(CYL,(5,5),RLSE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120)
//*
//* SORT WORK FILES - ADJUST SIZE RELATIVE TO THE AMOUNT OF INPUT DATA
//*
//SORTWK01 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
//SORTWK02 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
//SORTWK03 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
//SORTWK04 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
//*
//SYSOUT DD SYSOUT=* ** SORT MESSAGES **
//*
//* OPTIONAL DDNAME SRCTRACE TO GET DIAGNOSTIC INFORMATION ABOUT
//* INPUT DATA
//*
//*SRCTRACE DD SYSOUT=*
//*
//* OPTIONAL DDNAMES FOR VIRTUAL FILES TO OVERRIDE INSTALLATION
//* AND USER OPTIONS. ADJUST R0001VFL (SELECT FILE) BASED ON
//* NUMBER OF INPUT RECORDS, R0001VFG (GROUP FILE) BASED ON
//* NUMBER OF INPUT RECORDS AND DEGREE OF RECORD REDUCTION.
//*
//*R0001VFL DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
//*R0001VFG DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
//*
//* OPTIONAL DDNAME NGTLOAD INDICATES THAT THE DATA WILL BE
//* LOADED WITH THE NGT LOAD UTILITY. USE THIS DD STATEMENT
//* TO GENERATE LOAD CONTROL STATEMENTS THAT ARE COMPLIANT WITH
//* NGT LOAD.
//*NGTLOAD DD DUMMY
//*
//*-----------------------------------------------------------------*
//* SUPPORTED CONTROL STATEMENTS:
//*
//* INTERVAL( - GLOBAL (DEFAULT) INTERVAL
//* START(MM/DD/YY,HH:MM:SS) - INTERVAL STARTING DATE/TIME
//* END(MM/DD/YY,HH:MM:SS)) - INTERVAL ENDING DATE/TIME
//* -OR-
//* INTERVAL( - GLOBAL (DEFAULT) INTERVAL
//* START(MM/DD/YY,HH:MM:SS) - INTERVAL STARTING DATE/TIME
//* FOR( DAYS,HH:MM:SS)) - INTERVAL DURATION DAYS/TIME
//* DATE/TIME CAN BE RELATIVE (E.G. -1 FOR YESTERDAY)
//* INTERVAL( START(-1) FOR(1) ) SPECIFIES ALL DAY YESTERDAY
//*
//*-------------------------------------------------------------------*
//* DATASOURCE(COLLECTOR(XXXX)) - SPECIFIES THAT DATA BE RETRIEVED
//* DIRECTLY FROM DBC SUBSYSTEM XXXX
//* RUNNING ON THE LOCAL SYSTEM.
//* (OVERRIDES TRACEIN, DOMBARC)
//*-------------------------------------------------------------------*
//* APPPROF(PROFILE.OWNER) - APPLICATION PROFILE USED WHEN
//* GROUP FILTERING IS SPECIFIED
//*-------------------------------------------------------------------*
//* QUALIFIER( - GLOBAL (DEFAULT) QUALIFIERS FOR RESTRICTING
//* DB2(XXXX) - ' BY DB2 SSID NAMED XXXX
//* ,DSGROUP( ) - ' BY DS GROUP
//* ,PLAN( ) - ' BY PLAN
//* ,COLL( ) - ' BY COLLECTION ID
//* ,PROGRAM( ) - ' BY DBRM/PACKAGE ID
//* ,CONN( ) - ' BY CONNECT-IDS
//* ,OPER( ) - ' BY OPER ID
//* ,CORR( ) - ' BY CORRELATION ID
//* ,APGRP( ) - ' BY APPLICATION PROFILE-APPTUNE ONLY
//* ,CALLTYPE( ) - ' BY CALL TYPE (STATIC, DYNAMIC)
//* ,STMTTYPE( ) - ' BY STMT TYPE (OPEN, SELECT, ETC.)
//* ,TEXTHASH( ) - ' BY STMT TEXT KEY/HASH VALUE
//* ,BP( ) - ' BY BUFFER POOL
//* ,DBNAME( ) - ' BY DATABASE NAME
//* ,PAGESET( ) - ' BY PAGESET NAME
//* ,TBCREATOR( ) - ' BY ASSOCIATED TABLE CREATOR
//* ,TBNAME( ) - ' BY ASSOCIATED TABLE NAME
//* ) - END OF QUALIFIER KEYWORDS
//*-------------------------------------------------------------------*
//* PURGE_DAYS(DAYS) - APPLIES TO LOADIN DATA,
//* PURGE IF OLDER THAN N DAYS
//*-------------------------------------------------------------------*
//* FIRST_DAY(SUNDAY) - SPECIFIES THE FIRST DAY OF THE WEEK FOR
//* WEEKLY TABLES--SPECIFY SUNDAY, MONDAY,
//* TUESDAY, WEDNESDAY, THURSDAY, FRIDAY,
//* OR SATURDAY; DEFAULT IS SUNDAY
//*-------------------------------------------------------------------*
//* TBCREATOR(XXXXXXXX) - SPECIFIES THE TABLE OWNER FOR GENERATED
//* LOAD UTILITY CONTROL STATEMENTS;
//* DEFAULT IS BMCSFTWR
//*-------------------------------------------------------------------*
//* CCSID(NNNN) - SPECIFIES THE EBCDIC CHARACTER SET TO BE
//* USED FOR CONVERSION FROM/TO UNICODE;
//* DEFAULT IS 0037
//*-------------------------------------------------------------------*
//* DECIMAL(X) - SPECIFIES U FOR U.S. FORMAT, PERIOD DECIMAL
//* POINTS IN OUTPUT, E FOR EUROPEAN FORMAT,
//* COMMA DECIMAL POINTS (OVERRIDES SETTING IN
//* PROFILE)
//*-------------------------------------------------------------------*
//* NODATA(N) - SPECIFIES AN INTEGER 0-8 TO BE USED AS
//* RETURN CODE WHEN NO DATA IS SELECTED
//*-------------------------------------------------------------------*
//* FILSZ(N) - SPECIFIES AN INTEGER 0-99999999 AS AN
//* ESTIMATE OF INPUT RECORDS FOR SORT PROGRAM
//*-------------------------------------------------------------------*
//* AVGRLEN(N) - SPECIFIES AN INTEGER 0-99999 AS AN ESTIMATE
//* OF AVERAGE RECORD SIZE FOR SORT PROGRAM
//*-------------------------------------------------------------------*
//* TABLE(NAME(XXXXXXXX,YYYYYYYY,...)) - SPECIFIES TABLE(S) FOR WHICH
//* DATA WILL BE GENERATED
//* SUPPORTED TABLES:
//* OBJ_STATISTICS OBJECT STATISTICS BY INTERVAL
//* STMT_STATISTICS STATEMENT STATISTICS BY INTERVAL
//* STMT_SUMMARY STATEMENT SUMMARY BY INTVL
//* STMT_STATISTICS_OB OBJ STATS PER STATEMENT BY INTVL
//* WKLD_STATISTICS WORKLOAD STATISTICS BY INTERVAL
//* DAILY_OBJ_STATISTICS OBJECT STATISTICS BY DAY
//* DAILY_STMT_STATISTICS STATEMENT STATISTICS BY DAY
//* DAILY_STMT_SUMMARY STATEMENT SUMMARY BY DAY
//* DAILY_WKLD_STATISTICS WORKLOAD STATISTICS BY DAY
//* WEEKLY_OBJ_STATISTICS OBJECT STATISTICS BY WEEK
//* WEEKLY_STMT_STATISTICS STATEMENT STATISTICS BY WEEK
//* WEEKLY_STMT_SUMMARY STATEMENT SUMMARY BY WEEK
//* WEEKLY_WKLD_STATISTICS WORKLOAD STATISTICS BY WEEK
//* MONTHLY_OBJ_STATISTICS OBJECT STATISTICS BY MONTH
//* MONTHLY_STMT_STATISTICS STATEMENT STATISTICS BY MONTH
//* MONTHLY_STMT_SUMMARY STATEMENT SUMMARY BY MONTH
//* MONTHLY_WKLD_STATISTICS WORKLOAD STATISTICS BY MONTH
//* BASELINE_OBJ_STATISTICS OBJECT STATISTICS BASELINE
//* BASELINE_STMT_STATISTICS STATEMENT STATISTICS BASELINE
//* BASELINE_STMT_SUMMARY STATEMENT SUMMARY BASELINE
//* BASELINE_WKLD_STATISTICS WORKLOAD STATISTICS BASELINE
//* STMT_TEXT SQL TEXT
//* STMT_ERRORS STATEMENT ERRORS
//* STMT_EXCEPTIONS STATEMENT EXCEPTIONS
//* STMT_EXCEPTIONS_HV HOST VARIABLES FROM EXCEPTIONS
//* STMT_EXCEPTIONS_OB OBJECT STATISTICS FROM EXCEPTIONS
//*
//********************************************************************/
//SYSIN DD *
INTERVAL( START(-1) FOR(1) )
PURGE_DAYS(30)
TBCREATOR(BMCSFTWR)
TABLE (NAME( OBJ_STATISTICS, -
STMT_STATISTICS, -
WKLD_STATISTICS ) )
//
DMDBMERG data sets
The following table describes the data sets that DMDBMERG processes.
DDNAME | Description | DCB Attributes |
|---|---|---|
CSVOUT (optional) | Comma-separated values (CSV) output is created. |
|
DB2LOAD | Db2 load format data is written to this data set, which can be passed to a load utility in another step to load the data. 1 |
|
DOMAUTH | The VSAM data set containing the user profile security values | Not applicable |
DOMBARC | The VSAM data set that stores the names of the archived trace data sets for use by the archive directory | Not applicable |
DOMCUST | The sequential data set containing the report customization elements | Not applicable |
DOMHELP | The VSAM data set that contains help members | Not applicable |
DOMPROF | The VSAM data set containing the user profiles | Not applicable |
LOADCTL | Output LOAD utility control statements for input as SYSIN to a subsequent LOAD utility step. 1 |
|
NGTLOAD (optional) | Apptune generates all load control statements in BMC AMI Load compliant syntax when you specify DUMMY for this data set. | Not applicable |
PADBCNTL | A concatenation of the distributed hlq.llqSAMP and the PADB Starter library. These data sets are used to determine which tables and columns to maintain. Optional DD statement: If not specified,all specified tables and columns are processed. | Not applicable |
PADBTEP2 | The SYSPRINT output from the previous DSNTEP2 execution. If partitioned tables exist, the output contains the PADB partition counts. Optional DD statement: If not specified, the MAXPART control statement can be used to indicate how many partitions exist in the table being processed.
| Not applicable |
R0001VFG (optional) | Data set used to hold group records for each table if there are too many records to fit in the internal buffers This value can be adjusted according to the number of selected records, and the degree to which the data is reduced. For example, a table summarizing by hour requires fewer group records than a table summarizing by day. 2 | Not applicable |
R0001VFL (optional) | Data set used to hold selected records for each table if there are too many records to fit in the internal buffers This value can be adjusted according to the number of selected records. 2 | Not applicable |
SORTWKnn (optional) | Interim data sets for the SORT utility working storage This data set might be required by a SORT program if large amounts of trace data are being processed. Refer to your site’s SORT program documentation for details about this DD statement's specification. | Not applicable |
SRCTRACE (optional) | Data set containing a listing of all records found in the input archive files A single line is generated for each valid record, including an indication of whether the record is used to generate table data. |
|
STEPLIB | Data Collector load library | Not applicable |
SYSIN | DMDBMERG control statements | Not applicable |
SYSOUT | SORT messages | Not applicable |
SYSPRINT | DMDBMERG messages and statistics |
|
TRACEIN (optional) | Trace input data sets to override automatic archive selection The input data sets can be any of the following data sets: archived data sets or EXPORT data sets. You must either include the TRACEIN data set or specify the INTERVAL control statement. By default, DOMBARC dynamically locates the data. | Not applicable |
TRACEWRK | Interim data set for holding the records that were selected and sorted for table data The amount of required space increases as the amount of selected data increases. Data is selected if any table requires the record, based on IFCID type, date and time, and qualifiers. |
|
1 You have the option to use dedicated DD statements with this data set. This increases efficiency. For more information see Optional dedicated DD statements for DB2LOAD and LOADCTL.
2 If not specified, the file is dynamically allocated by using the space allocation that is specified in your User Profile and the unit that is specified in your installation options.
Default values are generally sufficient, but can be customized in the Data Collector administration panels. To size the data set:
a. Multiply 32 KB (maximum size of each record) with the number of records to calculate a total size of the input records.
b. Adjust this figure to the track size and approximate sizes of the initial allocation.
Optional dedicated DD statements for DB2LOAD and LOADCTL
You have the option to use dedicated DD statements with the DB2LOAD and LOADCTL DD statements as described. These additional DD statements are dedicated to a particular table's load data and control statements. Using these dedicated DD statements, you can increase the efficiency of subsequent LOAD steps by running a separate step to load each table. If you use dedicated DD statements for all tables being generated, set DB2LOAD and LOADCTL statements to DUMMY.
The additional DD statements supported appear in the following table:
Table name | Dedicated DB2LOAD | Dedicated LOADCTL |
|---|---|---|
STMT_STATISTICS | LDSTSTAT | CTSTSTAT |
OBJ_STATISTICS | LDOBSTAT | CTOBSTAT |
WKLD_STATISTICS | LDWKSTAT | CTWKSTAT |
STMT_STATISTICS_OB | LDSTSTOB | CTSTSTOB |
DAILY_STMT_STATISTICS | LDSTSTAD | CTSTSTAD |
DAILY_OBJ_STATISTICS | LDOBSTAD | CTOBSTAD |
DAILY_WKLD_STATISTICS | LDWKSTAD | CTWKSTAD |
WEEKLY_STMT_STATISTICS | LDSTSTAW | CTSTSTAW |
WEEKLY_OBJ_STATISTICS | LDOBSTAW | CTOBSTAW |
WEEKLY_WKLD_STATISTICS | LDWKSTAW | CTWKSTAW |
MONTHLY_STMT_STATISTICS | LDSTSTAM | CTSTSTAM |
MONTHLY_OBJ_STATISTICS | LDOBSTAM | CTOBSTAM |
MONTHLY_WKLD_STATISTICS | LDWKSTAM | CTWKSTAM |
BASELINE_STMT_STATISTICS | LDSTSTAB | CTSTSTAB |
BASELINE_OBJ_STATISTICS | LDOBSTAB | CTOBSTAB |
BASELINE_WKLD_STATISTICS | LDWKSTAB | CTWKSTAB |
STMT_ERRORS | LDSTERRS | CTSTERRS |
STMT_EXCEPTIONS | LDSTEXCP | CTSTEXCP |
STMT_EXCEPTIONS_HV | LDSTEXHV | CTSTEXHV |
STMT_EXCEPTIONS_OB | LDSTEXOB | CTSTEXOB |
STMT_TEXT | LDSTTEXT | CTSTTEXT |
Control statements
The control statements listed in the following table can be used with the DMDBMERG utility.
Control Statement | Use this Control Statement to |
|---|---|
APPPROF (profile.owner) | Specify the application profile for group reporting. |
AVGRLEN | Specify an integer from 0–99999999 to estimate the records' expected average length for a SORT program. 1 |
CCSID (nnnn) | Specify the EBCDIC character set to be used for conversion to or from Unicode. |
COLUMN_DELIMITER (,) | Specify the column delimiter when running DMDBMERG and creating CSV output. The default value is a comma. |
DECIMAL (x) | Specify the decimal format in output, overriding profile settings:
|
DATASOURCE | Specify the source of data for batch reporting. DATASOURCE can be specified only once in a job stream. If DATASOURCE is specified, it must appear before any REPORT or QUALIFIER statement. You must also specify either COLLECTOR or ARCHIVE_DD. You cannot specify both. If DATASOURCE is omitted, the TRACEIN data set is used as the source. If there is no TRACEIN DD in the JCL, the Archive Directory is searched for archived trace data sets based on the interval selection that you specified as parameters for the batch reports. The TRACEIN DD is dynamically allocated with the data sets that are retrieved from the Archive directory. |
FILSZ (n) | Specify an integer from 0–99999 to estimate the number of records input into the utility via archives and the LOADIN DD statement for a SORT program. 1 |
FIRST_DAY (Sunday) | Specifiy the first day of the week for weekly tables. |
GROUPBY LOGICAL|DSGROUP | Indicate whether data is grouped by logical Db2 or data sharing group instead of by SSID. |
IGNORE(keyName[,keyName…]) | Use this control statement to reduce the number of rows in your table or tables by ignoring keys. Ignoring keys causes different key values to be grouped into one row. For a list of valid key names, see the Valid key names for IGNORE control statement table. |
INTERVAL | Specify a time interval for the data to be included in tables. For more information, see INTERVAL-statement. You must either include the TRACEIN data set or specify the INTERVAL control statement. |
LOADIN_XINTS YES|NO | Use this statement only when converting a non-partitioned table to partitioned. YES enables loading LOADIN intervals that have no interval match in the input archive data. |
MAXPART | Specify the number of partitions in the table being processed. If you specify this value, DMDBMERG assumes that the value is correct. If the PADBTEP2 DD statement exists, DSNTEP2 output overrides the MAXPART value. |
NODATA (n) | Specify the return code that the DMDBMERG utility generates in various situations where no data is generated. For information about default return codes when no data is generated, see Default return code tables. |
PURGE_DAYS (nn) | Purge data older than the specified number of days. |
QUALIFIER (type) | Use qualifier values to restrict the data that is selected for tables. For more information about valid qualifiers, see the Qualifiers Table. |
TABLE | Produce tables. |
TBCREATOR (name) | Specify the table owner for generated load utility control statements. |
1 Use these control statements if you can accurately estimate (based on prior runs, for example) the average length and number of records. The external SORT program uses your estimates to optimize the SORT's efficiency. These control statements provide control over the external SORT program's work file allocation.
These control statements are optional, but FILSZ must be specified prior to AVGRLEN if AVGRLEN is specified.
Valid key names for IGNORE control statement
The following table displays Valid key names for IGNORE control statement.
Tables | Valid key names |
|---|---|
OBJ_STATISTICS DAILY_OBJ_STATISTICS WEEKLY_OBJ_STATISTICS MONTHLY_OBJ_STATISTICS BASELINE_OBJ_STATISTICS | DBNAME PSNAME TBCREATOR |
STMT_STATISTICS DAILY_STMT_STATISTICS WEEKLY_STMT_STATISTICS MONTHLY_STMT_STATISTICS BASELINE_STMT_STATISTICS | AUTHID CLNTAP CLNTCT CLNTUS COLLID CONNID CORRID ENDTNAME ENDUNAME ENDWNAME PLAN PROGRAM RLOCATION STMTTYNO TEXTHASH |
STMT_SUMMARY DAILY_STMT_SUMMARY WEEKLY_STMT_SUMMARY MONTHLY_STMT_SUMMARY BASELINE_STMT_SUMMARY | COLLID PLAN PROGRAM RLOCATION STMTTYNO TEXTHASH |
STMT_ERRORS | AUTHID CLNTAP CLNTUS COLLID PROGRAM RLOCATION TEXTHASH |
STMT_EXCEPTIONS | AUTHID CLNTAP CLNTCT CLNTUS COLLID ENDTNAME ENDUNAME ENDWNAME PROGRAM RLOCATION TEXTHASH |
STMT_EXCEPTIONS_OB | DBNAME PSNAME |
STMT_OBJ DAILY_STMT_OBJ WEEKLY_STMT_OBJ MONTHLY_STMT_OBJ BASELINE_STMT_OBJ STMT_STATISTICS_OBJ | AUTHID CLNTAP CLNTCT CLNTUS COLLID CONNID CORRID DBNAME ENDTNAME ENDUNAME ENDWNAME PLAN PROGRAM PSNAME RLOCATION TBCREATOR TEXTHASH |
WKLD_STATISTICS DAILY_WKLD_STATISTICS WEEKLY_WKLD_STATISTICS MONTHLY_WKLD_STATISTICS BASELINE_WKLD_STATISTICS | AUTHID CLNTAP CLNTCT CLNTUS COLLID CONNID CORRID ENDTNAME ENDUNAME ENDWNAME PLAN PROGRAM RLOCATION |
Qualifiers Table
The following table displays a list of tables and the qualifiers that can be specified on the QUALIFIER statements.
Table(s) | Valid qualifiers |
|---|---|
OBJ_STATISTICS DAILY_OBJ_STATISTICS WEEKLY_OBJ_STATISTICS MONTHLY_OBJ_STATISTCIS BASELINE_OBJ_STATISTICS | BP DB2 DSGROUP DBNAME LOGICALDB2 PAGESET TBCREATOR TBNAME |
STMT_STATISTICS DAILY_STMT_STATISTICS WEEKLY_STMT_STATISTICS MONTHLY_STMT_STATISTICS BASELINE_STMT_STATISTICS STMT_SUMMARY DAILY_STMT_SUMMARY WEEKLY_STMT_SUMMARY MONTHLY_STMT_SUMMARY BASELINE_STMT_SUMMARY STMT_ERRORS STMT_EXCEPTIONS STMT_EXCEPTIONS_HV STMT_EXCEPTIONS_OB | APGRP AUTHID CALLTYPE CLNTAP CLNTCT CLNTUS COLLECTION CONNECTION CORRNAME DB2 DSGROUP OPERATOR LOGICALDB2 PLAN PROGRAM STMTTYPE TEXTHASH |
STMT_STATISTICS_OB | APGRP AUTHID BP CALLTYPE CLNTAP CLNTCT CLNTUS COLLECTION CONNECTION CORRNAME DB2 DSGROUP DBNAME LOGICALDB2 OPERATOR PAGESET PLAN PROGRAM STMTTYPE TBCREATOR TBNAME TEXTHASH |
WKLD_STATISTICS DAILY_WKLD_STATISTICS WEEKLY_WKLD_STATISTICS MONTHLY_WKLD_STATISTICS BASELINE_WKLD_STATISTICS | APGRP AUTHID CLNTAP CLNTCT CLNTUS COLLECTION CONNECTION CORRNAME DB2 DSGROUP LOGICALDB2 OPERATOR PLAN PROGRAM |
Default return code tables
The tables in this section display the return code that the DMDBMERG utility generates under the conditions specified when no data is selected for input or generated for output.
The NODATA control statement can override those values designated with one asterisk (*). In the designated cases, the NODATA control statement generates alternate values for the DMDBMERG utility's default return codes. The syntax for the NODATA control statement is NODATA (n), where n is 0–8 inclusive.
The following table lists the default return codes that are generated when the LOADIN DD statement is coded.
Condition | Message | Code |
|---|---|---|
Error in allocation of selected archive | BMC24711 | 8 |
No archives selected | BMC24701 | 8* |
No data selected from archives for any table | NO RECORDS FOUND | 8* |
Data generated for at least one, but not all, DB2LOAD or LDxxxxxx DD statements | BMC24309 | 8* |
The following table lists the default return codes that are generated when no LOADIN DD statement is coded.
Condition | Message | Code |
|---|---|---|
Error in allocation of selected archive | BMC24711 | 8 |
No archives selected | BMC24701 | 4* |
No data selected from archives for any table | NO RECORDS FOUND | 4* |
Data generated for at least one, but not all, DB2LOAD or LDxxxxxx DD statements | None | 0 |
This section contains the following topics:
Related topic