DB2不记日志的 LOB 字段与 BLOCKNONLOGGED 参数、HADR同步的关系

结论：
1. BLOCKNONLOGGED 开启的情况下，无法创建含有 NOT LOGGED LOB 字段的表
2. 对于已经创建完成的表，开启 BLOCKNONLOGGED 阻止不了 NOT LOGGED LOB 字段成功写入， 无论LOB的长度是否超过 INLINE LENGTH 。
3. 长度超过 INLINE LENGTH 的 LOB 字段，无法同步至HADR备机（备机会写入0）。
4. 长度不超过 INLINE LENGTH 的 LOB 字段，可以正常同步至HADR备机。
 

结论1测试：
db2 get db cfg for mqsdb |grep -i blockno
 Block non logged operations            (BLOCKNONLOGGED) = YES
db2 "create table t61(id int, name clob(200) not logged)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL20054N  The operation was not performed because the table is in an invalid
state for the operation. Table name: "lob options prohibited with
blocknonlogged enabled". Reason code: "".  SQLSTATE=55019
db2 update db cfg using blocknonlogged no
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 "create table t61(id int, name clob(200) not logged)"
DB20000I  The SQL command completed successfully.

结论2、3、4测试：

HADR主机上创建 NOT LOGGED 的 LOB 字段，INLINE LENGTH 为 140， 然后开启 BLOCKNONLOGGED ，发现无论长度多少，都可以插入成功。但长度超过136(140-4)，无法同步至备机，备机会写入0来代替。1. HADR主机

db2 "create tablespace tbs_4k pagesize 4096"

db2 update db cfg using blocknonlogged NO

db2 "create table t613(name clob(409600) not logged ) in tbs_4k"


db2 "select LENGTH,LOGGED,INLINE_LENGTH from syscat.columns where tabname='T613'"

LENGTH      LOGGED INLINE_LENGTH
----------- ------ -------------
     409600 N                140

  1 record(s) selected.


db2 update db cfg using blocknonlogged YES

#
db2 "insert into t613 values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')"
db2 "insert into t613 values('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb')"

db2 "select varchar(name,150) as name from t613"

NAME                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa             
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb             

  2 record(s) selected.

db2 "select length(name) from t613" 

1         
-----------
        136
        137

  2 record(s) selected.

 

备机：备机可读模式下查询会报错
SQL1773N  The statement or command failed because it requires functionality
that is not supported on a read-enabled HADR standby database. Reason code =
"3".做HADR切换后，查询的两条记录中，有一条为“空”

db2 takeover hadr on db db0603
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

db2 connect to db0603

db2 "select length(name) from t613"

1         
-----------
        136
        137

  2 record(s) selected.
 
db2 "select varchar(name,150) as name from t613"

NAME                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa             
                                                                                                                                                     

  2 record(s) selected.

 
附：

1. 关于为什么 INLINE LENGTH为140，但长度超过136就不能 INLINE 了？ 这个应该是因为有4字节的 overhead

2. 为什么 BLOCKNONLOGGED 开启的情况下，无法阻止 NOT LOGGED 字段的写入？ 根据信息中心的说法， BLOCKNONLOGGED 只针对以下操作有影响

Usage notes
If blocknonlogged is set to YES, then CREATE TABLE and ALTER TABLE statements will fail if one of the following conditions is true:
--The NOT LOGGED INITIALLY parameter is specified.
--The NOT LOGGED parameter is specified for a LOB column.
--A CLOB, DBCLOB, or BLOB column is defined as not logged.

If blocknonlogged is set to YES, then the LOAD command fails if the following situations exist:
--You specify the NONRECOVERABLE option.
--You specify the COPY NO option.