Category Archive: DB2

Aug 18

List all base tables in a DB2 database

— — List DB2 base Tables — select sit.TABLE_NAME, sit.TABLE_TYPE, substr(sit.TABLE_NAME,2,instr(sit.TABLE_NAME,’_’)-2) as SYSTEM, sct.COLCOUNT, sct.CARD as ROWCOUNT from SYSIBM.TABLES sit inner join syscat.TABLES sct on sct.TABSCHEMA = sit.TABLE_SCHEMA and sct.TABNAME = sit.TABLE_NAME where sit.TABLE_SCHEMA=’@MVPADM’ and sit.TABLE_TYPE = ‘BASE TABLE’;

Aug 18

List unreferenced tables in a DB2 database

— — List DB2 base tables which don’t have References — SELECT sit.TABLE_NAME, ‘No References’ FROM SYSIBM.TABLES sit left outer join syscat.REFERENCES ref1 on ref1.tabschema = sit.TABLE_SCHEMA and ref1.tabname = sit.TABLE_NAME left outer join syscat.REFERENCES ref2 on ref2.reftabschema = sit.TABLE_SCHEMA and ref2.reftabname = sit.TABLE_NAME where ref1.constname is null and ref2.constname is null and sit.TABLE_SCHEMA = …

Continue reading »

Jun 06

Generating DB2 RUNSTATS SQL

This SQL will generate a set of RUNSTATS commands for the tables in your database that have no RUNSTATS values recorded. select ‘CALL SYSPROC.ADMIN_CMD(”RUNSTATS ON TABLE ‘|| trim(tabschema) || ‘.’ || tabname || ‘ ON ALL COLUMNS AND INDEXES ALL ALLOW READ ACCESS”);’ from syscat.tables where stats_time is null and tabschema not like ‘SYS%’ order …

Continue reading »

Apr 11

List tables without Primary Keys in a DB2 database

— — List DB2 base tables which don’t have Primary Keys — SELECT sit.TABLE_NAME, ‘No Primary Key’ FROM SYSIBM.TABLES sit LEFT OUTER JOIN SYSIBM.SYSCOLUMNS sic ON sic.TBNAME = sit.TABLE_NAME AND sic.KEYSEQ > 0 WHERE sit.TABLE_SCHEMA = ‘@MVPADM’ AND sit.TABLE_TYPE = ‘BASE TABLE’ AND sic.NAME IS NULL;