Db2 Experts on a Data Science Quest
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 grant CONNECT to before revoking connect from PUBLIC.… Read the rest
Continue reading »
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 answer the question “What permissions does this ID have?”… Read the rest
Continue reading »
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 large number of columns.… Read the rest
Continue reading »
To list all RUNSTATS done in a database in the last three days, whether manual or automatic.
Tested on 10.5. Should work on 10.1 and up.
select pid, tid,
substr(eventtype, 1, 10),
substr(objtype, 1, 30) as objtype,
substr(objname_qualifier, 1, 20) as objschema,
substr(objname, 1, 10) as objname,
substr(first_eventqualifier, 1, 26) as event1,
substr(second_eventqualifiertype, 1, 2) as event2_type,
substr(second_eventqualifier, 1, 20) as event2,
substr(third_eventqualifiertype, 1, 6) as event3_type,
substr(third_eventqualifier, 1, 15) as event3,
substr(eventstate, 1, 20) as eventstate
from table(sysproc.pd_get_diag_hist… Read the rest
Continue reading »
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 take up?… Read the rest
Continue reading »
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.
The administrative view used here was introduced in DB2 9.5. Should work on 9.5 and up.… Read the rest
Continue reading »
This entry modified on 2/25/2016 to add some details in the “Modifications” section at the end.
To generate a list of tables in a specific tablespace that do not have RI involved, and do have primary keys. Why would I want this?… Read the rest
Continue reading »
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 SQL looks for Indexes that are:
Should work on 9.7 and up.… Read the rest
Continue reading »
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 as of the writing of this article.… Read the rest
Continue reading »
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 all columns are referenced by queries.… Read the rest
Continue reading »
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 BLU.… Read the rest
Continue reading »