06-12-12, 07:58 #1Registered User
- Join Date
- Jan 2012
Unanswered: data capture for sysibm.systables
I have a question about the data capture for sysibm.systables
While reading the DB logs using db2ReadLog() I see that I get records for the table sysibm.systables inspite of its data capture set to none
select data_capture from sysibm.systables where creator='SYSIBM' and name='SYSTABLES'
The above query returns an N.
But every time I run the following query I get a record for sysibm.systables from db2ReadLog()
alter table usertable data capture changes
How is it that I am seeing the changes to sysibm.systables being captured in the DB log when the data capture for it is set to N ?
06-12-12, 08:20 #2Registered User
Provided Answers: 11
- Join Date
- Apr 2006
all the tables that get modified are in the log
only if datacapture is on - they save more data and before data if needed
in the logrecord there is an indication if dc is on so they can identify the rows needed for captureBest Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified
06-12-12, 09:12 #3Registered User
- Join Date
- Jan 2007
- Jena, Germany
I guess there is a misconception on what the log is for. Logging always happens on all DB2 tables, including the tables of the DB2 catalog. That is done for recovery purposes in case of a system crash to get the database back to the most current point in time right before the crash. Also, the log can be used to undo changes done in a SQL statement and/or transaction in case the statement fails or the transaction is rolled back. Another, secondary usage for the log data is in replication situations where you want to get all changes from system A (where the log is written) to some other system B to keep A and B in sync. DATA CAPTURE changes a bit what exactly is being logged so that replication tools have sufficient information to replicate the data changes to the target systems.
For example, consider the case where some attributes of a row are updated. For recovery purposes, DB2 only needs to log the actual changes using before/after images for those changes. For replication, you may need more information like the complete row or the value in a unique key column. Otherwise, you couldn't identify uniquely the row in the target system on which the update should be applied. So if DATA CAPTURE has been turned on, the additional details are written to the normal log as well.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development