DSNZPARM Performance and Optimization Parameters

This panel shows information about the parameters that affect DB2 performance and optimization. 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.


________________ ZPPFM    VTM     O2       V540.#P DC11 S 04/08/16 20:56:36 2  
>       Help PF1      Back PF3      Left PF10      Right PF11                   
> R.H.O                                                                         
>       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   H-DATASET   I-DDCS      J-DATA SHARING  K-STORED PROC    L-UTIL  
>  M-APPL      N-DATA      *-PERF      P-BUFFERPOOL    Q-OTHERS                 
=============================================================================== 
>            DSNZPARM PERFORMANCE  OPTIMIZATION PARAMETERS                      
 ZPFM                                                                           
+ Collection Interval:  REALTIME                SNAPTIME: 04/08/16 20:56:36.20  
+                                                                               
+ DSNZPARM Module                         DSNZPARM                              
+ Assembly Date                           03/17/16                              
+ Initial Module                          DSNZPARM                              
+ Assembly Date                           03/17/16                              
+ Previous Module                         DSNZPARM                              
+ Assembly Date                           03/17/16                              
+                                                                               
+ DSNTIP8-Perf and Optimization 1                                               
+------------------------------------                                           
+ Cache Dynamic SQL (CACHEDYN)                 YES                              
+ (CACHEDYN_STABILIZATION)                    BOTH                              
+ Optimization Hints (OPTHINTS)                 NO                              
+ Evaluate Uncommitted (EVALUNC)                NO                              
+ Skip Uncomm Inserts (SKIPUNCI)                NO                              
+ Immediate Write (IMMEDWRI)                    NO                              
+ Plan Management (PLANMGMT)              EXTENDED                              
+ Plan Mgmt Scope (PLANMGMTSCOPE)                S    
+ Pkgrel Commit (PKGREL_COMMIT)                YES     
+ (XML_RANDOMIZE_DOCID)                         NO   
+ Real Time Stats (STATSINT)                    30                            
+ Disable EDM RTS (DISABLE_EDMRTS)              NO                              
+ Statistics Feedback (STATFDBK_SCOPE)         ALL
+ (STATFDBK_PROFILE)                           YES                            
+                                                                              
+ DSNTIP81-Perf and Optimization 2                                             
+------------------------------------                                          
+ Current Degree (CDSSRDEF)                      1                             
+ Max Degree (PARAMDEG)                          0                             
+ Max Degree for DPSI (PARAMDEG_DPSI)            0                             
+ Parallel Efficiency (PARA_EFF)                50                             
+ Star Join Queries (STARJOIN)             DISABLE                             
+ Max Data Caching (MXDTCACH)                   20                             
+ Current Refresh Age (REFSHAGE)                 0                             
+ Current Maint Types (MAINTYPE)            SYSTEM                             
+                                                                              
+ DSNTIP82-Query Accelerator Ref                                               
+------------------------------------                                          
+ Accelerator Startup (ACCEL)                            A                     
+ (GET_ACCEL_ARCHIVE)                                   NO                     
+ (QUERY_ACCELERATION)                                NONE                     
+ (QUERY_ACCEL_WAITFORDATA)

+ DSNTIP8A-Acceleration Options
+------------------------------------
+ (QUERY_ACCEL_OPTIONS) - None                          NO
+ 1-MBCS EBCDIC                                        YES
+ 2-INSERT FROM SELECT                                 YES
+ 3-BYTE-BASED STRING FUNCTIONS                        YES
+ 4-DD/MM/YYYY LOCAL DATE FORMAT                        NO
+ 5-SYSTEM_TIME TEMPORAL QUERIES                       YES  
+ 6-TS COLS WITH PRECISION OF 12                       YES  
+ 7-YYYYMMDD LOCAL DATE FORMAT                          NO 
+ 8-FAVOR NEW ACCELERATOR_TYPE                          NO
+ 9-ENABLE UNCERTAINTY COST EST                         NO
+ 10-BALANCE WORKLOAD BETWEEN VERS                      NO        
+ 11-USE ONLY NEW ACCELERATOR_TYPE                     YES             
===============================================================================

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.

DSNTIP8-Perf and Optimization 1

Cache Dynamic SQL (QWP4CDYN)
The CACHEDYN subsystem parameter determines whether prepared static SQL statements or dynamic SQL statements are cached for later use by eligible application processes.
Valid Values: 
NO
YES
Default: YES
(CACHEDYN_STABILIZATION)

This ZPARM specifies how DB2 is to stabilize cached dynamic SQL statement. When a statement is stabilized, the current SQLID, statement text, and runtime structures are written to catalog tables. If a dynamic SQL statement is not present in the dynamic SQL statement cache, DB2 will load the runtime structures from catalog table rather than performing a full prepare. This extends the stability and reliability of performance of a dynamic SQL. It can have the following values:

CAPTURE
Statements may be stabilized through the -START DYNQUERY command using both MONITOR(NO) and MONITOR(YES). DB2 will not load stabilized statements from SYSDYNQRY.
LOAD
Statements may not be stabilized via any means. The -START DYNQUERY command will fail, and any MONITOR(YES) commands in progress will not stabilize statements, even if stabilization criteria are matched. During long prepare, DB2 will attempt to load stabilized statements from SYSDYNQRY with which to run.
BOTH
Statements may be stabilized through the -START DYNQUERY command via both MONITOR(NO) and MONITOR(YES). During long prepare, DB2 will attempt to load stabilized statements from SYSDYNQRY with which to run. BOTH is the default setting.
NONE
Statements may not be stabilized via any means. The -START DYNQUERY command will fail, and any MONITOR(YES) commands in progress will not stabilize statements, even if stabilization criteria are matched. DB2 will not load stabilized statements from SYSDYNQRY.
Db2 12 and later.
Optimization Hints (QWP4HINT)
The OPTHINTS subsystem parameter determines whether Db2 applies optimization hints when static SQL statements are bound or dynamic SQL statements are prepared.
Valid Values: 
NO
YES
Default: NO
Evaluate Uncommitted (QWP4EVUN)
The EVALUNC subsystem parameter determines whether predicate evaluation is allowed on uncommitted data of other transactions. This parameter applies only to stage 1 predicate processing that uses table access for queries with isolation level RS or CS. Table access includes table space scan, index-to-data access, and RID-list processing.
Valid Values: 
NO
YES
Default: NO
Skip Uncomm Inserts (QWP4SKUI)
The SKIPUNCI subsystem parameter determines whether statements ignore a row that is inserted by another transaction if the row is not yet detected as committed. A newly inserted row can be detected as committed only after the lock that is held on the row is released.
Valid Values: 
NO
YES
Default: NO
Immediate Write (QWPAIMMW)
The IMMEDWRI subsystem parameter determines when updates to group buffer pool-dependent buffers are written to the coupling facility.
Valid Values: 
NO
YES
Default: NO
Plan Management (QWP4PMGT)
The PLANMGMT subsystem parameter determines the default plan management policy that is used when the PLANMGMT option is not explicitly specified for the bind or the rebind of a package.
Valid Values: 
BASIC
EXTENDED
OFF
Default: EXTENDED
Plan Mgmt Scope (QWP4PMSC)
The PLANMGMTSCOPE subsystem parameter determines the default plan management scope that is used when the PLANMGMTSCOPE option is not explicitly specified for the bind or rebind of a package.
The value in this field is meaningful only when the value of the PLAN MANAGEMENT field is set to BASIC or EXTENDED.
Valid Values: 
A (ALL)
D (DYNAMIC)
S (STATIC)
Default: S (STATIC)
Pkgrel Commit (PKGREL_COMMIT) (QWP4PKRC)
YES indicates that the following operations on a package that are bound with RELEASE(DEALLOCATE) are permitted while the package is active and allocated by DB2:
  • BIND and REBIND requests, including AUTOMATIC REBIND
  • Data definition language changes to objects that are statically referenced by the package
Db2 11 and later.
(XML_RANDOMIZE_DOCID) (QWP1XRDI)
The XML_RANDOMIZE_DOCID subsystem parameter determines whether DB2 is sequentially or randomly generating the DOCID values for XML columns.
This parameter does not affect existing tables that have XML columns. These tables continue to generate DOCIDs in the order that is specified by the XML_RANDOMIZE_DOCID parameter when the table is created or in sequential order if the parameter was not specified.
Valid Values: 
NO
YES
Default: NO
Real Time Stats (STATSINT) (QWP4INTE)
The time interval that DB2 waits before it attempts to write out page set statistics to the real-time statistics tables. This value is between 1 and 65535 minutes.
Disable EDM RTS (QWP4DEDR)
The DISABLE_EDMRTS subsystem parameter determines whether the collection of real-time statistics is disabled by the environmental description manager (EDM). Real-time statistics is tracking when packages were last used.
Valid Values: 
NO
YES
Default: NO
Statistics Feedback (STATFDBK_SCOPE) (QWP4SFBS)

Specifies the scope of SQL statements for which DB2 is to recommend statistics. Possible values are:

  • All (default): Statistics recommended for all SQL statements.
  • Dynamic: Statistics recommended only for dynamically processed SQL statements.
  • None: DB2 does not recommend statistics.
  • Static: Statistics recommended only for statically processed SQL statements, that is, those that are bound to a package.
Db2 11 and later.
STATFDBK_PROFILE (QWP4SFPR)
Specifies whether statistics recommendations identified during query optimization directly result in modifications to statistics profiles.
Valid values:
YES
NO
Default: YES.
Db2 12 and later.

DSNTIP81-Perf and Optimization 2

Current Degree (CDSSRDEF)
The CDSSRDEF subsystem parameter determines the default value that is used for the CURRENT DEGREE special register. The default value is used when a degree is not explicitly set in the SQL statement SET CURRENT DEGREE.
Valid Values: 
1
ANY
Default: 1
Max Degree (PARAMDEG)
The PARAMDEG subsystem parameter determines the maximum degree of parallelism that is allowed for a parallel group. If you specify a value for this parameter, you limit the degree of parallelism so that Db2 does not create too many parallel tasks that use virtual storage.
Valid Values: 
0 to 254
Default: 0
Max Degree for DPSI (PARAMDEG_DPSI)
The PARAMDEG_DPSI system parameter determines the maximum degree of parallelism that you can specify for a parallel group in which a data partitioned secondary index (DPSI) is used to drive parallelism.
A DPSI is a nonpartitioning index that is physically partitioned according to the partitioning scheme of the table. If you specify a value that is greater than 0 for this parameter, you limit the degree of parallelism for DPSIs so that Db2 does not create too many parallel tasks that use virtual storage.
Valid Values: 
0-254
DISABLE
Default: 0
Parallel Efficiency (PARA_EFF)
The PARA_EFF subsystem parameter determines the efficiency that DB2 assumes for parallelism when Db2 chooses an access path. The integer value that is used for this parameter represents a percentage efficiency.
Valid Values:
0  Ì 100
Default: 50
Star Join Queries (STARJOIN)
The STARJOIN subsystem parameter determines whether star join processing is enabled.
Valid Values: 
DISABLE
ENABLE
1 to 32768
Default: DISABLE
ZPARM: DSN6SPRM STARJOIN
Max Data Caching (MXDTCACH)
The MXDTCACH subsystem parameter determines the maximum amount of memory in MB that is allocated for data caching per thread.
Valid Values:
 0 to 512
Default: 20
Current Refresh Age (REFSHAGE)
The REFSHAGE subsystem parameter determines the default value for the CURRENT REFRESH AGE special register. The default value is used when no value is explicitly set by using the SET CURRENT REFRESH AGE statement.
Valid Values: 
0
ANY
Default: 0
Current Maint Types (MAINTYPE)
The MAINTYPE subsystem parameter determines the default value for the CURRENT MAINTAINED TABLE TYPES FOR > OPTIMIZATION special register. This value is used when no value is explsicitly set by using the SQL statement SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION.
Valid Values: 
ALL
NONE
SYSTEM
USER
Default: SYSTEM
DSNTIP82-Query Accelerator Refs
ACCELERATOR STARTUP (ACCEL) (QWP4ACCS)
This parameter determines whether accelerator servers are used with a DB2 subsystem, and how the accelerator servers are enabled and started.
Before you can start an accelerator server, it must be enabled.
You can modify this parameter online, however, if you change the setting from NO or COMMAND to AUTO, you must stop and restart Db2 to activate the new setting.
Valid Values: 
A(AUTO)
C(COMMAND)
N(NO)
Default: NO
(GET_ACCEL_ARCHIVE) (QWP4CGAA)
The GET_ACCEL_ARCHIVE subsystem parameter determines the default value that is used for the CURRENT GET_ACCEL_ARCHIVE special register.
The GET_ACCEL_ARCHIVE subsystem parameter is used when no value is explicitly set for the CURRENT GET_ACCEL_ARCHIVE special register by the SQL statement SET CURRENT GET_ACCEL_ARCHIVE.
Valid Values: 
NO
YES
Default: NO
(QUERY_ACCEL_OPTIONS) - None (QWP4QACO)
The QUERY_ACCEL_OPTIONS subsystem parameter determines whether certain types of queries are allowed to execute on an accelerator server.
Valid options:
YES
Query offloading is restricted to the standard SQL statements
NO
Options are set to the following list of possible values:
1, 2, 3, 4, 5, 6, 7
Valid Values:
Default: NONE
NONE
Query offloading is restricted to the standard SQL statements.
1
The queries that include data that is encoded by the multi-byte character set EBCDIC encoding scheme is not blocked from executing on IBM® Db2 Analytics Accelerator for z/OS® although IBM Db2 Analytics Accelerator for z/OS encodes the same data in the UTF-8 UNICODE encoding scheme.
EBCDIC and UNICODE implement different collating sequences.
  • The collating sequence for UNICODE is numeric, uppercase characters, and lower case characters. (1, 2, 3, A, B, C, a, b, c).
  • In EBCDIC, the collating sequence is lower case, upper case, and numeric (a, b, c, A, B, C, 1, 2, 3).
There are also differences in collating for the national characters. This affects the ordering of data and the results from range predicates. Therefore, in the following cases, a query that is executed in Db2 might return a different result set than the same query executed in IBM Db2 Analytics Accelerator for z/OS:
  • If the tables include character columns where more than one of these groups can be found in the column values
  • If the SQL statements include range predicates or ordering on these columns
2
The queries that include an INSERT from SELECT statement, the select part is not blocked from executing on IBM Db2 Analytics Accelerator for z/OS although the data operated on by the SELECT might not be current in IBM Db2 Analytics Accelerator for z/OS.
3
The queries that include Db2 byte-based string functions on data that is encoded by multi-byte character sets encoding schemes, for example, UNICODE, are not blocked from executing on IBM Db2 Analytics Accelerator for z/OS although IBM Db2 Analytics Accelerator for z/OS supports only character-based string functions.
If the data on which the string function is specified contains only single-byte characters, executing the function on IBM Db2 Analytics Accelerator for z/OS returns the same result as executing the function on DB2 no matter what encoding scheme is used for the data. However, if the data contains multi-byte characters, the results is not the same.
3
Queries that include DB2 byte-based string functions on data encoded by multi-byte character sets encoding schemes (like UNICODE) are not blocked from running on IDAA although IDAA supports only character-based string functions.
If the data on which the string function is specified contains only single-byte characters, running the function on IDAA returns the same result as running the function on DB2 irrespective of what encoding scheme is used for the data. However, if the data contains multi-byte characters, the results is not the same.
4
Queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from executing on IDAA. IDAA uses the DD/MM/YYYY format to interpret the input and output data value. Specify option 4 only when you also specify LOCAL as the setting for the DSNHDECP.DATE parameter and your LOCAL date exit defines the specific DD/MM/YYYY date format. Otherwise, queries may return unpredictable results.
Note: Option 4 cannot be specified in combination with option 7.
5
SYSTEM_TIME temporal queries are offloaded to an accelerator. DB2 12 and later.
6
Queries that reference timestamp columns with precision of 12 are offloaded to an accelerator. DB2 12 and later.
7
Queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from running on IDAA. IDAA uses the yyymmdd format to interpret the input and output date value.
Specify option 7 only when you also specify LOCAL as the setting for the DSNHDECP.DATE parameter and your LOCAL date exit defines the specific yyymmdd date format. Otherwise, queries may return yyymmdd date format or unpredictable results. DB2 12 and later.
(QUERY_ACCELERATION) (QWP4CQAC)
The QUERY_ACCELERATION subsystem parameter determines the default value that is used for the CURRENT QUERY ACCELERATION special register.
The QUERY_ACCELERATION subsystem parameter is used when no value is explicitly set for the CURRENT QUERY ACCELERATION special register by the SQL statement SET CURRENT QUERY ACCELERATION.
Valid Values: 
1, 2, 3, 4, 5
Default: 1
1 (NONE)
Determines that query acceleration is not used.
2 (ENABLE)
Determines that queries are accelerated if Db2 determines that acceleration is of advantage.
If an accelerator failure occurs while a query is running, or if the accelerator returns an error, Db2 returns a negative SQLCODE to the application.
3 (ENABLE_WITH_FAILBACK)
Determines that queries are accelerated if Db2 determines that acceleration is of advantage.
If the accelerator returns an error during PREPARE or first OPEN for the query, DB2 executes the query without the accelerator.
If the accelerator returns an error during FETCH or a subsequent OPEN, DB2 returns the error to the user. The query is not executed.
4 (ELIGIBLE)
Determines that queries are accelerated if they are eligible for acceleration.
Db2 does not use cost information to determine whether to accelerate the queries. Queries that are not eligible for acceleration are executed by DB2. If an accelerator failure occurs while a query is running, or if the accelerator returns an error, Db2 returns a negative SQLCODE to the application.
5 (ALL)
Determines that queries are accelerated if they are eligible for acceleration.
Db2 does not use cost information to determine whether to accelerate the queries. Queries that are not eligible for acceleration are not executed by DB2. An SQL error is returned. If an accelerator failure occurs while a query is running, or if the accelerator returns an error, DB2 returns a negative SQLCODE to the application.
(QUERY_ACCEL_WAITFORDATA) (QWP4QAWFD)
The QUERY_ACCEL_WAITFORDATA subsystem parameter determines the default value that is to be used for the CURRENT QUERY ACCELERATION WAITFORDATA special register. The QUERY_ACCEL_WAITFORDATA subsystem parameter is used when no value is explicitly set for the CURRENT QUERY ACCELERATION WAITFORDATA special register by the SQL statement.
Valid values
0.0 - 3600.0 or 0 - 3600 (seconds)
Default
0.0
ZPARM NAME:
DSN6SPRM.QUERY_ACCEL_WAITFORDATA
Db2 11 and later.
DSNTIP8A - Accelerator options
(QUERY_ACCEL_OPTIONS) - None (QWP4QACO/QWP4QANONE)
The QUERY_ACCEL_OPTIONS subsystem parameter controls whether certain types of queries are allowed to execute on an accelerator server.
Valid options
YES
Indicates that query offloading is restricted to the standard SQL statements.
NO
Indicates that options are set to the following list of possible values: 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10.
1 - MBCS EBCDIC (QWP4QAEBCM)

YES - Indicates that the queries that include data encoded by multi-byte character set EBCDIC encoding scheme are not blocked from executing on IDAA although IDAA encodes the same data in the UTF-8 UNICODE encoding scheme. Note that EBCDIC and UNICODE implement different collating sequences. The collating sequence for UNICODE is numeric, uppercase characters and then lower case characters (1, 2, 3, a, b, c, a, b, c). In EBCDIC, the collating sequence is lower case, upper case and then numeric (a, b, c, a, b, c, 1, 2, 3).

There are also differences in collating for the national characters. This affects both data ordering and the results from range predicates. Therefore, if the tables include character columns where more than one of these groups can be found in the column values and the SQL statements include range predicates or ordering on these columns, a query executed in DB2 may return a different result set than the same query executed in IDAA.

2 - INSERT FROM SELECT (QWP4QAIFSS)
YES - Indicates that the queries that include an insert from select statement, the select part is not blocked from executing on IDAA although the data operated on by the select may not be current in IDAA.
3 - BYTE-BASED STRING FUNCTIONS (QWP4QABBSF)

YES - Indicates that the queries that include DB2 byte-based string functions on data encoded by multi-byte character sets encoding schemes (like UNICODE) are not blocked from executing on IDAA although IDAA supports only character-based string functions. If the data on which the string function is specified contains only single-byte characters, executing the function on IDAA will return the same result as executing the function on DB2 irrespective of what encoding scheme is used for the data. However, if the data contains multi-byte characters, the results will not be the same.

4 - DD/MM/YYYY LOCAL DATE FORMAT (QWP4QALDMY)
YES - Indicates that the queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from executing on IDAA. IDAA will use the dd/mm/yyyy format to interpret the input and output data value. Specify option 4 only when you also specify LOCAL as the setting for the DSNHDECP.DATE parameter and your LOCAL date exit defines the specific dd/mm/yyyy date format. Otherwise, queries may return unpredictable results.
Note: Option 4 cannot be specified in combination with option 7.
5 - SYSTEM_TIME TEMPORAL QUERIES (QWP4QASTTQ)
YES - Indicates that SYSTEM_TIME temporal queries will be offloaded to an accelerator.
6 - TS COLS WITH PRECISION OF 12 (QWP4QATS12)
Indicates that the queries that reference timestamp columns with precision of 12 will be offloaded to an accelerator.
7 - YYYYMMDD LOCAL DATE FORMAT (QWP4QALYMD)
YES - Indicates that the queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from executing on IDAA. IDAA will use the yyymmdd format to interpret the input and output date value. Specify option 7 only when you also specify LOCAL as the setting for the DSNHDECP.DATE parameter and your LOCAL date exit defines the specific yyymmdd date format. Otherwise, queries may return unpredictable results.
8 - FAVOR NEW ACCELERATOR_TYPE (QWP1QAFNAT)
YES - Specifies that in a Db2 environment that includes both a V5 accelerator server and a V7 accelerator server, statements that are eligible to be accelerated on either of the accelerator servers are sent to the V7 accelerator server.
9 - ENABLE UNCERTAINTY COST EST (QWP4QADUCE)
YES - Specifies that uncertainty cost consideration is enabled on the accelerator for offloaded queries.
10 - BALANCE WORKLOAD BETWEEN VERS (QWP4QAWBAL)
YES - Specifies that Db2 will attempt to balance query acceleration between different versions of accelerator servers based on the actual workload of each available server. Workload balancing can also be influenced by the CURRENT ACCELERATOR register. For example, when BALANCE WORKLOAD BETWEEN VERSIONS option = YES, if the CURRENT ACCELERATOR special register specifies a particular accelerator server, the workload will be offloaded only to that server if it is available, even if other accelerator servers with less workload are available.
11 - USE ONLY NEW ACCELERATOR_TYPE (QWP4AONAT)
Specifies that in a Db2 environment, only V7 or later accelerators will be recognized. When there exists an accelerator server lower than V7, Db2 will not exploit it. This includes EXPLAIN, accelerator modeling and query execution. When this option is specified, the following options, if also specified, are ignored:
  • 8: FAVOR NEW ACCELERATOR_TYPE
  • 10: BALANCE WORKLOAD BETWEEN VERSIONS
Default
NONE(YES)
ZPARM NAME
DSN6SPRM.QUERY_ACCEL_OPTIONS