How to detect pending settings on db2set, dbm and db cfg level?

Question

On Db2 v11.5.8.0 on Linux x86_64 I have several instances and databases.

I would like to find out if there is some pending setting on:

• db2set variables
• dbm cfg
• dg cfg level?

Questions:

1. Is there a way to find out which db2set command has been changed since instance restart and is not yet applied until next instance restarts? Or at least to get true or false info that at least one of the setting is in pending state.
2. Is there any simpler way then execute "get db/dbm cfg show detail" and compare two displayed columns for pending changes? The "db cfg" is particularly annoying, because I have to connect to database to get the result. Is there any simpler way to just get info dbm cfg or db cfg has changed and some settings are in pending.

I need some simple solution to write Linux bash script to check differences automatically and return me warning if there is some pending setting.

• Mark Barinstein: There is ENV_GET_REG_VARIABLES table function for Db2 registry variables. But it works strage (afair) - it doesn't show changed / new settings made after the instance restart (may be it's fixed in newer fixpacks). But why could the database connection be "annoying" for a script?

Answer 1

For db2set I don't know, but for the rest you can use:

db2pd -db <db> -dbcfg
db2pd -dbmcfg

To detect whether something is pending you have to do some parsing.

Answer 2

This is all in the online docs. https://www.ibm.com/docs/en/db2/11.5?topic=commands-get-database-configuration

db2 get db cfg show detail

db2 attach to instance

db2 get dbm cfg show detail

• folow: I was probably misunderstood. My question was: "Is there any simpler way..." then the solution you have provided? I have found out the solution and today posted it.

Answer 3

I forgot to write solution I have come up with and it is working perfectly.

Changes for db2set:

select
 'variable: [' concat lcase(level) concat '] ' concat reg_var_name concat ',
 ' concat 'current value: ' concat coalesce(reg_var_value,'NULL') concat ',
 ' concat 'pending value: ' concat coalesce(reg_var_on_disk_value,'NULL')
from
 table(env_get_reg_variables(-2, 0))
where
 coalesce(reg_var_value,'NULL') <> coalesce(reg_var_on_disk_value,'NULL')

Changes for dbm cfg:

select
 'parameter: ' concat name concat ',
 current value: ' concat coalesce(varchar(value),'') concat ',
 pending value: ' concat coalesce(varchar(deferred_value),'')
from
 sysibmadm.dbmcfg
where
 case when value_flags <> 'NONE' then '0'
 else rtrim(replace(coalesce(varchar(value),''),'(Active)', '') end
 <> case when deferred_value_flags <> 'NONE' then '0' 
 else trim(replace(coalesce(varchar(deferred_value),''),'(Active)',
 '')) end

Changes for db cfg:

select
 'parameter: ' concat name concat ',
 current value: ' concat coalesce(varchar(value),'') concat ',
 pending value: ' concat coalesce(varchar(deferred_value),'')
from
 sysibmadm.dbcfg
where
 case when value_flags <> 'NONE' then '0' 
 else rtrim(replace(coalesce(varchar(value),''),'(Active)', '')) end
 <> case when deferred_value_flags <> 'NONE' then '0' 
 else rtrim(replace(coalesce(varchar(deferred_value),''),'(Active)',
 '')) end