==============================
WorkSheets - DB2 Databse
==============================
db2 restore database tk1xapib from /olap/TT12258603 taken at 20101008164629 with 4 buffers buffer 4096 parallelism 4 without prompting
** In IVK task- Update the script with LONG Tablespace size : 15316320
** All DB2 licence Krys are stored in - s96gsuc4:/software/DB/db2software/db2_license_keys
gb_db@s96gsuc4:/software/DB/db2software/db2_license_keys $ ls -ltr
total 16
drwxrwxrwx 2 root sys 96 Apr 12 2007 db2v7
drwxrwxrwx 2 root sys 96 May 29 2008 db2v95
drwxrwxrwx 2 root sys 96 Oct 15 2008 db2v9
drwxrwxrwx 2 root sys 8192 Sep 22 10:54 db2v8
In Fleetboard - db2start;nohup db2 -tvf redirct_zu5xai02_160809.sql -l redirct_zu5xai02_160809.log &
For IBLIS Eg TT - 9637862
All possible CI Keys - /sysmgmt/sat/base/bin/sat_ci_provider -db
For doing verify on one db - sat_verify_db -ci s96mif8d6:fk1xat09:FK1XAT09
For NAG/Libelle TT Eg: 10457716
Ref : DB2 process model - http://www.ibm.com/developerworks/data/library/techarticle/0304chong/0304chong.html
For running a SQL in nohup -
zuoxap01@s96vdc0d0:/cluster/vdrg00/db2home/zuoxap01/TT11519486 : cat 11519486.sh
#!/bin/ksh
db2 connect to zuoxap01;
db2 -tvf Delete_FINList2.sql -l Delete_FINList2.log;
db2 terminate;
zuoxap01@s96vdc0d0:/cluster/vdrg00/db2home/zuoxap01/TT11519486 : nohup ./11519486.sh &
For instance creation in EDC - Create user and group
cd /sysmgmt/sat/db_tools/bin
db2_create_instance -U -F -v 9.1 vkixap01
DB2 Database
============
db2 list active databases;echo '';db2 get db cfg for $DB2INSTANCE|grep -i pend;echo '';db2 get snapshot for dbm|grep -i start;echo '';tail -15 sqllib/db2dump/db2diag.log
==============================
1) Checking DB2 error description
==============================
Check diag.log file -
less sqllib/db2dump/db2diag.log
tail -f sqllib/db2dump/db2diag.log
Check the error-
db2diag -rc
db2 "?"
DB2 Knowledge Bank
==================
To check startup time : db2 get snapshot for database manager|head -20
- how to
select system time - db2 values current timestamp
- how to
list the instances configured in the server - in SUN - /opt/IBM/db2/V8.1/instance : ./db2ilist
in AIX - /usr/opt/IBM/db2/db2_08_01/instance
in db2 prompt - db2ilist
list the instances that are running - ps -ef | grep -i db2
create an instance - ./db2icrt -u
list the connected instance - db2 get instance
drop instance - ./db2idrop -f
start an instance - db2start
stop an instance - db2stop
find the version of db2 that the database is using - db2level
find the bit version of db2 that the database is using - db2level
find the fixpack version - db2level
track the memory - db2mtrk -
list the default database path - db2 get dbm cfg | grep -i dbpath
list databases created under one instance - db2 "list db directory"
or
db2 "list db directory"|grep -i "database name"
list the max number of databases activate simultaneously - db2 "get dbm cfg" | grep -i numdb
create database - db2 "create database using codeset territory "
the number of active databases under that instance - db2 "list active databases"
the nodes created - db2 "list node directory"
drop databse - db2 "drop db"
activate the database - db2 "activate db"
know if the database is active - db2 "list active databases"
deactivate the database - db2 force applications all;db2 terminate;db2 deactivate db
restart the database - db2 "restart db"
know if you are already connected to the database - db2 "get connection state"
manually archive a log file - db2 archive log for db
know if the database is remote or local - db2 "list database directory"
disconnect only one application - db2 "force application ()"
disconnect all applications - db2 "force applications all"
start the das - db2 "start database manager"
disconenct from the db after connecting to it - db2 "terminate"
update the parameter newlogpath - db2 "update db cfg for using NEWLOGPATH "
find log file size - 1) db2 get db cfg for | grep -i log
2) (LOGFILSIZ * 4KB)/1024
find total log file size (including primary & secondary) - 1) db2 get db cfg for | grep -i log
2) ((LOGFILSIZ * 4KB)/1024)* (LOGPRIMARY + LOGSECOND)
Eg: ((50000 * 4K)/1024)* (245+10) = 200000 (200 Mb) * 255 = 51000 (51 GB)
list dbm parameters - db2 "get dbm cfg"
update the dbm parameters - db2 "update dbm cfg using "
check what are the environment variables set - db2set -all
update one environment variable - db2set= Eg: db2set db2comm=tcpip
set the environment variable to null / reset the variable - db2set=
provide IBM with db information while opening a PMR - db2support . -d $DB2INSTANCE -cl 0
display the license information - db2licm -l
add a license to the product - db2licm -a
for listing all options go for db2licm
find the database port - 1) db2 get dbm cfg | grep -i svcename
2) cat /etc/services | grep -i
find the location of the installed product , on AIX, sun etc - 1) issue uname -a
2) if sun => opt/IBM/db2/V8.1
3) if AIX => /usr/opt/IBM/db2/db2_08_01
*** what is the difference betweeen connect reset / terminate -
find the authoritises of the user on the database / - query syscat.dbauth or db2 get authorizations
on the tablespaces / query table syscat.tbspaceauth or
on the tables query table syscat.tabauth
find the authorizations for the current user - db2 get authorizations
*** grant connection to the database - db2 "grant connect on database to user"
verify whether the db is in automatic memory management mode - db2 "get db cfg for db2inst1" | grep -i memory
grant role to a user - db2 connect to $DB2INSTANCE user db2secad using db2secad; db2 grant role ZA2_DEVELOPER to kgampe5; db2 terminate;
verify the granted role - db2 connect to $DB2INSTANCE; db2 "select substr(grantee,1,15) grantee ,substr(rolename,1,15) role from syscat.roleauth where rolename='ZA2_DEVELOPER' and grantee='KGAMPE5'";db2 terminate
- how to find
db2diag.log file - db2 get dbm cfg | grep -i diagpath or sqllib/db2dump/
diag level - db2 get dbm cfg | grep -i diaglevel
db2inst1.nfy file - sqllib/db2dump/
db2eventlog.000 - sqllib/db2dump/
- how to find
the number of applications connected to the database - db2 "list active databases"|grep -i applications
the tables in the database - db2 "select count(*) cnt from syscat.tables"
the tablespaces, and find the tablespace size of id=2, find the HWM - db2 "list tablespaces show detail"
db2 "select pagesize from syscat.tablespaces where tbspaceid=2"
if there is a restore/ backup or load in progress - db2 list utilities show detail
db2pd -utilities
the location of the containers of the tablespaces - db2 list tablespace containers for show detail
- how to
find the number of configured bufferpools - query table syscat.bufferpools
create a bufferpool - db2 "create bufferpool size 500 pagesize <4>k"
drop bufferpool - db2 "drop bufferpool"
alter bufferpool - db2 "alter bufferpool immediate size
find active bufferpools - db2 get snapshot for bufferpools on za2xai03|egrep "Bufferpool name|Current size"
find bufferpools with pages - db2 'select substr(BPNAME,1,15) BPNAME,BUFFERPOOLID,PAGESIZE,BLOCKSIZE,npages from syscat.bufferpools'
create tablespace (DMS) - db2 "create tablespace pagesize <4k k..="k.." k="k"> managed by database using (file '/ 1000) extentsize 4 PREFETCHSIZE 8 bufferpool bp_8k"
create tablespace (SMS) - db2 "create tablespace pagesize <4k k..="k.." k="k"> managed by system using ('') extentsize 8 prefetchsize 32 bufferpool bf_16k"
alter tablespace - resize pages - db2 "alter tablespace resize(all )" /
- resize size - db2 "alter tablespace resize(all )" /
- resize pages - db2 "alter tablespace resize(file '' )" /
- extend pages - db2 "alter tablespace extend(file '' )" /
- extend pages - db2 "alter tablespace extend(all )" /
- for simultaniously add|extend|resize containers - db2 "alter tablespace
add (file <'file>')
extend (file <'file>')
rezize (file <'file>')"
- with enabling autoresize - db2 "alter tablespace
autoresize yes increasesize percent
maxsize|none"
find out what is the state of the tablespace means - db2 "list tablespaces show detail" | grep -i state
then issue db2tbst
0x0000 => normal
0x0004 => reorg pending
0x0008 => backup pending
....etc...
- how to find
tablesapce workload - a) db2 "select TBSP_ID,TBSP_NAME,TBSP_TYPE,TBSP_TOTAL_SIZE_KB,TBSP_USABLE_SIZE_KB,TBSP_USED_SIZE_KB,TBSP_FREE_SIZE_KB,TBSP_UTILIZATION_PERCENT,TBSP_USED_PAGES,TBSP_FREE_PAGES,TBSP_PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION"
b) db2 "select SUM(TBSP_USED_SIZE_KB) from SYSIBMADM.TBSP_UTILIZATION"
c) db2 "select (SUM(TBSP_USED_SIZE_KB)*100)/SUM(TBSP_TOTAL_SIZE_KB) from SYSIBMADM.TBSP_UTILIZATION"
- how to find
tablespaces using automatic storage/Auto-resize enabled - db2 get snapshot for tablespaces on $DB2INSTANCE|egrep -i "Tablespace ID|Tablespace name|Using automatic storage|Auto-resize enabled"
- how to find the
territory of the database - db2 get db cfg for | grep -i territory
code set of the database - db2 get db cfg for | grep -i code
service name - db2 get dbm cfg | grep -i svcename
log file size - db2 get db cfg for | grep -i log
heap values - db2 get db cfg for | grep -i heap
*** path to archive log - db2 get db cfg for | grep -i log
*** active log - db2 get db cfg for | grep -i log
number of primary / secondary logs - db2 get db cfg for | grep -i log
how many logs are allocated when the db is started - db2 get db cfg for | grep -i logprimary
*** if the database is in backup / restore / rollforward pending state - db2 get db cfg for | grep -i pending
if the database is in recovery mode - db2 get db cfg for | grep -i recovery
- how to
extract the DDL statements of the objects in the database - using db2look utility
Eg: db2look -d db2test -e -o dup_db2test.sql -l -a -xd -f -fd
Eg: db2look -d vk0xap02 -e -tw J51RHIST -o tab_J51RHIST_data.sql -l -a -xd -f -fd
- how to
export to an ixf file - db2 export to of ixf ""
export a schema - using db2move utility
export a table - db2 export to of ixf "select * from "
*** remove a table from check pending state -
*** export with out using db2export tool -
import into a table - db2 import from of ixf replace_create into in
- how to
*** find if the tables are in check pending state - db2 "select substr(tabname,1,15) TABNAME,status from syscat.tables where status='C'"
*** find the time of last run runstats on the table - db2 "select substr(tabname,1,15) TABNAME,stats_time from syscat.tables where tabname=''"
- how to
do offline reorg on a table - db2 reorg table index
stop reorg on a table - db2 reorg table inplace stop
monitor reorg in a table - db2pd -db -reorgs
reorg a table and use a temp file for this reorg - db2 reorg table use
reorg all indexes for a table - db2 reorg indexes all for table allow read access
update statistics on a table - db2 reorgchk update statistics on table
find the last analyzed date - db2 "select tabname,stats_time from syscat.tables where tabname='' and tabschema=''"
runstats on a table - db2 runstats on table allow read access
Online reorg - db2 "select 'REORG TABLE '||TRIM(tabschema)||'.'||tabname||' INPLACE ALLOW WRITE ACCESS;' from syscat.tables where tabschema in (select distinct substr(tabschema,1,15)tabschema from syscat.tables where tabschema not like 'SYS%')"
Online RUNSTAT -
db2 "select 'RUNSTATS ON TABLE '||TRIM(tabschema)||'.'||tabname||' AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;' from syscat.tables where tabschema in (select distinct substr(tabschema,1,15)tabschema from syscat.tables where tabschema not like 'SYS%') and type='T'"
- how to check reorg stats
db2 "select REORG_START,REORG_END from sysibmadm.snaptab_reorg where tabname='FK1R_MGNTUNITFILTE'"
select rtrim(substr(table_schema,1,10)) as "Schema",rtrim(substr(table_name,1,29)) as "Table_name",
case reorg_status
when 1 then 'Started'
when 2 then 'Paused'
when 3 then 'Stopped'
when 4 then 'Completed'
when 5 then 'Truncated'
end as status,
(minute(reorg_end - reorg_start)) as "Minutes_Ran"
from table(sysproc.snapshot_tbreorg('',-1)) as t where date (reorg_start) > (current_date - 1 day) order by reorg_start desc;
db2 "describe select * from table(snapshot_tbreorg('',0))a"
- how to find
since when the database has been up - db2 get snapshot for dbm | grep -i timestamp or
db2 get snapshot for dbm | grep -i start
detailed inforamation of the applications - db2 list applications for db show detail
*** number of deadlocks - db2 get snapshot for database on | grep -i lock
*** if any application is blocking another application - db2 get snapshot for all applications | grep -i lock
- db2 get snapshot for all applications | egrep -i "held|handle"
No of locked tables - db2 get snapshot for locks on $DB2INSTANCE|grep -i "Table Name" | sort -u
No of locked tablespaces - db2 get snapshot for locks on $DB2INSTANCE|grep -i "Tablespace Name" | sort -u
No of locked schemas - db2 get snapshot for locks on $DB2INSTANCE|grep -i "Table Schema" | sort -u db2 get snapshot for application agentid "59805"
- how to
Check the integrity of backup image - db2ckbkp -h
check database size - db2 "call get_dbsize_info(?,?,?,-1)"
backup a database to disk - db2 backup database online to
check backup retention policy - db2 get db cfg for | grep -i num_db_backups
*** backup a database to tsm -
*** One tablespace is in backup pending state, how to remove it from - db2 backup db tablespace online use tsm
*** backup pending state - db2 get db cfg for | grep -i pending
*** check the backup file present in tsm - db2adutl query full db
*** verify if the backup is not corrupted - db2adutl verify
verify the tablesapce info in backup - db2adutl verify tablespaceonly taken at db
retrive the backup image to the local FS - db2adutl extract full taken at without prompting
- how to
list backup history - db2 list history backup all for db
list archive history - db2 list history archive log all for | tail -5
list history of backups stored in tsm - db2adutl query full db
*** list the history of tables that are altered -
- how to
recover a database to the first consistency point - db2 rollforward db query status;
db2 rollforward db stop;
db2 rollforward db to ;
db2 rollforward db complete
recover a database to end of logs - db2 rollforward db to end of logs
recover a database to a PIT - db2 rollforward db to
complete the recovery process - db2 rollforward db to [|end of logs] complete
stop the recovery process - db2 rollforward db stop
- how to restore a database
from a backup on disk - db2 restore db from taken at
*** from a backup IN TSM - db2 restore db use tsm taken at
*** with different FS structures, how would you set the target container -
paths
Restore a database from its own backup:
---------------------------------------
db2adutl query full db $DB2INSTANCE
db2 list history backup all for $DB2INSTANCE
db2 get db cfg for $DB2INSTANCE|grep -i "Path to"
db2 connect to $DB2INSTANCE; db2 "call get_dbsize_info(?,?,?,-1)" ; db2 terminate
db2 list active databases
db2 force applications all;db2 terminate
db2 force applications all;db2 terminate
db2stop force
db2start;nohup db2 restore db $DB2INSTANCE use tsm open 1 sessions taken at with 2 buffers buffer 1024 parallelism 1 without prompting &
tail -f nohup.out
db2 list utilities show detail
db2 rollforward db $DB2INSTANCE stop
db2 rollforward db $DB2INSTANCE to ""
db2 rollforward db $DB2INSTANCE complete
db2 get db cfg for $DB2INSTANCE |grep -i pend
db2 connect to $DB2INSTANCE;db2 terminate
db2 backup db $DB2INSTANCE tablespace syscatspace online use tsm
db2 get db cfg for $DB2INSTANCE|grep -i "Path to"
---------------------------------------
- how to check the listener status
check service name - db2 get dbm cfg |grep -i svc
check services for port number - cat /etc/services | grep -i|grep -v DB2_
grep "^$DB2INSTANCE" /etc/services | awk '{print $2}' | awk -F'/' '{print $1}'
Check the port availability - netstat -an | grep -i
Check reorg on user defined date - db2 "select * from table(snapshot_tbreorg('',-1)) x where date(reorg_start) = '07.06.2009'"
- how to
drop objects in a schema - db2 "select 'DROP '||case type when 'T' then 'TABLE ' when 'V' then 'VIEW ' end||tabschema||'.'||tabname||';' from syscat.tables where tabschema not like '%SYS%'"
- how to
Kill a DB2 application - db2 force application ""
select current timestamp from dual - db2 "SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1"
Checking the db is whether in archive/noarchive mode - db2 get db cfg for VKJXAP90|egrep "LOGRETAIN|USEREXIT"
- how to
Find the backup size directly taken to TSM - dsmc query backup "//*"
Find the object name by object ID - db2 "select substr(rtrim(tabschema)||'.'||rtrim(tabname),1,60) from syscat.tables where tbspaceid=12 and tableid=535"
-how to
Drop deadlock event monitor - db2 "select * from syscat.eventmonitors where EVMONNAME ='DB2DETAILDEADLOCK'"
db2 set
db2detaildeadlock state 0
db2 drop event monitor db2detaildeadlock
DB Export for db2:
==================
root / ssh / O
su -
Check db size
db2 connect to
db2 "call get_dbsize_info(?,?,?,-1)"
db2 terminate
Check the space in OS level and craete one directory in the name of TT
Go to the directory and issue-
db2look -d -a -x -e -l -f -o db2look__.ddl
db2move export -aw
Reorg/Runstat on database
=========================
reorg on database-
1) db2 "select distinct TABSCHEMA from syscat.tables"
2) db2 "select 'reorg table '||ltrim(rtrim(TABSCHEMA))||'.'||ltrim(rtrim(TABNAME))||' inplace allow write access;' from syscat.tables where TABSCHEMA='' and type='T'"
3) db2 "select 'reorg indexes all for table '||ltrim(rtrim(TABSCHEMA))||'.'||ltrim(rtrim(TABNAME))||' allow write access;' from syscat.tables where TABSCHEMA='' and type='T'"
runstat on database-
1) runstats on table. and detailed indexes all allow write access;
2) db2 "select 'runstats on table '||ltrim(rtrim(TABSCHEMA))||'.'||ltrim(rtrim(TABNAME))||' and detailed indexes all allow write access;' from syscat.tables where TABSCHEMA='' and type='T'"
or
db2 "runstats on table MTMCSEIN.MTMROR AND detailed INDEXES all ALLOW NO ACCESS"
For a runstat with no access
or
db2 "runstats on table MTMCSEIN.MTMROR AND detailed INDEXES all ALLOW read ACCESS"
For a runstat with read access
--------
Example:
--------
Following is an example for RUNSTAT through shell script (From IVK)
vl6xat01@s96ivk2d0:/home/vl6xat01 : cat runstats.sh
#!/usr/bin/ksh
DB=$LOGNAME
tmpf=/tmp/runstats.$$.tmp
db2 connect to $DB
db2 -x "select tabschema,tabname from syscat.tables where tabschema not like 'SYS%' and type='T'" > $tmpf
cat $tmpf | while read s t
do
db2 -v "runstats on table $s.$t and detailed indexes all"
done
rm -f $tmpf
db2 connect reset;
----------------------------------------------------------------------------------------
Example script to REORG and RUNSTATS on a database other than tabschema not like 'SYS%':
----------------------------------------------------------------------------------------
#!/usr/bin/ksh
DB=$LOGNAME
tmpe=/tmp/reorg.$$.tmp
tmpf=/tmp/runstats.$$.tmp
db2 connect to $DB
db2 -x "select tabschema,tabname from syscat.tables where tabschema not like 'SYS%'" > $tmpe
cat $tmpe | while read p q
do
db2 -v "reorg table $p.$q inplace allow write access"
done
rm -f $tmpe
db2 -x "select tabschema,tabname from syscat.tables where tabschema not like 'SYS%' and type='T'" > $tmpf
cat $tmpf | while read s t
do
db2 -v "runstats on table $s.$t and detailed indexes all allow write access"
done
rm -f $tmpf
db2 connect reset;
Backup/Restore
==============
DB2 offline backup to local directory:
--------------------------------------
1) mkdir
2) chown -R:
3) db2 force applications all;db2 force applications all
4) db2 terminate;db2stop force
5) db2start;db2 backup db $DB2INSTANCE to
or
5) db2start;nohup db2 backup db $DB2INSTANCE to compress &
6) db2 terminate;db2 activate db $DB2INSTANCE
DB2 online backup to local directory:
-------------------------------------
1) mkdir
2) chown -R:
3) db2start;db2 backup db $DB2INSTANCE online to
or
3) db2start;nohup db2 backup db $DB2INSTANCE online to compress &
DB2 offline backup to TSM:
--------------------------
1) db2 force applications all;db2 deactivate database $DB2INSTANCE;db2stop force
2) db2start;db2 backup db $DB2INSTANCE use tsm
3) db2 list utilities show detail
4) db2 list history backup all for database $DB2INSTANCE | tail -30
5) db2 terminate;db2 activate db $DB2INSTANCE
6) db2adutl query full db $DB2INSTANCE
DB2 online backup to TSM:
-------------------------
Database level backup:
----------------------
1) db2 backup db $DB2INSTANCE online use tsm (We can add parallelism and buffers for a fast backup)
2) db2 list utilities show detail
3) db2 list history backup all for database $DB2INSTANCE | tail -30
4) db2adutl query full db $DB2INSTANCE
Tablespace level backup:
------------------------
1) db2 backup db $DB2INSTANCE tablespace online use tsm
2) db2 list utilities show detail
3) db2 list history backup all for database $DB2INSTANCE | tail -30
DB2 Restore:
------------
db2 restore db TK0XAT01 use tsm open 1 sessions taken at 20091125230545 with 2 buffers buffer 1024 parallelism 1
db2 rollforward db TK0XAT01 to 2009-11-26-08.50.00.000000 USING LOCAL TIME
db2start;db2 restore db oftxat01 from /dbdumps/oftxat01 taken at 20110110072033
Shell scripting with DB2
========================
IVK tasks -Downloading dump files
---------------------------------
for i in
do
wget $i
done
Example:
--------
for i in https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-a https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-b https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-c https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-d https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-e https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-f https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-g https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-h
do
wget $i
done
database="ZU5XAP01"
echo "START"
date
db2 connect to $dataBase
echo "convert to large"
time db2 "ALTER TABLESPACE USERSPACE5 CONVERT TO LARGE"
echo "doing reorg"
time db2 "REORG TABLE FB.TOUREVENTRECORDDATA ALLOW NO ACCESS USE TEMPSYS16K"
echo "DONE"
date
-------------------------------
- Multiple db restarted in DB2:
-------------------------------
for i in fk8xap01 tkaxat01 tkexap01 tkexat01 vkdxap01 vlhxae01 vlhxae02 vlhxai01 vlhxai02 vlhxap01 vlhxap02 zu1xap01 zu1xap02 zurxat01
do
echo "Database $i@`hostname`"
echo "--------------------------"
su - $i -c "db2 list active databases;db2 get db cfg for $i|grep -i pend"
echo ''
echo '++++++++++++++++++++++++++++++++++++++++++++++++++'
echo ''
done
for i in fk8xap01 tkaxat01 tkexap01 tkexat01 vkdxap01 vlhxae01 vlhxae02 vlhxai01 vlhxai02 vlhxap01 vlhxap02 zu1xap01 zu1xap02 zurxat01
do
echo "Database $i@`hostname`"
echo "--------------------------"
su - $i -c "db2 force applications all;db2 force applications all;db2 force applications all;db2 terminate;db2stop force;db2start;db2 terminate;db2 activate db $i;db2 list active databases;db2 get db cfg for $i|grep -i pend;db2 get snapshot for dbm|grep -i start"
echo ''
echo "Restart completed for Instance $i@`hostname`"
echo ''
echo '++++++++++++++++++++++++++++++++++++++++++++++++++'
echo ''
done
-- For checking all instance/databases memory in a server
for i in zu8xai01 zu8xai02 zu8xai04 zu8xai05 zu8xai06
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 attach to $i;db2 get dbm cfg show detail|grep -i memory;db2 connect to $i;db2 get db cfg for $i show detail|grep -i memory;db2 terminate"
echo ''
done
-- For checking all databases in a server
for i in
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;db2 connect to $i;db2 terminate"
echo ''
done
Example:
--------
root@s96dbt01 > for i in oftxat01 db2inst1
root@s96dbt01 > do
root@s96dbt01 > echo ''
root@s96dbt01 > echo "Database $i@`hostname`"
root@s96dbt01 > echo "----------------------------"
root@s96dbt01 > su - $i -c "db2 list active databases;db2 connect to $i;db2 terminate"
root@s96dbt01 > echo ''
root@s96dbt01 > done
-- For shutdown all databases in a server
for i in vk1xap02 vk1xap03 vk1xap04 vk1xap07 vk1xap08 vk1xap09 vk1xap10 vk1xap11 vk1xap12 vk1xap13
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases"
echo ''
done
for i in vk1xap02 vk1xap03 vk1xap04 vk1xap07 vk1xap08 vk1xap09 vk1xap10 vk1xap11 vk1xap12 vk1xap13
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;db2 force applications all;db2 terminate;db2 deactivate db $i;db2stop"
echo ''
done
-- For restarting all databases in a server
do
echo $i
su - $i -c "db2start;db2 activate db $i"
done
-- For checking the current active log for all dbs in a server -
for i in vk1xap02 vk1xap03 vk1xap04 vk1xap07 vk1xap08 vk1xap09 vk1xap10 vk1xap11 vk1xap12 vk1xap13 vk1xap14
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;db2 get db cfg for $i | grep -i log | grep -i "First active log file""
echo ''
done
-- For collecting the current settings for all dbs in a server -
for i in pk6xae01 pk6xae02 pk6xae04
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "cd /db2/TT9324315/$i;db2 list db directory > db_dir.txt;db2set -all > dbset_cfg.txt"
echo ''
done
-- For checking Restoration status of Fleetboard databases
for i in zu5xai01 zu5xai03
do
echo ' '
echo "Restoration status for $i@`hostname`"
echo "-------------------------------------------"
su - $i -c "db2 list utilities show detail"
echo ' '
done
-- For checking active status and the last logged message in .log
for i in za3xat01 za3xat03 za3xat07
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;echo '';echo 'from diag.log -';echo '';cat sqllib/db2dump/db2diag.log|tail -15"
echo ''
done
-- For taking offline backup to local FS
for i in zaexap40 zaexap41 zaexap42 zaexap43 zaexap44 zaexap45 zaexap46 zaexap47
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 connect to $i;db2 'call get_dbsize_info(?,?,?,-1)';db2 terminate"
echo ''
done
root@s96cd2d1:/db2_omnib/TT9928633 : for i in zaexap40 zaexap41 zaexap42 zaexap43 zaexap44 zaexap45 zaexap46 zaexap47
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "mkdir /db2_omnib/TT9928633/$i "
echo ''
root@s96cd2d1 > do> done
for i in zaexap40 zaexap41 zaexap42 zaexap43 zaexap44 zaexap45 zaexap46 zaexap47
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2stop force;db2start;db2 backup db $i to /db2_omnib/TT9928633/$i/ "
echo ''
done
-- For checking locks in a server
for i in zujxap01 zujxap02 zujxap03 zujxap04 zujxap05
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 get snapshot for all applications|egrep -i 'held|handle'; db2 get snapshot for locks on $i|grep -i 'Table Name'|sort -u; db2 get snapshot for locks on $i|grep -i 'Tablespace Name'|sort -u; db2 get snapshot for locks on $i|grep -i 'Table Schema'|sort -u"
echo ''
done
how to interpret error in db2diag.log
=========================================
Example: 2008-09-19-09.58.01.887762+120 I3253534C487 LEVEL: Error
PID : 13806 TID : 1 PROC : db2agent () 0
INSTANCE: NODE : 000 DB :
APPHDL : 0-48 APPID: *LOCAL..080919075801
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:540
RETCODE : ZRC=0x850F0081=-2062614399=SQLO_SSEM_EXCEED_MAX
"Requesting too many semaphores"
DIA8336C Requested too many semaphores
Action : db2diag -rc 0x850F0081
Input ZRC string '0x850F0081' parsed as 0x850F0081 (-2062614399).
ZRC value to map: 0x850F0081 (-2062614399)
V7 Equivalent ZRC value: 0xFFFFD681 (-10623)
ZRC class :
Resource Capacity Error (Class Index: 5)
Component:
SQLO ; oper system services (Component Index: 15)
Reason Code:
129 (0x0081)
Identifer:
SQLO_SSEM_EXCEED_MAX
Identifer (without component):
SQLZ_RC_MAX_SEM
Description:
Requesting too many semaphores
Associated information:
Sqlcode -902
SQL0902C A system error (reason code = "") occurred. Subsequent SQL
statements cannot be processed.
Number of sqlca tokens : 1
Diaglog message number: 8336
@s96dbt01:/home//sqllib/db2dump : db2 ? SQL0902C
SQL0902C A system error (reason code = "")
occurred. Subsequent SQL statements cannot be
processed.
Explanation:
A system error occurred.
User Response:
Record the message number (SQLCODE) and reason code in the
message.
If trace was active, invoke the Independent Trace Facility at
the operating system command prompt. Then contact your technical
service representative with the following information:
o Problem description
o SQLCODE and embedded reason code
o SQLCA contents if possible
o Trace file if possible.
Federated system users: isolate the problem to the data source
failing the request (refer to the problem determination guide to
determine which data source is failing to process the SQL
statement) and take the necessary diagnostic steps for that data
source. The problem determination procedures for data sources
vary, so refer to the applicable data source manuals.
sqlcode : -902
sqlstate : 58005
@s96dbt01:/home//sqllib/db2dump : db2 ? "SQL1040"
SQL1040N The maximum number of applications is already connected
to the database.
Explanation:
The number of applications connected to the database is equal to
the maximum value defined in the configuration file for the
database.
The command cannot be processed.
User Response:
Wait for other applications to disconnect from the database. If
more applications are required to run concurrently, increase the
value for maxappls . After all applications disconnect from the
database and the database is restarted, the new value takes
effect.
sqlcode : -1040
sqlstate : 57030
DB2 commands
============
For uploading the db2 instance details for PMR - db2support . -d -cl 0
To find out who is the fenced user - Check the owner of the file/sqllib/adm/.fenced
DB2 max connections exceeded - db2 get dbm cfg |grep -i max
ps -ef | grep|wc -l
db2 get dbm cfg |grep -i agents
db2pd -agents|head
Backup and restore parallelism
==============================
Go to the > db2_omnib > > > > usetsm > issue ls -ltr|tail > cd
check file name like ora_adsm.result.0
root@s96gsuc1:/root : ora_omnib -l s96fbc1-200902081900
09.02.09 06:35:55 (s96gsuc1-l) Logfile '/log/ora_omnib/detail/s96fbc1.200902081900'
08.02.09 19:00:07 (s96fbc1-T) Begin s96fbc1-200902081900-TM
08.02.09 19:00:07 (s96fbc1-T) Perform total Backup of Oracle/DB2 and Unix
08.02.09 19:00:08 (s96fbc1-T) Override REDOXNODE=53.71.190.62
08.02.09 19:00:09 (s96fbc1-T) Found dsm.sys Address '172.17.126.21'
08.02.09 19:00:09 (s96fbc1-T) Set OMNIHOST to 's96tsm09'
08.02.09 19:00:10 (s96fbc1-T) Set ROBOT to '1'
08.02.09 19:00:13 (s96fbc1-T) Notice, create Flagfile '/sysadm/utils/ora_adsm.act.s96fbc1.O'
08.02.09 19:00:23 (s96fbc1-T) Access remote Oracle/DB2 Kontext, possibly DB2 Backup
08.02.09 19:00:24 (s96fbc1-T) Remote Call '/tmp/ora_adsm -GO s96fbc1.OM_omnib'
08.02.09 18:00:24*(s96fbc1-G) Invoked '/cluster/s96fbc1/glockware/sysadm/utils/ora_adsm -GO s96fbc1.OM_omnib'
08.02.09 18:00:24*(s96fbc1-G) Set OMNIHOST to '.'
08.02.09 18:00:24*(s96fbc1-G) Set ROBOT to '1'
08.02.09 18:00:24*(s96fbc1-G) Notice, Option 'G1' detected
08.02.09 18:03:27*(s96fbc1-G) Invoke DB2 Backup Pass
08.02.09 18:03:29*(s96fbc1-G) Node 's96fbc1' has 16 Filesystems
08.02.09 18:03:38*(s96fbc1-G) DB2 Database 'ZU5XAP01=0', Instance 'zu5xap01' found
08.02.09 18:03:38*(s96fbc1-G) Notice, check DB2 INCLUDE LOGS 'zu5xap01.ZU5XAP01=0'
08.02.09 18:04:09*(s96fbc1-G) Longest Dump has 239292416 Byte, Space for 3 more Dumps..
08.02.09 18:04:09*(s96fbc1-G) DB2 Dump to '/db2_omnib/s96fbc1/zu5xap01/ZU5XAP01=0/20090208180023'
08.02.09 18:04:09*(s96fbc1-G) Invoke 'backup db ZU5XAP01 online use tsm open 2 sessions with 8 buffers buffer 8000 paralle
lism 16 without prompting'
08.02.09 23:13:50*(s96fbc1-G) Warning, Error '2025' at DB2 online Backup 'ZU5XAP01=0'
08.02.09 23:13:51*(s96fbc1-G) DB2 Database 'ZU5XAP02=0', Instance 'zu5xap02' found
08.02.09 23:13:51*(s96fbc1-G) Notice, check DB2 INCLUDE LOGS 'zu5xap02.ZU5XAP02=0'
08.02.09 23:14:21*(s96fbc1-G) Longest Dump has 239292416 Byte, Space for 3 more Dumps..
08.02.09 23:14:21*(s96fbc1-G) DB2 Dump to '/db2_omnib/s96fbc1/zu5xap02/ZU5XAP02=0/20090208180023'
08.02.09 23:14:21*(s96fbc1-G) Invoke 'backup db ZU5XAP02 online use tsm open 1 sessions with 2 buffers buffer 2048 paralle
lism 1 without prompting'
08.02.09 23:19:40*(s96fbc1-G) Instance 'ZU5XAP02=0' backed up (C)
08.02.09 23:19:40*(s96fbc1-G) DB2 Database 'ZU5XAP04=0', Instance 'zu5xap04' found
08.02.09 23:19:40*(s96fbc1-G) Notice, check DB2 INCLUDE LOGS 'zu5xap04.ZU5XAP04=0'
08.02.09 23:20:15*(s96fbc1-G) Longest Dump has 239292416 Byte, Space for 3 more Dumps..
08.02.09 23:20:15*(s96fbc1-G) DB2 Dump to '/db2_omnib/s96fbc1/zu5xap04/ZU5XAP04=0/20090208180023'
08.02.09 23:20:15*(s96fbc1-G) Invoke 'backup db ZU5XAP04 online use tsm open 1 sessions with 2 buffers buffer 2048 paralle
lism 1 without prompting'
08.02.09 23:21:20*(s96fbc1-G) Instance 'ZU5XAP04=0' backed up (C)
08.02.09 23:21:21*(s96fbc1-G) Max.Redolength 1000000 Byte
09.02.09 00:21:55 (s96fbc1-T) Break, DB2 Backup failed
09.02.09 00:21:56 (s96fbc1-T) Error 2025 User 'zu5xap01', Instance 'ZU5XAP01=0' (An I/O error "-50" occurred on media "TSM
".)
09.02.09 00:21:58 (s96fbc1-T) End s96fbc1-200902081900-TM-2025-321-s96gsuc1-0-1659248
09.02.09 06:36:12 (s96gsuc1-l) Done
root@s96gsuc1:/root :
RESTORE DATABASE ZU5XAP01
USE TSM
OPEN 2 SESSIONS
TAKEN AT 20090206180425
INTO ZU5XAI01
-- NEWLOGPATH /onlineredo/ZU5XAI01/NODE0000
WITH 8 BUFFERS BUFFER 8000
REDIRECT
PARALLELISM 8
;
DB2 SQLs
========
db2 "select substr(TABNAME,1,15) TABNAME , CASE TYPE WHEN 'A' THEN ' Alias' WHEN 'H' THEN 'Hierarchy table' WHEN 'N' THEN 'Nickname' WHEN 'S' THEN 'Materialized query table' WHEN 'T' THEN 'Table' WHEN 'U' THEN 'Typed table' WHEN 'v' THEN 'View' WHEN 'W' THEN 'Typed vew' END TYPE from syscat.tables where status='C' and TABSCHEMA='MIF_DEV'"
Create db with default tablespaces in desired location
======================================================
create database fk1xat03 using codeset UTF-8 territory DE catalog tablespace managed by database using ( file '/db2_data/FK1XAT03/system1/SYSCATSPACE_1.dat' 165320 ) extentsize 4 prefetchsize 16 temporary tablespace managed by system using ('/db2_data/FK1XAT03/temp1sms/TEMPSPACE1_1' ) extentsize 32 prefetchsize 32 user tablespace managed by database using ( file '/db2_data/FK1XAT03/data1/USERSPACE1_1.dat' 60000 ) extentsize 32 prefetchsize 32;
update parameter CATALOGCACHE_SZ
=================================
db2 update db cfg for fk1xat03 using CATALOGCACHE_SZ -1
$ cat db2look_mifdev.log | grep -i SQL | sort -u
$ cat db2look_mifdev.log | egrep "SQL0|DB20" | grep -v "DB20000I" | grep -v "ALTER"
nohup db2 restore db vl6xap02 logs use TSM taken at 20110523030248 logtarget /onlineredo/VL6XAQ01/NODE0000/ &
WorkSheets - DB2 Databse
==============================
db2 restore database tk1xapib from /olap/TT12258603 taken at 20101008164629 with 4 buffers buffer 4096 parallelism 4 without prompting
** In IVK task- Update the script with LONG Tablespace size : 15316320
** All DB2 licence Krys are stored in - s96gsuc4:/software/DB/db2software/db2_license_keys
gb_db@s96gsuc4:/software/DB/db2software/db2_license_keys $ ls -ltr
total 16
drwxrwxrwx 2 root sys 96 Apr 12 2007 db2v7
drwxrwxrwx 2 root sys 96 May 29 2008 db2v95
drwxrwxrwx 2 root sys 96 Oct 15 2008 db2v9
drwxrwxrwx 2 root sys 8192 Sep 22 10:54 db2v8
In Fleetboard - db2start;nohup db2 -tvf redirct_zu5xai02_160809.sql -l redirct_zu5xai02_160809.log &
For IBLIS Eg TT - 9637862
All possible CI Keys - /sysmgmt/sat/base/bin/sat_ci_provider -db
For doing verify on one db - sat_verify_db -ci s96mif8d6:fk1xat09:FK1XAT09
For NAG/Libelle TT Eg: 10457716
Ref : DB2 process model - http://www.ibm.com/developerworks/data/library/techarticle/0304chong/0304chong.html
For running a SQL in nohup -
zuoxap01@s96vdc0d0:/cluster/vdrg00/db2home/zuoxap01/TT11519486 : cat 11519486.sh
#!/bin/ksh
db2 connect to zuoxap01;
db2 -tvf Delete_FINList2.sql -l Delete_FINList2.log;
db2 terminate;
zuoxap01@s96vdc0d0:/cluster/vdrg00/db2home/zuoxap01/TT11519486 : nohup ./11519486.sh &
For instance creation in EDC - Create user and group
cd /sysmgmt/sat/db_tools/bin
db2_create_instance -U -F -v 9.1 vkixap01
DB2 Database
============
db2 list active databases;echo '';db2 get db cfg for $DB2INSTANCE|grep -i pend;echo '';db2 get snapshot for dbm|grep -i start;echo '';tail -15 sqllib/db2dump/db2diag.log
==============================
1) Checking DB2 error description
==============================
Check diag.log file -
less sqllib/db2dump/db2diag.log
tail -f sqllib/db2dump/db2diag.log
Check the error-
db2diag -rc
db2 "?
DB2 Knowledge Bank
==================
To check startup time : db2 get snapshot for database manager|head -20
- how to
select system time - db2 values current timestamp
- how to
list the instances configured in the server - in SUN - /opt/IBM/db2/V8.1/instance : ./db2ilist
in AIX - /usr/opt/IBM/db2/db2_08_01/instance
in db2 prompt - db2ilist
list the instances that are running - ps -ef | grep -i db2
create an instance - ./db2icrt -u
list the connected instance - db2 get instance
drop instance - ./db2idrop -f
start an instance - db2start
stop an instance - db2stop
find the version of db2 that the database is using - db2level
find the bit version of db2 that the database is using - db2level
find the fixpack version - db2level
track the memory - db2mtrk -
list the default database path - db2 get dbm cfg | grep -i dbpath
list databases created under one instance - db2 "list db directory"
or
db2 "list db directory"|grep -i "database name"
list the max number of databases activate simultaneously - db2 "get dbm cfg" | grep -i numdb
create database - db2 "create database
the number of active databases under that instance - db2 "list active databases"
the nodes created - db2 "list node directory"
drop databse - db2 "drop db
activate the database - db2 "activate db
know if the database is active - db2 "list active databases"
deactivate the database - db2 force applications all;db2 terminate;db2 deactivate db
restart the database - db2 "restart db
know if you are already connected to the database - db2 "get connection state"
manually archive a log file - db2 archive log for db
know if the database is remote or local - db2 "list database directory"
disconnect only one application - db2 "force application (
disconnect all applications - db2 "force applications all"
start the das - db2 "start database manager"
disconenct from the db after connecting to it - db2 "terminate"
update the parameter newlogpath - db2 "update db cfg for
find log file size - 1) db2 get db cfg for
2) (LOGFILSIZ * 4KB)/1024
find total log file size (including primary & secondary) - 1) db2 get db cfg for
2) ((LOGFILSIZ * 4KB)/1024)* (LOGPRIMARY + LOGSECOND)
Eg: ((50000 * 4K)/1024)* (245+10) = 200000 (200 Mb) * 255 = 51000 (51 GB)
list dbm parameters - db2 "get dbm cfg"
update the dbm parameters - db2 "update dbm cfg using
check what are the environment variables set - db2set -all
update one environment variable - db2set
set the environment variable to null / reset the variable - db2set
provide IBM with db information while opening a PMR - db2support . -d $DB2INSTANCE -cl 0
display the license information - db2licm -l
add a license to the product - db2licm -a
for listing all options go for db2licm
find the database port - 1) db2 get dbm cfg | grep -i svcename
2) cat /etc/services | grep -i
find the location of the installed product , on AIX, sun etc - 1) issue uname -a
2) if sun => opt/IBM/db2/V8.1
3) if AIX => /usr/opt/IBM/db2/db2_08_01
*** what is the difference betweeen connect reset / terminate -
find the authoritises of the user on the database / - query syscat.dbauth or db2 get authorizations
on the tablespaces / query table syscat.tbspaceauth or
on the tables query table syscat.tabauth
find the authorizations for the current user - db2 get authorizations
*** grant connection to the database - db2 "grant connect on database to user
verify whether the db is in automatic memory management mode - db2 "get db cfg for db2inst1" | grep -i memory
grant role to a user - db2 connect to $DB2INSTANCE user db2secad using db2secad; db2 grant role ZA2_DEVELOPER to kgampe5; db2 terminate;
verify the granted role - db2 connect to $DB2INSTANCE; db2 "select substr(grantee,1,15) grantee ,substr(rolename,1,15) role from syscat.roleauth where rolename='ZA2_DEVELOPER' and grantee='KGAMPE5'";db2 terminate
- how to find
db2diag.log file - db2 get dbm cfg | grep -i diagpath or sqllib/db2dump/
diag level - db2 get dbm cfg | grep -i diaglevel
db2inst1.nfy file - sqllib/db2dump/
db2eventlog.000 - sqllib/db2dump/
- how to find
the number of applications connected to the database - db2 "list active databases"|grep -i applications
the tables in the database - db2 "select count(*) cnt from syscat.tables"
the tablespaces, and find the tablespace size of id=2, find the HWM - db2 "list tablespaces show detail"
db2 "select pagesize from syscat.tablespaces where tbspaceid=2"
if there is a restore/ backup or load in progress - db2 list utilities show detail
db2pd -utilities
the location of the containers of the tablespaces - db2 list tablespace containers for
- how to
find the number of configured bufferpools - query table syscat.bufferpools
create a bufferpool - db2 "create bufferpool
drop bufferpool - db2 "drop bufferpool
alter bufferpool - db2 "alter bufferpool
find active bufferpools - db2 get snapshot for bufferpools on za2xai03|egrep "Bufferpool name|Current size"
find bufferpools with pages - db2 'select substr(BPNAME,1,15) BPNAME,BUFFERPOOLID,PAGESIZE,BLOCKSIZE,npages from syscat.bufferpools'
create tablespace (DMS) - db2 "create tablespace
create tablespace (SMS) - db2 "create tablespace
alter tablespace - resize pages - db2 "alter tablespace
- resize size - db2 "alter tablespace
- resize pages - db2 "alter tablespace
- extend pages - db2 "alter tablespace
- extend pages - db2 "alter tablespace
- for simultaniously add|extend|resize containers - db2 "alter tablespace
add (file <'file>'
extend (file <'file>'
rezize (file <'file>'
- with enabling autoresize - db2 "alter tablespace
autoresize yes increasesize
maxsize
find out what is the state of the tablespace means - db2 "list tablespaces show detail" | grep -i state
then issue db2tbst
0x0000 => normal
0x0004 => reorg pending
0x0008 => backup pending
....etc...
- how to find
tablesapce workload - a) db2 "select TBSP_ID,TBSP_NAME,TBSP_TYPE,TBSP_TOTAL_SIZE_KB,TBSP_USABLE_SIZE_KB,TBSP_USED_SIZE_KB,TBSP_FREE_SIZE_KB,TBSP_UTILIZATION_PERCENT,TBSP_USED_PAGES,TBSP_FREE_PAGES,TBSP_PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION"
b) db2 "select SUM(TBSP_USED_SIZE_KB) from SYSIBMADM.TBSP_UTILIZATION"
c) db2 "select (SUM(TBSP_USED_SIZE_KB)*100)/SUM(TBSP_TOTAL_SIZE_KB) from SYSIBMADM.TBSP_UTILIZATION"
- how to find
tablespaces using automatic storage/Auto-resize enabled - db2 get snapshot for tablespaces on $DB2INSTANCE|egrep -i "Tablespace ID|Tablespace name|Using automatic storage|Auto-resize enabled"
- how to find the
territory of the database - db2 get db cfg for
code set of the database - db2 get db cfg for
service name - db2 get dbm cfg | grep -i svcename
log file size - db2 get db cfg for
heap values - db2 get db cfg for
*** path to archive log - db2 get db cfg for
*** active log - db2 get db cfg for
number of primary / secondary logs - db2 get db cfg for
how many logs are allocated when the db is started - db2 get db cfg for
*** if the database is in backup / restore / rollforward pending state - db2 get db cfg for
if the database is in recovery mode - db2 get db cfg for
- how to
extract the DDL statements of the objects in the database - using db2look utility
Eg: db2look -d db2test -e -o dup_db2test.sql -l -a -xd -f -fd
Eg: db2look -d vk0xap02 -e -tw J51RHIST -o tab_J51RHIST_data.sql -l -a -xd -f -fd
- how to
export to an ixf file - db2 export to
export a schema - using db2move utility
export a table - db2 export to
*** remove a table from check pending state -
*** export with out using db2export tool -
import into a table - db2 import from
- how to
*** find if the tables are in check pending state - db2 "select substr(tabname,1,15) TABNAME,status from syscat.tables where status='C'"
*** find the time of last run runstats on the table - db2 "select substr(tabname,1,15) TABNAME,stats_time from syscat.tables where tabname=''"
- how to
do offline reorg on a table - db2 reorg table
stop reorg on a table - db2 reorg table
monitor reorg in a table - db2pd -db
reorg a table and use a temp file for this reorg - db2 reorg table
reorg all indexes for a table - db2 reorg indexes all for table
update statistics on a table - db2 reorgchk update statistics on table
find the last analyzed date - db2 "select tabname,stats_time from syscat.tables where tabname='' and tabschema='
runstats on a table - db2 runstats on table
Online reorg - db2 "select 'REORG TABLE '||TRIM(tabschema)||'.'||tabname||' INPLACE ALLOW WRITE ACCESS;' from syscat.tables where tabschema in (select distinct substr(tabschema,1,15)tabschema from syscat.tables where tabschema not like 'SYS%')"
Online RUNSTAT -
db2 "select 'RUNSTATS ON TABLE '||TRIM(tabschema)||'.'||tabname||' AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;' from syscat.tables where tabschema in (select distinct substr(tabschema,1,15)tabschema from syscat.tables where tabschema not like 'SYS%') and type='T'"
- how to check reorg stats
db2 "select REORG_START,REORG_END from sysibmadm.snaptab_reorg where tabname='FK1R_MGNTUNITFILTE'"
select rtrim(substr(table_schema,1,10)) as "Schema",rtrim(substr(table_name,1,29)) as "Table_name",
case reorg_status
when 1 then 'Started'
when 2 then 'Paused'
when 3 then 'Stopped'
when 4 then 'Completed'
when 5 then 'Truncated'
end as status,
(minute(reorg_end - reorg_start)) as "Minutes_Ran"
from table(sysproc.snapshot_tbreorg('',-1)) as t where date (reorg_start) > (current_date - 1 day) order by reorg_start desc;
db2 "describe select * from table(snapshot_tbreorg('',0))a"
- how to find
since when the database has been up - db2 get snapshot for dbm | grep -i timestamp or
db2 get snapshot for dbm | grep -i start
detailed inforamation of the applications - db2 list applications for db
*** number of deadlocks - db2 get snapshot for database on
*** if any application is blocking another application - db2 get snapshot for all applications | grep -i lock
- db2 get snapshot for all applications | egrep -i "held|handle"
No of locked tables - db2 get snapshot for locks on $DB2INSTANCE|grep -i "Table Name" | sort -u
No of locked tablespaces - db2 get snapshot for locks on $DB2INSTANCE|grep -i "Tablespace Name" | sort -u
No of locked schemas - db2 get snapshot for locks on $DB2INSTANCE|grep -i "Table Schema" | sort -u db2 get snapshot for application agentid "59805"
- how to
Check the integrity of backup image - db2ckbkp -h
check database size - db2 "call get_dbsize_info(?,?,?,-1)"
backup a database to disk - db2 backup database
check backup retention policy - db2 get db cfg for
*** backup a database to tsm -
*** One tablespace is in backup pending state, how to remove it from - db2 backup db
*** backup pending state - db2 get db cfg for
*** check the backup file present in tsm - db2adutl query full db
*** verify if the backup is not corrupted - db2adutl verify
verify the tablesapce info in backup - db2adutl verify tablespaceonly taken at
retrive the backup image to the local FS - db2adutl extract full taken at
- how to
list backup history - db2 list history backup all for db
list archive history - db2 list history archive log all for
list history of backups stored in tsm - db2adutl query full db
*** list the history of tables that are altered -
- how to
recover a database to the first consistency point - db2 rollforward db
db2 rollforward db
db2 rollforward db
db2 rollforward db
recover a database to end of logs - db2 rollforward db
recover a database to a PIT - db2 rollforward db
complete the recovery process - db2 rollforward db
stop the recovery process - db2 rollforward db
- how to restore a database
from a backup on disk - db2 restore db
*** from a backup IN TSM - db2 restore db
*** with different FS structures, how would you set the target container -
paths
Restore a database from its own backup:
---------------------------------------
db2adutl query full db $DB2INSTANCE
db2 list history backup all for $DB2INSTANCE
db2 get db cfg for $DB2INSTANCE|grep -i "Path to"
db2 connect to $DB2INSTANCE; db2 "call get_dbsize_info(?,?,?,-1)" ; db2 terminate
db2 list active databases
db2 force applications all;db2 terminate
db2 force applications all;db2 terminate
db2stop force
db2start;nohup db2 restore db $DB2INSTANCE use tsm open 1 sessions taken at
tail -f nohup.out
db2 list utilities show detail
db2 rollforward db $DB2INSTANCE stop
db2 rollforward db $DB2INSTANCE to "
db2 rollforward db $DB2INSTANCE complete
db2 get db cfg for $DB2INSTANCE |grep -i pend
db2 connect to $DB2INSTANCE;db2 terminate
db2 backup db $DB2INSTANCE tablespace syscatspace online use tsm
db2 get db cfg for $DB2INSTANCE|grep -i "Path to"
---------------------------------------
- how to check the listener status
check service name - db2 get dbm cfg |grep -i svc
check services for port number - cat /etc/services | grep -i
grep "^$DB2INSTANCE" /etc/services | awk '{print $2}' | awk -F'/' '{print $1}'
Check the port availability - netstat -an | grep -i
Check reorg on user defined date - db2 "select * from table(snapshot_tbreorg('',-1)) x where date(reorg_start) = '07.06.2009'"
- how to
drop objects in a schema - db2 "select 'DROP '||case type when 'T' then 'TABLE ' when 'V' then 'VIEW ' end||tabschema||'.'||tabname||';' from syscat.tables where tabschema not like '%SYS%'"
- how to
Kill a DB2 application - db2 force application "
select current timestamp from dual - db2 "SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1"
Checking the db is whether in archive/noarchive mode - db2 get db cfg for VKJXAP90|egrep "LOGRETAIN|USEREXIT"
- how to
Find the backup size directly taken to TSM - dsmc query backup "/
Find the object name by object ID - db2 "select substr(rtrim(tabschema)||'.'||rtrim(tabname),1,60) from syscat.tables where tbspaceid=12 and tableid=535"
-how to
Drop deadlock event monitor - db2 "select * from syscat.eventmonitors where EVMONNAME ='DB2DETAILDEADLOCK'"
db2 set
db2detaildeadlock state 0
db2 drop event monitor db2detaildeadlock
DB Export for db2:
==================
root / ssh / O
su -
Check db size
db2 connect to
db2 "call get_dbsize_info(?,?,?,-1)"
db2 terminate
Check the space in OS level and craete one directory in the name of TT
Go to the directory and issue-
db2look -d
db2move
Reorg/Runstat on database
=========================
reorg on database-
1) db2 "select distinct TABSCHEMA from syscat.tables"
2) db2 "select 'reorg table '||ltrim(rtrim(TABSCHEMA))||'.'||ltrim(rtrim(TABNAME))||' inplace allow write access;' from syscat.tables where TABSCHEMA='
3) db2 "select 'reorg indexes all for table '||ltrim(rtrim(TABSCHEMA))||'.'||ltrim(rtrim(TABNAME))||' allow write access;' from syscat.tables where TABSCHEMA='
runstat on database-
1) runstats on table
2) db2 "select 'runstats on table '||ltrim(rtrim(TABSCHEMA))||'.'||ltrim(rtrim(TABNAME))||' and detailed indexes all allow write access;' from syscat.tables where TABSCHEMA='
or
db2 "runstats on table MTMCSEIN.MTMROR AND detailed INDEXES all ALLOW NO ACCESS"
For a runstat with no access
or
db2 "runstats on table MTMCSEIN.MTMROR AND detailed INDEXES all ALLOW read ACCESS"
For a runstat with read access
--------
Example:
--------
Following is an example for RUNSTAT through shell script (From IVK)
vl6xat01@s96ivk2d0:/home/vl6xat01 : cat runstats.sh
#!/usr/bin/ksh
DB=$LOGNAME
tmpf=/tmp/runstats.$$.tmp
db2 connect to $DB
db2 -x "select tabschema,tabname from syscat.tables where tabschema not like 'SYS%' and type='T'" > $tmpf
cat $tmpf | while read s t
do
db2 -v "runstats on table $s.$t and detailed indexes all"
done
rm -f $tmpf
db2 connect reset;
----------------------------------------------------------------------------------------
Example script to REORG and RUNSTATS on a database other than tabschema not like 'SYS%':
----------------------------------------------------------------------------------------
#!/usr/bin/ksh
DB=$LOGNAME
tmpe=/tmp/reorg.$$.tmp
tmpf=/tmp/runstats.$$.tmp
db2 connect to $DB
db2 -x "select tabschema,tabname from syscat.tables where tabschema not like 'SYS%'" > $tmpe
cat $tmpe | while read p q
do
db2 -v "reorg table $p.$q inplace allow write access"
done
rm -f $tmpe
db2 -x "select tabschema,tabname from syscat.tables where tabschema not like 'SYS%' and type='T'" > $tmpf
cat $tmpf | while read s t
do
db2 -v "runstats on table $s.$t and detailed indexes all allow write access"
done
rm -f $tmpf
db2 connect reset;
Backup/Restore
==============
DB2 offline backup to local directory:
--------------------------------------
1) mkdir
2) chown -R
3) db2 force applications all;db2 force applications all
4) db2 terminate;db2stop force
5) db2start;db2 backup db $DB2INSTANCE to
or
5) db2start;nohup db2 backup db $DB2INSTANCE to
6) db2 terminate;db2 activate db $DB2INSTANCE
DB2 online backup to local directory:
-------------------------------------
1) mkdir
2) chown -R
3) db2start;db2 backup db $DB2INSTANCE online to
or
3) db2start;nohup db2 backup db $DB2INSTANCE online to
DB2 offline backup to TSM:
--------------------------
1) db2 force applications all;db2 deactivate database $DB2INSTANCE;db2stop force
2) db2start;db2 backup db $DB2INSTANCE use tsm
3) db2 list utilities show detail
4) db2 list history backup all for database $DB2INSTANCE | tail -30
5) db2 terminate;db2 activate db $DB2INSTANCE
6) db2adutl query full db $DB2INSTANCE
DB2 online backup to TSM:
-------------------------
Database level backup:
----------------------
1) db2 backup db $DB2INSTANCE online use tsm (We can add parallelism and buffers for a fast backup)
2) db2 list utilities show detail
3) db2 list history backup all for database $DB2INSTANCE | tail -30
4) db2adutl query full db $DB2INSTANCE
Tablespace level backup:
------------------------
1) db2 backup db $DB2INSTANCE tablespace
2) db2 list utilities show detail
3) db2 list history backup all for database $DB2INSTANCE | tail -30
DB2 Restore:
------------
db2 restore db TK0XAT01 use tsm open 1 sessions taken at 20091125230545 with 2 buffers buffer 1024 parallelism 1
db2 rollforward db TK0XAT01 to 2009-11-26-08.50.00.000000 USING LOCAL TIME
db2start;db2 restore db oftxat01 from /dbdumps/oftxat01 taken at 20110110072033
Shell scripting with DB2
========================
IVK tasks -Downloading dump files
---------------------------------
for i in
do
wget $i
done
Example:
--------
for i in https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-a https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-b https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-c https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-d https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-e https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-f https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-g https://ivk.she2.emea.bg.corpintra.net/ivkmds/MDSDB12.0.db2inst2.NODE0000.CATN0000.20090122175058.001.gz-h
do
wget $i
done
database="ZU5XAP01"
echo "START"
date
db2 connect to $dataBase
echo "convert to large"
time db2 "ALTER TABLESPACE USERSPACE5 CONVERT TO LARGE"
echo "doing reorg"
time db2 "REORG TABLE FB.TOUREVENTRECORDDATA ALLOW NO ACCESS USE TEMPSYS16K"
echo "DONE"
date
-------------------------------
- Multiple db restarted in DB2:
-------------------------------
for i in fk8xap01 tkaxat01 tkexap01 tkexat01 vkdxap01 vlhxae01 vlhxae02 vlhxai01 vlhxai02 vlhxap01 vlhxap02 zu1xap01 zu1xap02 zurxat01
do
echo "Database $i@`hostname`"
echo "--------------------------"
su - $i -c "db2 list active databases;db2 get db cfg for $i|grep -i pend"
echo ''
echo '++++++++++++++++++++++++++++++++++++++++++++++++++'
echo ''
done
for i in fk8xap01 tkaxat01 tkexap01 tkexat01 vkdxap01 vlhxae01 vlhxae02 vlhxai01 vlhxai02 vlhxap01 vlhxap02 zu1xap01 zu1xap02 zurxat01
do
echo "Database $i@`hostname`"
echo "--------------------------"
su - $i -c "db2 force applications all;db2 force applications all;db2 force applications all;db2 terminate;db2stop force;db2start;db2 terminate;db2 activate db $i;db2 list active databases;db2 get db cfg for $i|grep -i pend;db2 get snapshot for dbm|grep -i start"
echo ''
echo "Restart completed for Instance $i@`hostname`"
echo ''
echo '++++++++++++++++++++++++++++++++++++++++++++++++++'
echo ''
done
-- For checking all instance/databases memory in a server
for i in zu8xai01 zu8xai02 zu8xai04 zu8xai05 zu8xai06
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 attach to $i;db2 get dbm cfg show detail|grep -i memory;db2 connect to $i;db2 get db cfg for $i show detail|grep -i memory;db2 terminate"
echo ''
done
-- For checking all databases in a server
for i in
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;db2 connect to $i;db2 terminate"
echo ''
done
Example:
--------
root@s96dbt01 > for i in oftxat01 db2inst1
root@s96dbt01 > do
root@s96dbt01 > echo ''
root@s96dbt01 > echo "Database $i@`hostname`"
root@s96dbt01 > echo "----------------------------"
root@s96dbt01 > su - $i -c "db2 list active databases;db2 connect to $i;db2 terminate"
root@s96dbt01 > echo ''
root@s96dbt01 > done
-- For shutdown all databases in a server
for i in vk1xap02 vk1xap03 vk1xap04 vk1xap07 vk1xap08 vk1xap09 vk1xap10 vk1xap11 vk1xap12 vk1xap13
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases"
echo ''
done
for i in vk1xap02 vk1xap03 vk1xap04 vk1xap07 vk1xap08 vk1xap09 vk1xap10 vk1xap11 vk1xap12 vk1xap13
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;db2 force applications all;db2 terminate;db2 deactivate db $i;db2stop"
echo ''
done
-- For restarting all databases in a server
do
echo $i
su - $i -c "db2start;db2 activate db $i"
done
-- For checking the current active log for all dbs in a server -
for i in vk1xap02 vk1xap03 vk1xap04 vk1xap07 vk1xap08 vk1xap09 vk1xap10 vk1xap11 vk1xap12 vk1xap13 vk1xap14
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;db2 get db cfg for $i | grep -i log | grep -i "First active log file""
echo ''
done
-- For collecting the current settings for all dbs in a server -
for i in pk6xae01 pk6xae02 pk6xae04
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "cd /db2/TT9324315/$i;db2 list db directory > db_dir.txt;db2set -all > dbset_cfg.txt"
echo ''
done
-- For checking Restoration status of Fleetboard databases
for i in zu5xai01 zu5xai03
do
echo ' '
echo "Restoration status for $i@`hostname`"
echo "-------------------------------------------"
su - $i -c "db2 list utilities show detail"
echo ' '
done
-- For checking active status and the last logged message in .log
for i in za3xat01 za3xat03 za3xat07
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 list active databases;echo '';echo 'from diag.log -';echo '';cat sqllib/db2dump/db2diag.log|tail -15"
echo ''
done
-- For taking offline backup to local FS
for i in zaexap40 zaexap41 zaexap42 zaexap43 zaexap44 zaexap45 zaexap46 zaexap47
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 connect to $i;db2 'call get_dbsize_info(?,?,?,-1)';db2 terminate"
echo ''
done
root@s96cd2d1:/db2_omnib/TT9928633 : for i in zaexap40 zaexap41 zaexap42 zaexap43 zaexap44 zaexap45 zaexap46 zaexap47
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "mkdir /db2_omnib/TT9928633/$i "
echo ''
root@s96cd2d1 > do> done
for i in zaexap40 zaexap41 zaexap42 zaexap43 zaexap44 zaexap45 zaexap46 zaexap47
do
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2stop force;db2start;db2 backup db $i to /db2_omnib/TT9928633/$i/ "
echo ''
done
-- For checking locks in a server
for i in zujxap01 zujxap02 zujxap03 zujxap04 zujxap05
do
echo ''
echo "Database $i@`hostname`"
echo "----------------------------"
su - $i -c "db2 get snapshot for all applications|egrep -i 'held|handle'; db2 get snapshot for locks on $i|grep -i 'Table Name'|sort -u; db2 get snapshot for locks on $i|grep -i 'Tablespace Name'|sort -u; db2 get snapshot for locks on $i|grep -i 'Table Schema'|sort -u"
echo ''
done
how to interpret error in db2diag.log
=========================================
Example: 2008-09-19-09.58.01.887762+120 I3253534C487 LEVEL: Error
PID : 13806 TID : 1 PROC : db2agent (
INSTANCE:
APPHDL : 0-48 APPID: *LOCAL.
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:540
RETCODE : ZRC=0x850F0081=-2062614399=SQLO_SSEM_EXCEED_MAX
"Requesting too many semaphores"
DIA8336C Requested too many semaphores
Action : db2diag -rc 0x850F0081
Input ZRC string '0x850F0081' parsed as 0x850F0081 (-2062614399).
ZRC value to map: 0x850F0081 (-2062614399)
V7 Equivalent ZRC value: 0xFFFFD681 (-10623)
ZRC class :
Resource Capacity Error (Class Index: 5)
Component:
SQLO ; oper system services (Component Index: 15)
Reason Code:
129 (0x0081)
Identifer:
SQLO_SSEM_EXCEED_MAX
Identifer (without component):
SQLZ_RC_MAX_SEM
Description:
Requesting too many semaphores
Associated information:
Sqlcode -902
SQL0902C A system error (reason code = "") occurred. Subsequent SQL
statements cannot be processed.
Number of sqlca tokens : 1
Diaglog message number: 8336
SQL0902C A system error (reason code = "
occurred. Subsequent SQL statements cannot be
processed.
Explanation:
A system error occurred.
User Response:
Record the message number (SQLCODE) and reason code in the
message.
If trace was active, invoke the Independent Trace Facility at
the operating system command prompt. Then contact your technical
service representative with the following information:
o Problem description
o SQLCODE and embedded reason code
o SQLCA contents if possible
o Trace file if possible.
Federated system users: isolate the problem to the data source
failing the request (refer to the problem determination guide to
determine which data source is failing to process the SQL
statement) and take the necessary diagnostic steps for that data
source. The problem determination procedures for data sources
vary, so refer to the applicable data source manuals.
sqlcode : -902
sqlstate : 58005
SQL1040N The maximum number of applications is already connected
to the database.
Explanation:
The number of applications connected to the database is equal to
the maximum value defined in the configuration file for the
database.
The command cannot be processed.
User Response:
Wait for other applications to disconnect from the database. If
more applications are required to run concurrently, increase the
value for maxappls . After all applications disconnect from the
database and the database is restarted, the new value takes
effect.
sqlcode : -1040
sqlstate : 57030
DB2 commands
============
For uploading the db2 instance details for PMR - db2support . -d
To find out who is the fenced user - Check the owner of the file
DB2 max connections exceeded - db2 get dbm cfg |grep -i max
ps -ef | grep
db2 get dbm cfg |grep -i agents
db2pd -agents|head
Backup and restore parallelism
==============================
Go to the
check file name like ora_adsm.result.0
root@s96gsuc1:/root : ora_omnib -l s96fbc1-200902081900
09.02.09 06:35:55 (s96gsuc1-l) Logfile '/log/ora_omnib/detail/s96fbc1.200902081900'
08.02.09 19:00:07 (s96fbc1-T) Begin s96fbc1-200902081900-TM
08.02.09 19:00:07 (s96fbc1-T) Perform total Backup of Oracle/DB2 and Unix
08.02.09 19:00:08 (s96fbc1-T) Override REDOXNODE=53.71.190.62
08.02.09 19:00:09 (s96fbc1-T) Found dsm.sys Address '172.17.126.21'
08.02.09 19:00:09 (s96fbc1-T) Set OMNIHOST to 's96tsm09'
08.02.09 19:00:10 (s96fbc1-T) Set ROBOT to '1'
08.02.09 19:00:13 (s96fbc1-T) Notice, create Flagfile '/sysadm/utils/ora_adsm.act.s96fbc1.O'
08.02.09 19:00:23 (s96fbc1-T) Access remote Oracle/DB2 Kontext, possibly DB2 Backup
08.02.09 19:00:24 (s96fbc1-T) Remote Call '/tmp/ora_adsm -GO s96fbc1.OM_omnib'
08.02.09 18:00:24*(s96fbc1-G) Invoked '/cluster/s96fbc1/glockware/sysadm/utils/ora_adsm -GO s96fbc1.OM_omnib'
08.02.09 18:00:24*(s96fbc1-G) Set OMNIHOST to '.'
08.02.09 18:00:24*(s96fbc1-G) Set ROBOT to '1'
08.02.09 18:00:24*(s96fbc1-G) Notice, Option 'G1' detected
08.02.09 18:03:27*(s96fbc1-G) Invoke DB2 Backup Pass
08.02.09 18:03:29*(s96fbc1-G) Node 's96fbc1' has 16 Filesystems
08.02.09 18:03:38*(s96fbc1-G) DB2 Database 'ZU5XAP01=0', Instance 'zu5xap01' found
08.02.09 18:03:38*(s96fbc1-G) Notice, check DB2 INCLUDE LOGS 'zu5xap01.ZU5XAP01=0'
08.02.09 18:04:09*(s96fbc1-G) Longest Dump has 239292416 Byte, Space for 3 more Dumps..
08.02.09 18:04:09*(s96fbc1-G) DB2 Dump to '/db2_omnib/s96fbc1/zu5xap01/ZU5XAP01=0/20090208180023'
08.02.09 18:04:09*(s96fbc1-G) Invoke 'backup db ZU5XAP01 online use tsm open 2 sessions with 8 buffers buffer 8000 paralle
lism 16 without prompting'
08.02.09 23:13:50*(s96fbc1-G) Warning, Error '2025' at DB2 online Backup 'ZU5XAP01=0'
08.02.09 23:13:51*(s96fbc1-G) DB2 Database 'ZU5XAP02=0', Instance 'zu5xap02' found
08.02.09 23:13:51*(s96fbc1-G) Notice, check DB2 INCLUDE LOGS 'zu5xap02.ZU5XAP02=0'
08.02.09 23:14:21*(s96fbc1-G) Longest Dump has 239292416 Byte, Space for 3 more Dumps..
08.02.09 23:14:21*(s96fbc1-G) DB2 Dump to '/db2_omnib/s96fbc1/zu5xap02/ZU5XAP02=0/20090208180023'
08.02.09 23:14:21*(s96fbc1-G) Invoke 'backup db ZU5XAP02 online use tsm open 1 sessions with 2 buffers buffer 2048 paralle
lism 1 without prompting'
08.02.09 23:19:40*(s96fbc1-G) Instance 'ZU5XAP02=0' backed up (C)
08.02.09 23:19:40*(s96fbc1-G) DB2 Database 'ZU5XAP04=0', Instance 'zu5xap04' found
08.02.09 23:19:40*(s96fbc1-G) Notice, check DB2 INCLUDE LOGS 'zu5xap04.ZU5XAP04=0'
08.02.09 23:20:15*(s96fbc1-G) Longest Dump has 239292416 Byte, Space for 3 more Dumps..
08.02.09 23:20:15*(s96fbc1-G) DB2 Dump to '/db2_omnib/s96fbc1/zu5xap04/ZU5XAP04=0/20090208180023'
08.02.09 23:20:15*(s96fbc1-G) Invoke 'backup db ZU5XAP04 online use tsm open 1 sessions with 2 buffers buffer 2048 paralle
lism 1 without prompting'
08.02.09 23:21:20*(s96fbc1-G) Instance 'ZU5XAP04=0' backed up (C)
08.02.09 23:21:21*(s96fbc1-G) Max.Redolength 1000000 Byte
09.02.09 00:21:55 (s96fbc1-T) Break, DB2 Backup failed
09.02.09 00:21:56 (s96fbc1-T) Error 2025 User 'zu5xap01', Instance 'ZU5XAP01=0' (An I/O error "-50" occurred on media "TSM
".)
09.02.09 00:21:58 (s96fbc1-T) End s96fbc1-200902081900-TM-2025-321-s96gsuc1-0-1659248
09.02.09 06:36:12 (s96gsuc1-l) Done
root@s96gsuc1:/root :
RESTORE DATABASE ZU5XAP01
USE TSM
OPEN 2 SESSIONS
TAKEN AT 20090206180425
INTO ZU5XAI01
-- NEWLOGPATH /onlineredo/ZU5XAI01/NODE0000
WITH 8 BUFFERS BUFFER 8000
REDIRECT
PARALLELISM 8
;
DB2 SQLs
========
db2 "select substr(TABNAME,1,15) TABNAME , CASE TYPE WHEN 'A' THEN ' Alias' WHEN 'H' THEN 'Hierarchy table' WHEN 'N' THEN 'Nickname' WHEN 'S' THEN 'Materialized query table' WHEN 'T' THEN 'Table' WHEN 'U' THEN 'Typed table' WHEN 'v' THEN 'View' WHEN 'W' THEN 'Typed vew' END TYPE from syscat.tables where status='C' and TABSCHEMA='MIF_DEV'"
Create db with default tablespaces in desired location
======================================================
create database fk1xat03 using codeset UTF-8 territory DE catalog tablespace managed by database using ( file '/db2_data/FK1XAT03/system1/SYSCATSPACE_1.dat' 165320 ) extentsize 4 prefetchsize 16 temporary tablespace managed by system using ('/db2_data/FK1XAT03/temp1sms/TEMPSPACE1_1' ) extentsize 32 prefetchsize 32 user tablespace managed by database using ( file '/db2_data/FK1XAT03/data1/USERSPACE1_1.dat' 60000 ) extentsize 32 prefetchsize 32;
update parameter CATALOGCACHE_SZ
=================================
db2 update db cfg for fk1xat03 using CATALOGCACHE_SZ -1
$ cat db2look_mifdev.log | grep -i SQL | sort -u
$ cat db2look_mifdev.log | egrep "SQL0|DB20" | grep -v "DB20000I" | grep -v "ALTER"
nohup db2 restore db vl6xap02 logs use TSM taken at 20110523030248 logtarget /onlineredo/VL6XAQ01/NODE0000/ &
No comments:
Post a Comment