DB2 in the affairs of ID

  DB2 in the affairs of ID Author: Yiming time :2005-08-12 17:39 Source: Internet Zebian: Xiaoyu Abstract: DB2 in the affairs of ID from the DB2 log records in search of the only unit identifier 

  Level: Intermediate 

  Knut Stolze 
  DB2 WebSphere Information Integration Development, IBM Germany 
  June 20, 2005 

  You need to identify operating in IBM ® DB2 ® Universal Database ™ (DB2 UDB) server on the current work unit? »    Knut Stolze will show you how to complete this task, is to use the storage process and each log records only the first in the affairs of the identifier. 

  Profile Under normal circumstances, the database server and interactive applications need to understand the internal affairs of the database engine deal with.    Application procedures for issuing a series of SQL statements (such as INSERT, UPDATE, DELETE, SELECT or CREATE TABLE), the Panel at the end of the end of Operation Services COMMIT statements or ROLLBACK.    COMMIT statement concluded Affairs and inform the database server to store all the lasting changes.    On the other hand, ROLLBACK the database server to withdraw affairs by all the changes. 

  In this world, not only "conventional" applications.    There are also special applications, such applications usually have very special needs.    For example, the database system involved in the replication of data among applications (such as DB2 Replication [2]) need to know information services.    When the copy of the data changes, usually together with the needs of the individual services in all the changes made to copy down.    Therefore, the need to know in which affairs are conducted changes, and each branch must be the only logo. 

  DB2 UDB did not provide a special registry or any other direct way to retrieve the affairs of identifier, for internal purposes it is necessary.    In the following section, I will give you an example of how to DB2 UDB combination of several properties, to generate Affairs identifier.    The general idea is to visit the log records, the log records are DB2 system to ensure that in case of collapse, hard drive failure or power outage restoration of valuable data and written.    DB2 ID will be stored in the internal affairs of each log records.    The implementation of some additional steps to set the right trigger for the retrieval and preparation of the affairs of ID specific log records. 

  Services and work units (UOW) 
  Read the DB2 Manual [1], you usually see the term work unit, known as UOW.    In other databases in the literature, you will often see the term affairs.    The two terms mean the same concept that they are in business or work unit to atomic, consistent, isolated and sustained implementation of a set of SQL statements.    Therefore, in this article, I will use this as a synonym for two terms. 

  Access Services ID 
  DB2 UDB fringe for a variety of database systems to manage their own API.    One of these API allows access to the database engine in operation in the course of writing the log records.    These logs record for INSERT, UPDATE, DELETE, REORGANIZE and you used to modify a database table data management system itself or the many other operations and the write. 

  Each log records in their head, including a record trigger write the affairs of the only identifier (six-byte coding) [3].    This identifier is to ensure that all the durability of the database services, is expected to prevent the accident or interruption of the operation (such as system failures) is necessary.    DB2 automatically safeguard the identifier. 

  This method of storage in the log records in the affairs of the first identifier.    We use the first log API access log records, and extract from the Service ID.    To do this, remember: 

  Log head of the restrictions on the use of API API means that the first log database configuration parameters must be set to RECOVERY LOGRETAIN and / or parameters USEREXIT set to ON.    Otherwise, the API can not crawl through the log records.    Most of the production system has been used both in a set, rather than the use of log records. 

  In addition, the call was to db2ReadLog as other SQL statement to deal with.    The API is not dynamic complex in a statement by the support of the statement.    However, the flip-flop (and table functions) can not perform any arbitrary SQL operation, and was restricted to a dynamic complex sentences.    Therefore, you can not call to trigger embedded in the existing process in getTransactionId. 

  •   Logging must be written request to set identifier for the current affairs. 
  •   As a single database engine will log (which may be split into a number of documents) with services for all these matters with many or all of the possible changes in data, resulting in at the same time, the contents into the log records, there is a need to find real Of the current affairs of log records. 

  Together the two issues can be resolved.    We use the built-in functions DB2 GENERATE_UNIQUE only to generate value.    Insert the value in the table, the trigger logging write.    The next step will be to read all the new log records (since the last INSERT operation since the record) until you find the only values that include the log records so far.    It is what we have to find the log records and extracted from the Service ID.    Finally, insert the operation was revoked, so that the table does not expire accumulated data.    The entire process as shown in Figure 1. 

  Figure 1. Extracted from the database logs the logic of affairs identifier 
Capture Services ID process

  For storage of the entire process described above in a single package was logical storage process.    In this way, all the applications have a simple, standardized way to retrieve Affairs ID.    DB2 API to any direct calls are not necessary.    Service ID process will be stored as a string (that is, type CHAR (12) of the value) to return. 

  In the process of compiling storage before, you need to create a group called TA_ID_FORCE_LOGWRITE the table.    The table in the process within the visit.    The table itself is very simple structure, only one out, the only function GENERATE_UNIQUE generate value stored in them.    As shown in the use of a list of SQL statements to create the table: 

  List 1. TA_ID_FORCE_LOGWRITE create table 

  CREATE TABLE ta_id_force_logwrite (unique_val VARCHAR (18) FOR BIT DATA NOT NULL, CONSTRAINT taid_pk PRIMARY KEY (unique_val)) @ 

  The process to achieve C + +, is used to access the asynchronous read log API [4], it uses embedded SQL to implement the necessary SQL operation.    Save for the rollback point in the process of implementation of the INSERT operations, to trigger the write log records.    The SQL statement in the process can take full advantage of the DB2 UDB Version 8.2 of SQL support functions. 

  Therefore, we only generate value will be inserted in the table, in a single sentence and it will crawl to the code stored procedure call.    This action in the following list of completed in the middle part of italics.    If you need an earlier version of DB2 in the use of this process, this logic must be split into several independent of the SQL statement. 

  DB2 logs is to use the API db2ReadLog read.    The very beginning, the system will call the API to determine the current log serial number (LSN).    Completing this step can be avoided for this process called pre-written log records.    After all, we are interested in only a single log records: INSERT written by that operation. 

  INSERT after the operation, all new logging will be crawled.    For each record, we have to check to see that it is written for the INSERT operation.    If it is, and the insertion of data include the use of INSERT statements in the course of the only value, then we will find the necessary log records, you can return to the correct Affairs of the identifier. 

  Before leaving the process, we need to deal with rolls back to the beginning of the preservation of set points.    In this way, beyond the scope of the process will not modify any data retention. 

  The integrity of the process of the code as shown in the list 2.    2 in the list, the API to log the call is set to bold, SQL statements show that in italics.    Other parameters related to only part of the preparation. 

  List 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 use in sqllib / samples / c / directory in the bldrtn script to compile this storage process.    The script will generate a shared library, shared library will be copied to sqllib / function directory.    Upon completion of this operation, you can list in accordance with the three will be shown during the registration database.    This completed the final step before they can start using the process. 

  List 3. Will be in the process of registration 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 @ 

  Testing process is the final step in the process of inspection function is correct.    4 shows in the list of some of DB2 on the implementation of a command line to automatically shut down the SQL statement.    Top of the scenes that are still active cycle of logging there will be mistakes.    After this, you will see certain revised data, the storage process of calling the result.    Of course, only in the implementation of the COMMIT or ROLLBACK after the current affairs and trigger a log records the write operation, DB2 only the affairs of the allocation of new ID. 

  List 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 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 

  Concluding remarks why DBA would like to identify with the current work unit's ability »    The reasons for so on, the process of copying management is an example.    Here on the use of the skills, to take full advantage of the unique features of DB2 UDB, and a better understanding of the background database operation. 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: