Category: DB2 Settings

5

Using TSA/db2haicu to Automate Failover Part 3: Testing, Ways Setup can go Wrong and What to do.

Part 3 in this series is a bit overdue. Parts 1 and 2 were back in April. This is a complicated topic. Please use any procedures here with extreme care, and keep in mind that if you have anything other than the standard two-server HADR-only TSA implementation, these procedures probably aren’t the best idea, as they could break other things....

7

Parameter Wednesday: DB CFG UTIL_HEAP_SZ

DB2 Version This Was Written For 9.7 Parameter Name UTIL_HEAP_SZ Where This Parameter Lives DB CFG How To Check Value > db2 get db cfg for sample |grep UTIL_HEAP_SZ Utilities heap size (4KB) (UTIL_HEAP_SZ) = 70982 OR > db2 “select name, substr(value,1,12) value, substr(deferred_value,1,12) deferred_value from sysibmadm.dbcfg where name=’util_heap_sz’ with ur” NAME VALUE DEFERRED_VALUE ——————————– ———— ————– util_heap_sz 70982 70982...

0

Parameter Wednesday: DB2 Registry DB2_EVALUNCOMMITTED

DB2 Version This Was Written For 9.7 Parameter Name DB2_EVALUNCOMMITTED Where This Parameter Lives DB2 Registry (db2set) How To Check Value > db2set -all |grep DB2_EVALUNCOMMITTED [i] DB2_EVALUNCOMMITTED=YES [DB2_WORKLOAD] OR > db2 “select substr(reg_var_name,1,32) name, substr(reg_var_value,1,16) value, level, is_aggregate, substr(aggregate_name,1,32) aggregate_name from SYSIBMADM.reg_variables where reg_var_name=’DB2_EVALUNCOMMITTED’ with ur” NAME VALUE LEVEL IS_AGGREGATE AGGREGATE_NAME ——————————– —————- —– ———— ——————————– DB2_EVALUNCOMMITTED YES I...

3

Installing DB2 Using a Response File

Some friends on Twitter were commenting that db2_install is not just deprecated in DB2 10, but is fully discontinued and not available. Melanie Stopfer mentioned this on her webcast on June 22. I do dozens of installs a year, and most of them are using a response file. The same response file, actually, since a standard starting point makes sense at...

0

Parameter Wednesday: DB2 Registry DB2_SKIPINSERTED

DB2 Version This Was Written For 9.7 Parameter Name DB2_SKIPINSERTED Where This Parameter Lives DB2 Registry (db2set) How To Check Value > db2set -all |grep DB2_SKIPINSERTED [i] DB2_SKIPINSERTED=YES [DB2_WORKLOAD] OR > db2 “select substr(reg_var_name,1,32) name, substr(reg_var_value,1,16) value, level, is_aggregate, substr(aggregate_name,1,32) aggregate_name from SYSIBMADM.reg_variables where reg_var_name=’DB2_SKIPINSERTED’ with ur” NAME VALUE LEVEL IS_AGGREGATE AGGREGATE_NAME ——————————– —————- —– ———— ——————————– DB2_SKIPINSERTED YES I...

3

Parameter Wednesday – DB CFG – pckcachesz

DB2 Version This Was Written For 9.7 Parameter Name PCKCACHESZ Where This Parameter Lives Database Configuration How To Check Value > db2 get db cfg for sample |grep PCKCACHESZ Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(250509) OR > db2 “select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBCFG where name=’pckcachesz’ with ur” NAME VALUE VALUE_FLAGS DEFERRED_VALUE DEFERRED_VALUE_FLAGS...

2

Parameter Wednesday – DB CFG – LOCKLIST

DB2 Version This Was Written For 9.7 Parameter Name LOCKLIST Where This Parameter Lives Database Configuration How To Check Value $ db2 get db cfg for wc005s01 |grep LOCKLIST Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(4096) OR $ db2 “select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBCFG where name=’locklist’ with ur” NAME VALUE VALUE_FLAGS...

7

Parameter Wednesday: DBM CFG – INTRA_PARALLEL

DB2 Version This Was Written For 9.7 Parameter Name INTRA_PARALLEL Where This Parameter Lives Database Manager Configuration How To Check Value $ db2 get dbm cfg |grep INTRA_PARALLEL Enable intra-partition parallelism (INTRA_PARALLEL) = NO OR $ db2 “select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBMCFG where name=’intra_parallel’ with ur” NAME VALUE VALUE_FLAGS DEFERRED_VALUE DEFERRED_VALUE_FLAGS DATATYPE ——————————–...

2

Parameter Wednsday – DBM CFG: DIAGLEVEL

Continuing my trend of attacking some of the simpler parameters first, I’m going to cover DIAGLEVEL this week. DB2 Version This Was Written For 9.7 Parameter Name DIAGLEVEL Where This Parameter Lives Database Manager Configuration How To Check Value > db2 get dbm cfg |grep DIAGLEVEL Diagnostic error capture level (DIAGLEVEL) = 3 OR > db2 “select name, substr(value,1,16) value,...

0

What DBAs can do to Reduce Deadlocks

Deadlocking is an application problem. There are only a few things that DBAs can do to reduce deadlocking, and they all require buy-in from the application. Let me repeat that another way. Don’t set the parameters mentioned here without understanding the impact on your application. Currently Committed This is new behavior in DB2 9.7. It has a similar effect to...