DSNZPARM Data set and Database Parameters
This panel shows information about the data set and database-related installation parameters in the DSNZPARM module. It shows the name of the DSNZPARM module that is specified for DB2® startup and the date on which the module is assembled. It also shows a list of the default values of the DB2 application.
If a field is not available for the current DB2 release, the string N/A is displayed. For other conditions, for example, if specific DB2 traces are not started or control block data is not available, the string N/P is displayed.
________________ ZPDSN VTM O2 V540.#P SC11 S 07/08/16 10:16:33 2
> Help PF1 Back PF3 Left PF10 Right PF11
> R.H.H
> DSNZPARM INFORMATION: Enter a selection letter on the top line.
> A-THREAD B-TRACE C-LOGGING D-ARCHIVING E-AUTH/RLF/DDF F-IRLM
> G-STORAGE *-DATASET I-DDCS J-DATA SHARING K-STORED PROC L-UTIL
> M-APPL N-DATA O-PERF P-BUFFERPOOL Q-OTHERS
===============================================================================
> DSNZPARM DATASET AND DATABASE PARAMETERS
ZDSN
+ Collection Interval: REALTIME SNAPTIME: 07/08/16 10:16:33.22
+
+ DSNZPARM Module DSNZPARM
+ Assembly Date 04/14/16
+ Initial Module DSNZPARM
+ Assembly Date 04/14/16
+ Previous Module DSNZPARM
+ Assembly Date 04/14/16
+
+ DSNTIP7-SQL Object Defaults 1
+------------------------------------
+ Reordered Row Format (RRF) N/A
+ (OBJECT_CREATE_FORMAT) EXTENDED
+ (UTILITY_OBJECT_CONVERSION) NONE
+ Vary DS Control Interval (DSVCI) YES
+ Table Space Allocation (TSQTY) 0
+ Index Space Allocation (IXQTY) 0
+ Optimize Extent Sizing (MGEXTSZ) NO
+
+ DSNTIP71-SQL Object Defaults 2
+------------------------------------
+ Default Partition Segsize (DPSEGSZ) 32
+ Pct Free for Update (PCTFREE_UPD) 0
+ (PAGESET_PAGENUM) ABSOLUTE
+ (RETRY_STOPPED_OBJECTS) NO
+ Pad Index By Default (PADIX) NO
+ (INDEX_MEMORY_CONTROL) AUTO
+ (DDL_MATERIALIZATION) ALWAYS_IMMEDIATE
+ (DEFAULT_INSERT_ALGORITHM) FAST 
+
+ DSNTIP72-SQL Object Defaults 3
+------------------------------------
+ Define Data Sets (IMPDSDEF) YES
+ Use Data Compression (IMPTSCMP) NO
+ (PREVENT_ALTERTB_LIMITKEY) NO
+ (IX_TB_PART_CONV_EXCLUDE) YES
+ Rename Table (RENAMETABLE) DISALLOW
+
+ DSNTIP91-Workfile Database
+------------------------------------
+ Max Temp STG/AGENT (MAXTEMPS) 0
+ Separate Work Files (WFDBSEP) NO
+ Max Temp RID (MAXTEMPS_RID) 0
+ (WFSTGUSE_AGENT_THRESHOLD) 0
+ (WFSTGUSE_SYSTEM_THRESHOLD) 90
+
+ DSNTIPS-Auto Start DB/TS
+------------------------------------
+ Restart or Defer (RESTART) RESTART
+ Auto Start Type (LIST or ALL) ALL
===============================================================================Navigation
For additional information about
- related topics, select one of the options on the top of the panel.
- other topics, use the PF keys.
Fields
The DSNZ command displays
the following lines to reflect the usage of the DB2 SET SYSPARM command. To
each of these lines, the corresponding date on which this particular
module is assembled is displayed.
- DSNZPARM Module
- The name of the DSNZPARM module that is specified for DB2 startup.
- Initial Module
- The name of the initial DSNZPARM load module.
- Previous Module
- The name of the previous DSNZPARM load module.
- Assembly Date
- The date on which this module was assembled.
DSNTIP7-SQL Object Default 1
- Reordered Row Format (RRF) (QWP4RRF)
- This parameter determines whether reordered row format is enabled.
- YES
- Enables reordered row format.
- (OBJECT_CREATE_FORMAT) (QWP4OBCF)
- This parameter determines whether new table spaces and indexes are created in one of the
following formats:
- 1
- Extended log record format
- 0
- Basic log record format
- (UTILITY_OBJECT_CONVERSION) (QWP4UTOC)
- If RBALRSW format is not specified, this parameter converts existing table spaces and indexes that use extended log record addressing to basic log record addressing and vice versa. DB2 11 and later.
- Vary DS Control Interval (DSVCI) (QWP1VVCI)
- This parameter determines whether CI SIZE=PAGE is used when DB2-managed data sets are defined.
- Table Space Allocation (TSQTY) (QWP1TSQT)
- This parameter determines the default values in KB for PRIQTY and SEQCTY for table spaces.
- Index Space Allocation (IXQTY) (QWP1IXQT)
- This parameter determines the default values in KB for PRIQTY and SEQCTY for index spaces.
- Optimize Extent Sizing (MGEXTSZ) (QWP1MESZ)
- This parameter determines whether a sliding scale for secondary space allocations for DB2-managed data sets is enabled.
DSNTIP71-SQL Object Default 2
- Default Partition Segsize (DPSEGSZ) (QWP1DPSS)
- This parameter determines the default segment size that is used for a partitioned table space when the CREATE TABLESPACE statement does not include the SEGSIZE parameter.
- Pct Free for Update (PCTFREE_UPD) (QWP4PFUP)
- This parameter determines the default percentage of each page that DB2 leaves as free space for updates when you create a table space without specifying the FOR UPDATE CLAUSE of the PCTFREE option and a table within that table space is populated. DB2 11 and later. DB2 11 and later.
- (PAGESET_PAGENUM) (QWP4PSPN)
- Specifies whether range-partitioned table spaces and associated indexes will be created
to use absolute page numbers across partitions or relative page numbers.
- A
- Absolute page numbers
- R
- Relative page numbers
- (RETRY_STOPPED_OBJECTS) (QWP4RSO)
- Specifies whether DB2 should immediately reject requests for a stopped object or retry
them, up to the IRLM timeout limit, to see if the object has been started.
- NO
- This is the default. It indicates that DB2 immediately rejects requests for a stopped object.
- YES
- DB2 retries such requests, up to the IRLM timeout setting, if the stopped object is restarted.
- Pad Index By Default (PADIX) (QWP4PDIX)
- This parameter determines whether new indexes are padded
- Valid values:
- NO
- New indexes are not padded unless the PADDED option is included in the CREATE INDEX statement.
- YES
- New indexes are padded unless the NOT PADDED option is included in the CREATE INDEX statement.
NO - (INDEX_MEMORY_CONTROL) (QWP4IXMC)
- Shows the amount of memory that DB2 should allocate for fast traversing of DB2 indexes:
- 1 = AUTO (default)
- DB2 sets the upper limit of the storage to 20% of the currently allocated buffer pools.
- 0 = DISABLE
- DB2 returns any existing storage allocated for fast index traversal and does not allocate any further storage for this purpose.
- 10 - 200000
- Indicates the storage limit for fast index traversal in megabytes.
- (DDL_MATERIALIZATION) (QWP4DDLM)
- Specifies when DB2 materializes changes to the definition of an object. Valid values are:
- ALWAYS IMMEDIATE (default)
- Changes are materialized at the time the request is executed, and the containing table space is placed in the AREO* or REORP status.
- ALWAYS PENDING
- Changes are not materialized at the time that the request is executed, and the affected objects are available until it is convenient to implement changes. The containing table space is placed in the AREO* status.
- (DEFAULT_INSERT_ALGORITHM) (QWP4DINA)
- Specifies the default algorithm for inserting data into table spaces. Valid values:
- BASIC(1) (default)
- The basic insert algorithm is used.
- FAST(2)
- The fast insert algorithm is used.
- Define Data Sets (IMPDSDEF) (QWP1DIDS)
- This parameter determines the underlying data sets when a table space that is in an implicitly created database is created.
- Use Data Compression (IMPTSCMP) (QWP1CITS)
- This parameter determines whether data compression in table spaces in implicitly defined databases is used.
DSNTIP72-SQL Object Default 3
- Define Data Sets (IMPDSDEF) (QWP1DIDS)
- This parameter determines the underlying data sets when a table space that is in an implicitly created database is created.
- Use Data Compression (IMPTSCMP) (QWP1CITS)
- This parameter determines whether data compression in table spaces in implicitly defined databases is used.
- (PREVENT_ALTERTB_LIMITKEY) (QWP4XPKE)
- DB2 11 and later.Determines whether DB2 disallows altering the limit key by using an ALTER TABLE
statement for index-controlled partitioned table spaces. This alter operation places the table space
in REORG-pending (REORP) restrictive status, and the data is not available until the affected
partitions are reorganized. Use PREVENT_ALTERTB_LIMITKEY to avoid this data unavailability.
- NO
- Specifies that you can alter a limit key by using an ALTER TABLE statement for index-controlled partitioned table spaces. NO is the default.
- YES
- Specifies that it is not permitted to alter a limit key by using an ALTER TABLE statement for index-controlled partitioned table spaces. An ALTER TABLE statement must not attempt to alter the limit key for an index-controlled partitioned table.
- (IX_TB_PART_CONV_EXCLUDE) (QWP4XPKE)
- This parameter determines whether to include all columns in the partitioning key during the conversion from index-controlled partitioning to table-controlled partitioning.
- Rename Table (RENAMETABLE) (QWP4ERTS)
- DB2 12 and later.Specifies whether the RENAME TABLE statement should extend to tables that are
referenced in a view definition or the definition of an SQL table function:
- ALLOW_DEP_VIEW_SQLTUDF
- YES
- DISALLOW_DEP_VIEW_SQLTUDF
- NO
DSNTIP91-Workfile Database
- Max Temp STG/AGENT (MAXTEMPS) (QWP4WFAL)
- Specifies the maximum number of megabytes of temporary storage in the work file database that can be used by a single agend at any given time for all temporary tables. If 0 is specified, a limit is not enforced.
- Valid values:
0 - 2147483647Default: 0
- Separate Work Files (WFDBSEP) (QWP4WFDBSEP)
- This parameter determines whether DB2 provides an unconditional separation of table spaces in the work file database based on the allocation attributes of the table spaces.
- Valid values:
- YES
- DB2 directs declared global temporary table (DGTT) work only to DB2-managed (STOGROUP) work file table spaces that are defined with a non-zero SECQTY and work file work only to other work file table spaces (DB2-managed table spaces that are defined with zero SECQTY or user-managed table spaces).
- If a table space with the preferred allocation type is not available, an error messages is issued by DB2.
- NO
- DB2 attempts to direct (DGTT) work to DB2-managed (STOGROUP) work file table spaces that are defined with a non-zero SECQTY and work file work to any other work file table space (DB2-managed table spaces that are defined with zero SECQTY or user-managed table spaces.
- If a table space with the preferred allocation type is available, DB2 selects a table space with a non-preferred allocation type.
- Max Temp RID (MAXTEMPS_RID) (QWP4WFRD)
- The maximum number of RID blocks of temporary storage in the work file database that a single RID list can use at any point in time.
- (WFSTGUSE_AGENT_THRESHOLD) (QWP4WFAT)
- Determines the percentage of the space that is available in the work file database on this DB2 subsystem or in this data sharing member that can be consumed by a single agent before a warning message is issued.
- Space in the work file database can be configured for temporary (DGTT-ORIENTED) work and for sort work including CGTTS and trigger transition tables.
- Valid values:
- 0
- Agent-level space-usage alerts for the work file database are not issued by DB2.
- This is the default setting.
- 1 - 100
- If DSN6SPRM.WFDBSEP is set to YES, a warning message is issued by DB2 if the percentage of the total temporary work file space or the percentage of the total sort work file space that is consumed by an agent exceeds the specified threshold.
- If DSN6SPRM.WFDBSEP is set to NO, a warning message is issued by DB2 if the percentage of the entire work file space (temporary and sort) that is consumed by an agent exceeds the specified threshold.
- If DSN6SPRM.MAXTEMPS is greater than 0 and if the threshold that is specified for DSN6SPRM.MAXTEMPS is exceeded before the threshold that is specified for the WFSTGUSE_AGENT is reached, the warning message is not issued because MAXTEMPS is reached first by the agent. This results in a error message "Resource unavailable" with the SQL code -904.
- (WFSTGUSE_SYSTEM_THRESHOLD) (QWP4WFST)
- Determines the percentage of the space that is available in the work file database on this DB2 subsystem or in this data sharing member that can be consumed by all agents before a warning message is issued.
- Valid values:
- 0
- System-level space-usage alerts for the work file database are not issued by DB2.
- 1 - 100
- If DSN6SPRM.WFDBSEP is set to YES, a warning message is issued by DB2 if the percentage of total temporary work file space or the percentage of total sort work file space that is consumed by all agents exceeds the specified threshold.
- If DSN6SPRM.WFDBSEP is set to NO, a warning message is issued by DB2 if the percentage of the entire work file space (temporary and sort) that is consumed by all agents exceeds the specified threshold.
DSNTIPS-Auto Start DB/TS
- Restart or Defer (RESTART) (QWP7STR )
- This parameter determines whether DB2 databases are started automatically or are deferred.
- Valid values:
- NO
- The specified databases and page sets are deferred.
- YES
- The specified databases are started automatically.
- Auto Start Type (LIST or ALL) (SWP7STR )
- This parameter determines the DB2 databases that are started or deferred.
- Valid values:
- ALL
- When DB2 starts, all databases are started automatically.
- LIST
- When DB2 starts, a list of databases and table spaces and indexes is displayed. The databases and table spaces and indexes of this list are started automatically or they are deferred.