text: ﻿--
  -- Db2 Discovery Supplement Script for DB2 LUW
  -- Version 0.2
  --
  -- Tested on IBM DB2 version 10.5 on AIX
  --
  -- DIRECTIONS:
  -- To run script, use the following command using the 'db2' command:
  --
  -- Where 'db2output.csv' is the name of the output file
  -- 'DB2_Discovery_Script_Supp_v0.2.sql' is the name of the input script
  --
  -- [db2user@host ~]$ db2 -txf DB2_Discovery_Script_Supp_v0.2.sql -z db2output2.csv
  --
  ;
  --
  -- RAW DATA
  --
  
  select
  
  	trim(e.OS_NAME) CONCAT ',' CONCAT
  	trim(e.HOST_NAME) CONCAT ',' CONCAT
  	trim(i.INST_NAME) CONCAT ',' CONCAT
  	trim(i.SERVICE_LEVEL) CONCAT ',' CONCAT
  	trim((SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1)) CONCAT ',' CONCAT
  	trim(v.TABSCHEMA) CONCAT ',,' CONCAT
  	'RAW DATA (MB)' CONCAT ',' CONCAT	
  	round((sum(v.CARD * v.AVGROWSIZE) * 0.000001), 2) CONCAT ',' CONCAT ',' CONCAT ','
  	
  from syscat.tables as v, TABLE(SYSPROC.ENV_GET_SYS_INFO()) as e, TABLE(SYSPROC.ENV_GET_INST_INFO()) as i
  group by e.OS_NAME, i.SERVICE_LEVEL, i.INST_NAME, e.HOST_NAME, v.TABSCHEMA
  
  UNION ALL
  --
  -- TABLE SIZING
  --
  
  select
  
  	trim(e.OS_NAME) CONCAT ',' CONCAT
  	trim(e.HOST_NAME) CONCAT ',' CONCAT
  	trim(i.INST_NAME) CONCAT ',' CONCAT
  	trim(i.SERVICE_LEVEL) CONCAT ',' CONCAT
  	trim((SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1)) CONCAT ',' CONCAT
  	trim(v.TABSCHEMA) CONCAT ',' CONCAT	
  	v.TABNAME CONCAT ',' CONCAT
  	'TABLE SIZING' CONCAT ',' CONCAT
  	v.CARD CONCAT ',' CONCAT
  	v.AVGROWSIZE CONCAT ',' CONCAT
  	(v.CARD * v.AVGROWSIZE) CONCAT ','
  		
  from syscat.tables as v, TABLE(SYSPROC.ENV_GET_SYS_INFO()) as e, TABLE(SYSPROC.ENV_GET_INST_INFO()) as i
  where v.type = 'T'
  
  UNION ALL
  --
  -- Individual USER AUTH Details
  --
  
  SELECT
  	trim(e.OS_NAME) CONCAT ',' CONCAT
  	trim(e.HOST_NAME) CONCAT ',' CONCAT
  	trim(i.INST_NAME) CONCAT ',' CONCAT
  	trim(i.SERVICE_LEVEL) CONCAT ',' CONCAT
  	trim((SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1)) CONCAT ',' CONCAT
  	'Individual User Authority' CONCAT ',' CONCAT GRANTEE 
  	CONCAT ',' CONCAT ',' CONCAT ',' CONCAT ',' CONCAT ',' 
  	FROM 
  (SELECT DISTINCT GRANTEE FROM SYSCAT.COLAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.DBAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.INDEXAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.MODULEAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.PACKAGEAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.PASSTHRUAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.ROUTINEAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.SCHEMAAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.SEQUENCEAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.TABAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.TBSPACEAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.VARIABLEAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.WORKLOADAUTH WHERE GRANTEETYPE = 'U' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.XSROBJECTAUTH WHERE GRANTEETYPE = 'U') AS g,
  TABLE(SYSPROC.ENV_GET_SYS_INFO()) as e, TABLE(SYSPROC.ENV_GET_INST_INFO()) as i
  
  UNION ALL
  --
  -- Group AUTH Details
  --
  
  SELECT
  	trim(e.OS_NAME) CONCAT ',' CONCAT
  	trim(e.HOST_NAME) CONCAT ',' CONCAT
  	trim(i.INST_NAME) CONCAT ',' CONCAT
  	trim(i.SERVICE_LEVEL) CONCAT ',' CONCAT
  	trim((SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1)) CONCAT ',' CONCAT
  	'Group Authority' CONCAT ',' CONCAT GRANTEE 
  	CONCAT ',' CONCAT ',' CONCAT ',' CONCAT ',' CONCAT ',' 
  	FROM 
  (SELECT DISTINCT GRANTEE FROM SYSCAT.COLAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.DBAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.INDEXAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.MODULEAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.PACKAGEAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.PASSTHRUAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.ROUTINEAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.SCHEMAAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.SEQUENCEAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.TABAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.TBSPACEAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.VARIABLEAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.WORKLOADAUTH WHERE GRANTEETYPE = 'G' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.XSROBJECTAUTH WHERE GRANTEETYPE = 'G') AS g,
  TABLE(SYSPROC.ENV_GET_SYS_INFO()) as e, TABLE(SYSPROC.ENV_GET_INST_INFO()) as i
  
  UNION ALL
  --
  -- Role AUTH Details
  --
  
  SELECT
  	trim(e.OS_NAME) CONCAT ',' CONCAT
  	trim(e.HOST_NAME) CONCAT ',' CONCAT
  	trim(i.INST_NAME) CONCAT ',' CONCAT
  	trim(i.SERVICE_LEVEL) CONCAT ',' CONCAT
  	trim((SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1)) CONCAT ',' CONCAT
  	'Role Authority' CONCAT ',' CONCAT GRANTEE 
  	CONCAT ',' CONCAT ',' CONCAT ',' CONCAT ',' CONCAT ',' 
  	FROM 
  (SELECT DISTINCT GRANTEE FROM SYSCAT.COLAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.DBAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.INDEXAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.MODULEAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.PACKAGEAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.PASSTHRUAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.ROUTINEAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.SCHEMAAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.SEQUENCEAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.TABAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.TBSPACEAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.VARIABLEAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.WORKLOADAUTH WHERE GRANTEETYPE = 'R' UNION
  SELECT DISTINCT GRANTEE FROM SYSCAT.XSROBJECTAUTH WHERE GRANTEETYPE = 'R') AS g,
  TABLE(SYSPROC.ENV_GET_SYS_INFO()) as e, TABLE(SYSPROC.ENV_GET_INST_INFO()) as i;