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       V550.#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                     
+ (TS_COMPRESSION_TYPE)
+                                                                              
+ 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)                          SELECTED
+ INDEX_MEMORY_CONTROL(Selected IX)                   2000                     
+ (DDL_MATERIALIZATION)                   ALWAYS_IMMEDIATE                     
+ (DEFAULT_INSERT_ALGORITHM)                          FAST   
+ (FTB_NON_UNIQUE_INDEX)                               YES                  
+                                                                              
+ 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.
Db2 10 and Db2 11 only..
(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
Db2 11 and later..
(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.
(TS_COMPRESSION_TYPE) - (QWP4TSCT)
The TS_COMPRESSION_TYPE subsystem parameter specifies the type of compression algorithm to use when Db2 creates new compressed table spaces, loads into, and reorganizes existing compressed table spaces.

ZPARM NAME: DSN6SPRM.TS_COMPRESSION_TYPE

Valid values are:

FIXED_LENGTH
(Default) Db2 uses its existing compression algorithm to perform compression and decompression of data stored in the table space. This is the default setting.
HUFFMAN
Db2 uses Huffman compression provided by IBM z14 or later hardware to perform compression and decompression of data stored in the table space. Huffman compression only applies to universal table spaces. All other table space types will use fixed-length compression regardless of the TS_COMPRESSION_TYPE setting.
Db2 12 and later.
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
Db2 12 and later.
(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.
Db2 12 and later.
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.
Default:
NO
(INDEX_MEMORY_CONTROL) (QWP4IXMC)
The amount of memory Db2 allocates for fast traversing of Db2 indexes. In a data sharing environment, it is recommended that all members use the same setting.
Valid values are:
AUTO
Db2 sets the upper limit of the storage to 20 percent of currently allocated buffer pools (as opposed to the defined buffer pool size).
DISABLE
Db2 returns any existing storage allocated for fast index traversal and allocates no further storage for the purpose.
SELECTED,AUTO
Fast index traversal is enabled only for specific indexes, and Db2 calculates the storage size limit. When this setting is specified, Db2 considers fast index traversal only for indexes recorded with ACTION='A' in the SYSIBM.SYSINDEXCONTROL catalog table.
SELECTED,n
Fast index traversal is enabled only for selected indexes, where n is the storage size limit (in megabytes in the range 10 to 200000).
INDEX_MEMORY_CONTROL(Selected IX) (QWP4IXMCS)
Indicates the storage limit for fast index traversal for selected indexes when INDEX_MEMORY_CONTROL is set to SELECTED.
Valid values are:
NO
(Default) Fast index traversal for non-unique indexes is disabled.
YES
Fast index traversal for non-unique indexes is enabled.
(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.
Db2 12 and later.
(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.
Db2 12 and later.
(FTB_NON_UNIQUE_INDEX) (QWP4FTBUO)
Indicates whether fast index traversal for non-unique indexes is enabled.
Valid values are:
NO
(Default) Fast index traversal for non-unique indexes is disabled.
YES
Fast index traversal for non-unique indexes is enabled.
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)
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.
Db2 11 and later.
(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)
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
Db2 12 and later.
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 - 2147483647 
Default: 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.
Default: NO
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.
Db2 11 and later.
(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.
The default setting is 90. Db2 11 and later.
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.