DB2 Administrative SQL Cookbook: Listing Database Authorities that an ID/Group Holds

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 answer the question “What permissions does this ID have?” can be a bit frustrating. This SQL could also be union-ed with SQL to query sysibmadm.privileges to include the privileges on database objects in one result set.

Continue reading »

Calculating Row Size for a Table

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? SYSCAT.TABLES gives us the average row size, which is useful for some use cases, but not all of them.

Continue reading »

DB2 Administrative SQL Cookbook: Listing the Number of Pages in Tablespaces, by Bufferpool

Purpose

This is a bit of a rare use case. The main use I see for it is if a database server has an excess of memory and you want to size your bufferpools so that the entire database fits in-memory. That’s not a common situation. In order to properly size the bufferpools for this edge case, I need to know how many pages my tablespaces have by bufferpool, not by tablespace.

Continue reading »