DB2 9 is a feature packed database with performance features, pureXML, high availability etc. As a DBA, you might want to know which features are being used in your database so that you are not caught with surprise.
Run the attached script in your database to find out if you are using Intra-query parallelism, Connection Concentrator, Materialized Query Tables, Multidimensional Cluster Tables, High Availability Disaster Recovery, and pureXML features.
WITH TEMP ( feature, function, evaluation, detail ) AS
(
SELECT 'High Availability Feature' feature
, 'High Availability Disaster Recovery' function
, 'HADR_DB_ROLE = ' || VALUE evaluation
, CASE VALUE
WHEN 'STANDARD' THEN 'HADR is NOT being used'
WHEN 'PRIMARY' THEN 'HADR is being used'
WHEN 'STANDBY' THEN 'HADR is being used'
END AS detail
FROM SYSIBMADM.DBCFG WHERE NAME = ('hadr_db_role')
UNION ALL
SELECT 'Advanced Access Control Feature' feature
, 'Label Based Access Control' function
, 'Table => ' || tabschema || '.' || tabname evaluation
, tabschema || '.' || tabname || ' is using LBAC' detail
FROM SYSCAT.TABLES
WHERE SECPOLICYID > 0
UNION ALL
SELECT 'Storage Optimization Feature' feature
, 'Row Compression' function
, 'Table => ' || tabschema || '.' || tabname evaluation
, tabschema || '.' || tabname ||
' has row compression activated' detail
FROM SYSCAT.TABLES
WHERE COMPRESSION IN ('R','B')
UNION ALL
SELECT 'pureXML Feature' feature
, 'XML Columns' function
, 'XML Table => ' || tabschema || '.' || tabname evaluation
, tabschema || '.' || tabname ||
' contains XML column ' || colname detail
FROM SYSCAT.COLUMNS
WHERE TYPENAME = 'XML'
UNION ALL
SELECT 'Performance Optimization Feature' feature
, 'Materialized Query Tables' function
, 'MQT => ' || CREATOR || '.' || NAME evaluation
, NULLIF('1','1') detail
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'S'
UNION ALL
SELECT 'Performance Optimization Feature' feature
, 'Multidimensional Cluster Tables' function
, 'MDC Table => ' || A.TABSCHEMA || '.' || A.TABNAME evaluation
, 'Block index in the MDC table is ' ||
A.INDSCHEMA || '.' || A.INDNAME detail
FROM SYSCAT.INDEXES A, SYSCAT.TABLES B
WHERE ( A.TABNAME = B.TABNAME
AND A.TABSCHEMA = B.TABSCHEMA )
AND A.INDEXTYPE = 'BLOK'
UNION ALL
SELECT 'Performance Optimization Feature' feature
, 'Intraquery parallelism' function
, 'INTRA_PARALLEL = ' || VALUE evaluation
, CASE VALUE
WHEN 'NO' THEN 'Intraquery parallelism is DISABLED'
WHEN 'YES' THEN 'Intraquery parallelism is ENABLED'
WHEN 'SYSTEM' THEN 'Intraquery parallelism is ENABLED or
DISABLED based on the hardware on which the database manager is running'
END AS detail
FROM SYSIBMADM.DBMCFG
WHERE NAME IN ('intra_parallel')
UNION ALL
SELECT 'Workload Management Feature' feature
, 'Connection Concentrator' function
, 'MAX_CONNECTIONS = ' || b.value || ' MAX_COORDAGENTS = ' || a.value evaluation
, CASE WHEN int(b.value) > int(a.value) THEN
'Connection Concentrator is ENABLED'
ELSE 'Connection Concentrator is DISABLED' END DETAIL
FROM sysibmadm.dbmcfg a, sysibmadm.dbmcfg b
WHERE a.name = 'max_coordagents'
AND b.name = 'max_connections'
)
SELECT *
FROM temp order by feature
;
Run above SQL to determine if you are using new features of DB2.
Update to above
DB2 9.5 provided a new view to get information as mentioned above:
SELECT SUBSTR(FEATURE_NAME, 1, 10) AS "FEATURE", SUBSTR(FEATURE_FULLNAME, 1, 35) AS "FEATURE_NAME", LICENSE_INSTALLED AS "LIC", SUBSTR(PRODUCT_NAME, 1, 5) AS "PRODUCT", SUBSTR(FEATURE_USE_STATUS, 1, 15) AS "USE_STATUS" FROM SYSIBMADM.ENV_FEATURE_INFO ORDER BY 1;