-SET LOG (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.

FL 500

Start of changeYou 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.End of change

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

To execute this command, you must use a privilege set of the process that includes one of the following privileges or authorities:
  • 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.

Syntax

Read syntax diagramSkip visual syntax diagram SET LOG SINGLE1BOTH,LOGLOAD( integer)CHKTIME( integer)SUSPENDRESUMENEWLOG( data-set-name)REMOVELOG( data-set-name)COPY(log-copy)
Notes:
  • 1 If you specify SINGLE, you must also specify LOGLOAD or CHKTIME.

Option descriptions

The following option overrides the LOGLOAD subsystem parameter that is specified in the CHECKPOINT FREQ field on installation panel DSNTIPN.
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.
Start of changeFL 500 REMOVELOG( data set name)End of change
Start of change

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.
End of change
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.

Start of changeFL 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.End of change

Examples

Example 1: Initiate a system checkpoint without modifying the current LOGLOAD value.
-SET LOG LOGLOAD(0)
Example 2: Modify the system checkpoint interval to every 150000 log records.
-SET LOG LOGLOAD(150000)
Example 3: Suspend logging activity.
-SET LOG SUSPEND
Example 4: Resume logging activity.
-SET LOG RESUME
Example 5: Change checkpoint scheduling to use both log records and time.
-SET LOG BOTH CHKTIME(10) LOGLOAD(500000)
Example 6: Change checkpoint scheduling to use only log records.
-SET LOG SINGLE LOGLOAD(500000)
Example 7: Change checkpoint scheduling to use both log records and time by using the existing value for the parameter that was controlling checkpoints and the default value for the other.
-SET LOG BOTH

Example 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

Start of change Example 9: Successfully remove an active log data set from the BSDS. End of change

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      
FL 500 Example 10: Remove an active log data set that is currently in use.

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.