DB2 commands 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

Database commands via Command Line Processor (CLP)

    DB2 LIST NODE DIRECTORY -- Lists all nodes
    DB2 CATALOG TCPIP NODE DB2NODE REMOTE servername SERVER 50000 -- catalogs node.  In this case, node is db2Node on the machine with name servername. 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 dbname USER myuser USING mypass -- connect to db. In this case, database is dbname, usern is myuser and password is mypass.
    DB2 DISCONNECT dbname  -- 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.


    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

Tables

    DB2 LIST TABLES FOR schema_name -- list all tables for particular schema
    DB2 LIST TABLES SHOW DETAIL; -- show detail about tables
    DECLARE GLOBAL TEMPORARY TABLE -- declares a temporary table
    CREATE TABLE MQT AS (SELECT c.cust_name, c.cust_id, a.balance FROM customer c, account a WHERE c._cust_name IN ('Alex') AND a.customer_id - c.cust_id) DATA INITIALLY DEFERRED REFRESH DEFERRED -- Creates a materialised query table. In this case the MQT is based on a join query from the customer and account table.

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

Sequences

CREATE SEQUENCE STESTRESULT AS INTEGER INCREMENT BY 1 START WITH 1 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 10 ORDER;  -- Create Sequence starting with 1 which cache 10 values
    SELECT * FROM SYSCAT.SEQUENCES; -- Gets systcat info on sequences
    VALUES NEXT VALUE FOR MYSEQ; -- Gets next value from sequence myseq
    ALTER SEQUENCE MYSEQ RESTART WITH 11 INCREMENT BY 1 MAXVALUE 10000 CYCLE CACHE 12 ORDER -- Changes MySeq sequence

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 commands

Runstats

    SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1; -- gets current date.
    SELECT HEX(36) FROM SYSIBM.SYSDUMMY1;  -- same as VALUES HEX(36)
    SELECT XMLCOMMENT ('This is an XML comment') FROM SYSIBM.SYSDUMMY1;


    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';

The following catalog columns can be queried to determine if RUNSTATS has been performed on the tables and indexes:

    If the CARD column of the SYSCAT.TABLES view displays a value of -1, or the STATS_TIME column displays a NULL value for a table, then the RUNSTATS utility has not run for that table.
    If the NLEAF, NLEVELS and FULLKEYCARD columns of the SYSCAT.INDEXES view display a value of -1, or the STATS_TIME column displays a NULL value for an index, then the RUNSTATS utility has not run for that index.