Transaction ID in DB2

Transaction ID in DB2

Author: anonymous time: Source: Internet Editor: XiaoYu

Abstract: The transaction ID in DB2

Retrieve the unique identifier of a unit from a DB2 log Level: Intermediate Knut stolze DB2 WebSphere Information Integration Development, IBM Germany June 20, 2005 Do you need to identify the current unit of work running on the IBM DB2 Universal Database (DB2 UDB) server? Knut stolze will show you how to complete this task by using the stored procedure and the unique transaction identifier in each log record header. Introduction Generally, applications that interact with the database server do not need to understand the internal transaction processing of the database engine. The application issues a series of SQL statements (such as insert, update, delete, select, or create table) and runs the final transaction statement commit or rollback at the end of the transaction. The commit statement ends the transaction and notifies the database server to persistently store all changes. On the other hand, rollback enables the database server to cancel all changes made in the transaction. In this world, not just "regular" applications. There are also dedicated applications, which are usually very special. For example, applications that involve data replication between database systems (such as DB2 replication [2]) need to understand transaction information. When the copied data changes, it usually needs to be copied together with all the changes made in a single transaction. Therefore, you need to know which changes are made in which transaction, and each transaction must be uniquely identified. DB2 UDB does not provide a special registry or any other direct method to retrieve the identifier of a transaction, which is required for internal purposes. In the following sections, I will show you how to combine several features of DB2 UDB to generate transaction identifiers. The general idea is access logging, which is written by DB2 to ensure that valuable data is restored in case of a system crash, power failure, or hard disk failure. DB2 stores the internal transaction ID in each log record. Execute some additional setting steps to trigger the writing of specific logging to retrieve the correct transaction ID. Transactions and work units (uow) When reading the DB2 manual [1], you will usually see the termsWork Unit, Abbreviated as uow. In other database documents, you will often see the termsTransactions. These two terms refer to the same concept; both indicate that a set of SQL statements are executed in an atomic, consistent, isolated, and persistent manner in a transaction or unit of work. Therefore, in this article, I will use these two terms as synonyms. Access transaction ID DB2 UDB comes with a variety of APIS for managing the database system itself. One of these Apis allows access to the log records written by the database engine during operations. These log records are written for insert, update, delete, reorganize, and many other operations you have issued to modify data in database tables or the management system itself. Each log record includes a unique identifier (in 6 bytes) of the transaction that triggers the write operation in its header [3]. This identifier is required to ensure the durability of all database transactions and prevent unexpected or unexpected operation interruptions (such as system faults. DB2 automatically maintains this identifier. This method utilizes the transaction identifier stored in the log record header. We use the log header API to access logging and extract the transaction ID from it. Remember: Log header API restrictions Using the log header API means you must configure the database ParametersLOGRETAINSetRECOVERYAnd/or set parametersUSEREXITSetON. Otherwise, it is impossible to retrieve log records through API. Most production systems already use one of these two settings, instead of loop logging. In additiondb2ReadLogIs processed as other SQL statements. The API is notDynamic compound statementA supported statement. However, triggers (and table functions) cannot execute any SQL operations and are limited to dynamic composite statements. Therefore, you cannot embed a call into an existing process in the trigger.getTransactionId. The log must be written into the current transaction for which you want to determine the request identifier. Because the database engine uses a single log (which may be split into several files) for all concurrent transactions, many or all of these concurrent transactions may be changing data, as a result, the content is written to the log record at the same time, so it is necessary to find the log record that truly belongs to the current transaction. Two problems can be solved together. We use the built-in functions of DB2.GENERATE_UNIQUETo generate a unique value. Insert this value into the table to trigger the write operation of log records. The next step is to read all new log records (records since the last insert operation) until a log record containing the unique value is found. It is the log record we are looking for and extracts the transaction ID from it. Finally, the insert operation is revoked so that the table does not accumulate expired data. The entire process 1 is shown. Figure 1. Logic for extracting transaction identifiers from database logs Implement stored procedures The entire logic described above is encapsulated in a single stored procedure. In this way, all applications have a simple and standardized way to retrieve transaction IDs. Any direct call to the DB2 API is not required. The stored procedure returns the transaction ID as a string (that is, the value of type char (12. Before compiling the stored procedure, you must createTA_ID_FORCE_LOGWRITE. This table is accessed during the process. The table structure is very simple, with only one column, FunctionGENERATE_UNIQUEThe generated unique value is stored in it. Use the SQL statement shown in Listing 1 to create the table: Listing 1. Creating a table named ta_id_force_logwrite CREATE TABLE ta_id_force_logwrite ( unique_val VARCHAR(18) FOR BIT DATA NOT NULL, CONSTRAINT taid_pk PRIMARY KEY (unique_val) )@ This process is implemented in C ++ and is used to access the asynchronous log reading API [4]. It uses embedded SQL to perform necessary SQL operations. The storage point is used to roll back the insert operation executed during the process to trigger write operations on log records. The SQL statements in this process can take full advantage of the SQL functions supported by DB2 UDB version 8.2. Therefore, we generate a unique value, insert it into the table, and retrieve it in a single statement to call the stored procedure code. InItalics. To use this process in earlier versions of DB2, you must split the logic into multiple independent SQL statements. DB2 logs use APIsdb2ReadLogRead. At the beginning, the system will call this API to determine the current log serial number (LSN ). This step avoids querying the logs written before calling this process. After all, we are only interested in a single log record: the one written by the insert operation. After the insert operation, all new log records will be retrieved. For each record, we need to check whether it is written for the insert operation. If yes, and the inserted data contains the unique value used in the insert statement, we can find the required log records and return the correct transaction identifier. Before leaving this process, we need to roll back the processing to the starting storage point. In this way, any data modifications beyond the scope of this process will not be retained. The complete code for this process is shown in Listing 2. In Listing 2, the call to the log API is setBoldThe SQL statement is displayedItalics. The other part only involves parameter preparation. Listing 2. Stored Procedure Code #include <string.h> // memset(), memcpy(), strncpy() #include <stdio.h> // sprintf() #include <sqludf.h> #include <db2ApiDf.h> #if defined(__cplusplus) extern "C" #endif int SQL_API_FN getTransactionId( SQLUDF_VARCHAR *taId, SQLUDF_NULLIND *taId_ind, SQLUDF_TRAIL_ARGS) { SQL_API_RC rc = SQL_RC_OK; struct sqlca sqlca; db2ReadLogInfoStruct logInfo; db2ReadLogStruct logData; SQLU_LSN startLsn; SQLU_LSN endLsn; char buffer[64 * 1024] = { '/0' }; // for log record data EXEC SQL BEGIN DECLARE SECTION; char uniqueVal[13] = { '/0' }; EXEC SQL END DECLARE SECTION; // we assume NULL return *taId_ind = -1; /* * Step 1: Set a savepoint to be able to undo the data modifications */ EXEC SQL SAVEPOINT get_transaction_id ON ROLLBACK RETAIN CURSORS; /* * Step 2: Query the DB2 Log to get the start LSN */ memset(&sqlca, 0x00, sizeof sqlca); memset(&logInfo, 0x00, sizeof logInfo); memset(&logData, 0x00, sizeof logData); logData.iCallerAction = DB2READLOG_QUERY; logData.piStartLSN = NULL; logData.piEndLSN = NULL; logData.poLogBuffer = NULL; logData.iLogBufferSize = 0; logData.iFilterOption = DB2READLOG_FILTER_OFF; logData.poReadLogInfo = &logInfo; rc = db2ReadLog(db2Version810, &logData, &sqlca); if (rc < 0) { memcpy(SQLUDF_STATE, "38TA0", SQLUDF_SQLSTATE_LEN); strncpy(SQLUDF_MSGTX, "Could not query log for last LSN", SQLUDF_MSGTEXT_LEN); goto exit; } else if (sqlca.sqlcode) { memcpy(SQLUDF_STATE, "38TA1", SQLUDF_SQLSTATE_LEN); snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN, "SQL error while " "reading log records. SQLCODE = %d, SQLSTATE=%s", sqlca.sqlcode, sqlca.sqlstate); goto exit; } memcpy(&startLsn, &logInfo.nextStartLSN, sizeof startLsn); /* * Step 3: Force a log record to be written * * Insert a unique value into our table, which triggers a log record to be * written. The same value is also returned right away so that we can use * it to search through the new log records. */ EXEC SQL SELECT value INTO :uniqueVal FROM NEW TABLE ( INSERT INTO ta_id_force_logwrite VALUES ( GENERATE_UNIQUE() ) ) AS t(value); if (sqlca.sqlcode) { memcpy(SQLUDF_STATE, "38TA2", SQLUDF_SQLSTATE_LEN); snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN, "SQL error while " "triggering log record. SQLCODE = %d, SQLSTATE=%s", sqlca.sqlcode, sqlca.sqlstate); goto exit; } /* * Step 4: Search through the new log records to find our INSERT */ while (true) { char *ptr = NULL; char *transactionId = NULL; sqlint32 recordLength = 0; memset(&sqlca, 0x00, sizeof sqlca); memset(&logInfo, 0x00, sizeof logInfo); memset(&logData, 0x00, sizeof logData); memset(&endLsn, 0xFF, sizeof endLsn); logData.iCallerAction = DB2READLOG_READ_SINGLE; logData.piStartLSN = &startLsn; logData.piEndLSN = &endLsn; logData.poLogBuffer = buffer; logData.iLogBufferSize = sizeof buffer; logData.iFilterOption = DB2READLOG_FILTER_OFF; logData.poReadLogInfo = &logInfo; rc = db2ReadLog(db2Version810, &logData, &sqlca); if (rc < 0) { memcpy(SQLUDF_STATE, "38TA3", SQLUDF_SQLSTATE_LEN); sprintf(SQLUDF_MSGTX, "Could not read log record. rc = %d", (int)rc); goto exit; } else if (sqlca.sqlcode == SQLU_RLOG_READ_TO_CURRENT) { memcpy(SQLUDF_STATE, "38TA4", SQLUDF_SQLSTATE_LEN); strncpy(SQLUDF_MSGTX, "Last log record reached prematurely.", SQLUDF_MSGTEXT_LEN); goto exit; } else if (sqlca.sqlcode) { memcpy(SQLUDF_STATE, "38TA5", SQLUDF_SQLSTATE_LEN); snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN, "SQL error while " "reading log records. SQLCODE = %d, SQLSTATE=%s", sqlca.sqlcode, sqlca.sqlstate); goto exit; } if (logInfo.logBytesWritten < 20) { memcpy(SQLUDF_STATE, "38TA6", SQLUDF_SQLSTATE_LEN); strncpy(SQLUDF_MSGTX, "Log Manager Header of record too small.", SQLUDF_MSGTEXT_LEN); goto exit; } memcpy(&startLsn, &logInfo.nextStartLSN, sizeof startLsn); // the data in the buffer starts with the LSN, followed by the Log // Manager Header; skip the LSN ptr = buffer; ptr += sizeof(SQLU_LSN); // get the length of the log record (plus LSN) recordLength = *(sqlint32 *)ptr + sizeof(SQLU_LSN); ptr += 4; // verify that this is a "Normal" log record if (*(sqlint16 *)ptr != 0x004E) { continue; } ptr += 2; // skip behind the Log Manager Header (to the DMS Log Record Header); // (we do not have "Compensation" records here and "Propagatable" // doesn't occur either) ptr += 2 + // flags 6; // LSN of previous record in same transaction // remember the location of the transaction id transactionId = ptr; ptr += 6; // now we are at the beginning of the DML Log Record Header if (ptr - buffer + 18 + 4 > recordLength) { continue; } // check that the "Function identifier" in the DMS header indicates an // "INSERT" log record ptr += 1; if (*(unsigned char *)ptr != 118) { continue; } // skip to the record data ptr += 5 + // remainder of DMS Log Record Header 2 + // padding 4 + // RID 2 + // record Length 2 + // free space 2; // record offset // the record contains data if the 1st byte of the record header (the // record type) is 0x00 or 0x10, or if the bit 0x04 is set if (*ptr != 0x00 && *ptr != 0x10 && (*ptr & 0x04) == 0) { continue; } ptr += 4; // we reached the record data and the unique value can be found after // the record length ptr += 1 + // record type 1 + // reserved 2 + // length of fixed length data 4; // RID // that's where the unique value should be // once we found the unique value, extract the transaction ID and // convert it to a string if (memcmp(ptr, uniqueVal, 13) == 0) { int i = 0; char *result = taId; for (i = 0; i < 6; i++) { sprintf(result, "%02hhx", ptr[i]); result += 2; } *result = '/0'; *taId_ind = 0; break; // found the correct log record } } exit: EXEC SQL ROLLBACK TO SAVEPOINT get_transaction_id; return SQLZ_DISCONNECT_PROC; } You can usesqllib/samples/c/DirectorybldrtnScript to compile this stored procedure. This script will generate a shared library, which will be copiedsqllib/functionDirectory. After this operation is complete, you can register the process to the database as shown in listing 3. This process can be used only after the last step is completed. Listing 3. register the process to the database CREATE PROCEDURE getTransactionId ( OUT transactionId CHAR(12) ) SPECIFIC getTaId DYNAMIC RESULT SETS 0 MODIFIES SQL DATA NOT DETERMINISTIC NEW SAVEPOINT LEVEL LANGUAGE C EXTERNAL NAME 'transaction-id!getTransactionId' FENCED THREADSAFE NO EXTERNAL ACTION PARAMETER STYLE SQL PROGRAM TYPE SUB NO DBINFO@ Test process The last step is to check whether the process functions correctly. Listing 4 shows some SQL statements that are automatically submitted and closed after being executed on the DB2 command line. The preceding scenario indicates the error that occurs when the cyclic log is activated. After that, you will see the results of various calls to the stored procedure after some data modifications are made. Of course, DB2 allocates a new transaction Id only when the commit or rollback is executed and the current transaction triggers write operations on the log records. Listing 4. Test process $ db2 -c- -td@ db2 => CALL getTransactionId(?)@ SQL0443N Routine "*ACTIONID" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL error while reading log records. SQLCODE = -2651, SQLS". SQLSTATE=38TA1 db2 => ? sql2651@ SQL2651N The log records associated with the database can not be asynchronously read. Explanation: The asynchronous read log API was used against a connected database which does not have LOG RETAIN or USER EXITS ON. Only databases which are forward recoverable may have their associated logs read. User Response: Update the database configuration for the database, identified to the asynchronous read log API, turning LOG RETAIN and/or USER EXITS ON. db2 => UPDATE DATABASE CONFIGURATION USING LOGRETAIN ON@ db2 => BACKUP DATABASE sample TO /dev/null@ Backup successful. The timestamp for this backup image is : 20050305214103 db2 => TERMINATE@ $ db2stop force && db2start && db2 -c- -td@ db2 => CONNECT TO sample@ db2 => CALL getTransactionId(?)@ Value of output parameters -------------------------- Parameter Name : TRANSACTIONID Parameter Value : 200503052054 Return Status = 0 db2 => COMMIT@ DB20000I The SQL command completed successfully. db2 => CALL getTransactionId(?)@ Value of output parameters -------------------------- Parameter Name : TRANSACTIONID Parameter Value : 200503052054 Return Status = 0 db2 => CREATE TABLE t ( a INT )@ DB20000I The SQL command completed successfully. db2 => CALL getTransactionId(?)@ Value of output parameters -------------------------- Parameter Name : TRANSACTIONID Parameter Value : 200503052055 Return Status = 0 db2 => ROLLBACK@ DB20000I The SQL command completed successfully. db2 => CALL getTransactionId(?)@ Value of output parameters -------------------------- Parameter Name : TRANSACTIONID Parameter Value : 200503052056 Return Status = 0 Conclusion Why does DBA want to be able to identify the current unit of work? The reason is different. The management of the replication process is an example. The skills described here can be used to take full advantage of the unique features of DB2 UDB and further understand the background operations of the database.