Other System Parameters

This topic shows detailed information about "System Parameters - Other System Parameters".

This block shows values not shown on other DB2 installation panels. These values are either set internally by DB2, or calculated from other install parameter values.

OTHER SYSTEM PARAMETERS                                        
-----------------------   
DUAL BSDS MODE (TWOBSDS)....................................YES
ROLL UP PARALLEL TASK ACCOUNTING (PTASKROL).................YES
NO. PAGES SMALL TABLE THRESHOLD (NPGTHRSH)....................0
OFFLOAD OPTION (OFFLOAD)....................................YES
SU CONVERSION FACTOR........................................224
MINIMUM DIVIDE SCALE (MINDVSCL)............................NONE
STAR JOIN THRESHOLD (SJTABLES)...............................10
ONLINE SYSTEM PARM USER ID MONITOR..........................N/P
ONLINE SYSTEM PARM CORREL ID MONITOR........................N/P
ONLINE SYSTEM PARM TIME CHANGED.............................N/P
ONLINE SYSTEM PARM TYPE.....................................N/P
DB2-SUPPLIED DECP INDICATOR...............................X'D5'
MAX CONCURRENT PKG OPS (MAX_CONCURRENT_PKG_OPS)..............10
ADMIN SCHEDULER JCL PROC NAME (ADMTPROC)....................N/P
FREE LOCAL CACHED STATEMENTS (CACHEDYN_FREELOCAL).............1
INDEX I/O PARALLELISM (INDEX_IO_PARALLELISM)................YES
ZOSMETRICS..................................................YES
USE TRACKMOD FOR IMPLICIT TS (IMPTKMOD).....................YES
DSSIZE FOR IMPLICIT TS (IMPDSSIZE)............................4
ENABLE MULTIPLE INDEX ACCESS (SUBQ_MIDX)....................YES
SP_PARMS_JV (DDF_COMPATIBILITY)..............................NO
SP_PARMS_NJV (DDF_COMPATIBILITY).............................NO 
DISABLE_IMPCAST_JV (DDF_COMPATIBILITY).......................NO  
DISABLE_IMPCAST_NJV (DDF_COMPATIBILITY)......................NO  
IGNORE_TZ (DDF_COMPATIBILITY)...............................YES
DDF COMP PRIOR VERSION (DDF_COMPATIBILITY)..................121
DYN STMT CACHE STOR (CACHE_DEP_TRACK_STOR_LIM)................2
ACTIVATE I/O SCHEDULING.....................................YES
VALUE FOR TRIGGER DRAIN.......................................1
MAX NUMBER OF DDS WITH HOLD...................................3
FIELD PROCS FOR DESCRIBE TABLE BLOCK..........................5
RESTRICT ALTER COLUMN FOR DCC (RESTRICT_ALT_COL_FOR_DCC).....NO
SPACE RESERVED FOR Z/OS FUNCTIONS........................40,960
SPACE RESERVED FOR CRITICAL WORK.....................39,387,136
SPACE RESERVED ON TOP OF Z/OS AND CRITICAL SPACE.....39,387,136
DETAILED MEASURED UNIT PRICE TRACKING........................NO
OTC LICENSE TERMS ACCEPTED...................................NO
SIMULATED CPUS................................................0
EXECUTION TIME OF TASK/SRB IN MICROSECONDS....................0
MAX 'NOT FOUND' HASH RECORDS................................100
MAX EXTEND SERVICE TASKS.....................................20
PROJECT Z INSERTION THRESHOLD.................................2
MAX ZIVLEMPEL DICTIONARY ENTRIES..........................4,096
REORG KEEPDICTIONARY IN BRF TO RRF CONVERSION................NO
DRDA RESOLVE ALIAS (DRDA_RESOLVE_ALIAS).....................YES
PC YES SPECIFIED............................................YES
BLOCK OPT 1 ROW SORT (OPT1ROWBLOCKSORT)......................NO
SMS DATACLASS NAME FOR TS (SMSDCFL).........................N/P (Prior to DB2 11)
SMS DATACLASS NAME FOR IS (SMSDCIX).........................N/P (Prior to DB2 11)
OUTER JOIN PERFORMANCE ENHANCEMENTS (OJPERFEH)..............YES (Prior to DB2 11)
EMPTY XML ELEMENT (XML_RESTRICT_EMPTY_TAG) .................YES
SUPPRESS_HINT_SQLCODE_DYN (SUPPRESS_HINT_SQLCODE_DYN) ......ALL  
INDEX MEMORY CONTROL (INDEX_MEMORY_CONTROL)................AUTO
SELECT FOR UNLOAD (AUTH_COMPATIBILITY).......................NO
MATERIALIZE NODET SQLTUDF (MATERIALIZE_NODET_SQLTUDF)........NO

DUAL BSDS MODE (TWOBSDS)

Shows whether two BSDS data sets are used.

A second BSDS (strongly recommended) makes recovery much easier in most situations. In cases that normally require recovery and restart, a second BSDS allows you to continue working. The storage overhead required is small and the data set is relatively inactive.

DB2 parameter TWOBSDS in DSN6LOGP.

Field Name: QWP2DBSD

ROLL UP PARALLEL TASK ACCOUNTING (PTASKROL)

Indicates whether DB2 generates a trace record at the originating task level that summarizes accounting information for all parallel tasks.

DB2 parameter PTASKROL in DSN6SYSP.

Field Name: QWP1PROL

NO. PAGES SMALL TABLE THRESHOLD (NPGTHRSH)

This parameter allows you to specify the optimizer threshold for qualifying a table as small.

-1

Every table qualifies as small.

0

No table qualifies as small (this is the default).

1

Only tables with zero pages qualify as small.

2

Tables with less than two pages qualify as small.

10

Tables with less than ten pages qualify as small.

502

Tables with less than 502 pages, and tables that have not had statistics collected qualify as small. For example, when NPAGES = -1.

DB2 parameter NPGTHRSH in DSN6SPRM.

Background and Tuning Information

Tables can be populated using insert just prior to their use by queries and then cleared immediately on completion of the queries. These tables are permanent even though the data they contain is transient.

This can cause problems when RUNSTATS is run overnight, or at other times when these tables are empty. This gives the optimizer the false indication that these tables contain no data when in fact, the tables will contain data when the query executes. This causes the optimizer to pick an inefficient access path. Usually the optimizer chooses to do a table scan, which would be the most efficient access path if the table were truly empty. Because the table is not empty when the query executes, it would be more efficient to use matching index access.

With this parameter, you can force the optimizer to treat tables containing no data as small tables. For these tables, the optimizer will:

• Select a matching index access rather than a table space scan and non-matching index access.
• Select the index with the most matching columns when more than one index qualifies for matching index access.
• Select indexes with the same number of matching columns on cost.

Field Name: QWP4NPAG

OFFLOAD OPTION (OFFLOAD)

Shows whether the offload process is initiated online.

ZPARM OFFLOAD in macro DSN6LOGP.

Field Name: QWP2OFFL

SU CONVERSION FACTOR

The CPU service unit conversion factor for this CPU.

This factor allows conversion CPU time in seconds to a common unit, called service unit (SU). The conversion factor used depends on the machine. Service units allow you to calculate CPU execution times across a data sharing group.

The conversion factor is used as follows:

CP secs * 16,000,000 / Conversion Factor = SUs

SUs * Conversion Factor / 16,000,000 = CP secs

This field does not map to an installation panel.

Field Name: QWPASUCV

MINIMUM DIVIDE SCALE (MINDVSCL)

The minimum scale for the result of a decimal division. The values for this parameter are none (the default), 3, or 6. If 3 or 6 is specified, this parameter overrides the DECDIV3 parameter.

Field Name: QWP4MDSC

STAR JOIN THRESHOLD (SJTABLES)

The minimum number of tables in the star schema query block, including the fact table, dimensions tables, and snowflake tables. This value is considered only if the subsystem parameter STARJOIN qualifies the query for star join.

Possible values are:

0

Star join is disabled. This is the default.

1, 2, or 3

Star join is always considered.

4 through 255

Star join is considered if the query block has at least the specified number of tables.

256 and greater

Star join is never considered.

DB2 parameter SJTABLES in DSN6SPRM.

Background and Tuning Information

Although star join can reduce bind time significantly it does not provide optimal performance in all cases. Performance of star join depends on a number of factors such as the available indexes on the fact table, the cluster ratio of the indexes, and the selectivity of rows through local and join predicates. Follow these general guidelines for setting the value of SJTABLES:

• If you have star schema queries with less than 10 tables and you want to make the star join method applicable to all qualified queries, set the value of SJTABLES to a low number, such as 5.
• If you have some star schema queries that are not necessarily suitable for star join but want to use star join for relatively large queries, use the default. The star join method will be considered for all qualified queries that have 10 or more tables.
• If you have star schema queries but normally do not want to use star join, you could increase SJTABLES, say to 15. This will greatly cut the bind time for large queries and avoid a potential bind time SQL return code -101 for large qualified queries.

Field Name: QWP4SJTB

ONLINE SYSTEM PARM USER ID MONITOR

The user ID that made the last online change to DB2 system settings.

Field Name: QWP4OZUS

ONLINE SYSTEM PARM CORREL ID MONITOR

The correlation ID of the online application that made the last change to DB2 system settings.

Field Name: QWP4OZCI

ONLINE SYSTEM PARM TIME CHANGED

Time of the last online change made to DB2 system settings.

Field Name: QWP4OZTM

ONLINE SYSTEM PARM TYPE

The type of DB2 system parameter changed by the last SET SYSPARM statement.

Field Name: QWP4OZTP

DB2-SUPPLIED DECP INDICATOR

Indicates that DECP is supplied by DB2.

Using a DB2 supplied DECP could cause data corruption due to applications using wrong CCSIDs.

Field Name: QWPBDB2S

MAX CONCURRENT PKG OPS (MAX_CONCURRENT_PKG_OPS)

The maximum number of package requests that can be processed simultaneously.

DB2 parameter MAX_CONCURRENT_PKG_OPS in DSN6SPRM.

Field Name: QWP4MXAB

ADMIN SCHEDULER JCL PROC NAME (ADMTPROC)

The name of the JCL procedure for starting the DB2 administrative scheduler task address space.

DB2 parameter ADMTPROC in DSN6SPRM.

Field Name: QWP4ADMT

FREE LOCAL CACHED STATEMENTS (CACHEDYN_FREELOCAL)

Indicates whether DB2 can free statements from the local dynamic statement cache to relieve storage constraints below the 2 GB bar. This parameter applies only for packages or plans that are bound with KEEPDYNAMIC(YES). Possible values are:

0

DB2 cannot free statements from the local cache

1

DB2 can free statements from the local cache

DB2 parameter CACHEDYN_FREELOCAL in DSN6SPRM.

Field Name: QWP4FRLC

INDEX I/O PARALLELISM (INDEX_IO_PARALLELISM)

In DB2 12 this field is a serviceability field.

The enablement of the index I/O parallelism ZPARM.

Field Name: QWP4IIOP

ZOSMETRICS

YES indicates that gathering of z/OS metrics using the RMF interface is enabled. ZPARM ZOSMETRICS in DSN6SPRM.

Field Name: QWP4METE

USE TRACKMOD FOR IMPLICIT TS (IMPTKMOD)

Shows whether you have specified the TRACKMOD option on ALTER TABLESPACE for an implicitly created table space.

This field corresponds to field TRACK MODIFIED PAGES on installation panel DSNTIP7. The ZPARM name is IMPTKMOD in DSN6SPRM.

Field Name: QWP1TKMD

DSSIZE FOR IMPLICIT TS (IMPDSSIZE)

Shows the maximum DSSIZE in gigabytes that DB2 uses for creating each partition of an implicitly created base table space.

This field corresponds to field DEFAULT DSSIZE on installation panel DSNTIP7. The ZPARM name is IMPDSSIZE in DSN6SPRM.

Field Name: QWP1DSSZ

ENABLE MULTIPLE INDEX ACCESS (SUBQ_MIDX)

Specifies whether to enable or disable multiple index access for queries that have subquery predicates:

NO

Disables multiple index access for queries.

YES

Enables multiple index access for queries.

The ZPARM name is SUBQ_MIDX IN DSN6SPRM.

Field Name: QWP4SQMX

SP_PARMS_JV (DDF_COMPATIBILITY)

Specifies that when a Java client application calls a DB2 for z/OS stored procedure, DB2 returns output argument values with data types that match the data types that were specified in the CallableStatement.registerOutParameter method calls.

If SP_PARMS_JV is not specified, DB2 returns output parameter values with data types that match the data types of the parameters in the stored procedure definition.

ZPARM name DDF_COMPATIBILITY and ZPARM value SP_PARMS_JV in DSN6FAC.

Field Name: QWP9SPPMJ

SP_PARMS_NJV (DDF_COMPATIBILITY)

Specifies that when a non-Java client application calls a DB2 for z/OS stored procedure, DB2 returns output argument values with data types that match the data types of the corresponding CALL statement arguments, unless one of the following conditions are true:

• The non-Java client is Version 10 or later.
• The stored procedure uses a parameter data type that was introduced in DB2 for z/OS Version 10 (XML, TIMESTAMP WITH TIMEZONE, or TIMESTAMP with precision greater than 6).

If one condition is true, DB2 returns output parameter values with data types that match the data types of the parameters in the stored procedure definition. If SP_PARMS_NJV is not specified, DB2 returns output parameter values with data types that match the data types of the parameters in the stored procedure definition.

ZPARM name DDF_COMPATIBILITY and ZPARM value SP_PARMS_NJV in DSN6FAC.

Field Name: QWP9SPPM

DISABLE_IMPCAST_JV (DDF_COMPATIBILITY) (DB2 11 or later)

Specifies whether the DB2 for z/OS server disables implicit casting of input host variables from numeric data types to string data types, or from string data types to numeric data types, when the application is a Java client application that uses the IBM Data Server Driver for JDBC and SQLJ. If application compatibility is set to:

• V10R1: DB2 uses DISABLE_IMPCAST_JV.
• V11R1 or later: DB2 always does implicit casting.

ZPARM name DDF_COMPATIBILITY ZPARM value DISABLE_IMPCAST_JV in DSN6FAC.

Field Name: QWP9ICIJ

DISABLE_IMPCAST_NJV (DDF_COMPATIBILITY)

Specifies that DB2 for z/OS disables implicit casting of input host variables from numeric data types to string data types, or from string data types to numeric data types, when the application is a non-Java client application that uses an IBM Data Server client or driver that is at Version 10.5 or earlier.

ZPARM name DDF_COMPATIBILITY and ZPARM value DISABLE_IMPCAST_NJV in DSN6FAC.

Field Name: QWP9ICIN

IGNORE_TZ (DDF_COMPATIBILITY)

Shows whether to ignore the time zone (TMZ) in TMZ input for Java.

Field Name: QWP9ITZJ

DDF COMP PRIOR VERSION (DDF_COMPATIBILITY)

The DDF compatibility parameter. The DB2 server with new-function mode has not yet been activated. The DDF compatibility parameter causes this server to identify itself to all remote clients as being in new-function mode for the previous version. The format of this field in the trace record is nnr, where nn is the version of the DB2 server and r is the release.

Field Name: QWP9DDFCIP

DYN STMT CACHE STOR (CACHE_DEP_TRACK_STOR_LIM)

Specifies the number of gigabytes of storage that DB2 allocates for hashing entries in the dynamic statement cache. This parameter can avoid storage shortages for long-running threads. The storage is allocated above the bar.

The ZPARM name is CACHE_DEP_TRACK_STOR_LIM in DSN6SPRM.

Field Name: QWP4CDTSL

ACTIVATE I/O SCHEDULING

Determines whether the I/O scheduling feature is activated. DB2 parameter SPRMIOP in DSN6SPRM.

Field Name: QWP4IOP

VALUE FOR TRIGGER DRAIN

The percentage below 100% DSMAX that open data sets can reach before an asynchronous drain is started. The default is 1, meaning that asynchronous drain starts when the number of open data sets reaches 99% of DSMAX.

DB2 defers closing and deallocating the table spaces or indexes until the number of open data sets reaches one of the following limits:

• The MVS limit for the number of concurrently open data sets.
• 99% (default) of the value that you specified for DSMAX.

When one of these limits is reached, DB2 closes a number of data sets not in use equal to 3% (default) of the value DSMAX. Thus, DSMAX controls not only the limit of open data sets, but also the number of data sets that are closed when that limit is reached.

DB2 parameter SPRMTDD in DSN6SPRM.

Field Name: QWP4TDDN

MAX NUMBER OF DDS WITH HOLD

The percentage of maximum open data sets until the asynchronous drain operations are stopped.

DB2 parameter SPRMMDD in DSNDSPRM.

Field Name: QWP4MDDN

FIELD PROCS FOR DESCRIBE TABLE BLOCK

The number of field procedures for the DESCRIBE TABLE block.

ZPARM SPRMFDP.

Field Name: QWP4FDP

RESTRICT ALTER COLUMN FOR DCC (RESTRICT_ALT_COL_FOR_DCC)

A value of YES prevents the use of ALTER table ALTER column with SET DATA TYPE, SET DEFAULT, and DROP DEFAULT when data capture changes is enabled on the target table. The ZPARM name is RESTRICT_ALT_COL_FOR_DCC in DSN6SPRM.

Field Name: QWP4RACD

SPACE RESERVED FOR Z/OS FUNCTIONS

The amount of space reserved for MVS functions.

Field Name: QWP1DB1M

SPACE RESERVED FOR CRITICAL WORK

The amount of space reserved for critical work that must be completed.

Field Name: QWP1CRIT

SPACE RESERVED ON TOP OF Z/OS AND CRITICAL SPACE

The amount of space above z/OS and critical (QWP1DB1M + QWP1CRIT) that DB2 tries to leave available.

Field Name: QWP1SOS

DETAILED MEASURED UNIT PRICE TRACKING

Specifies whether DB2® performs detailed tracking for measured usage pricing. You can select the following values:

YES

DB2 does detailed measured usage tracking if SMF type 89 records are activated. When SMF89 is set to YES, DB2 invokes a z/OS® service on every entry into or exit out of DB2 to ensure accurate tracking.

NO (the default value)

DB2 does not do detailed measured usage tracking. If the SMF type 89 record is activated, only high-level tracking is recorded in the SMF type 89 record. Selecting NO reduces CPU usage, but also increases the amount of time spent in DB2 as measured by SMF 89.

Note: Select SMF89 YES only if you use measured usage pricing.

DB2 parameter SMF89.

Field Name: QWP1SM89

OTC LICENSE TERMS ACCEPTED

Indicates that the one-time charge (OTC) license terms are accepted for this DB2 installation.

DB2 parameter OTC_LICENSE.

Field Name: QWP1OLAC

SIMULATED CPUS

The number of CPUs that are online.

Field Name: QWP4NCPU

EXECUTION TIME OF TASK/SRB IN MICROSECONDS

The accumulated SRB time for the address space. This value includes both, the preemptable and nonpreemptable SRB time. It does not include CPU time that is consumed on an IBM zIIP.

Field Name: QWSASRBT

MAX 'NOT FOUND' HASH RECORDS

The maximum number of NOT FOUND hash records.

Field Name: QWP4KNFC

MAX EXTEND SERVICE TASKS

Maximum number of extended service tasks.

Field Name: QWP4EST

PROJECT Z INSERTION THRESHOLD

Project z insertion threshold.

Field Name: QWP4ZTN

MAX ZIVLEMPEL DICTIONARY ENTRIES

The maximum number of ZIVLEMPEL dictionary entries.

Field Name: QWP4MDE

REORG KEEPDICTIONARY IN BRF TO RRF CONVERSION

Indicates that KEEPDICTIONARY is used when a REORG converts a table space from basic row format (BRF) to reordered row format (RRF).

Field Name: QWP4HKEEPD

DRDA RESOLVE ALIAS (DRDA_RESOLVE_ALIAS)

YES means that in SQL statements, DB2 replaces aliases that refer to three-part names with qualified object names before it sends the statements to the remote location. This substitution is done in the following cases:

• When PREPARE or EXECUTE IMMEDIATE is performed
• When REMOTE BIND of a package is performed.

ZPARM name DRDA_RESOLVE_ALIAS in DSN6SPRM.

Field Name: QWP4RSLV

PC YES SPECIFIED

Shows whether the IRLM uses the cross-address-space program call. This parameter determines where the IRLM lock control block structure is stored.

If you run a tightly-controlled environment and virtual storage is not constrained, use PC=NO. PC=YES is the conservative choice where insufficient information about the environment is available to make a well-informed decision.

With PC=NO, locks are managed in extended common service area (ECSA) and it is possible to achieve better CPU performance, because DB2 does not use cross-memory services for IRLM requests. However, ECSA is a limited resource and constrains the size of the private address space area available above the 16-MB line. The demand for ECSA storage to support locks may be excessive when one or more of the following conditions are true:

• Extensive use of row-level locking
• Ineffective lock avoidance
• Infrequent application commits
• Lock escalation via NUMLKTS and LOCKMAX is disabled because the applications cannot tolerate the impact
• Effectively no limit on the number of locks taken by an application (NUMLKUS is set very high)
• Multiple DB2 subsystems with IRLM PC=NO reside on the same z/OS image

Assuming the average lock consumes 536 bytes of storage, a single application which takes 100000 locks before a commit would consume almost 52 MB of ECSA when IRLM is configured with PC=NO. MAXCSA would have to be set to at least 52 MB. If a very large number of locks are held by concurrent application processes, the demand for ECSA may not be able to be supported.

Recommendation: If you run applications that have many of the above characteristics, it is strongly recommended to use PC=YES. Certain ERP vendor applications that run concurrent processes can acquire a very large number of held locks that would require a very large setting for MAXCSA, or cause an ECSA overflow which would adversely impact the availability of the z/OS image.

If PC=NO is selected, MAXCSA should be sized to support the concurrent number of held locks required and to avoid an ECSA overflow condition. When setting MAXCSA, check to ensure that the ECSA setting in PARMLIB is sufficient to support the aggregate demand from IRLM and other subsystems. The ECSA size for z/OS is specified by the CSA keyword in the IEASSYSnn member in SYS1.PARMLIB.

With PC=YES, locks are managed in the extended private area of the IRLM address space. This can increase the CPU cost of lock and unlock requests relative to PC=NO. However, with reasonable lock avoidance, the total CPU overhead is likely to be limited to 1 to 2%, which is well within measurement noise and therefore not significant.

With PC=YES, the MAXIMUM ECSA option is ignored but must not be zero. The amount of storage allowed for LOCK usage is determined from the extended storage provided to the IRLM address space at startup time. This amount is reduced by 200 MB to allow a buffer for IRLM and z/OS required storage and for DMBS MUST COMPLETE processes. The amount being monitored can be seen in the display message from the irlmproc,STATUS,STOR command. IRLM still uses CSA and ECSA for other purposes. If you need to create a dump for DB2 diagnostic purposes, you need to ensure that IRLM is included in the dump, and that the dump data sets are large enough to hold IRLM.

PC=NO is a good solution when one or more of the following conditions are true, particularly when running a data sharing configuration:

• Optimal CPU performance is required
• No constraint is necessary on available ECSA
• Significant IRLM lock contention and a very large number of lock requests with ineffective lock avoidance
• Relatively high IRLM SRB time

YES puts the lock control block structure in the IRLM private address space, and the program call instruction is used to address it. IRLM still uses CSA and ECSA for other purposes. With PC=YES, the MAXIMUM ECSA option is ignored.

Field Name: QWP5PCY

BLOCK OPT 1 ROW SORT (OPT1ROWBLOCKSORT)

Specifies whether DB2 explicitly blocks sort operations when the OPTIMIZE FOR 1 ROW clause is specified on a query:

NO = DISABLE

Means that when OPTIMIZE FOR 1 ROW is specified, DB2 avoids access paths that involve sorts. If an access path that avoids a sort exists, it is possible, although unlikely, that an access path that involves a sort is chosen instead. This behavior is used in DB2 9 and earlier releases.

YES = ENABLE

Means that when OPTIMIZE FOR 1 ROW is specified, DB2 chooses access paths that avoid sorts whenever such a path is available.

ZPARM name is OPT1ROWBLOCKSORT in DSN6SPRM.

Field Name: QWP4O1RBS

SMS DATACLASS NAME FOR TS (SMSDCFL) (Prior to DB2 11)

SMS data class for data table spaces. The data class name is a string of one to eight characters. The default is an empty string, which means that the SMS cluster is defined without the DATACLASS parameter.

When a valid data class name is specified, the SMS cluster is specified with the DATACLASS parameter using the name specified. If the name is not valid, SMS returns an error.

DB2 parameter SMSDCFL in DSN6SPRM.

Field Name: QWP4DCFS

SMS DATACLASS NAME FOR IS (SMSDCIX) (Prior to DB2 11)

SMS data class for index table spaces. The data class name is a string of one to eight characters. The default is an empty string, which means that the SMS cluster is defined without the DATACLASS parameter.

When a valid data class name is specified, the SMS cluster is specified with the DATACLASS parameter using the name specified. If the name is not valid, SMS returns an error.

DB2 parameter SMSDCIX in DSN6SPRM.

Field Name: QWP4DCIX

OUTER JOIN PERFORMANCE ENHANCEMENTS (OJPERFEH) (Prior to DB2 11)

Indicates whether outer join performance enhancements are enabled.

DB2 parameter OJPERFEH in DSN6SPRM.

Field Name: QWP4OJEH

EMPTY XML ELEMENT (XML_RESTRICT_EMPTY_TAG)

Indicates whether empty XML elements are serialized:

NO

Serialization of empty XML elements is not defined.

YES

Empty XML elements are serialized using a start-element tag followed by an end-element tag.

ZPARM name is XML_RESTRICT_EMPTY_TAG in DSN6SPRM.

Field Name: QWP4NOET

SUPPRESS_HINT_SQLCODE_DYN (SUPPRESS_HINT_SQLCODE_DYN)

Specifies whether DB2 suppresses SQLCODE +394 and SQLCODE +395 when specified access paths are applied for dynamic SQL statements.

NO

DB2 issues SQLCODEs +394 and +395 for statement-level access paths and PLAN_TABLE access paths. NO is the default value.

STMT

DB2 suppresses SQLCODEs +394 and +395 for statement-level access paths for dynamic SQL statements.

ALL

DB2 suppresses SQLCODEs +394 and +395 for statement-level access paths and PLAN_TABLE access paths for dynamic SQL statements.

The ZPARM name is SUPPRESS_HINT_SQLCODE_DYN in DSN6SPRM.

Field Name: QWP4SHDE

INDEX MEMORY CONTROL (INDEX_MEMORY_CONTROL)

Shows the amount of memory that DB2 should allocate for fast traversing of DB2 indexes:

-1 = AUTO

Specifies that DB2 sets the upper limit of the storage to 20% of the currently allocated buffer pools.

0 = DISABLE

Specifies that DB2 returns any existing storage allocated for fast index traversal and does not allocate any further storage for this purpose.

500 - 200000

Indicates the storage limit for fast index traversal.

The ZPARM name is INDEX_MEMORY_CONTROL in DSN6SPRM.

Field Name: QWP4IXMC

SELECT FOR UNLOAD (AUTH_COMPATIBILITY)

Shows the AUTH_COMPATIBILITY ZPARM values (DB2 field: QWP4AUTCSU):

1

The unload utility checks whether the user has the SELECT privilege on the target table.

0

The unload utility checks whether the user has the UNLOAD privilege on the target table.

Field Name: QWP4AUTCSU

MATERIALIZE NODET SQLTUDF (MATERIALIZE_NODET_SQLTUDF)

DB2 materializes the result of a user-defined SQL table function that is defined as not deterministic.

• 0=NO
• 1=YES

ZPARM name is MATERIALIZE_NODET_SQLTUDF in DSN6SPRM.

Field Name: QWP4MNSU