DB2 train

DBA task:

high data availabiity
high performance

Day1:


DDL-create,alter,truncate,drop -> to deal with structure of objects
DML-insert,update,del   ->  to manipulate data in tables
DCL-grant,revoke          -> to control data from outside access
TCL-commit,rollback      -> to control transactions(DML)
DRL-select                      -> to fetch data from tabales/views



server
````````

low level  -  windows linux solaris aix hp-ux > IBM  systems
middle level -   OS400   >  as400 (I series)
high ---------  z/os,mvs  > mainframes


db2:
db2 personal edition
    Express C
    worgroup edition
    ESE - enterprise server edition ( we are using)
    EEE

major version changes:

db2v8.2
db2v9.1  purexml
db2v9.5  enhanced performance and monitoring support
db2v9.7   SQL compatability
db2v9.8   purescale
db2v10.1  multy temporature, multy standby,adoptive compression and temporal tables
db2v10.5




objects in db2 :

instances
databases
bufferpools  (4k,8k,16k,32k)
tablespaces  

    SMS- system managed space
    DMS- database managed space

tables
indexs
views
synonynms
aliasas


fn
procedures


Utilities::

export
import
db2look
load

reorg
reorgchk
runstats

backup
restore
rollforward


db2exfmt


tools :

toad
sql optimizer
spotlight

or

ibmdatastudio





Day2 :


how to create objects :


instance : it will start the process and allocate
                     memory which is required for db to up and run ..


db2icrt
db2idrop
db2imigr          9.7  -- >  10.1    version
db2iupdt          9.7.1 -- > 9.7.8   fix pack update  
db2ilist


db2start - start instance
db2stop

./db2icrt -u db2inst1 db2inst1  (Linux)


bufferpools  (4k,8k,16k,32k)

tablespaces  

    SMS- system managed space  
    DMS- database managed space   20mb

Default tablespaces: (created when db created)
SYSCATSPACE
TEMPSPACE1
USERSPACE1

default bufferpool:           IBMDEFAULTBP


db2 create db nod
db2 connect to nod


db2 create bufferpool bp4k0 size 200 pagesize 4k


--DMS

db2 create tablespace tsd4k0 pagesize 4k managed by database using (file 'c:\db2\ts\intel\tsd4k01.dat' 20m) bufferpool bp4k0

--SMS

db2 create tablespace tss4k0 pagesize 4k managed by system using ('c:\db2\ts\intel\tss4k0') bufferpool bp4k0



db2 create table employee(eid int,ename varchar(10)) in tsd4k01


Monitor :

 db2 list db directory
 db2 list tablespaces
 db2 list tables



day 3 :

db2 movement utilities:

Export
Import
Load
db2look

db2 export to 'c:\db2\move\dat\intelempexp1.ixf' of ixf messages 'c:\db2\move\msg\intelmpexp1.msg' select * from employee

db2 import from 'c:\db2\move\dat\intelempexp1.ixf' of ixf messages 'c:\db2\move\msg\intelempimp1.msg' insert into emp

db2 load from 'c:\db2\move\dat\intelempexp1.ixf' of ixf messages 'c:\db2\move\msg\intelempload1.msg' insert into emp1

db2look -d intel -e -l -o c:\db2\move\intel.ddl

db2 -tvf c:\db2\move\intel.ddl


day4:

Backup
             (online and offline)


db2 backup db nod to 'c:\db2\backup'       --full offline


  Full backup
  incremental backup
  delta backup


Restore
Rollforward


admin task:
high data availability
high performance

Day 5 n 6 :

installation

Win:

1 unzip
2 goto server folder
3 click on setup
4 install
5 create new
6 next > typical > next > next ..> user id and password >..>uncheck server notification > .. > install > finish

Linux :

Installation must be as ROOT user ..

1 untar (tar -zxvf <filename>
2 goto server directory (cd server)
3 run ./db2ls to check for existing copies of db2
      ./prereqcheck to check hw n sw compatabilities
      ./db2_setup to run in gui
       ./db2_install to installation as CUI   (note :: if warnings :: use  ./db2_install -f sysreq)

    do u want to change path, /opt/ibm/db2/V9.7 ?
NO

    select product ??

ESE

   after successfull completion of several tasks installation process completed ..


then,

useradd db2inst1
passwd db2inst1
pwd :: <give password>
repeat:: <>

to create instance ::

cd /opt/ibm/db2/V9.7/instance

./db2icrt -u db2inst1 db2inst1
instance created succesfully ..

su - db2inst1
db2level
db2 get instance

to de_install

stop all dbs and db2stop
su - root

cd /opt/ibm/db2/V9.7/instance
./db2idrop db2inst1

cd /opt/ibm/db2/V9.7/install
./db2_deinstall -a



Day 7,8 & 9:


Backup
Restore
Rollforward



  Full backup
  incremental backup   TRACKMOD ON
  delta backup



offline
online     logarchmeth1 db cfg disk:c:\db2\arch

db2 backup db test to 'XXXX'
db2 backup db test tablespace ts1 to 'XXX'


Logging types:

circuler     (logs are being override so recovering not possible)
archieval (active logs are offloaded to archieval logs)

Inorder take Online backup we have to change logging type to Archival

pwd      > if  /home/db2inst1 then
mkdir archs

db2 "update db cfg for test using LOGARCHMETH1 DISK:/home/db2inst1/archs"

Inorder to take incremental backupS upate trackmode

db2 update db cfg for test using TRACKMOD ON


Backup

 db2 backup db test to '/home/db2inst1/backups' -- offline backup
 db2 backup db test to '/home/db2inst1/backups','/home/db2inst1/backups1'             -- two backup copies
 db2 backup db test online to '/home/db2inst1/backups'
 db2 backup db test incremental to '/home/db2inst1/backups'
 db2 backup db test incremental delta online to '/home/db2inst1/backups'
 db2 backup db test tablespaces tsd4k1 o '/home/db2inst1/backups' -- TS level backup

Restore

 db2 restore db test from '/home/db2inst1/backups' taken at '20150813102013'
 db2 restore db test online from '/home/db2inst1/backups' taken at '20150813103013'
 db2 restore db test incremental delta automatic '/home/db2inst1/backups' taken at '20150813104013'


Rollforward

 db2 rollforward db test to end of logs and complete


 db2 list history backup all for db test


   OP> operation > B for backup
   Obj> object > D for db , P for ts
   Type >  F,N,I,O,D,E
   Status > A-ACTIVE,D-DEL,E-EXPIRED,I-INACTIVE

F full offline
N full online

i incremental offline
o incr online

d delta offline
e delta online


Backup file name :

TEST.0.db2inst1.NODE0000.CATN0000.20150813052532.001

Here,

 TEst - db name
 0  -- db level backup  -- 3- for ts level backup
 db2inst1 -- instance name
 Node0000 -- partition num
 20150813052532 -- timestamp
 001 -- backup file number

Data maintenance utilities :

  db2 reorgchk on table db2inst1.emp
  db2 runstats on table db2inst1.emp
  db2 reorg table db2inst1.emp > offline
  db2 reorg table db2inst1.emp inplace  > online

      db2 "select card from syscat.tables where TABNAME = 'EMP'"

       If CARD = -1 > no runstats done after table creation
 0 > runstats done after table creation
any + value > number of rows in the table emp


runstats : to update data part of catalog tables  .. ex: card coumn of syscat.tables table
reorgchk : to check for whether tablee needs reorg or not ..  if any f1 f2 .. f8 are * then do reorg ..
reorg : to reclaim the fragmented space and rearrange the data in the table ..


Day 10 & 11 :


 db2 list applications
 db2 list applications show detail
 db2 force applications (12)
 db2 force applications all

APPLICATIONS STATus

connect completed
uow executing  (unit of work)
uow waiting
lock waiting



LOCKTIMEOUT:

session 1 :

db2 +c "insert into emp (select * from emp fetch first 10 rows only )

session 2 :

 db2 "select count(*) from emp"
sql0911n rc 68

DEADLOCK :

s1:

 db2 +c "insert into emp(select * from emp fetch first 10 rows only )"

s2:
 db2 +c "insert into emp1(select * fro emp1 fetch first 10 rows only)"

s1:

 db2 "select count(*) from emp1"

s2:
 db2 "select count(*) from emp"
sql0911n  rc 2

 db2 update db cfg for test using CUR_COMMIT OFF
 db2 update db cfg for test using LOCKTIMEOUT 20      --sec
 db2 update db cfg for test using DLCHKTIME 30000     --ms




Day 12


Load phaseS :(offline)

LOAD  > FLAT TO TABLE
BUILD > DATA BUILD
DELETE > VIOLATED DELETE
INDEX COPY >


Reorg phases :(offline)

SORT >
BUILD >
REPLACE >
RECREATE ALL INDEXS >

db2 create table empexcept like emp

db2 "load from '/home/db2inst1/move/data/testempexp.ixf' of ixf messages '/home/db2inst1/move/msg/testempload.msg' insert into db2inst1.emp statistics yes with destribution and detail index all copy yes to '/home/db2inst1/backups' indexing mode rebuild allow read access check pending cascade immediate exception empexcept"

db2 set integrity for emp immediate check force generated for exception in emp use empexcept

To check table check pending status :

db2 "select TABNAME,STATUS from syscat.tables where TABNAME = 'EMP'"

STATUS :  N- normal, X- inoperative, C-check pending

Day 13:


apshot monitor > still camera
event monitor    > video camera



db2 get snapshot for dbm
db2 get snapshot for applications on test
db2 get snapshot for tablespace/bufferpools/db/tables/locks.... on test
..
.


mkdir monitor

db2 create event monitor evstmt1 for statements write to file '/home/db2inst1/monitor'

db2 set event monitor evstmt1 state = 1

run transactions,, then

db2 set event monitor evstmt1 state = 0

cd monitor
ls
0000000.evt -- this is ur required file

db2emon -path '/home/db2inst1/monitor' > '/home/db2inst1/monitor/teststmt1808.evm'

vi teststmt1808.evm

check for start , end and elapsed times (if for statements) n bla bla ..

Backup

authorities

DB2 Architecture

HADR