DB2

DB2 Cheat Sheet

db2 System Commands
    DB2LEVEL -- checks version of db2 installed.
    DB2ILIST -- lists all instances installed
    DB2CMD -- opens a command line processor
    DB2CC -- opens db2 control center
    DB2LICM -l -- gets db2 type.

Command Line Processor Commands
    db2 LIST NODE DIRECTORY -- Lists all nodes
    db2 CATALOG TCPIP NODE DB2NODE REMOTE MACHINE215 SERVER 50000 -- catalogs node. Node is db2Node on the machine with name machine215. Port is 50000.
    db2 LIST DATABASE DIRECTORY -- list databases
    db2 GET DB CFG FOR SAMPLE -- get configuration info for the SAMPLE db.
    db2 CONNECT TO alexDB USER myuser USING mypass -- connect to db. In this case, database is alexdb, usern is myuser and password is mypass.
    db2 DISCONNECT alexdb  -- disconnects
    db2 LIST APPLICATIONS SHOW DETAIL -- shows all running db's
    db2 GET DBM CFG -- view authentication paramater (e.g. something like server_encrypt)
    db2 UPDATE DBM CFG USING AUTHENTICATION SERVER_ENCRYPT -- alter the authentication mechanism to server_encrypt
    db2 GET AUTHORIZATIONS -- get authorisation level.

Database commands via Command Line Processor (CLP)
    db2 GET DATABASE CONFIGURATION -- gets current database configuration
    db2 VALUES CURRENT USER - - gets the current user
    db2 VALUES CURRENT SCHEMA -- gets the current schema
    db2 VALUES CURRENT QUERY OPTIMIZATION -- get query optimization level.

Schemas
    db2 SELECT SCHEMANAME FROM SYSCAT.SCHEMATA -- list all schemas
    db2 VALUES CURRENT SCHEMA -- gets the current schema
    db2 SET SCHEMA ALEXSCHEMA -- set schema
List Tables
    db2 LIST TABLES FOR schema_name -- list all tables for particular schema
    db2 LIST TABLES SHOW DETAIL; -- show detail about tables
  
Tablespaces
    db2 LIST TABLESPACES SHOW DETAIL -- show detail about table spaces
    SELECT * FROM SYSCAT.TABLESPACES;  -- show what syscat has about tablespaces
    SELECT tbspace, bufferpoolid from syscat.tablespaces;  -- get tablespace and bufferpoolid
    SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACE=2; -- Check what TABLES are in tablespace where id = 2.

Constraints
    SELECT * FROM SYSCAT.TABCONST;  -- Table constraints
    SELECT * FROM SYSCAT.CHECKS;  -- Colum checks
    SELECT * FROM SYSCAT.COLCHECKS; -- Column constraints
    SELECT * FROM SYSCAT.REFERENCES; --  Referential constraints

Locksize
    SELECT TABNAME, LOCKSIZE FROM SYSCAT.TABLES WHERE TABNAME = ' EMPLOYEES';  -- Check locksize which can be tablespace, table, partition, page, row - (usually row).

Bufferpools
    SELECT bpname, npages, pagesize from syscat.bufferpools -- get useful buffer pool info.
    SELECT buffer.bufferpoolid, buffer.bpname, buffer.npages, buffer.pagesize, tablespace.tbspace, tablespace.tbspaceid from syscat.bufferpools buffer, syscat.tablespaces tablespace where tablespace.bufferpoolid = buffer.bufferpoolid;  -- gets buffer pool and corresponding tablespace info.

Indexes
    SELECT * FROM SYSCAT.INDEXES --  show all indexes
    SELECT COLNAMES, TABNAME, INDEXTYPE, CLUSTERRATIO, CLUSTERFACTOR FROM SYSCAT.INDEXES WHERE TABNAME = 'TPERSON';  -- some useful columns

Functions
    SELECT * FROM SYSCAT.FUNCTIONS;  -- check what functions DB has.

SYSDUMMY1
    SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1; -- gets current date.
    SELECT XMLCOMMENT ('This is Comment') FROM SYSIBM.SYSDUMMY1;

Runstats
    RUNSTATS ON TABLE TAUSER1.TOSUSER FOR INDEXES ALL;  -- runstats for all indexes
 Checking the last time runstats was run...
     SELECT CARD, STATS_TIME FROM SYSCAT.TABLES WHERE TABNAME = 'TOSUSER';
     SELECT NLEAF, NLEVELS, FULLKEYCARD, STATS_TIME, TABNAME, INDNAME FROM SYSCAT.INDEXES WHERE TABNAME = 'TOSUSER';