Design FAQ: Why are LOBs logged in the Db2 database? #1
Unanswered
jrickard27
asked this question in
Q&A
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
The db2look command allows you to examine the DDL of a table. For example, we can look at the DDL for a session attribute table (output trimmed):
Unlike the git repository DDL used to create this table, the db2look output shows additional detail - default options that were not explicitly specified.
I'm going to focus on the OBJECT column in this post:
OBJECT BLOB(2097152) INLINE LENGTH 32000 LOGGED NOT COMPACT
. This column is a BLOB (binary LOB aka large object) that supports attributes up to 2MB. The definition is a bit diffferent to most columns. Importantly, it contains the keywordLOGGED
. Why is that?The reason that LOB data types have the
LOGGED
andNOT LOGGED
options is because, unlike alll other data types, they are not automatically logged by the database. This is because they can be large, potentially defined to permit objects up to 2GB. The additional overhead of saving a 2GB object to the transaction log (in addition to saving it in the table itself) is non-trivial. Therefore Db2 gives you the option to not log LOB columns.What are the implications of not logging LOBs? In a word, availability. Say your database storage suffers a failure, and you need to restore the database from backup to its state an instant prior to the failure. When you roll forward the restored database through the transaction logs, you need the LOB changes to be in those logs to recover that data. If the LOBs were not logged, they will contain only zeroes after the recovery completes.
You might be thinking that session data and attrribute data is short lived, and that the ability to recover that data is not that important. The likelihood of a failure as just described is extremely low, and users could log back in with new sessions? All of that is true.
However, there is a stronger reason to keep the logging enabled - HADR replication requires the logging to replicate the LOBs. In an enterprise setting, we normally run HADR to maintain a warm standby copy of the database in peer state at a remote site. Updates to the primary are applied by the standby in sync. This full redundancy allows high availability. If we need to fail over the database to the standby, typically to allow maintenance activities (e.g. operating system patching), we can do this in a few seconds. Application connections will be configured to re-establish automatically, and the end user will be unaware of the switch (although he or she may experience a delayed response).
If you feel none of the benefits to logging LOBs will apply in your situation, you can change the LOB columns to use the
NOT LOGGED
option.Beta Was this translation helpful? Give feedback.
All reactions