Transaction ID in DB2

transaction ID in DB2

Author: anon Time: 2005-08-12 17:39 Source: Internet Zebian: Small fishing

Summary: Transaction IDs in DB2

To retrieve a unique identifier for a work cell from DB2 log records Level: Intermediate Knut Stolze DB2 WebSphere Information Integration Development, IBM Germany June 20, 2005 you need to identify the current unit of work that is running on the Ibm®db2®universal database™ (DB2 UDB) server. Knut Stolze will show you how to do this by using the stored procedure and the unique transaction identifier in each log header. Brief introduction In general, an application that interacts with a database server does not need to understand transactions within the database engine. The application emits a series of SQL statements (such as INSERT, UPDATE, DELETE, SELECT, or CREATE TABLE) that, at the end of the transaction, run the transaction closing sentence COMMIT or ROLLBACK. The COMMIT statement ends the transaction and tells the database server to persist all changes. ROLLBACK, on the other hand, causes the database server to undo all changes made in the transaction. In this world, there is not only a "regular" application. There are also specialized applications that typically have very specific requirements. For example, an application that involves replicating data between database systems (such as DB2 Replication [2]) needs to know the transaction information. When the replicated data changes, it is often necessary to copy them along with all the changes made in a single transaction. Therefore, you need to know what changes have been 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 for the transaction, which is required for internal purposes. In the following sections, I'll show you how to combine several features of the DB2 UDB to generate a transaction identifier. The general idea is to access the log records, which are written by DB2 to ensure that valuable data is recovered in the event of a system crash, power outage, or hard disk failure. DB2 stores the internal transaction IDs in each log record. Performing additional setup steps can trigger specific logging to retrieve the correct transaction ID. Transactions and units of work (UOW) When reading the DB2 Manual [1], you will typically see the term work unit, abbreviated as UOW. In other database documents, you will often see terminology transactions. These two terms refer to the same concept; they all represent the execution of a set of SQL statements 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 that the database engine writes during the operation. These logs are written for inserts, updates, deletes, REORGANIZE, and many other actions you send to modify data in a database table or manage the system itself. Each log record contains a unique identifier (encoded in 6 bytes) of a transaction that triggers a record write operation in its head [3]. This identifier is required to guarantee the persistence of all database transactions, to prevent unexpected or unexpected operational disruptions, such as system failures. The DB2 automatically maintains this identifier. This method utilizes the transaction identifier that is stored in the log record header. We use the log header API to access the log records and extract the transaction IDs from them. When you do this, remember: Limitations of the log header API Using the log header API means that you must set the database configuration parameter Logretain to RECOVERY and/or set the parameter USEREXIT to on. Otherwise, it is not possible to retrieve logging through the API. Most production systems already use one of these two settings, instead of using circular logging. In addition, calls to Db2readlog are treated as other SQL statements. The API is not a supported statement in a dynamic compound statement. However, triggers (and table functions) cannot perform any arbitrary SQL operations and are limited to dynamic compound statements. Therefore, you cannot embed the call into the procedure gettransactionid that already exists in the trigger. Log records must be written to 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, causing the content to be written to the log record at the same time, so it is necessary to find the log records that are truly part of the current transaction. Two problems can be solved together. We use DB2 's built-in function Generate_unique to generate unique values. Inserts the value into the table, triggering the write operation of the log record. The next step reads all new log records (records since the last INSERT operation) until you find the log record that contains that unique value. It is the log record we are looking for, and the transaction ID is extracted from it. Finally, the insert operation is undone so that the table does not accumulate expired data. The entire process is shown in Figure 1. Figure 1. Extracting the logic of transaction identifiers from the database log Implementing Stored Procedures The entire logic described above is encapsulated in a single stored procedure. In this way, all applications have a simple, standardized way to retrieve the transaction IDs. Any direct calls to the DB2 API are not required. The stored procedure returns the transaction ID as a string (that is, the value of type CHAR (12)). Before you compile the stored procedure, you need to create a table named Ta_id_force_logwrite. The table is accessed within the procedure. The table itself is simple in structure, with only one column, where the unique values generated by the function generate_unique are stored. Create the table using the SQL statement shown in Listing 1: Listing 1. CREATE TABLE Ta_id_force_logwrite CREATE TABLE ta_id_force_logwrite ( unique_val VARCHAR) for BIT DATA not NULL, CONSTRAINT taid_pk PRIMARY KEY ( Unique_val) ) @ This procedure is implemented in C + + to access the asynchronous read log API [4], which uses embedded SQL to perform the required SQL operations. The savepoint is used to rollback the INSERT operation that was performed during the procedure to trigger the write operation of the log record. The SQL statements in this procedure can take full advantage of the SQL features supported by DB2 UDB Version 8.2. So we generate a unique value, insert it into the table, and retrieve it to the stored procedure Code call in a single statement. This action is done in the Italic section of the following list. If you need to use this procedure in earlier versions of DB2, you must split this logic into separate SQL statements. The DB2 log is read using API db2readlog. Initially, the API is invoked to determine the current log sequence number (LSN). Complete this step to avoid logging that is written before the query calls this procedure. After all, we're interested in just a single log record: The one that was written by the INSERT operation. After the INSERT operation, all new log records are retrieved. For each record, we have to check whether it was written for the INSERT operation. If yes, and the inserted data contains the unique value used during the INSERT statement, then we find the required logging and we can return the correct transaction identifier. Before we leave this process, we need to roll back the processing to the savepoint where the settings were started. In this way, any data modifications that exceed the scope of the procedure are not preserved. The complete code for the procedure is shown in Listing 2. In Listing 2, the call to the log API is set to bold , and the SQL statement is displayed in italics. Other parts only involve the preparation of parameters. 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 is 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 (&AMP;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, &AMP;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, &AMP;LOGINFO.NEXTSTARTLSN, sizeof STARTLSN); * * Step 3:force a log record to is 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 we can use * it to search through the new log records. */EXEC SQL SELECT value into:uniqueval from NEW TABLE (INS ERT 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 we INSERT * * * WH Ile (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 (&AMP;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, &AMP;SQLCA); if (RC < 0) {memcpy (sqludf_state, "38ta3", Sqludf_sqlstate_len); sprintf (SQLUDF_MSGTX, "could not read log". rc =%d ", (int) RC); Goto exit; else if (Sqlca.sqlcode = = sqlu_rlog_read_to_current) {memcpy (sqludf_state, "38ta4", Sqludf_sqls Tate_len); strncpy (SQLUDF_MSGTX, "last log 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, &AMP;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 this is a ' Normal ' log record if (* (sqlint16 *) ptr!= 0x004e) {continue; ptr = 2; Skip behind the log Manager header (to the DMS log header); (We don't 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 trans ActionId = ptr; PTR + 6; Now we are are at the beginning of the DML Log record Header if (Ptr-buffer + + 4 > RecordLength) { Continue }//Check that ' 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 +//P Adding 4 +//RID 2 +//Record Length 2 +//free spaces 2; Record offset//The record contains data if the 1st byte to the record header (the//record T ype) is 0x00 or 0x10, or if the bit 0x04 is set if *ptr!= 0x00 &&Amp *ptr!= 0x10 && (*ptr & 0x04) = = 0) {continue; ptr = 4; We reached the record data and the unique value can is 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 T Ransaction ID and//convert it to a string if (memcmp (PTR, uniqueval,) = = 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 G et_transaction_id; Return Sqlz_disconnect_proc; } This stored procedure can be compiled using the Bldrtn script located in the sqllib/samples/c/directory. The script will generate a shared library that will be replicated to the Sqllib/function directory. When this is done, you can register the procedure in the database as shown in Listing 3. After you complete this last step, you can begin to use the procedure. Listing 3. Registering a procedure in a database CREATE PROCEDURE Gettransactionid (out TransactionID CHAR (a)) specific gettaid DYNAMIC result SETS 0 modif IES 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@ Testing process The final step is to check that the procedure is functioning correctly. Listing 4 shows some SQL statements that have been executed on the DB2 command line and are automatically submitted for shutdown. The first scenario describes the error that occurs when looping logging is still activated. After that, you will see the results of various calls to the stored procedure after some data modifications have been made. Of course, DB2 assigns a new transaction ID only if a COMMIT or ROLLBACK is executed and the current transaction triggers a write operation for the log record. Listing 4. Testing 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 isn't asynchronously read. Explanation:the asynchronous read log API was used against a connected database which D OES 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 and identified to the asynchronous Read log API, turning log RETAIN and/or USER EXITS on. DB2 => UPDATE DATABASE CONFIGURATION USING logretain on@ DB2T 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 Gettransactioni D (?) @ Value of output parameters--------------------------Parameter Name:transactioni D Parameter value:200503052054 Return Status = 0 DB2 => commit@ DB200 00I the SQL command completed successfully. DB2 => call Gettransactionid (?) @ Value of output parameters--------------------------Parameter Name:transactioni D 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:transactioni D Parameter value:200503052055 Return Status = 0 DB2 => rollback@ DB2 0000I the SQL command completed successfully. DB2 => call Gettransactionid (?) @ Value of output parameters--------------------------Parameter Name:transactioni D Parameter value:200503052056 Return Status = 0 Closing Why the DBA wants to have the ability to recognize the current work unit. For the most part, management of the replication process is an example. Using the techniques described here, you can take advantage of the unique features of the DB2 UDB, while further understanding the background operations of the database.