-SET LOG command (Db2)
You can use the Db2 command SET LOG to modify checkpoint frequency, to suspend or resume logging, to add a new active log, or remove an existing active log.
The Db2 command SET LOG modifies the checkpoint frequency that is specified during installation. This command also overrides the value that was specified in a previous invocation of the SET LOG command.
For some options, the changes that SET LOG makes are temporary; at restart, Db2 uses the values that were used for restart. The LOGLOAD value takes effect following the next system checkpoint. You can use SET LOG to suspend or resume logging and update activity for the current Db2 subsystem.
You can also use the NEWLOG option of SET LOG to add an active log to the BSDS and REMOVELOG to delete an active log dataset from the BSDS. Changes made by NEWLOG and REMOVELOG are pervasive.
If Db2 is in dual logging mode, it is recommended that you allocate active logs in pairs.
Environment
This command can be issued from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program that uses the instrumentation facility interface (IFI).
Data sharing scope: Member
Authorization
- ARCHIVE privilege
- SYSOPR authority
- SYSCTRL authority
- SYSADM authority
Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. A logged-on z/OS user ID must be defined in RACF or a similar security server.
Syntax
Option descriptions
- SINGLE
- Specifies that only a single option, either LOGLOAD or CHKTIME, is
used to control checkpoint frequency. If you specify SINGLE, you must specify LOGLOAD or CHKTIME.
SINGLE is optional. If you do not use this option, the existing mode, SINGLE or BOTH, is used. If you specify SINGLE but BOTH was previously in effect, the mode changes to SINGLE.
- BOTH
- Specifies that both LOGLOAD and CHKTIME are used control checkpoint frequency. The threshold that is reached first triggers a system checkpoint and resets both thresholds.
BOTH is optional. If you do not use this option, the existing mode, SINGLE or BOTH, is used. If you specify BOTH but SINGLE was previously in effect, the mode changes to BOTH and the input values for LOGLOAD and CHKTIME are used. If you do not specify LOGLOAD or CHKTIME, the existing value for the option not specified remains in effect. If the value for CHKTIME or LOGLOAD has not been set and the option is not specified in the SET LOG command, the default value for the parameter that is not specified is used.
If a SET LOG command changes SINGLE mode to BOTH, and that command does not specify CHKTIME, the default value for CHKTIME is 3 minutes.
- LOGLOAD(integer)
- Specifies the number of log records that Db2 writes between the start of successive checkpoints. You can specify a value of 0 to initiate a system checkpoint without modifying the current LOGLOAD value. Possible values of integer are:
- 1000 to 16000000, if SINGLE is explicitly specified in the SET LOG command
- 0, or 1000 to 16000000, if SINGLE mode is in effect
- 0, or 1000 to 99999999, if BOTH is explicitly specified in the SET LOG command, or BOTH mode is in effect
The default value for LOGLOAD is 500000 log records.
If Db2 was previously running in SINGLE mode, CHKTIME was previously controlling checkpoints, and you specify a value greater than 0 for LOGLOAD, LOGLOAD controls future checkpoints, and CHKTIME is not used.
- CHKTIME( integer )
- Specifies the number of minutes between the start of successive checkpoints.
integer is any integer from 0 to 5. Specifying 0 starts a system checkpoint immediately without modifying the checkpoint frequency.
Possible values of integer are:- 1 to 5, if SINGLE is explicitly specified in the SET LOG command
- 0 to 5, if SINGLE mode is in effect
- 0 to 5, if BOTH is explicitly specified in the SET LOG command, or BOTH mode is in effect
If Db2 was previously running in SINGLE mode, LOGLOAD was previously controlling checkpoints, and you specify a value greater than 0 for CHKTIME, CHKTIME controls future checkpoints, and LOGLOAD is not used.
- SUSPEND
-
Specify to suspend logging and update activity for the current Db2 subsystem until SET LOG RESUME is issued. Db2 externalizes unwritten log buffers, takes a system checkpoint (in non-data-sharing
environments), updates the BSDS with the high-written RBA, and then suspends the update activity.
Message DSNJ372I is issued and remains on the console until update activity resumes.
SUSPEND quiesces the writes for 32-KB pages and the data set extensions for all page sizes. If a 32-KB page write is in progress when you take volume-level copies of your data, SUSPEND prevents an inconsistent copy of a 32-KB page when the copy of your data is restored. If a data set extension is in progress, SUSPEND prevents inconsistencies between the VSAM catalog and the Db2 data set when the copy of your data is restored.
This option is not allowed when the ARCHIVE LOG or STOP DB2 commands activate a system quiesce. Update activity remains suspended until SET LOG RESUME or STOP DB2 is issued. (Also, when logging is suspended, do not issue the ARCHIVE LOG command without also specifying CANCEL OFFLOAD.)
Recommendation: Do not keep log activity suspended during periods of high activity or for long periods of time. Suspending update activity can cause timing-related events such as lock timeouts or Db2 and IRLM diagnostic dumps.
- RESUME
-
Specify to resume logging
and update activity for the current Db2 subsystem and to
remove the message DSNJ372I from the console. Resumes 32-KB page writes and data set extensions
for pages of all sizes.
Recommendation: Issue this command from a z/OS console or from the installation SYSADM ID to avoid possible contention during command authorization checking. When logging is suspended by the SET LOG SUSPEND command, the contention that is generated by holding the log-write latch can cause command-authorization checking to hang until logging resumes.
- NEWLOG( data set name )
- Adds a newly defined
active log data set to the active log inventory. If Db2 can
open the newly defined data set, the log is added to the active log inventory in the BSDS data sets
and is immediately available for use without recycling Db2.
Before you issue this command, you must define the data set with IDCAMS.
Important: Ensure that each archive log data set is at least as large as your active log data sets. See Active log data sets storage requirements for more information about requirements for active log data sets.Recommendation: Format the new active log data set with the DSNJLOGF utility before you issue the SET LOG command to add the data set to the active log inventory. - FL 500 REMOVELOG(
data set name)
Removes the specified active log data set from the active log inventory in the BSDS data set without recycling Db2. If it is in use and cannot be deallocated at this time, it is marked removal pending. The command should be reissued when the log is no longer in use to remove it from the BSDS. A removal pending status prevents it from being used again.
Recommendation: You should issue the command -DISPLAY LOG DETAIL and MVS command D GRS,RES=(SYSDSN,data-set-name) before issuing the SET LOG REMOVELOG command to see more usage information on the log data set.- COPY( log copy )
- specifies the log copy number for the new active log data set or the active log data set to be removed.
The value of log copy can be 1 or 2. Specify 1 for copy 1 of the active log data set or 2 for copy 2 of the active log data set.
Recommendation: If Db2 is in dual logging mode, add log data sets for both copy 1 and copy 2 of the new active log data set.
Usage notes
How LOGLOAD and CHKTIME values affect Db2 performance: LOGLOAD and CHKTIME values can affect the amount of time needed to restart Db2 after abnormal termination. A large value for either option can result in lengthy restart times. A low value can result in Db2 taking excessive checkpoints. However, when you specify LOGLOAD(0) or CHKTIME(0), the checkpoint request is synchronous when issued from a batch job, and it is asynchronous when issued from a z/OS or TSO console.
The behavior of LOGLOAD(0) and CHKTIME(0) is different in a data sharing environment. Avoid issuing SET LOG LOGLOAD(0) or SET LOG CHKTIME(0) in the data sharing environment if logging has been suspended with SET LOG SUSPEND on any member in the group. If you specify LOGLOAD(0) or CHKTIME(0), the synchronous checkpoint might be suspended until all logging has been resumed when you issue the SET LOG RESUME command.
Use the DISPLAY LOG command to display the current checkpoint parameters. You can see if CHKTIME, LOGLOAD, or both are being used to schedule checkpoints.
The value that you specify for LOGLOAD or CHKTIME is reset to the value specified in the subsystem parameter when Db2 is restarted. If you load a different value by issuing the command SET SYSPARM, the new value is used.
When to suspend logging: Specify SET LOG SUSPEND before making a remote copy of the entire database and logs for a system-level, point-in-time recovery or disaster recovery. You can make remote copies with peer-to-peer remote recovery (PPRC) and FlashCopy®. Suspending logging to make a remote copy of the database lets you avoid quiescing update activity. Read-only activity continues while logging is suspended.
The backup that is made between the SET LOG SUSPEND and the SET LOG RESUME window might contain uncommitted data. If you must restore the entire Db2 subsystem to the time when the log was suspended, restore the entire database and logs from the backup, and then restart Db2 to recover the entire Db2 subsystem to a consistent state.
Avoiding deadlocks when using SET LOG SUSPEND in a data sharing environment: To avoid deadlock in a data sharing environment, issue the SET LOG SUSPEND command on one data sharing member first, wait until it completes, and then issue the command for the remaining members.
Order in which newly defined active log data sets are used: Currently, if you do not stop and start Db2 during the period after you add new active log data sets to the inventory and before Db2 uses those active log data sets, Db2 uses the active log data sets in the reverse order from the order in which you add them to the active log inventory with the SET LOG command. For example, suppose that the active log inventory contains data sets DS01, DS02, and DS03, and you add data set DS04 and then data set DS05. If data set DS03 is active, and you issue the ARCHIVE LOG command, the new active log becomes DS05. However, if you stop and start Db2 during the period after you add new active log data sets and before Db2 uses them, the order of use might be different.
This behavior might change in the future, so schemes for adding and switching active logs should not depend on this order.
This process differs from the order that the database manager uses when data sets are added to the active log inventory through the DSNJU003 utility (change log inventory utility). After the NEWLOG control statement is used to add new data sets, the database manager uses active log data sets in the order in which they are added.
Suppose that the active log inventory contains DS01, DS02, and DS03. You run DSNJU003 with two NEWLOG control statements. The first control statement adds data set DS04, and the second control statement adds data set DS05 to the active log inventory. After DSNJU003 runs, the data sets are listed in the BSDS in the order DS01, DS02, DS03, DS04, DS05. When the current active log data set is DS03, and the database manager switches to a new active log data set, it switches to data set DS04.
FL 500 Removing active logs: You can remove an active log data set from the BSDS without a Db2 outage by issuing SET LOG REMOVELOG instead of using the DSNJU003 change log inventory utility. If the log is not in use, Db2 deallocates it and deletes the log entry from the BSDS. If the active log is in use, it is flagged with the removal pending status, so it cannot be used for new log requests. You will need to monitor the usage and issue the command again once it is no longer in use.
Examples
-SET LOG LOGLOAD(0)-SET LOG LOGLOAD(150000)-SET LOG SUSPEND-SET LOG RESUME-SET LOG BOTH CHKTIME(10) LOGLOAD(500000)-SET LOG SINGLE LOGLOAD(500000)-SET LOG BOTHExample 8: Add copies of a new active log data set to the active log inventory (Db2 is in dual logging mode).
//JOBLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//NEWLOG EXEC PGM=IKJEFT01,DYNAMNBR=20
//DSNTRACE DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN1)
-SET LOG NEWLOG(DSNCAT.LOGCOPY1.DS04) COPY(1)
-SET LOG NEWLOG(DSNCAT.LOGCOPY1.DS05) COPY(1)
-SET LOG NEWLOG(DSNCAT.LOGCOPY1.DS06) COPY(1)
-SET LOG NEWLOG(DSNCAT.LOGCOPY1.DS07) COPY(1)
-SET LOG NEWLOG(DSNCAT.LOGCOPY2.DS04) COPY(2)
-SET LOG NEWLOG(DSNCAT.LOGCOPY2.DS05) COPY(2)
-SET LOG NEWLOG(DSNCAT.LOGCOPY2.DS06) COPY(2)
-SET LOG NEWLOG(DSNCAT.LOGCOPY2.DS07) COPY(2)
END
/*
//SYSIN DD *
/* The following messages are issued:
DSNJ363I ) DSNJW106 COPY1 LOG DATA SET
DSNCAT.LOGCOPY1.DS04 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY1 LOG DATA SET
DSNCAT.LOGCOPY1.DS05 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY1 LOG DATA SET
DSNCAT.LOGCOPY1.DS06 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY1 LOG DATA SET
DSNCAT.LOGCOPY1.DS07 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY2 LOG DATA SET
DSNCAT.LOGCOPY2.DS04 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY2 LOG DATA SET
DSNCAT.LOGCOPY2.DS05 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY2 LOG DATA SET
DSNCAT.LOGCOPY2.DS06 ADDED TO THE ACTIVE LOG INVENTORY
DSNJ363I ) DSNJW106 COPY2 LOG DATA SET
DSNCAT.LOGCOPY2.DS07 ADDED TO THE ACTIVE LOG INVENTORY
Example 9:
Successfully remove an active log data set from the BSDS.
READY
DSN S(DB2A) R(1) T(1)
DSN
-SET LOG REMOVELOG(DSNC000.DB2A.LOGCOPY1.DS03) COPY(1)
DSNJ392I -DB2A DSNJW106 COPY1 LOG DATA SET
DSNC000.DB2A.LOGCOPY1.DS03 REMOVED FROM THE ACTIVE LOG INVENTORY
STARTRBA=000000000001A70A3000 ENDRBA=000000000001A77AAFFF
STARTLRSN=00DA76F7AC9E34A69400 ENDLRSN=00DA76F7B0D54EC70600
STATUS=TRUNCATED, REUSABLE
DSN9022I -DB2A DSNJC001 '-SET LOG' NORMAL COMPLETION
DSN
END
READY
END
The following messages are issued and displayed on the console:
2$HASP373 REMOVE STARTED - INIT 4 - CLASS K - SYS STL1
DSNJ392I -DB2A DSNJW106 COPY1 LOG DATA SET 454
DSNC000.DB2A.LOGCOPY1.DS03 REMOVED FROM THE ACTIVE LOG INVENTORY
STARTRBA=000000000001A70A3000 ENDRBA=000000000001A77AAFFF
STARTLRSN=00DA76F7AC9E34A69400 ENDLRSN=00DA76F7B0D54EC70600
STATUS=TRUNCATED, REUSABLE
SMF000I REMOVE STEP01 IKJEFT01 0000
$HASP395 REMOVE ENDED - RC=0000
Issue the command with the REMOVELOG option:
READY
DSN S(DB2A) R(1) T(1)
DSN
-SET LOG REMOVELOG(DSNC000.DB2A.LOGCOPY1.DS11) COPY(1)
DSNJ393I -DB2A DSNJW106 COPY1 LOG DATA SET
DSNC000.DB2A.LOGCOPY1.DS11 MARKED AS REMOVAL PENDING IN THE ACTIVE
LOG INVENTORY
STARTRBA=000000000001C0207000 ENDRBA=000000000001C15B6FFF
STARTLRSN=00DA777A0F3429040200 ENDLRSN=00DA777A190DE76C6000
STATUS=REMOVAL PENDING
DSN9022I -DB2A DSNJC001 '-SET LOG' NORMAL COMPLETION
DSN
END
READY
END
The following messages are issued and displayed on the console:
DSNJ393I -DB2A DSNJW106 COPY1 LOG DATA SET 597
DSNC000.DB2A.LOGCOPY1.DS11 MARKED AS REMOVAL PENDING IN THE ACTIVE
LOG INVENTORY
STARTRBA=000000000001C0207000 ENDRBA=000000000001C15B6FFF
STARTLRSN=00DA777A0F3429040200 ENDLRSN=00DA777A190DE76C6000
STATUS=REMOVAL PENDING
SMF000I REMLOG STEP01 IKJEFT01 0000
$HASP395 REMLOG ENDED - RC=0000
The log was flagged as removal pending. The SET LOG REMOVELOG command must be reissued when the log is no longer in use.
It is recommended that you check if any Db2 members are still using the log before executing the SET LOG command using the DISPLAY LOG DETAIL command and the MVS D GRS command.