SET Statement Syntax

SET statements can be used to change Db2 ZPARMs, manage active log data sets, free page sets of all users, and refresh (or replace) exit routines.
dm19
SET statements can be used to change Db2 ZPARMs, manage active log data sets, free page sets of all users, and refresh (or replace) exit routines.
The SET statement has the following format:
[SET]
command
command-operand
[FOR(
ssid
)]
  • command
    Specifies the function to perform. This command is the same as the ZPARM macro operand when possible.
  • command
    -
    operand
    Specifies the function options to use.
  • ssid
    Specifies the target subsystem for the requested action. This parameter is required when the subsystem ID is not specified in a DEFAULTS statement.
    If the subsystem ID is not specified in the SET statement, this value defaults to the most recent DEFAULTS subsystem ID value.
    If the subsystem ID is not specified in the DEFAULTS or the SET statement, an error message is generated.
SET Statement Commands and Operands
Use the following command conventions when specifying your SET statement syntax:
  • Enter uppercase characters as shown.
  • Lowercase italicized characters represent user-specified variables.
  • The | character denotes an OR condition.
  • Required parameters appear in braces { }.
  • Optional parameters appear in brackets [ ].
  • Any power of 2 value of 1024 or greater can be represented as
    n
    K. For example, you can enter 8K to represent 8192.
  • Use the BLANKS keyword for character operand data to set the desired value to blanks.
The following commands and operands can be used with the SET statement:
  • ADDLOG DSN(
    name
    ) COPY(1 | 2)
    Adds a new active log data set.
    • name
      Specifies the data set name. Allocate the data set before issuing this command. Data set verification is not performed. Fully qualify the data set name without quotes.
    • 1 | 2
      Specifies which log copy to add the data set to.
  • AEXITLIM VALUE(
    occurrences
    )
    Controls the authorization exit error limit.
    • occurrences
      Specifies how many times the Db2 access control authorization exit can abend before it is shut down.
      Limits:
      0 to 32767
  • BPDMC BPID(
    id
    ) VALUE(
    percentage
    )
    Changes the Data Manager Critical (DMC) threshold for the buffer pool.
    If the buffer pool is deleted and reallocated, the original DMC threshold is in effect.
  • BPPREF BPID(
    id
    ) VALUE(
    value
    )
    Changes the default prefetch quantity size for a buffer pool (in number of pages).
    The prefetch value can be changed only for an allocated buffer pool. The value remains in effect until the buffer pool is deallocated. If the buffer pool is reallocated, Db2 recalculates the prefetch quantity.
  • BPSIZE BPID(id) VALUE([+|]
    value
    [%])
    Changes the size of a buffer pool:
    • id
      Specifies the buffer pool ID as BP0 BP49 and BP32K BP32K9. You can also specify BP8K BP8K9 and BP16K BP16K9.
    • value
      Specifies the buffer pool size. Enter the desired size, or enter an offset (+ or -), or a percentage (%) change from the current value.
  • BPSPT BPID(
    id
    ) VALUE
    (percentage
    )
    Changes the sequential prefetch DISABLE threshold for the buffer pool by the specified percentage.
    If the buffer pool is deleted and reallocated, the original SPT threshold is in effect.
  • CANCEL [CONN (
    connection)
    | AUTH (
    auth-id
    ) | PLAN (
    planname
    ) | CORR (
    corr-id
    ) | ACE (
    ace-address
    ) ] | [TOKEN (
    thread-token
    )]
    Cancels the qualifying threads that are currently running. This command invokes the native Thread Termination cancel command or the Db2 -CANCEL THREAD command to cancel the threads, depending on the thread attributes.
    Specify at least one of the qualifying parameters (CONN, AUTH, PLAN, or CORR). You can optionally specify ACE with any of these parameters, but it cannot be specified alone.
    Alternately, specify the TOKEN parameter. When TOKEN is specified, the CONN, AUTH, PLAN, CORR, and ACE parameters are ignored.
    • connection
      Specifies the connection ID of the threads to cancel.
    • auth-id
      Specifies the authorization ID of the threads to cancel.
    • planname
      Specifies the plan name of the threads to cancel.
    • corr-id
      Specifies the correlation ID of the threads to cancel. If the correlation ID contains special characters, such as blanks, place quotes around the correlation ID value.
    • ace-address
      Specifies the ACE control block address that is associated with the thread to cancel.
    • thread-token
      Specifies the token of the thread to cancel.
      Limits:
      1 to 6 digits
  • DB2CAN [CONN (
    connection)
    | AUTH (
    auth-id
    ) | PLAN (
    planname
    ) | CORR (
    corr-id
    ) | ACE (
    ace-address
    )] | [TOKEN (
    thread-token
    )]
    Cancels the qualifying threads that are currently running. This command invokes the Db2 -CANCEL THREAD command to cancel the threads.
    Specify at least one of the qualifying parameters (CONN, AUTH, PLAN, or CORR). You can optionally specify ACE with any of these parameters, but it cannot be specified alone.
    Alternately, specify the TOKEN parameter. When TOKEN is specified, the CONN, AUTH, PLAN, CORR, and ACE parameters are ignored.
    • connection
      Specifies the connection ID of the threads to cancel.
    • auth-id
      Specifies the authorization ID of the threads to cancel.
    • planname
      Specifies the plan name of the threads to cancel.
    • corr-id
      Specifies the correlation ID of the threads to cancel. If the correlation ID contains special characters, such as blanks, place quotes around the correlation ID value.
    • ace-address
      Specifies the ACE control block address that is associated with the thread to cancel.
    • thread-token
      Specifies the token of the thread to cancel.
      Limits:
      1 to 6 digits
  • DEFAULTS [DB2 (
    ssid
    )]
    Specifies runtime parameters. You can include multiple DEFAULTS in the input stream to reset the default subsystem ID for subsequent statements to use.
    • ssid
      Specifies the subsystem ID.
      If you do not specify this parameter, the subsystem ID in the subsequent SET statement command is used.
      If the subsystem ID is not specified in a SET or SET DEFAULTS statement, a syntax error is generated.
  • DELLOG DSN(
    datasetname
    ) COPY(1 | 2) [FORCE | NOFORCE]
    Deletes the active log data set.
    You cannot delete the current log data set. To delete an active log that has not been offloaded, use the FORCE option.
  • DWQT BPID(
    id
    ) VALUE(
    percentage
    )
    Changes the deferred write threshold for the buffer pool:
    • id
      Specifies the buffer pool ID as BP0 BP49 and BP32K BP32K9. You can also specify BP8K BP8K9 and BP16K BP16K9.
    • percentage
      Specifies the deferred write threshold value. This value represents the percentage of the buffer pool that is unavailable until Db2 schedules asynchronous writes to free up buffers.
      Limits:
      1 to 100
  • FREE
    DATABASE(
    dbname
    )
    PAGESET(
    pageset name
    )
    [STOP | NOSTOP ]
    [RESTART (NONE | RO | UT | RW| FORCE)]
    Frees the specified page set of all users.
    The RESTART option is valid only when the STOP option is used.
  • IRLMRWT VALUE(
    time_value)
    Sets the value of the IRLMRWT parameter. IRLMRWT is an IBM DSNZPARM that specifies the number of seconds to wait before issuing a timeout for a resource.
    Limits:
    1 to 3600
  • REPLACE (
    exitname
    ) DSN(
    datasetname
    )
    {CONNECTION | SIGNON | EDITPROC | VALIPROC | DATE | TIME | FIELDPROC | LOGCAPTURE | AUTH}
    Replaces (or refreshes) an exit routine dynamically.
    • exitname
      Specifies the exit module name. Certain exit types are required to have a specific name or entry point, or both.
      Verify that the name is not in use by another exit or Db2 module. The name can be the same name as the one that is being replaced. If the same name is specified, the current exit is deleted. If a different name is specified, the current exit is not deleted.
      Db2 has specific naming requirements for the CONNECTION, SIGNON, DATE, TIME, LOGCAPTURE, and AUTH (access control authorization) exits. However,
      Thread Termination/Dynamic DSNZPARM
      does not require these names. You can replace any of these exits with a module name of your choice.
    • datasetname
      Specifies the source load library from which the exit is loaded. Fully qualify the data set name without quotes. This library must be APF authorized. Otherwise, the load fails and the prior exit routine is left in place.
    • CONNECTION | SIGNON | EDITPROC | VALIPROC | DATE | TIME | FIELDPROC | LOGCAPTURE | AUTH
      Specifies the exit type that is being loaded.
    The following exit types have specific considerations:
    • Exits for edit procedures and validation procedures—Exits for edit and validation procedures must be loaded currently by Db2 for the exit names to be validated. If an exit is not currently loaded, a load on the module is performed so that the specified exit is used when the module is needed. The module use count is also set so that the count never drops to zero, therefore requested modules always stay loaded for use.
    • Access control authorization exit—When the access control authorization exit is refreshed, a new exit work area is allocated. The exit is invoked with initialization parameters so that the work environment can be properly initialized. If the exit routine abends during initialization, the current routine, its work area, and its setting (enabled/disabled) are left intact. A dump of the abend is written to a system dump data set. If the previous exit routine was disabled, refreshing the exit enables Db2 to use the exit again.
      To disable an active exit from being invoked, refresh the exit with the default Db2 exit module in SDSNLOAD. Doing so generates an RC12, which tells Db2 not to invoke the exit routine again until you re-enable it with another exit refresh.
  • RESTOPOBJ {YES|NO}
    Sets the value of the RETRY_STOPPED_OBJECTS parameter. RETRY_STOPPED_OBJECTS is an IBM DSNZPARM that specifies whether to retry processing a stopped object until the IRLMRWT value is reached. (IRLMRWT is described earlier in this article.)
  • VDWQT BPID(
    id
    ) VALUE(
    percentage
    [,
    pages
    ])
    Changes the vertical deferred write threshold for the buffer pool.
    • id
      Specifies the buffer pool ID as BP0 BP49 and BP32K BP32K9. You can also specify BP8K BP8K9 and BP16K BP16K9.
    • percentage
      Specifies the threshold value. This value represents the percentage of the virtual pool that can be consumed by updated pages of a page set before deferred writes are invoked for that page set.
      Limits:
      0 to 90
    • pages
      Specifies the threshold as the number of updated pages in the virtual buffer pool for a given page set. If the
      percentage
      value is 0, then the
      pages
      value is used. Otherwise, the
      pages
      value is ignored and the
      percentages
      value is used.
      Limits:
      0 to 9999
  • VPPSEQT BPID(
    id
    ) VALUE(
    percentage
    )
    Changes the parallel sequential steal threshold for the virtual pool. The ALTER BUFFERPOOL command is used to change the threshold.
    • id
      Specifies the buffer pool ID as BP0 BP49 and BP32K BP32K9. You can also specify BP8K BP8K9 and BP16K BP16K9.
    • percentage
      Specifies the threshold value. This value represents the percentage of sequential steal threshold for virtual pool buffers that can be used for accessing pages sequentially using parallel I/O.
      Limits:
      0 to 100
  • VPSEQT BPID(
    id
    ) VALUE(
    percentage
    )
    Changes the sequential steal threshold for the virtual pool.
    • id
      Specifies the buffer pool ID as BP0 BP49 and BP32K BP32K9. You can also specify BP8K BP8K9 and BP16K BP16K9.
    • percentage
      Specifies the threshold value. This value represents the percentage of the virtual pool that can be used for accessing pages sequentially.
      Limits:
      0 to 100
Example: Typical SET statements
The following statement enables Db2 authorization on subsystem DB12:
SET AUTH YES FOR(DB12)
The following statement increases the size of buffer pool BP32K on subsystem DB12 by 15 percent:
SET BPSIZE BPID(BP32K) VALUE(+15%) FOR(DB12)
Example: Use the DEFAULTS command in a SET statement
The SET DEFAULTS statement lets you set the subsystem ID for subsequent statements. You can include multiple SET DEFAULTS statements in the input stream.
In this example, SET DEFAULTS sets the subsystem ID to DB12 for the next statement. Therefore, the second statement sets MONSIZE to 16 KB on subsystem DB12:
SET DEFAULTS DB2 (DB12) SET MONSIZE VALUE (16K)