How to check if a db2luw registry variable is currently effective or not

Body

This  is a common question we face.
A  db2   registry  was set.
Then, a  problem faced.
The  collected data showed  it's   set currently  from  db2set  command.

But,   how to  know if  the  registry was  in effect  and  not  just  showing in the output of  db2set   command ?

Manyt  of the db2 registry settings are  not  effective  until  and unless   the instance is  recycled.
Also,   some of  the registries could be  made effective using   -im  option  of  db2set.
( Sometimes,  it still  might not be truly effective   due to buffer flushing issue )
But,   the question is  how to  check  if  the registry  is currently  in effect.

With -info option of db2set a registry could be checked for immediate or not.
But, there is no  option in  db2set  to  find  if  the registry is currently  effective.

To  get help  on that   a table function called  ENV_GET_REG_VARIABLES could be  used.
Refer,
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0058678.html

For example  let  us  pick a  registry  named DB2_EXTENDED_OPTIMIZATION

$ db2set -info DB2_EXTENDED_OPTIMIZATION
   Immediate change supported : YES
   Immediate by default :       NO

My current  db2set output :
$ db2set
DB2COMM=TCPIP

db2 connect to sample ;

The   table function query :
db2 "select substr(reg_var_name,1,20) as REGISTRY_NAME,\
   substr(reg_var_on_disk_value,1,20) as ON_DISK_VALUE, \
   substr(REG_VAR_VALUE,1,20) as IN_MEMORY_VALUE \
   from table(env_get_reg_variables(-1)) \
   where reg_var_name = 'DB2_EXTENDED_OPTIMIZATION'";

The  table function query output   :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------

  0 record(s) selected.

$ db2set DB2_EXTENDED_OPTIMIZATION=NJFLS

$ db2set
DB2_EXTENDED_OPTIMIZATION=NJFLS
DB2COMM=TCPIP

The  table function query output  still shows  :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------

  0 record(s) selected.

$ db2set -im DB2_EXTENDED_OPTIMIZATION=NJFLS

Now the table function query   output shows  :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------
DB2_EXTENDED_OPTIMIZ NJFLS                NJFLS

  1 record(s) selected.

So, the immediate  option  (-im) have  made  it  effective  immediately.

Now,
$ db2set DB2_EXTENDED_OPTIMIZATION=

$ db2set
DB2COMM=TCPIP

The  table function query  shows  :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------
DB2_EXTENDED_OPTIMIZ -                    NJFLS

  1 record(s) selected.

$ db2set -im DB2_EXTENDED_OPTIMIZATION=

And,  now the  table function  query shows  :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------

  0 record(s) selected.

Another  registry   DB2FODC which  is not enabled with immediate option.
$ db2set -info DB2FODC
   Immediate change supported : NO
   Immediate by default :       NO

Using this  table function :
db2 "select substr(reg_var_name,1,20) as REGISTRY_NAME,\
   substr(reg_var_on_disk_value,1,20) as ON_DISK_VALUE, \
   substr(REG_VAR_VALUE,1,20) as IN_MEMORY_VALUE \
   from table(env_get_reg_variables(-1)) \
   where reg_var_name = 'DB2FODC'";

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------

  0 record(s) selected.

$ db2set DB2FODC="DUMPCORE=OFF"

$ db2set
DB2FODC=DUMPCORE=OFF
DB2COMM=TCPIP

The table function shows :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------

  0 record(s) selected.

This  registry  cannot be set  immediately.   
So,  to  make it effective it  needs,
db2stop  / db2start

And, then the  table function query shows  :

REGISTRY_NAME        ON_DISK_VALUE        IN_MEMORY_VALUE
-------------------- -------------------- --------------------
DB2FODC              DUMPCORE=OFF         DUMPCORE=OFF

  1 record(s) selected.