DB2 personal tutorial


This tutorial on IBM DB2 based on my personal experience in particular in AIX environment. He cover some basic concept.

 

Introduction

Installation

Information commands

Update commands

Administration commands

Export - Import - Load

Logs

Work with TSM

Tunning

Db2 errors

Documentation

 


 

Introduction

IBM DB2 is an IBM relational database management system.

In this tutorial only a few options are listed for a command, for more information about a command use the DB2 official documentation in particular the DB2 information center.

For aix or linux the commands can be passed using a "db2 user" and a terminal like putty  

For windows the commands can be passed using dbcmd in a command line. 

 Top ↑


 

Installation

  • AIX - Linux
    • db2setup (install db2)
    • db2icrt (create an instance)
      • ./usr/opt/db2_08_01/instance/db2icrt -a SERVER -p DB2_db2test9 -u db2test9 db2test9
    • db2idrop (delete an instance)
      • ./usr/opt/db2_08_01/instance/db2idrop db2test
    • db2start (start db2)
    • db2stop (stop db2)
    • db2_kill (kill server if not responding)
    • db2 catalog (catalog a db)
      • db2 catalog tcpip node
      • db2 catalog database  dbname at node  nodename authentication dcs
  • Windows
    • d:\setup /i langue (install db2)
      • d:\setup /i FR

 

Information commands

  • db2level (Check the version of db2)
  • db2 list database directory (list the content of the system database directory)
  • db2 list node directory (list the contents of the node directory)
  • db2 list applications (list all active database applications)
    • db2 list applications show detail (list all active applications with details)
  • db2 list tablespaces (list tablespace for the current database)
  • db2 get db cfg for dbname (Returns the values of individual entries in a specific database configuration file)
    • db2 get db cfg for prod
  • db2 get database manager configuration (Returns the values of individual entries in the database manager configuration file)
  • db2 load query table tablename (see the status of a table)
  • db2 get snapshot for locks on dbname (see information about lock on a db)
  • db2tbst (get tablespace state)
    • db2tbst 0x00020

 

Update commands

  • db2 update db cfg for dbname using parameter value
    • db2 update db cfg for prod using logbufsz 128
  • db2 update dbm cfg using parameter value
    • db2 update dbm cfg using JAVA_HEAP_SZ 2048
  • db2 alter
    • db2 alter bufferpool ibmdefaulbp size -1

 

Administration commands

  • db2_kill (kill db2, if you can use db2stop or db2stop force)
  • ipclean (remove the interprocess communication, tipically after a db2_kill
  • db2 force application all (force db2 applications to stop)
  • db2 support . -d db2name -c -g -s (create a file for the support)

 

Export - Import - Load

  • export (export a table)
  • import (import table)
  • load (load table)
    • db2 load from tab68.ixf of ixf messages tab68.log REPLACE into shema.table
  • db2move dbname export (export all table in a table)
    • db2move sample  export
  • db2move load (load all table from a previous db2move export)
    • db2move sample load
  • db2 load terminate (terminate a load when a probem occured SQL0668N code '3')
    • db2 load from tab2.ixf of ixf messages tab2.log terminate into shema.table

 

Logs

  • db2diag.log
    • /instance_name/sqllib/db2dump/db2diag.log
  • db2cott.nfy
    • /instance_name/sqllib/db2dump/db2cott.nfy

  • db2diag -A
    • To backup the db2diag.log and recreate a new one

 

Work with TSM

  • db2adutl query full db dbname
  • db2adutl query logs
  • db2adutl query full
  • db2 backup db dbname online use tsm compress
  • db2 restore db dbname user tsm taken at timestamp

 

Tunning

  • DB2MONITOR
    • To check DB2 performance a useful program exist with the following characteristics :
      • Small DB2 performance monitoring program, shows active connections with lock information, SQLs in db cache, database snapshots, database objects information, list history. Can help resolve locking problems, identify worst performing queries.
      • This freeware is available to http://chuzhoi_files.tripod.com/or http://www.db2mon.com/
  • ADVIS AND EXPLAIN
      • Create the Advis and Explain (this tables are useful for db2 tunning)
      • cd /instance/sqllib/misc
      • db2 -tf EXPLAIN.DDL
    • Check the index to create using db2advis
        • 1) Create a file contening a sql request to tune
        • /tmp/request_20090331
          • --#SET FREQUENCY 5000
          • SELECT * FROM EMPLOY WHERE ID = '5000';
        • 2) Execute the db2advis
        • db2advis -d <dbname> -t <max-advise-time>  -n <schema-name> -q <schema-name> -i <file>
          • db2advis -d database  -t 60  -n schema -q schema -i /tmp/request_20090331
      • Optimization finished.
          1  indexes in current solution
         [1985000.0000] timerons  (without recommendations)
         [185000.0000] timerons  (with current solution)
         [90.68%] improvement

      • -- LIST OF RECOMMENDED INDEXES
        -- ===========================
        -- index[1],    0.161MB
           CREATE INDEX "SCHEMA "."IDX904021701010000" ON "SCHEMA "."EMPLOYES" ("USER_ID" ASC) ALLOW REVERSE SCANS ;
      • ....

      • 3) See the recommandations

DB2 errors

  • SQL0668N Operation not allowed for reason code '3' on table tablename (after a load problem)
    • db2 load from tablename.ixf messages message.log terminate into tablename
  • SQL0668N Operation not allowed for reason code '1' on table tablename
      • set integrity for tablename immediate checked
  • SQL0290N Table space access is not allowed (typically after a load)
        • db2 list tablespaces (to see all the status of the tablespace)
        • db2tst 0x00020 (to see the status of a tablespace with a problem, different from 0X00000)
          • State = Backup pending
        • Make a db2 backup from the tablespace to correct the problem
        • To prevent the problem add the option NONRECOVERABLE to the load