Purpose To list IDs that have one or more privileges, but do not have the CONNECT authority through a direct grant, a group, or a role, either explicitly or implicitly. I used this to find IDs I might need to…
Purpose To list the database authorities held by an ID or group – authorities, not privileges. This information is not available in sysibmadm.privileges. The format of syscat.dbauth has columns with ‘Y’ or ‘N’ in them, and reading that quickly to…
Purpose To generate a comma separated list of columns in a MON_GET table function, in the same order you should get the columns if you query the table. This SQL should handle larger tables like MON_GET_DATABASE that have a very…
Purpose To list all RUNSTATS done in a database in the last three days, whether manual or automatic. Version Tested on 10.5. Should work on 10.1 and up. Statement select pid, tid, substr(eventtype, 1, 10), substr(objtype, 1, 30) as objtype,…
There are several scenarios with DB2 where we need to know what the “worst-case” row length is. That is, if all varying fields are completely full and any LOBs are maximally inlined, how much space could a single row actually…
Purpose To generate a list of objects in a specific schema. Useful if you are analyzing how schemas are used, who uses them, or preparing to drop a schema. Version The administrative view used here was introduced in DB2 9.5.…
This entry modified on 2/25/2016 to add some details in the “Modifications” section at the end. Purpose To generate a list of tables in a specific tablespace that do not have RI involved, and do have primary keys. Why would…
Purpose To generate a list of unused or not recently used indexes. Remember to not go entirely off of LASTUSED when deciding to drop an index, but to consider factors like when the database was last restarted and such. This…
Purpose These statements calculates the buffer pool hit ratio for both BLU and non-BLU activity. This post includes three SQLs – for calculating at the database, bufferpool, and tablespace level. The separate BLU calculations are not yet included in sysibadm.bp_hitratio…
Purpose This statement reports how selective at the column level queries are that run against a specific table. This does not look at overall selectivity or row selectivity. It will only work in DB2 10.5. BLU performs best when not…
Purpose This statement calculates the compression ratio for BLU tables. The compression ration can be used to help identify tables where compression is not optimal and you may need to look into why. Compression is critical to optimal performance on…