IBM introduced some new administrative views in DB2 V9, widely known as viper. These new administrative views belong to the schema SYSIBMADM. Some of the important administrative views are:
• ENV_SYS_INFO: Returns information about OS, CPU and Memory.
• ENV_INST_INFO: Returns information about database instance.
• ENV_FEATURE_INFO: Returns information about all available products for which license is required.
• ENV_PROD_INFO: Returns information about installed DB2 products.
Let us retrieve some information from a couple of these administrative views. Connect to db2 database using command editor and execute the following sql.
SELECT os_name, os_version, total_cpus, total_memory
FROM SYSIBMADM.ENV_SYS_INFO;
Here is the result set for the above command.
OS_NAME OS_VERSION TOTAL_CPUS TOTAL_MEMORY
——- ———– ———– ————-
WIN32_NT 5.1 2 3062
Similarly we can get the information about DB2 instance as well using ENV_INST_INFO view.
SELECT inst_name, service_level, bld_level, fixpack_num
FROM SYSIBMADM.ENV_INST_INFO;
Te result is as shown below.
INST_NAME SERIVCE_LEVEL BLD_LEVEL FIXPACK_NUM
——— ————- ——— ———–
DB2 DB2 v9.1.200.166 special_17369 2
In a similar fashion, we get other metadata information also using other administrative views. In release 9.5, new view ENV_SYS_RESOURCES is introduced which contains enhanced information about operating system, physical memory, virtual memory and CPU. We will cover more about it in our future blog posts.
