DATA CAPTURE option and its effect on DB2 Logging

2019独角兽企业重金招聘Python工程师标准>>>

Welcome back, today’s blog is about the option DATA CAPTURE and what effects it will have on the DB2 log while logging the INSERT, UPDATE & DELETE statements. Let’s first discuss a few points about DB2 log. The log contains all of the information necessary to perform restart, recovery and rollback. Images of data rows before and after a change, as well as information on page sets and their status, show what update information has been externalized to DASD, and starting points for restart or recovery by page set. The log can be considered a (very) long sequential data set. It is keyed by either RBA or LRSN (in DB2 data sharing subsystems only). 

Both the RBA and LRSN are essentially sequential counters, and they are used to locate a particular record needed for restart or recovery. DB2 writes change information to log buffers in memory and periodically externalizes the recovery data to the DB2 active logs. Circumstances that will force the externalization include the following: 
• Log buffers full, 
• Write threshold reached (typically 20 percent of log buffers), and 
• Forced by DB2 (application COMMIT point). 

As updates are executed, the activity is logged in the DB2 recovery log. The DB2 recovery log stores before and after images of data that is being updated, before images of data being deleted, and after images of data being inserted. These images are stored in the form of undo/redo records. 

The Level of detail of these undo/redo records changes based on what DATA CAPTURE option is existing for the table. DATA CAPTURE option is used in the CREATE or ALTER TABLE statements. The two settings of DATA CAPTURE are NONE and CHANGES. The default is NONE. 

Now, let’s see how DATA CAPTURE NONE effects the logging for the DML statements. 
1. INSERT:- DB2 logs the complete after image of the new row 
2. UPDATE:- DB2 logs from the 1st byte that changes to the last byte that changes , A ‘before’ image with the above range is logged and An ‘after’ image with the above range is logged. To explain this better, let’s assume that a row in employee Table has been updated for the Employee number of Kathy from 123 to 124. In the log, only 124 is recorded for this update. 
3. DELETE:- The before image of all rows are logged for tables in a simple or partitioned tablespace. For tables in a segmented Tablespace for DELETE statements without predicates, only the change to the space map page gets logged, for DELETE statements with predicates, the before image of all rows are logged. 

Coming to DATA CAPTURE CHANGES and its effects on logging are as follows 

1. INSERT:- DB2 logs the complete after image of the new row 
2. UPDATE:- DB2 logs the complete ‘before’ image of the row and the complete ‘after’ image of the row. 
3. DELETE:- The before image of all rows are logged. 
If you have a TABLE with DATA CAPTURE NONE and if you are using any of the log analysis tools like IBM DB2 Log Analysis tool or CA Log Analyzer to generate UNDO SQL statements to undo the faulty updates to this table, these tools won’t be able to generate the exact UNDO SQL statement for the UPDATE statements without the help of a previous image copy. But, if the DATA CAPTURE option for the same table is CHANGES the UNDO SQL can be generated without the need of an image copy. 

The downside of DATA CAPTURE CHANGES is, it will increase the logging overhead. So, it is advisable to turn on DATA CAPTURE if the amount of DML activity is minimal on the table but the data in it is very important and you need to investigate immediately if some data has been changed in this table which didn’t come through the standard process. 

That’s all for now!!!. Thanks for reading through. 

Disclaimer:- The statements made in this blog are based upon my current knowledge and the experience I had working with DB2. Before taking any decision based on these statements you must validate them. 

转载于:https://my.oschina.net/uniquejava/blog/832322