[DB2] cheat sheet

Some useful DB2 commands

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. In this case, 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 investdb USER myuser USING mypass — connect to db. In this case, database is investdb , user is myuser and password is mypass.
• DB2 DISCONNECT investdb — 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 authorization 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.

Schema

• DB2 SELECT SCHEMANAME FROM SYSCAT.SCHEMATA — list all schemas
• DB2 VALUES CURRENT SCHEMA — gets the current schema
• DB2 SET SCHEMA ALEXSCHEMA — set schema

Table

• 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 (‘CHATRI’) AND a.customer_id – c.cust_id) DATA INITIALLY DEFERRED REFRESH DEFERRED — Creates a materialized 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

• 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

• 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.