DB2 for UNIX Admins

Want to stop a DB2 DB and there’s no DBAs on hand?

Here’s all you need:

# su – [db2instanceowner]

Try:

# db2stop

If that fails with:

 

31/08/2013 21:05:30 0 0 SQL1025N The database manager was not stopped because databases are still active.

SQL1025N The database manager was not stopped because databases are still active.

Then try:

# db2 list applications show detail

If that shows connections, run:

# ipclean

Application ipclean: Removing DB2 engine and client IPC resources for dbxxxx

 

# ipcs | grep dbxxx| awk ‘{print ” ipcrm -“$1” “$2 }’ > ipcln

# cat ipcln

ipcrm -q 1055916063

ipcrm -q 904921121

ipcrm -m 6291458

# chmod +x ipcln

# ipcln

ipcrm: 0515-020 shmid(6291458) was not found.

# db2stop

# ps -ef |grep db2s

If that returns nothing, the database is down!

Now reboot the box and run db2start if necessary.

TC

Here are some more basic commands you might need:

  • Size of your database:
    • =>db2 ‘call get_dbsize_info(?,?,?,0)’
  • Version of DB2 you are running:
    • =>db2level
  • Stop the db2 instance
    • =>db2stop
  • Stop an instance that has current connections
    • =>db2 force applications all
    • =>db2 deactivate db <dbname>
    • =>db2 terminate
    • =>db2stop force
    • =>ipclean
  • Start the db2 instance
    • =>db2start
  • Kill a hung instance (last resort)
    • =>db2_kill -all
  • List the databases in an instance
    • =>db2 list db directory
  • List the cataloged nodes
    • =>db2 list node directory
  • Show the database manager configuration settings
    • =>db2 get dbm cfg
  • Show the database level configuration settings
    • =>db2 get db cfg for <dbname>
  • Activate a database
    • =>db2 activate db <dbname>
  • Deactivate a database
    • =>db2 deactivate db <dbname>
  • View the DB2 License
    • =>db2licm -l
  • To switch between partitions
    • =>db2 terminate; export DB2NODE=<new-node-num>

     

More:

 

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.