what is Transactions and logging parameters

 A transaction is a sequence of one or more SQL statements grouped together as a single unit, typically within an application process.

The commands used to complete or end the transaction are as follows:

 COMMIT

ROLLBACK

Transaction logging is a process to keep track of changes made to a database (by one or more application connections or transactions) as they are made. These changes are recorded in the transaction logs for replay purposes. The following section describes the database configuration parameters related to transaction logging:

 logbufsz: This parameter specifies the amount of memory to use as a buffer for log records before writing these records to disk. The default value is 256 pages, however the Db2 configuration advisor will generally set the size to 2,151 pages (4 K page size).

logfilsiz: This parameter specifies the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written before the file becomes full and a new log file is required. The default value is 1,000 pages (4 K page size), however the Db2 configuration advisor updates the value based on initial workload statistics. The upper limit for this configuration parameter is 63.99 GB (64 pages short of 64 GB).

logprimary: This parameter specifies the number of primary log files to be pre-allocated. The default is 3, however the Db2 configuration advisor updates the value based on initial workload statistics. If the logsecond parameter is set to -1, logprimary should be set to <= 256. If the logsecond parameter does not have a value of -1, then (logprimary + logsecond) <=256.

logsecond: This parameter specifies the number of secondary log files that are created and used for recovery log files. The secondary log files are created only when needed. The default is 10, however the Db2 configuration advisor updates the value based on initial workload statistics. When you set this parameter to -1, it will activate infinite active log space so that there is no limit on the size or the number of in-flight transactions running on the database. The logarchmeth1 configuration parameter must be set to a value other than OFF or LOGRETAIN.

newlogpath: This parameter specifies a string of up to 242 bytes to specify the location where the active transaction log files are stored. This must always be a fully qualified path name. In both pureScale and ESE environments, the database partition number and a log stream ID are automatically appended to the path, for example, /home/db2inst1/activelog /NODE0000/LOGSTREAM0000/.

overflowlogpath: This parameter specifies a string of up to 242 bytes to specify the location for Db2 databases to find log files needed for a roll-forward operation, as well as a location to store active log files retrieved from an archive location. This must always be a fully qualified path name. If logsecond is set to -1, this directory will be used to store active transaction log files retrieved from the archive location for rollback operation (if necessary).

mirrorlogpath: This parameter specifies a string of up to 242 bytes to create a dual copy of active transaction log files. This must always be a fully qualified path name. If logarchmeth2 is set along with mirrorlogpath, then the database manager archives the mirrorlogpath active transaction logs to the logarchmeth2 location.

blk_log_dsk_ful: This parameter can be set to prevent disk full errors from being generated when the Db2 database system cannot create a new log file in the active log path due to the filesystem full condition. The default is NO. Setting this parameter to YES causes applications to hang when the Db2 database system encounters a log disk full error, thus allowing you to resolve the error and allowing the transaction to complete. Setting this parameter to NO causes transactions to fail and rollback.

blocknonlogged: This parameter specifies whether the database manager will allow NOT LOGGED operations to be executed on the database. The default is NO. If this parameter is set to YES, then the following statements would fail with an error of SQL0628N:

    CREATE TABLE ... NOT LOGGED INITIALLY;

    ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY;

Any NOT LOGGED attribute to LOB columns in the CREATE TABLE statement would fail with an error SQL20054N:

 

 CREATE TABLE ... (...BLOB (1M) NOT LOGGED);

Any LOAD command with NONRECOVERABLE or without the COPY YES option will fail with an error of SQL2032N.

 

max_log: This parameter specifies whether there is a limit to the percentage of the primary log space that a transaction can consume, and what that limit is. The default is 0, which means that there is no limit to the percentage of total primary log space that a transaction can consume. If this is set to a non-zero value, then the non-zero value indicates the percentage of total primary log space that a transaction can consume. If an application violates the max_log setting, it is forced to disconnect from the database with an error of SQL1224N. The Db2 registry variable DB2_FORCE_APP_ON_MAX_LOG controls the behavior of rollback when an application log consumption exceeds the max_log setting. If this registry is set to TRUE using the command db2set DB2_FORCE_APP_ON_MAX_LOG=TRUE -immediate, then the max_log violating application is forced off the database and the unit of work is rolled back. If this is set to FALSE, the max_log violating application can still commit the work completed by previous statements in the unit of work, or it can roll back the work completed to undo the unit of work.

num_log_span: This parameter specifies whether there is a limit to how many log files one transaction can span, and what that limit is. The default is 0, which means that there is no limit to how many log files one single transaction can span. If the value is other than zero, this value would indicate the number of active log files that one active transaction can span. If an application violates this setting, the application is forced to disconnect from the database and the transaction is rolled back.

softmax: This determines the frequency of soft checkpoints and the recovery range during the crash recovery process. This parameter is deprecated and replaced with the new set of parameters, page_age_trgt_mcr and page_age_trgt_gcr.

logarchmeth1: This parameter specifies the media type of the primary destination for logs that are archived from the current active transaction log path. The default is OFF. If this parameter, along with logarchmeth2, are set to OFF, the database is configured as a circular logging enabled database, and point in time database recovery is not possible. When this parameter is set to LOGRETAIN, the database is configured as archive logging enabled and point in time database recovery is possible using the ROLLFORWARD command. When this parameter is set to USEREXIT, the database is configured as archive logging enabled and a user exit program is used to archive and retrieve the log files. This is an old way of archiving the logs. When this parameter is set to DISK, the database is configured as archive logging enabled and logs get archived to the fully qualified storage path specified. When this parameter is set to TSM, the database is configured as archive logging enabled and logs get archived to the Tivoli Storage Manager (TSM) server. When this parameter is set to VENDOR, the database is configured as archive logging enabled and logs get archived to a vendor storage server using the vendor library.

logarchcompr1: This parameter specifies whether the log files written to the primary archive destination are compressed. The default is OFF. If this is set to ON, the log files written to the primary archive location are compressed. If this is set to NX842, the log files written to the primary archive location are compressed using the AIX Power 7 and Power 8 processor's next accelerator algorithm. This option is not available on Intel-based processors. If the logarchmeth1 configuration parameter is set to anything other than DISK, TSM, or VENDOR, the logarchcompr1 parameter will have no effect.

logarchopt1: This parameter specifies a string of options which control log archiving behavior when the primary archived log method, logarchmeth1, is enabled. For example, if you update the logarchopt1 database configuration parameter to -Servername=db2prodclass, the TSM client uses the db2prodclass stanza in the dsm.sys file to determine the management class to be used for archival.

logarchmeth2: This parameter specifies the media type of the secondary destination for logs that are archived from either the current active transaction log path or the mirror log path. The default is OFF. The option supports the DISK, TSM, and VENDOR values. If both logarchmeth1 and logarchmeth2 are set, then the transaction logs get archived from the newlogpath directory to both archive locations. However, if mirrorlogpath is set, the transaction logs are archived from mirrorlogpath to the location of logarchmeth2.

logarchcompr2: This parameter specifies whether the log files written to the secondary archive destination are for logs that are compressed. The default is OFF. If this is set to ON, the log files written to the secondary archive location are compressed. If this is set to NX842, the log files written to the secondary archive location are compressed using the AIX Power 7 and Power 8 processor's next accelerator algorithm. This option is not available on Intel-based processors. If the logarchmeth2 configuration parameter is set to anything other than DISK, TSM, or VENDOR, parameterlogarchcompr2 will have no effect.

logarchopt2: This parameter specifies a string of options which control log archiving behavior when the secondary archived log method, logarchmeth2, is enabled.

failarchpath: This parameter specifies a disk location to which the Db2 database will try to archive log files if the log files cannot be archived to either the primary or the secondary (if set) archive destinations because of a storage problem affecting those destinations.

numarchretry: This parameter specifies the number of attempts that the Db2 database must make to archive a log file to the primary or the secondary archive locations specified in logarchmeth1 or logarchmeth2 before trying to archive log files to the failover directory. The default is five attempts and this parameter will be ignored if the failarchpath database configuration parameter is not set.

archretrydelay: This parameter specifies the number of seconds to wait after a failed archive attempt before trying to archive the log file again from failarchpath to logarchmeth1 or logarchmeth2 locations. The default is 20 seconds.

logindexbuild: This parameter specifies whether index creation, recreation, or reorganization operations are logged so that indexes can be reconstructed during roll-forward recovery operations or HADR log replay procedures. The default is OFF and index objects are marked invalid when the roll-forward operation rolls through index creation, recreation, or reorganization operations. If this parameter is set to ON, index creation, recreation, and reorganization operations are logged and are replayed during roll-forward operations.

log_ddl_stmts: This parameter specifies that extra information regarding Data Definition Language (DDL) statements will be written to the log. The default is NO. If you want to capture the DDL changes such as CREATE TABLE, DROP TABLE, or ALTER TABLE that are to be replayed on the replicated systems like Q replication or change data capture (CDC), set the log_ddl_stmts configuration parameter to YES.

log_appl_info: This parameter specifies that the application information is written at the start of each update transaction into the transaction log. The default is NO and the application user information is written at the end of the transaction. When it is set to YES, the application user information is written at the beginning of the transaction and can be used by the replication capture programs to ignore unwanted transactions based on specific user IDs entered in the IBMQREP_IGNTRAN table.

page_age_trgt_mcr: This parameter specifies the time in seconds for changed pages to be kept in the local buffer pool before they are persisted to table space storage in a single node ESE database, or to table space storage or to the GBP in a pureScale cluster environment. This is the new parameter that replaces the deprecated parameter softmax, which is used to determine the frequency of soft checkpoints. The default is 240 seconds in an ESE environment and 120 seconds in a pureScale environment. This parameter will take effect only when softmax is set to 0.

page_age_trgt_gcr: This parameter specifies the time in seconds for changed pages to be kept in the GBP before the pages are persisted to table space storage. This is applicable only to pureScale databases. The default is 240 seconds. This parameter will take effect only when softmax is set to 0. The page_age_trgt_gcr parameter value must be greater than or equal to the page_age_trgt_mcr parameter value.

trackmod: This parameter specifies whether the database manager will track database modifications so that the backup utility can detect which subsets of the database pages must be examined by an incremental or delta backups and potentially included in the backup image. The default is NO. This parameter should be set to YES to enable the database for incremental and delta backups.