db2ReadLog API - Read log records

The db2ReadLog API reads log records from the Db2® database logs, or queries the Log Manager for current log state information. This API can only be used with recoverable databases. A database is recoverable if the database configuration parameters logarchmeth1 or logarchmeth2, or both, are not set to OFF.

Authorization

One of the following authorities:

SYSADM
DBADM

Required connection

Database

API include file

db2ApiDf.h

API and data structure syntax

SQL_API_RC SQL_API_FN
  db2ReadLog (
       db2Uint32 versionNumber,
       void * pDB2ReadLogStruct,
       struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2ReadLogStruct
{
   db2Uint32 iCallerAction;
   db2LRI *piStartLRI;
   db2LRI *piEndLRI;
   char *poLogBuffer;
   db2Uint32 iLogBufferSize;
   db2Uint32 iFilterOption;
   db2ReadLogInfoStruct *poReadLogInfo;
   db2LRI *piMinRequiredLRI;
   char *piStartTime;
} db2ReadLogStruct;

typedef SQL_STRUCTURE db2ReadLogInfoStruct
{
   db2LRI initialLRI;
   db2LRI firstReadLRI;
   db2LRI nextStartLRI;
   db2LRI firstReusedLRI;
   db2Uint32 logRecsWritten;
   db2Uint32 logBytesWritten;
   db2Uint32 timeOfLRIReuse;
   db2TimeOfLog currentTimeValue;
   db2Uint32 futureUse1;
   db2LSN oldestInFlightLSN;
   db2LRI finalLRI;
} db2ReadLogInfoStruct;

typedef SQL_STRUCTURE db2TimeOfLog
{
   db2Uint32 seconds;
   db2Uint32 accuracy;
} db2TimeOfLog;

typedef SQL_STRUCTURE db2ReadLogFilterData
{
   db2LRI         recordLRIType1;
   db2LRI         recordLRIType2;
   db2Uint32      realLogRecLen;
   db2int32       sqlcode;
} db2ReadLogFilterData;

/******************************************************************************
** db2LRI data structure
** db2LRI data structure parameters
**
** lriType
** The type of LRI structure.  The following are the valid types:
**    - DB2READLOG_LRI_1 : part1 is the LFS and part2 is the LSN for
**                         the log record
**    - DB2READLOG_LRI_2 : part1 is the logStreamID and part2 is the LSO for
**                         the log record
** (See "Log record header" in Db2 documentation for the meaning of LFS, 
**  LSN, LSO.)
**    
** part1     
** Part 1 of the LRI structure.
**
** part2
** Part 2 of the LRI structure.
**
*******************************************************************************/
typedef SQL_STRUCTURE db2LRI
{
   db2Uint64       lriType;            /* LRI type                            */
   db2Uint64       part1;              /* Part 1 of the LRI structure         */
   db2Uint64       part2;              /* Part 2 of the LRI structure         */
} db2LRI;

/* db2LRI types                                                               */
#define DB2READLOG_LRI_1 1             /* LRI type 1                          */
#define DB2READLOG_LRI_2 2             /* LRI type 2                          */

db2ReadLog API parameters

versionNumber: Input. Specifies the version and release level of the structure passed as the second parameter, pDB2ReadLogStruct.
pDB2ReadLogStruct: Input. A pointer to the db2ReadLogStruct structure.
pSqlca: Output. A pointer to the sqlca structure.

db2ReadLogStruct data structure parameters

iCallerAction

Input. Specifies the action to be performed.

DB2READLOG_READ: Read the database log from the starting log sequence to the ending log sequence number and return log records within this range.
DB2READLOG_READ_SINGLE: Read a single log record (propagatable or not) identified by the starting log sequence number.
DB2READLOG_QUERY: Query the database log. Results of the query are sent back through the db2ReadLogInfoStruct structure.

piStartLRI

Input. The starting log record identifier (LRI) specifies the starting point for reading the log. When using lriType DB2READLOG_LRI_1, part1 (LFS) and part2 (LSN) do not need to be the start of an actual log record.

When using DB2READLOG_LRI_2, part1 (logStreamID) must be for the connecting partition and part2 (LSO) must be the start of a log record.

piEndLRI

Input. The ending LRI specifies the end point for reading the log. This value must be greater than the piStartLRI parameter, and does not need to be the start or end of an actual log record.

poLogBuffer

Output. The buffer holds log records returned by the db2ReadLog API. The size of the buffer is specified by the iLogBufferSize parameter. The log records returned are stored sequentially in this buffer, each with a prefix that is based on iFilterOption:

If the iFilterOption is ON, the db2ReadLogFilterData structure will be prefixed to each log record.
If the iFilterOption is OFF, each log record in the buffer is prefixed by two 24-byte db2LRI structures.

iLogBufferSize

Input. Specifies the size, in bytes, of the poLogBuffer buffer. This buffer must be large enough to hold a single log record.

iFilterOption

Input. Specifies the level of log record filtering to be used when reading the log records. Valid values are:

DB2READLOG_FILTER_OFF: Read all log records in the given LRI range.
DB2READLOG_FILTER_ON: Reads only log records in the given LRI range marked as propagatable. This is the traditional behavior of the asynchronous log read API. The log records that are returned when this value is used are documented in the "Db2 log records" topic. All other log records are for IBM internal use only and are therefore not documented.

poReadLogInfo

Output. A structure detailing information regarding the call and the database log.

piMinRequiredLRI

Input. This field is for future functionality. Have the application set this field to 0.

piStartTime

Input. A character string containing a Coordinated Universal Time (UTC) timestamp in ISO format. This field can be specified only for DB2READLOG_QUERY. Pass a NULL value in all other cases. The query call attempts to return an LRI that either matches or is earlier-than the timestamp provided. The query call goes no further back than the last database restore, database rollforward, or topology life change event. The resulting LRI is returned in nextStartLRI. This field is referenced by the API only if the API versionNumber is set to db2Version11580 or later. Use of this field is supported on Db2 11.5.8.0 and later servers.

db2ReadLogInfoStruct data structure parameters

initialLRI: The first LRI used, or to be used, by the database after activation.
firstReadLRI: The first LRI present in poLogBuffer parameter.
nextStartLRI: The start of the next log record to be read by the caller. Using this LRI as the start of the next read, instead of the end of the last log record in the poLogBuffer parameter, prevents log records that have already been filtered from being rescanned. This is because some log records are filtered and not returned in the poLogBuffer parameter. Additionally, if the database is queried using a start time (DB2READLOG_QUERY with piStartTime), this field holds the LRI that most closely maps to the start time specified.
firstReusedLRI: The first LRI to be reused due to a database restore or rollforward operation.
logRecsWritten: The number of log records written to poLogBuffer parameter.
logBytesWritten: The total number of bytes of data written to poLogBuffer parameter.
timeOfLRIReuse: The time at which the LRI represented by firstReusedLRI was reused. The time is the number of seconds since January 1, 1970 (midnight UTC/GMT).
currentTimeValue: The current time according to the database.
futureUse1: This field is for future functionality. The current version of Db2 always returns a value of 0.
oldestInFlightLSN: The LSN of the oldest inflight transaction.
finalLRI: For DB2READLOG_QUERY callers, the final LRI used by the database (end of logs). The field is set by the API only when the API versionNumber is set to db2Version11580 or later. Use of this field is supported on Db2 11.5.8.0 and later servers. On older versions of Db2 server, the value is set to (0, 0, 0) (lriType, part1, part2).

db2TimeOfLog data structure parameters

seconds: The number of seconds since January 1, 1970 (midnight UTC/GMT).
accuracy: A high accuracy counter that allows callers to distinguish the order of events when comparing timestamps that occurred within the same second.

db2ReadLogFilterData data structure parameters

Output. The db2ReadLogFilterData structure holds metadata for the log record, The following parameters are used:

recordLRI

LRI of the following log record. There are two LRIs in the db2ReadLogFilterData structure:

recordLRIType1 LRI of the log record. This is a type 1 LRI.
recordLRIType2 LRI of the log record. This is a type 2 LRI.

realLogRecLen

The physical log record length in the Db2 logs.

sqlcode

This field is non-zero if an error occurs while trying to decompress the compressed row image in the log record. If an error occurs, the field contains an integer representing the SQL code associated with the error. For permanent errors, SQL0204N is commonly returned. Resubmitting the API request can return with the same error. For transient errors, the SQL code returned corresponds to the cause of the error, which can require a user action to rectify.

Usage notes

If the requested action is to read the log, you must provide a log record identifier range and a buffer to hold the log records. This API reads the log sequentially, bounded by the requested LRI range, and returns log records associated with tables defined with the DATA CAPTURE CHANGES clause, and a db2ReadLogInfoStruct structure with the current active log information. If the requested action is a query of the database log (indicated by specifying the value DB2READLOG_QUERY), the API returns a db2ReadLogInfoStruct structure with the current active log information.

Note: db2ReadLog API can not be used to read logs written before a drop member operation. Attempting to read those logs results in a SQL1273N error.

To use the Asynchronous Log Reader, first query the database log for a valid starting LRI. Following the query call, the read log information structure (db2ReadLogInfoStruct) contains a valid starting LRI (in the initialLRI member), to be used on a read call. The value used as the ending LRI on a read is one of the following values:

A value greater than initialLRI
(0000 0000 0000 0001, FFFF FFFF FFFF FFFF, FFFF FFFF FFFF FFFF) (lriType, part1, part2), which is interpreted by the asynchronous log reader as the end of the current log.

The propagatable log records that are read within the starting and ending LRI range are returned in the log buffer. If the iFilterOption option is set to DB2READLOG_FILTER_ON, the LRI is replaced with the db2ReadLogFilterData data structure, in the buffer. Descriptions of the various Db2 log records returned by db2ReadLog can be found in the "Db2 log records" topic.

To read the next sequential log record after the initial read, use the nextStartLRI field returned in the db2ReadLogStruct structure. Resubmit the call, with this new starting LRI and a valid ending LRI. The next block of records is then read. An sqlca code of SQLU_RLOG_READ_TO_CURRENT means that the log reader has read to the end of the current active log.

This API reads data from the Db2logs. Label-based access control (LBAC) is not enforced on such logs. Thus, an application that calls this API can gain access to table data if the caller has sufficient authority to call the API and is able to understand the log records format.

The db2ReadLog API works on the current database connection. If multiple database connections are created with the same process, then use the concurrent access APIs to manage the multiple contexts.

Calling the db2ReadLog API from an application can result in an error when the application disconnects from the database if a commit or rollback is not performed before the disconnect:

A CLI0116E error might be generated if the db2ReadLog API is called from a CLI application.
A SQL0428N error might be generated if the db2ReadLog API is called from an embedded SQL application written in C.

Workaround 1: For non-embedded SQL applications, set autocommit mode on before calling the db2ReadLog API.

Workaround 2: Issue a COMMIT or ROLLBACK statement after calling the db2ReadLog API and before disconnecting from the database.

db2ReadLog usage in pureScale environment

In a Db2 pureScale® environment, the call sequence for the db2ReadLog API remains unchanged from previous versions.

To begin reading log records, the caller can specify DB2READLOG_READ or DB2READLOG_READ_SINGLE. The LRI passed by the caller does not necessarily exist on a log steam, in which case the database manager returns the next log record.

The db2ReadLog API returns two LRI structures in the output buffer. The first LRI structure is used to read through the logs sequentially, and the second LRI structure identifies the log record within the log stream to which it belongs.

In a Db2 pureScale environment, the database has one log stream for each member. The db2ReadLog API supports reading all of the log streams and merging these to return the log records to the caller. This causes the data changes to be returned in the order in which they took effect.

The Db2 data server can still read the log files of inactive members or offline members. The db2ReadLog API might wait for Member Crash Recovery to complete on a member before returning with more log records.

In specific situations, the commit log record time might be slightly earlier than the commit occurred in the application.

db2ReadLog usage in partitioned database environment

In a partitioned database environment, the database is divided into database partitions, each having its own log stream. The db2ReadLog API only returns log records from the particular database partition that the application is connected to. To see and process all log records from the database, the application must connect to each database partition and merge the log records returned from the db2ReadLog API on each stream.

A transaction in a partitioned database environment can make changes to data on multiple database partitions. On each partition, the log records for the transaction are identified by the transaction identifier in the log record header. This transaction identifier is only meaningful within this database partition, and log records for the same transaction on another database partition can have a different transaction identifier in the log record header. The only way to identify that these log records belong to the same user transaction is by examining the MPP identifier of the transaction field of the MPP subordinator prepare log record and the MPP coordinator commit log record.

db2ReadLog usage to map a start time to a LRI

As of Db2 11.5.8.0, in all environments, the db2ReadLog API can map a start time to an LRI. Using the caller action DB2READLOG_QUERY, the piStartTime field can specify a pointer to a Coordinated Universal Time (UTC) timestamp in ISO format. The API attempts to find a log record that either matches or is earlier than the timestamp provided and returns the associated LRI. If a match is found, the nextStartLRI is set. This LRI can then be used as a start LRI to the API for subsequent log data read calls.

Note:

When using the db2Version11580 API, a Db2 11.5.8.0 client that specifies a valid piStartTime fails if it tries to communicate with any Db2 server of a version earlier than Db2 11.5.8.0.
A Db2 11.5.8.0 server supports requests from any pre-Db2 11.5.8.0 client. Requests from these clients do not have the piStartTime parameter, so their behavior is unchanged.
The db2ReadLog API has a lower bound to how far back mapping goes. If a start time stamp is supplied that is too old, and the db2ReadLog API cannot map it to an LRI, then the API returns SQL2650N rc=21.
For efficient time-stamp-to-log-record mapping, the API makes use of the DB2TSCHG.HIS file. For users that set the Db2 registry variable DB2_COLLECT_TS_REC_INFO to OFF, start time mapping can be less efficient.

Backward compatibility of the db2ReadLog API

Any application that passes a version of the db2ReadLogStruct, that precedes Db2 Version 9.8, to a Db2 Version 9.8 or later server fails with a SQL2032N error, which indicates that the db2ReadLogStruct parameter is not valid. Due to the vast differences in log record structure and the introduction of db2LRI, old applications must be rewritten and recompiled using the new db2ReadLog structures.

Db2 clients can use the old db2ReadLogStruct from Db2 Version 9.7 and pass it to a V9.7 server, or use db2ReadLogStruct for Db2 Version 9.8 and pass it to a V9.8 or Db2 V10 server.