SQL Object Defaults Panel (DSNTIP7, DSNTIP71, DSNTIP72)

System Parameters - SQL Object Defaults Panel (DSNTIP7, DSNTIP71, DSNTIP72)

The field labels shown in the following sample layout of System Parameters - SQL Object Defaults Panel (DSNTIP7, DSNTIP71, DSNTIP72) are described in the following section.

SQL OBJECT DEFAULTS PANEL (DSNTIP7,DSNTIP71,DSNTIP72)
-----------------------------------------------------
REORDERED ROW FORMAT (RRF)..................................YES
VARY DS CONTROL INTERVAL (DSVCI)............................YES
TABLE SPACE ALLOCATION IN KB (TSQTY)..........................0
INDEX SPACE ALLOCATION IN KB (IXQTY)..........................0
PAD INDEX BY DEFAULT (PADIX).................................NO
DEFAULT PARTITION SEGSIZE (DPSEGSZ)..........................32
PERCENT FREE FOR UPDATE (PCTFREE_UPD).........................0
DEFINE DATA SETS (IMPDSDEF).................................YES
USE DATA COMPRESSION (IMPTSCMP)..............................NO
LIMIT KEY CONV PART TAB (IX_TB_PART_CONV_EXCLUDE)............NO
PAGE SET PAGE NUMBERING (PAGESET_PAGENUM).....................A
RETRY STOPPED OBJECTS (RETRY_STOPPED_OBJECTS)................NO
RENAME TABLE (RENAMETABLE)...................................NO
PREVENT ALTER LIMITKEY (PREVENT_ALTERTB_LIMITKEY)............NO
PREVENT INDEX PART CREATE (PREVENT_NEW_IXCTRL_PART)..........NO (Db2 11 or later)
DDL MATERIALIZATION (DDL_MATERIALIZATION)......ALWAYS_IMMEDIATE
DEFAULT_INSERT_ALGORITHM (DEFAULT_INSERT_ALGORITHM)...........2
FTB NON UNIQUE INDEX (FTB_NON_UNIQUE_INDEX).................YES 
INDEX MEMORY CONTROL (INDEX_MEMORY_CONTROL)................AUTO
INDEX MEMORY STORAGE LIMIT................................1,208

REORDERED ROW FORMAT (RRF)

In Db2 12 this field is a serviceability field.

A value of YES shows that reordered row format is enabled.

This field corresponds to field REORDERED ROW FORMAT on installation panel DSNTIP7. The ZPARM name is RRF in DSN6SPRM.

Field Name: QWP4RRF

VARY DS CONTROL INTERVAL (DSVCI)

Indicates whether Db2 optimizes VSAM CONTROL INTERVAL to page size for data set allocation.

Install parameter VARY DS CONTROL INTERVAL on panel DSNTIP7, or ZPARM DSVCI in DSN6SYSP.

Field Name: QWP1VVCI

TABLE SPACE ALLOCATION IN KB (TSQTY)

Shows the amount of space in KB for primary and secondary space allocation for Db2-defined data sets for table spaces created without the USING clause. 0 indicates that Db2 uses standard defaults.

Install parameter TABLE SPACE ALLOCATION on panel DSNTIP7, or ZPARM TSQTY in DSN6SYSP.

Field Name: QWP1TSQT

INDEX SPACE ALLOCATION IN KB (IXQTY)

Shows the amount of space in KB for primary and secondary space allocation for Db2-defined data sets for index spaces created without the USING clause. 0 indicates that Db2 uses standard defaults.

Install parameter INDEX SPACE ALLOCATION on panel DSNTIP7, or ZPARM IXQTY in DSN6SYSP.

Field Name: QWP1IXQT

PAD INDEX BY DEFAULT (PADIX)

Shows whether new indexes are be padded by default.

• YES indicates that a new index is padded unless the NOT PADDED option is specified on the CREATE INDEX statement.
• The default value, NO, indicates that a new index is not padded unless the PADDED option is specified on the CREATE INDEX statement.

Install parameter PAD INDEXES BY DEFAULT on installation panel DSNTIPE, or ZPARM PADIX in DSN6SPRM.

Field Name: QWP4PDIX

DEFAULT PARTITION SEGSIZE (DPSEGSZ)

The default segment size to be used for a partitioned table space when the CREATE TABLESPACE statement does not include the SEGSIZE parameter. This field corresponds to field DEFAULT PARTITION SEGSIZE on installation panel DSNTIP7. The ZPARM name is DPSEGSZ IN DSN6SYSP.

Field Name: QWP1DPSS

PERCENT FREE FOR UPDATE (PCTFREE_UPD)

Specifies the default percentage of each page that Db2 leaves as free space in a table space when a table in this table space is populated. This value applies only to table spaces whose definitions do not include PCTFREE and for UPDATE.

This value corresponds to field PERCENT FREE FOR UPDATE on installation panel DSNTIP71. The ZPARM name is PCTFREE_UPD in DSN6SPRM.

Field Name: QWP4PFUP

DEFINE DATA SETS (IMPDSDEF)

Defines the underlying data sets when a table space (TS) that is contained in an implicitly created database is created.

Install parameter DEFINE DATA SETS on panel DSNTIP7 or ZPARM IMPDSDEF in DSN6SYSP.

Field Name: QWP1DIDS

USE DATA COMPRESSION (IMPTSCMP)

Shows whether data compression in table spaces in implicitly defined databases is used.

Install parameter USE DATA COMPRESSION on panel DSNTIP7 or ZPARM IMPTSCMP in DSN6SYSP.

Field Name: QWP1CITS

LIMIT KEY CONV PART TAB (IX_TB_PART_CONV_EXCLUDE)

Shows whether to include all columns in the partitioning key during conversion from index-controlled partitioning to table-controlled partitioning:

NO

Includes all columns

YES

Includes trailing columns only if they affect partitioning

This field corresponds to field EXCLUDE PART KEY ELEMENTS on installation panel DSNTIP71. The ZPARM name is IX_TB_PART_CONV_EXCLUDE in DSN6SPRM.

Field Name: QWP4XPKE

PAGE SET PAGE NUMBERING (PAGESET_PAGENUM)

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

Install parameter PAGE SET PAGE NUMBERING on panel DSNTIP71, or ZPARM PAGESET_PAGENUM in DSN6SPRM.

Field Name: QWP4PSPN

RETRY STOPPED OBJECTS (RETRY_STOPPED_OBJECTS)

Specifies whether Db2 should immediately reject requests for a stopped object or retry them, up to the IRLM timeout limit, if the object is restarted.

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.

Install parameter RETRY STOPPED OBJECTS on panel DSNTIP72, or ZPARM RETRY_STOPPED_OBJECTS in DSN6SPRM.

Field Name: QWP4RSO

RENAME TABLE (RENAMETABLE)

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:

YES

ALLOW_DEP_VIEW_SQLTUDF

NO

DISALLOW_DEP_VIEW_SQLTUDF

Install parameter RENAME TABLE on panel DSNTIP72, or ZPARM RENAMETABLE in DSN6SPRM.

Field Name: QWP4ERTS

PREVENT ALTER LIMITKEY (PREVENT_ALTERTB_LIMITKEY)

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.

Install parameter PREVENT ALTER LIMITKEY on panel DSNTIP71, or ZPARM: PREVENT_ALTERTB_LIMITKEY in DSN6SPRM.

Field Name: QWP4PALK

PREVENT INDEX PART CREATE (PREVENT_NEW_IXCTRL_PART) (Db2 11 or later)

Determines whether Db2 does not allow the creation of new index-controlled partitioned tables. This subsystem parameter ensures that new partitioned tables use table-controlled partitioning, which is the preferred partitioning method for non-universal table spaces.

NO

Specifies that new index-controlled partitioned tables can be created. NO is the default value.

YES

Specifies that new partitioned table spaces must use table-controlled partitioning. A CREATE INDEX statement must not attempt to create an index-controlled partitioned table.

Install parameter PREVENT INDEX PART CREATE on panel DSNTIP71, or ZPARM: PREVENT_NEW_IXCTRL_PART in DSN6SPRM.

Field Name: QWP4PCIP

DDL MATERIALIZATION (DDL_MATERIALIZATION)

Specifies when Db2 materializes changes to the definition of an object. This value corresponds to field DDL MATERIALIZATION on installation panel DSNTIP71. ZPARM name is DDL_MATERIALIZATION in DSN6SPRM.

Field Name: QWP4DDLM

DEFAULT_INSERT_ALGORITHM (DEFAULT_INSERT_ALGORITHM)

Specifies the default algorithm for inserting data into table spaces. This value corresponds to the field DEFAULT INSERT ALGORITHM on installation panel DSNTIP71. The ZPARM name DEFAULT_INSERT_ALGORITHM is in DSN6SPRM.

Field Name: QWP4DINA

FTB NON UNIQUE INDEX (FTB_NON_UNIQUE_INDEX)

Fast index traversal for non-unique indexes is enabled when index_memory_control is enabled. Valid values are YES and NO. This value corresponds to field FTB NON UNIQUE INDEX installation panel DSNTIP71. ZPARM name: FTB_NON_UNIQUE_INDEX in DSN6SPRM.

Field Name: QWP4FTBUO

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

INDEX MEMORY STORAGE LIMIT

The INDEX_MEMORY_CONTROL storage limit in megabytes, when INDEX_MEMORY_CONTROL is (selected,n).

Field Name: QWP4IXMCS