Parameters of the DSNADMSB program

Parameter descriptions

table-creator

The qualifier for the table, table list table (LIST_TABLE-list-table), or PLAN_TABLE for which DSNADMSB gathers information. The maximum length of table-creator is 128 bytes. table-creator cannot be null.

table-name

One of the following values:

• The name of a single user object that has table-creator as its qualifier. The name must identify one of the following types of objects:
  • Base table
  • View
  • Alias
  • Clone table
  • Created temporary table
  • History table
  • Materialized query table
  • Implicitly created table for an XML column
• LIST_TABLE-list-table

  list-table identifies a Db2 table on the local subsystem that has table-creator as the qualifier. The table must contain two columns, named CREATOR and TABLE. Each row of list-table identifies a table or view for which DSNADMSB is to gather information. The types of tables that are specified in LIST_TABLE-list-table are the same as the types of tables that can be specified by table-name.

• PLAN_TABLE

  Specifies that DSNADMSB uses table-creator.PLAN_TABLE and the plan-info parameter to determine the tables about which information is gathered. The referenced PLAN_TABLE must be a base table.

  Recommendation: When you specify PLAN_TABLE, ensure that the view reference table, DSN_VIEWREF_TABLE, exists before you execute EXPLAIN. The existence of DSN_VIEWREF_TABLE is especially important if the queries about which you are collecting information contain views. If DSN_VIEWREF_TABLE exists and has been populated by EXPLAIN, you can use DSNADMSB to collect view data that is specific to the queries of interest, instead of collecting data on all view dependencies. The DSN_VIEWREF_TABLE must have the same qualifier as the plan table. This qualifier is the first DSNADMSB input parameter, table-creator.

The maximum length of table-name is 128 bytes.

If the table name is a delimited identifier, do not include the delimiters in table-name.

catalog-creator

The qualifier for the Db2 catalog, or DEFAULT, which indicates that the catalog qualifier is SYSIBM.

The maximum length of catalog-creator is 128 bytes.

plan-info

If the table that is specified by table-name is PLAN_TABLE, plan-info is a value of the following form, or NONE:

program-begin-queryno-end-queryno

The meanings of these variables are:

program

A value from the PROGNAME column of the PLAN_TABLE, or a pattern that specifies a set of PROGNAME values in the PLAN_TABLE. Any pattern that is valid in a LIKE predicate can be specified. program represents programs or packages for which DSNADMSB collects PLAN_TABLE information.

begin-queryno

A value from the QUERYNO column of the PLAN_TABLE. The value represents the lowest statement number for which DSNADMSB collects PLAN_TABLE information.

end-queryno

A value from the QUERYNO column of the PLAN_TABLE. The value represents the highest statement number for which DSNADMSB collects PLAN_TABLE information.

If table-name does not specify PLAN_TABLE, the plan-info value must be NONE.

The maximum length of plan-info is 150 bytes.

collect-ddl

Specifies whether DSNADMSB returns the data definition language statements that were used to create the input tables or views that are specified by table-name, and data definition language statements for related objects. The length of collect-ddl is 1 byte.

When the input table is not PLAN_TABLE, possible values are:

N

Do not return the data definition language statements that created the objects.

Y

Return the data definition language statements that created:

• The input objects
• Foreign keys that reference the input objects
• Views on the input objects

0

Return the data definition language statements that created:

• The input objects. Statements that create views on the input objects or foreign keys that reference the input objects are not collected.

1

Return the data definition language statements that created:

• The input objects
• Views on the input objects

2

Return the data definition language statements that created:

• The input objects
• Foreign keys that reference the input objects

3

Return the data definition language statements that created:

• The input objects
• Foreign keys that reference the input objects
• Views on the input objects
• Other objects that depend on the input objects, such as materialized query tables

This option can result in a large amount of data. Do not use this option for data collection that is requested by IBM Support.

4

Return the same data definition language statements that are returned when option Y is specified.

When the input table is PLAN_TABLE, possible values are:

N

Do not return the data definition language statements that created the objects.

Y

Return the data definition language statements that created:

• The objects that are identified by plan-info
• Foreign keys that reference the objects that are identified by plan-info
• If DSN_VIEWREF_TABLE exists and is populated, views or materialized query tables that are used to process the queries that are identified by plan-info.
• If DSN_VIEWREF_TABLE does not exist, views on objects that are identified by plan-info. DSNADMSB requires more time to gather data if DSN_VIEWREF_TABLE is not available than if DSN_VIEWREF_TABLE is available.

0

Return the data definition language statements that created:

• The objects that are identified by plan-info only. Statements that create views on the objects or foreign keys that reference the objects that are identified by plan-info are not collected.

1

Return the data definition language statements that created:

• The objects that are identified by plan-info
• If DSN_VIEWREF_TABLE exists and is populated, views or materialized query tables that are used to process the queries that are identified by plan-info.
• If DSN_VIEWREF_TABLE does not exist, views on objects that are identified by plan-info. DSNADMSB requires more time to gather data if DSN_VIEWREF_TABLE is not available than if DSN_VIEWREF_TABLE is available.

2

Return the data definition language statements that created:

• Foreign keys that reference the objects that are identified by plan-info

3

Return the data definition language statements that created:

• The objects that are identified by plan-info
• Foreign keys that reference the objects that are identified by plan-info
• Views on objects that are identified by plan-info
• Other objects that depend on the objects that are identified by plan-info, such as materialized query tables

This option can result in a large amount of data. Do no specify this option for problem analysis by IBM Software Support unless they direct you to do so.

4

Return the data definition language statements that created:

• The objects that are identified by plan-info
• Foreign keys that reference the objects that are identified by plan-info
• Views on objects that are identified by plan-info

This option does not use information from DSN_VIEWREF_TABLE. DSNADMSB requires more time to gather data if you choose option 4 than if you choose option Y, and DSN_VIEWREF_TABLE is available.

collect-stats

Specifies whether DSNADMSB returns statistical information from Db2 catalog tables about the tables that are specified by table-name and related objects. Possible values are:

Y

Return statistical information about tables from the Db2 catalog.

N

Do not return statistical information about tables from the Db2 catalog.

Important: Setting a collect-stats value of Y might generate large amounts of data. Set collect-stats to N unless you specifically need statistical information from Db2 catalog tables.

The length of collect-stats is 1 byte.

collect-colstats

Specifies whether DSNADMSB returns statistical information from Db2 catalog tables about the columns in tables that are specified by table-name and related objects. Possible values are:

Y

Return statistical information about columns from the Db2 catalog.

N

Do not return statistical information about columns from the Db2 catalog.

Important: Setting a collect-colstats value of Y might generate large amounts of data. Set collect-colstats to N unless you specifically need statistical information from Db2 catalog tables.

The length of collect-colstats is 1 byte.

edit-ddl

Specifies whether DSNADMSB modifies the data definition language statements that it generates so that the data definition language statements can be more easily executed by IBM Support. Examples of changes that DSNADMSB makes are:

• Setting the STOGROUP to SYSDEFLT
• Setting PRIQTY and SECQTY to their minimum values
• Setting DEFINE to NO
• Commenting out foreign key definitions

Possible values are:

Y

Edit the data definition language statements that DSNADMSB produces.

Y is the recommended value if you do not send data to populate the tables that are specified by table-name to IBM Support.

N

Do not edit the data definition language statements that DSNADMSB produces.

The length of edit-ddl is 1 byte.

edit-version-mode

Specifies that the output format for a different version and mode of Db2 for z/OS® than is currently running when collecting information. In most cases, specify NONE, so that the output is not converted to another format. Otherwise, you must specify the version number and the mode.

Valid values for mode are C for conversion mode and N for new-function mode. For example, if you want to generate the output in the format used by Db2 11 new-function mode, specify 11-N.

This is an input parameter of type CHAR(4) and cannot be null.

partition-rotation

Specifies whether DSNADMSB checks the amount of rotation that a partitioned table has undergone. DSNADMSB determines the number of partition rotations that are needed to synchronize logical partitions with physical partitions. Possible values are:

Y

Check for the amount of partition rotation.

Y is valid only for partitioned tables.

N

Do not check for the amount of partition rotation.

The length of partition-rotation is 1 byte.

output-method

Specifies the type of destination for DSNADMSB output. Possible values are:

R

Output is returned in the job stream. In most cases, R should be used.

Q

Output is returned in data sets that DSNADMSB creates. You supply the qualifier name and primary and secondary allocation quantities for those data sets in the output-info parameter.

Important: The data sets are temporary data sets that are created on scratch packs. Depending on how the z/OS system is configured, the data sets might be deleted after a short time.

N

Output is returned in existing data sets that are allocated by the WLM environment startup procedure. You supply the data set names in the output-info parameter.

D

Output is returned in data sets that DSNADMSB creates. You supply the data set names and primary and secondary allocation quantities for those data sets in the output-info parameter.

The length of output-method is 1 byte.

output-info

Specifies output data set information. The information depends on the value of output-method.

output-method value	output-info value
R	NONE
Q	A string of this form: qualifier-primary-secondary qualifier A string of up to 29 bytes, or DEFAULT. DSNADMSB appends a string that defines the type of output data set. If qualifier is not DEFAULT, qualifier must conform to the rules for z/OS data set names. If qualifier is DEFAULT, DSNADMSB generates a qualifier value of the following form: PMnnnnn.Dyymmdd.Thhmmss nnnnn is the PMR number. yymmdd and hhmmss are the date and time when DSNADMSB ran. The strings that DSNADMSB appends to qualifier are: .DDL for the data set that contains data definition statements for user tables or the PLAN_TABLE .SQL for the data set that contains SQL statements that populate PLAN_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS .STATS for the data set that contains SQL statements that populate Db2 catalog tables with statistical information about tables .COLST for the data set that contains SQL statements that populate Db2 catalog tables with statistical information about columns .EXPL for the data set that contains output from tables PLAN_TABLE, DSN_PREDICAT_TABLE, DSN_DETCOST_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS .PARM for the data set that contains subsystem parameter settings primary The primary allocation quantity for the output data sets, or DEFLT. If you specify DEFLT, the primary allocation quantity is 200 tracks. secondary The secondary allocation quantity for the output data sets, or DEFLT. If you specify DEFLT, the secondary allocation quantity is 200 tracks.
N	A string of this form: ddldd-sqldd-statsdd-colstdd-colstdd-parmdd Each part of the string is the DD name for a data set that is defined in the WLM startup procedure for the WLM environment in which the ADMIN_INFO_SQL stored procedure runs. The DD names are: ddldd for the data set that contains data definition statements for user tables or the PLAN_TABLE sqldd for the data set that contains SQL statements that populate PLAN_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS statsdd for the data set that contains SQL statements that populate Db2 catalog tables with statistical information about tables colstdd for the data set that contains SQL statements that populate Db2 catalog tables with statistical information about columns expldd for the data set that contains output from tables PLAN_TABLE, DSN_PREDICAT_TABLE, DSN_DETCOST_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS parmdd for the data set that contains subsystem parameter settings
D	Records of this form: DDL;dsname;volser;allocunits;primary;secondary- +SQL;dsname;volser;allocunits;primary;secondary- +STATS;dsname;volser;allocunits;primary;secondary- +COLST;dsname;volser;allocunits;primary;secondary- +EXPL;dsname;volser;allocunits;primary;secondary- +PARM;dsname;volser;allocunits;primary;secondary 1 The plus (+) signs are not part of the output-info value. You need to put a plus (+) sign in column 1 when you continue the output-info value in a new record.

Note:

1. The meanings of the items in the string are:

   DDL, SQL, STATS, COLST, EXPL, PARM

   Identifies the type of output that DSNADMSB puts in the data set:

   • DDL for the data set that contains data definition statements for user tables or the PLAN_TABLE
   • SQL for the data set that contains SQL statements that populate PLAN_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS
   • STATS for the data set that contains SQL statements that populate Db2 catalog tables with statistical information about tables
   • COLST for the data set that contains SQL statements that populate Db2 catalog tables with statistical information about columns
   • EXPL for the data set that contains output from tables PLAN_TABLE, DSN_PREDICAT_TABLE, DSN_DETCOST_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS
   • PARM for the data set that contains subsystem parameter settings

   dsname

   The fully qualified name of the data set that DSNADMSB allocates, or DEFAULT.

   If you specify DEFAULT and pmr-info specifies a support case ID, the data set names are created with the following format, with the case ID numbers split into two parts:

   Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.COLST
   Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.DDL
   Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.EXPL
   Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.PARM
   Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.SQL
   Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.STATS

   For example, for support case 123456789, the data sets are generated with names in the following format: T123456.S6789.D190903.T170318.VXX.type

   If you specify DEFAULT and pmr-info specifies a PMR number, the data set names are created with the following format:

   PMnnnnn.Dyymmdd.Thhmmss.Vn.COLST
   PMnnnnn.Dyymmdd.Thhmmss.Vn.DDL
   PMnnnnn.Dyymmdd.Thhmmss.Vn.EXPL
   PMnnnnn.Dyymmdd.Thhmmss.Vn.PARM
   PMnnnnn.Dyymmdd.Thhmmss.Vn.SQL
   PMnnnnn.Dyymmdd.Thhmmss.Vn.STATS

   nnnnn is the PMR number, without the branch code or country code.

   yymmdd is the date and hhmmss is the time when DSNADMSB ran.

   n is a release indicator.

   For example, for PMR 12345, the data sets are generated with names in the following format: PMR12345.D190903.T170318.VXX.type

   

   volser

   The volume serial on which the data set is created.

   allocunits

   Valid values are TRK or CYL.

   primary

   The primary allocation quantity for the output data set.

   secondary

   The secondary allocation quantity for the output data set.

The maximum length of output-info is 1024 bytes.

pmr-info

Identifies the support case or the number of the PMR for which the data is being collected in one of the following forms:

• The 9-digit support case ID number.
• The number of the PMR in number.branch-code.country-code format