Wednesday, October 5, 2011

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

11 comments:

  1. Hi like your post.. very helpful..
    Need a permission to post in my blog with your blog link.
    thanks!

    ReplyDelete
  2. Great post, very helpful for a DB2 newbie (I've been doing Oracle for 17 years but have just come across the need to use DB2).

    FYI, small error in the "Tables" section: to list schemas, the correct syntax is (it was just missing the 'schema' keyword):
    DB2 LIST TABLES FOR SCHEMA schema_name -- list all tables for particular schema

    Thanks again and best regards,
    Terry

    ReplyDelete
  3. Nice post for newbie ,thanks

    ReplyDelete
  4. Very informative and helpful.

    ReplyDelete
  5. Thank you very much. These commands are very helpful to us. Once again thank you very much !!!

    ReplyDelete
  6. your post helped a lot. Thanks for the info.

    ReplyDelete
  7. Thanks for this post. It helps me a lot too.

    ReplyDelete