Table Function SNAP_GET_LOCKWAIT
To retrieve information about lock waits using SQL, you can use Db2’s table function SNAP_GET_LOCKWAIT and the administrative view SNAPLOCKWAIT.
The following statement, for example, returns information about lock waits of the currently connected database, its current partition, and the agent ID of the participating agents:
db2 "SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK, LOCK_MODE_REQUESTED FROM TABLE(SNAP_GET_LOCKWAIT('',-1)) AS T"
[db2inst1@xxxxxxxxxx]$ db2 "SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK, LOCK_MODE_REQUESTED FROM TABLE(SNAP_GET_LOCKWAIT('',-1)) AS T"
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE AGENT_ID_HOLDING_LK LOCK_MODE_REQUESTED
-------------------- ---------- ------------------ -------------------- -------------------
Monitoring Utility db2pd
To gather information about lock waits and deadlocks, you can also use the Db2 monitoring utility db2pd. This utility reads the information directly from the shared memory of the database and therefore is very fast.
A db2pd command looks, for example, as follows:
db2pd -db <dbsid> -locks showlocks wait
The output of this command can look as follows:
[db2inst1@xxxxxxxx]$ db2pd -db misprd1 -locks showlocks wait
Database Member 0 -- Database MISPRD1 -- Active -- Up 47 days 13:04:07 -- Date 2022-12-05-11.08.49.579862
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID TableNm SchemaNm
[db2inst1@xxxxxxxx]$
db2
Give information about db2 instance
[db2inst1@xxxxxx]$ db2level
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10058" with level
identifier "0609010E".
Informational tokens are "DB2 v10.5.0.8", "s160901", "IP23993", and Fix Pack
"8".
Product is installed at "/opt/ibm/db2/V10.5".
[db2inst1@xxxxxxxx]$
CREATE DATABASE:
1 . db2sampl --- creates sample database
2 . db2 "create database WEBTSTD2 on /db2ite/data1 ,/db2ite/data2 ,/db2ite/data3 ,/db2ite/data4 dbpath on /db2/dbdir "
db2 "update db cfg for WEBTSTD2 using newlogpath /db2ite/activelog"
db2 "update db cfg for WEBTSTD2 using LOGARCHMETH1 DISK:/db2ite/archmeth1 "
db2 "backup db webtstd2 to
-- Create A new DB
db2 list db directory
db2 create db COGAUDIT on /db2path
db2 get dbm cfg |grep SVCENAME
cat /etc/services |grep db2c_db2inst8
vi ~/.bashrc
export PATH=$PATH:/home/db2inst1/sqllib/bin
db2 list node directory
RESTORE DATABASE
restore db <database_name1> from '<path where the backup has been stored>' taken at <timestamp> on '<data_file>' , '<data_file>' , '<data_file>' , ............. dbpath on '<path where database files wiil stored>' into <database_name> without rolling forward without prompting ;
[database_name1 = db name from which the database will be restored , database_name2 = db name which will be created ]
DATABASE QUIESCE
setp 1 : connect to the database
step 2 : run the command
db2 quiesce database immediate force connections;
UNQUIESCE DATABASE
setp 1 : connect to the database
step 2 : run the command
db2 unquiesce database
CATALOG DATABASE
step 1: catalog node
db2 catalog tcpip node <NODE_NAME> remote <IP> server <PORT_NO> remote_instance <INSTANCE_NAME> ostype <OS-TYPE>
step 2: catalog db
db2 catalog db <DABASE_NAME> at node <NODE-NAME>
-- Create a nodde
db2 catalog tcpip node DB002 remote <ip address> server 60000 remote_instance db2inst1 ostype linux
-- Catlod a DB
db2 catalog database COGAUDIT at node DB002
UNCATALOG DATABASE
db2 uncatalog db misprd1 on /db2path
ADD STORAGE TO DATABASE
STEP 1 :
select DB_NAME,DB_STORAGE_PATH,DB_STORAGE_PATH_STATE from sysibmadm.SNAPSTORAGE_PATHS;
----- checks the storage path for database (in use/not in use )
STEP 2:
alter database add storage on '<new_mount_point>' ;
------- adds storage path (mount_point) to the db
STEP 3 :
db2 "select DB_NAME,DB_STORAGE_PATH,DB_STORAGE_PATH_STATE from sysibmadm.SNAPSTORAGE_PATHS"
----- checks the storage path for database (in use/not in use )
STEP 4 :
db2 "select substr(TBSP_NAME,1,20),TBSP_TOTAL_SIZE_KB,TBSP_UTILIZATION_PERCENT from sysibmadm.tbsp_utilization order by 2"
----------- list all the tablespaces
STEP 5 : Rebalance all the tablespace
Don't need to rebalance temporary tablespace , system tablespace .
db2 alter tablespace <tablespace_name> rebalance ;
STEP 6 : After rebalance done ,do reduce max to that table space .
Don't need to rebalance temporary tablespace , system tablespace .
db2 alter tablespace <tablespace_name> reduce max ;
[ bash-3.2$ db2 alter tablespace TSPRETDSSTMTDEDEA rebalance ;
SQL2094W The rebalance of table space "TSPRETDSSTMTDEDEA" either did not add
or drop containers, or there was insufficient disk space to create all of the
containers. Reason code: "1". SQLSTATE=01690
-------------This warning is fine ]
DATABASE STATUS :
db2 "select DB_STATUS from sysibmadm.snapdb"
DB_STATUS
----------------
ACTIVE
1 record(s) selected.
GET DATABASE/INSTANCE CONFIGURATION :
db2 get db cfg for prcprdd1
db2 get dbm cfg for prcprdd1
CREATE BUFFERPOOL:
CREATE BUFFERPOOL "<bufferpool_name>" SIZE AUTOMATIC PAGESIZE <value>;
CREATE TABLESPACE:
CREATE REGULAR TABLESPACE "<tablespace_name>" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE <value> MANAGED BY AUTOMATIC STORAGE USING STOGROUP "IBMSTOGROUP" AUTORESIZE YES INITIALSIZE 32 M MAXSIZE NONE EXTENTSIZE 32 PREFETCHSIZE AUTOMATIC BUFFERPOOL "<bufferpool_name>" DATA TAG INHERIT OVERHEAD 7.500000 TRANSFERRATE 0.060000 NO FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON;
db2 "CREATE Temporary TABLESPACE "TSSYSTEMP8K" PAGESIZE 8192 BUFFERPOOL "BPPPSYSTEMP8K" NO FILE SYSTEM CACHING "
ADD PARTITION TO A TABLE:
ALTER TABLE <schema_name>.<table_name> add partition <partition_name> STARTING(<start_range>) ENDING(<end_range>) IN <tablespace_name> INDEX IN <index_name> ;
ADD CONSTRAINTS TO A TABLE:
ALTER TABLE <schema_name>.<table_name> ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column_name>);
ALTER TABLE <schema_name>.<table_name> ADD CONSTRAINT <constraint_name> FOREIGN KEY (<Column_name_child_table>) REFERENCES <Column_name_child_table> (<Column_name_parent_table>);
ALTER TABLE <schema_name>.<table_name> ADD CONSTRAINT <constraint_name> UNIQUE (<Column_name>);
ALTER COLUMN:
ALTER TABLE <schema_name>.<table_name> ALTER COLUMN <Column_name> SET DATA TYPE <DATA-TYPE> ALTER COLUMN <Column_name> SET DATA TYPE <DATA-TYPE> .......;
ALTER TABLE <schema_name>.<table_name> ALTER COLUMN <Column_name> DROP NOT NULL ;
alter table <schema_name>.<table_name> add column <Column_name> <DATA-TYPE> not null with default <DEFAULT_VALUE>;
alter table <schema_name>.<table_name> alter column <Column_name> drop default;
alter table <schema_name>.<table_name> alter column <Column_name> set generated always as identity(STARTS AS 1);
db2 alter table txn.TDS_STMT_DED_DETL compress yes;
[After any alterration in a table reorg should be done ]
ADDING IDENTITY COLUMN TO THE EXISTING TABLE WITHOUT TRUNCATION OF THE TABLE :
alter table T26AS.TDS_CPC_PARTE_AIR add column AIR_ID DECIMAL(31) not null with default 0 ;
REORG TABLE:
reorg table <schema_name>.<table_name>
reorg table TXN.TDS_PROC_VER_QUEUE use "TSSYSTEMP8K"
db2 "reorg table T26AS.TDS_26AS_TDS_PARTAB use TSSYSTEMP8K on data partition U2008 "
REORG CHECK:
db2 "select substr(tabschema,1,20),substr(tabname,1,50),reorg_pending from sysibmadm.admintabinfo where reorg_pending='Y'"
REORG STATUS CHECK:
db2pd -db prcprdd1 -reorgs | grep <table_name>
CREATE INDEX:
CREATE INDEX <index_name> ON <schema_name>.<table_name> (<Column_name>,<Column_name>,<Column_name> ,.....);
CREATE UNIQUE INDEX <index_name> ON <schema_name>.<table_name> (<Column_name>,<Column_name>,<Column_name> ,.....);
DROP COMMAND:
drop procedure <schema_name>.<procedure_name> ;
RENAME TABLE:
rename table <schema_name>.<old_table_name> to <new_table_name> ;
LOAD QUARY:
db2 load query table <schema_name>.<table_name>
CHECK SET INTEGRITY STATE OF TABLES:
select TABSCHEMA ,TABNAME from syscat.tables where STATUS in ('C' ,'c' ) ;
CHECK TABLE DEPENDENCY:
select TABSCHEMA ,TABNAME ,REFTABSCHEMA ,REFTABNAME,REFKEYNAME from SYSCAT.REFERENCES where REFTABNAME='<TABLE_NAME>';
CHECK TABLESPACE STATE:
select SUBSTR(TBSP_NAME,1,30) AS TABLESPACE,TBSP_ID,SUBSTR(TBSP_STATE,1,30) AS TBSPACESTATE from SYSIBMADM.SNAPTBSP_PART where TBSP_STATE <>'NORMAL';
CHECK TABLE SIZE:
1. To get info about the whole table
select TABNAME,TABSCHEMA,sum(DATA_OBJECT_P_SIZE)+sum(INDEX_OBJECT_P_SIZE)+sum(LONG_OBJECT_P_SIZE)+sum(LOB_OBJECT_P_SIZE)+sum(XML_OBJECT_P_SIZE) from sysibmadm.admintabinfo where TABNAME='<table_name>' group by TABNAME,TABSCHEMA ;
select TABSCHEMA , tabname , sum(DATA_OBJECT_P_SIZE)+sum(INDEX_OBJECT_P_SIZE)+sum(LONG_OBJECT_P_SIZE)+sum(LOB_OBJECT_P_SIZE)+sum(XML_OBJECT_P_SIZE) from sysibmadm.admintabinfo where tabschema not like 'SYS%' and tabschema not like 'ASN%' group by TABSCHEMA , tabname ;
2. To get info about a row
select AVGROWSIZE from syscat.tables where tabname ='<table_name>' ;
[It will only give the correct output for a table if the syscat.tables is updated by running the runstat command on that table ]
3.To get info of a schema
SELECT TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROMSYSIBMADM.ADMINTABINFO GROUP BY TABSCHEMA;
RUNSTAT COMMAND:
db2 runstats on table <schema_name>.<table_name> and sampled detailed indexes all
GRANT SCRIPT:
1.
select 'grant select,delete,update,insert on table '||tabschema||'.'||tabname||' to user/role <user/role_name> ' from syscat.tables where tabschema not like 'SYS%' > <file_name>
2.
GRANT EXECUTE ON PROCEDURE <schema_name>.<procedure_name> to user/role <user/role_name>
3. privileges_to_create_procedure
revoke createtab on database from PUBLIC;
grant create_external_routine on database to user/role <user/role_name> ;
grant create_not_fenced_routine on database to user/role <user/role_name> ;
grant createin on schema<schema_name> to user/role <user/role_name> ;
4.
db2 grant dataaccess on database to user rpttsti1
db2 grant dataaccess on database to user db2admin
db2 grant accessctrl on database to user db2admin
db2 grant accessctrl on database to user rpttsti1
db2 grant dbadm on database to db2admin
TRANSFER OWNERSHIP:
transfer ownership of procedure ETL.ALLOCATE_NEXT_FEED_PROC to user ETLSTMT preserve privileges
LOAD COMMAND:
db2 "load from mstrtdscpcweb.ixf of ixf insert into <schema_name>.<table_name> nonrecoverable without prompting"
db2 "load from mstrtdscpcmulticoderef.ixf of ixf modified by identityoverride replace into <schema_name>.<table_name> nonrecoverable without prompting"
UPDATE TABLE:
update T26AS.TDS_CPC_PARTE_AIR set AIR_ID = default
TRUNCATE TABLE:
db2 truncate table <schema_name>.<table_name> IMMEDIATE
TRUNCATE TABLE <schema_name>.<table_name> DROP STORAGE IMMEDIATE
ROW COUNT:
db2 "select substr(tabname,1,50) as Tablename,card as COUNT from syscat.tables where tabname ='<table_name> ' "
[It will only give the correct output for a table if the syscat.tables is updated by running the runstat command on that table ]
DROP SCHEMA:
step 1 : Drop all the objects (tables ,procrdures ,functions ) under the schema .
drop <object_type> <schema_name>.<object_name>
step 2 : Drop the schema
drop schema <schema_name> restrict
CHECK DB CONTAINNER:
db2 "select substr(tbsp_name,1,25) as tablespace_name,count(*) from sysibmadm.snapcontainer group by tbsp_name order by 2"
This shows the no of container for each tablespace
SIZE OF SCHEMA:
db2 "select TABschema,sum(DATA_OBJECT_P_SIZE)+sum(INDEX_OBJECT_P_SIZE)+sum(LONG_OBJECT_P_SIZE)+sum(XML_OBJECT_P_SIZE) from sysibmadm.admintabinfo group by tabschema ORDER BY 2 "
TABLESPACE UTILIZATION:
db2 "select substr(TBSP_NAME,1,20),TBSP_TOTAL_SIZE_KB,TBSP_UTILIZATION_PERCENT from sysibmadm.tbsp_utilization order by 3"
TABLE SIZE:
db2 "select TABname,sum(DATA_OBJECT_P_SIZE)+sum(INDEX_OBJECT_P_SIZE)+sum(LONG_OBJECT_P_SIZE)+sum(XML_OBJECT_P_SIZE) from sysibmadm.admintabinfo where tabschema='TXN' group by tabNAME ORDER BY 2 "
TABLE DESCRIBTION: - STRUCTURE
db2look -d misprd1 -t DT_ASST_CLS_DIM -e
db2 "DESCRIBE TABLE MISEDW.DT_WAM_ACCOUNT_CODE_DIM"
TABLE LOCK
db2 "select * from sysibmadm.snaplock where tabname='DT_ASSET_DIM' fetch first 2 rows only"
db2 list applications|grep 33367
db2 "force application(31441)"
APPLICATION: ROLLBACK STATUS
db2 GET SNAPSHOT FOR ALL APPLICATIONS >snapshot.out
db2 GET SNAPSHOT FOR ALL APPLICATIONS | grep -i "Rollback to savepoint" -A 10
Table_State_Unavilable
1. If the table status is unavailable , take the ddl of the table by db2look among with all privileges given on the table into a file
2. Change the table name in the file with ‘_NEW’. (E.g. actual table name: "MISSTG “."BT_FT_ASST_CNSMPTN_BKT”, new table name: "MISSTG “."BT_FT_ASST_CNSMPTN_BKT_NEW”). Change all index and keys name according.
3. Create the new table (i.e."MISSTG “."BT_FT_ASST_CNSMPTN_BKT_NEW” )
4. Extract the data of the actual table using “db2dart”. A file with ‘.DEL” with be created. (For ‘no of pages’ give some really large infinite value )
5. Go to the path where the file has been created
6. Import the data into the new table with ‘_NEW’
7. Check the new table status
8. Rename the actual table as ‘_OLD’
9. Rename the new table as actual table
10. Check the status of newly created table after renaming.
Below is an example of the whole procedure mentioned above .
Problem:
[db2inst1@xxxxxxxxxx backupdrill]$ db2 "select count (*) from "MISSTG “."BT_FT_ASST_CNSMPTN_BKT" "
Error Message:
DB2 reported: SQLSTATE = 55019: Native Error Code = -1,477: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL1477N For table "MISSTG.STG_SA_INTMDT_1" an object "14081" in table space "2" cannot be accessed. SQLSTATE=55019
[db2inst1@xxxxxxxxxx DART0000]$ db2 load query table MISSTG.BT_FT_ASST_CNSMPTN_BKT
Tablestate:
Unavailable
Solution :
[db2inst1@xxxxxxxxxx ~]$ db2look -d MISPRD1 -e -x -z MISSTG -t BT_FT_ASST_CNSMPTN_BKT -o aa
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Specified SCHEMA is: MISSTG
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
-- Output is sent to file: aa.sql
[db2inst1@xxxxxxxxxx ~]$ cat aa.sql
-- This CLP file was created using DB2LOOK Version "9.7"
-- Timestamp: Fri 24 Apr 2015 01:50:58 PM IST
-- Database Name: MISPRD1
-- Database Manager Version: DB2/LINUXX8664 Version 9.7.6
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
CONNECT TO MISPRD1;
------------------------------------------------
-- DDL Statements for table "MISSTG "."BT_FT_ASST_CNSMPTN_BKT"
------------------------------------------------
CREATE TABLE "MISSTG "."BT_FT_ASST_CNSMPTN_BKT" (
"TRANSACTION_MONTH" DATE ,
"SCTN_CD" VARCHAR(50) ,
"INITAL_READING" DECIMAL(30,10) ,
"FINAL_READING" DECIMAL(30,10) ,
"METER_MULTIPLIER" DECIMAL(30,0) ,
"CONSUMPTION" DECIMAL(30,0) ,
"ENERGY_EXPORT" DECIMAL(30,0) ,
"ENERGY_IMPORT" DECIMAL(30,0) ,
"CHILD_CONSUMPTION" DECIMAL(30,0) ,
"ASST_CD" VARCHAR(50) ,
"UNIT_OF_MEASURE" VARCHAR(18) ,
"AUXILLARY_CONSUMPTION" DECIMAL(30,0) ,
"COMPLETION_FLAG" CHAR(1) ,
"EXPORTED_FLAG" CHAR(1) ,
"CHILD_CONSUMPTION_FLAG" CHAR(1) ,
"IRON_LOSS" VARCHAR(50) ,
"COPPER_LOSS" VARCHAR(50) ,
"EST_TECH_LOSS" VARCHAR(50) ,
"ROW_NUM" INTEGER ,
"TIM_ID" INTEGER NOT NULL ,
"IMP_OR_EXP_FLAG" VARCHAR(3) )
IN "TBSP4K" ;
--------------------------------------------
-- Authorization Statements on Tables/Views
--------------------------------------------
GRANT CONTROL ON TABLE "MISSTG "."BT_FT_ASST_CNSMPTN_BKT" TO USER "DB2INST1" ;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
[db2inst1@chnpmisdb102 ~]$ cat > aaaa
-- This CLP file was created using DB2LOOK Version "9.7"
-- Timestamp: Fri 24 Apr 2015 01:50:58 PM IST
-- Database Name: MISPRD1
-- Database Manager Version: DB2/LINUXX8664 Version 9.7.6
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
CONNECT TO MISPRD1;
------------------------------------------------
-- DDL Statements for table "MISSTG "."BT_FT_ASST_CNSMPTN_BKT_NEW"
------------------------------------------------
CREATE TABLE "MISSTG "."BT_FT_ASST_CNSMPTN_BKT_NEW" (
"TRANSACTION_MONTH" DATE ,
"SCTN_CD" VARCHAR(50) ,
"INITAL_READING" DECIMAL(30,10) ,
"FINAL_READING" DECIMAL(30,10) ,
"METER_MULTIPLIER" DECIMAL(30,0) ,
"CONSUMPTION" DECIMAL(30,0) ,
"ENERGY_EXPORT" DECIMAL(30,0) ,
"ENERGY_IMPORT" DECIMAL(30,0) ,
"CHILD_CONSUMPTION" DECIMAL(30,0) ,
"ASST_CD" VARCHAR(50) ,
"UNIT_OF_MEASURE" VARCHAR(18) ,
"AUXILLARY_CONSUMPTION" DECIMAL(30,0) ,
"COMPLETION_FLAG" CHAR(1) ,
"EXPORTED_FLAG" CHAR(1) ,
"CHILD_CONSUMPTION_FLAG" CHAR(1) ,
"IRON_LOSS" VARCHAR(50) ,
"COPPER_LOSS" VARCHAR(50) ,
"EST_TECH_LOSS" VARCHAR(50) ,
"ROW_NUM" INTEGER ,
"TIM_ID" INTEGER NOT NULL ,
"IMP_OR_EXP_FLAG" VARCHAR(3) )
IN "TBSP4K" ;
--------------------------------------------
-- Authorization Statements on Tables/Views
--------------------------------------------
GRANT CONTROL ON TABLE "MISSTG "."BT_FT_ASST_CNSMPTN_BKT_NEW" TO USER "DB2INST1" ;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
[db2inst1@chnpmisdb102 ~]$
[db2inst1@xxxxxxxxxx backupdrill]$ db2 -tvf aaaa
CONNECT TO MISPRD1
Database Connection Information
Database server = DB2/LINUXX8664 9.7.6
SQL authorization ID = DB2INST1
Local database alias = MISPRD1
CREATE TABLE "MISSTG "."BT_FT_ASST_CNSMPTN_BKT_NEW" ( "TRANSACTION_MONTH" DATE , "SCTN_CD" VARCHAR(50) , "INITAL_READING" DECIMAL(30,10) , "FINAL_READING" DECIMAL(30,10) , "METER_MULTIPLIER" DECIMAL(30,0) , "CONSUMPTION" DECIMAL(30,0) , "ENERGY_EXPORT" DECIMAL(30,0) , "ENERGY_IMPORT" DECIMAL(30,0) , "CHILD_CONSUMPTION" DECIMAL(30,0) , "ASST_CD" VARCHAR(50) , "UNIT_OF_MEASURE" VARCHAR(18) , "AUXILLARY_CONSUMPTION" DECIMAL(30,0) , "COMPLETION_FLAG" CHAR(1) , "EXPORTED_FLAG" CHAR(1) , "CHILD_CONSUMPTION_FLAG" CHAR(1) , "IRON_LOSS" VARCHAR(50) , "COPPER_LOSS" VARCHAR(50) , "EST_TECH_LOSS" VARCHAR(50) , "ROW_NUM" INTEGER , "TIM_ID" INTEGER NOT NULL , "IMP_OR_EXP_FLAG" VARCHAR(3) ) IN "TBSP4K"
DB20000I The SQL command completed successfully.
GRANT CONTROL ON TABLE "MISSTG "."BT_FT_ASST_CNSMPTN_BKT_NEW" TO USER "DB2INST1"
DB20000I The SQL command completed successfully.
COMMIT WORK
DB20000I The SQL command completed successfully.
CONNECT RESET
DB20000I The SQL command completed successfully.
TERMINATE
DB20000I The TERMINATE command completed successfully.
[db2inst1@xxxxxxxxxx backupdrill]$ db2dart misprd1 /DDEL
Warning: The database state is not consistent.
Warning: Errors reported about reorg rows may be due to the inconsistent state of the database.
Table object data formatting start.
Please enter
Table ID or name, tablespace ID, first page, num of pages:
MISSTG.BT_FT_ASST_CNSMPTN_BKT,2,0,999999999
21 of 21 columns in the table will be dumped.
Column numbers and datatypes of the columns dumped:
0 DATE
1 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
2 DECIMAL
3 DECIMAL
4 DECIMAL
5 DECIMAL
6 DECIMAL
7 DECIMAL
8 DECIMAL
9 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
10 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
11 DECIMAL
12 CHAR() -FIXED LENGTH CHARACTER STRING
13 CHAR() -FIXED LENGTH CHARACTER STRING
14 CHAR() -FIXED LENGTH CHARACTER STRING
15 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
16 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
17 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
18 INTEGER
19 INTEGER
20 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
Default filename for output data file is MISPRD1.DEL,
do you wish to change filename used? y/n
n
Filename used for output data file is MISPRD1.DEL. If existing file, data will be appended to it.
Formatted data being dumped ...
Dumping Page 0 ....
Dumping Page 1 ....
Dumping Page 2 ....
Dumping Page 3 ....
Dumping Page 4 ....
Dumping Page 5 ....
Dumping Page 6 ....
Dumping Page 7 ....
Dumping Page 8 ....
Dumping Page 9 ....
Dumping Page 10 ....
Dumping Page 11 ....
Dumping Page 12 ....
Dumping Page 13 ....
Dumping Page 14 ....
Dumping Page 15 ....
Dumping Page 16 ....
Dumping Page 17 ....
Dumping Page 18 ....
Dumping Page 19 ....
Dumping Page 20 ....
Dumping Page 21 ....
Dumping Page 22 ....
Dumping Page 23 ....
Dumping Page 24 ....
Dumping Page 25 ....
Dumping Page 26 ....
Dumping Page 27 ....
Dumping Page 28 ....
Dumping Page 29 ....
Dumping Page 30 ....
Dumping Page 31 ....
Dumping Page 32 ....
Dumping Page 33 ....
Dumping Page 34 ....
Dumping Page 35 ....
Dumping Page 36 ....
Dumping Page 37 ....
Dumping Page 38 ....
Dumping Page 39 ....
Dumping Page 40 ....
Dumping Page 41 ....
Dumping Page 42 ....
Dumping Page 43 ....
Dumping Page 44 ....
Dumping Page 45 ....
Dumping Page 46 ....
Dumping Page 47 ....
Dumping Page 48 ....
Dumping Page 49 ....
Dumping Page 50 ....
Dumping Page 51 ....
Dumping Page 52 ....
Dumping Page 53 ....
Dumping Page 54 ....
Dumping Page 55 ....
Dumping Page 56 ....
Dumping Page 57 ....
Dumping Page 58 ....
Dumping Page 59 ....
Dumping Page 60 ....
Dumping Page 61 ....
Dumping Page 62 ....
Dumping Page 63 ....
Dumping Page 64 ....
Dumping Page 65 ....
Dumping Page 66 ....
Dumping Page 67 ....
Dumping Page 68 ....
Dumping Page 69 ....
Dumping Page 70 ....
Dumping Page 71 ....
Dumping Page 72 ....
Dumping Page 73 ....
Dumping Page 74 ....
Dumping Page 75 ....
Dumping Page 76 ....
Dumping Page 77 ....
Dumping Page 78 ....
Dumping Page 79 ....
Dumping Page 80 ....
Dumping Page 81 ....
Dumping Page 82 ....
Dumping Page 83 ....
Dumping Page 84 ....
Dumping Page 85 ....
Dumping Page 86 ....
Dumping Page 87 ....
Dumping Page 88 ....
Dumping Page 89 ....
Dumping Page 90 ....
Dumping Page 91 ....
Dumping Page 92 ....
Dumping Page 93 ....
Dumping Page 94 ....
Dumping Page 95 ....
Dumping Page 96 ....
Dumping Page 97 ....
Dumping Page 98 ....
Dumping Page 99 ....
Dumping Page 100 ....
Dumping Page 101 ....
Dumping Page 102 ....
Dumping Page 103 ....
Dumping Page 104 ....
Dumping Page 105 ....
Dumping Page 106 ....
Dumping Page 107 ....
Dumping Page 108 ....
Dumping Page 109 ....
Dumping Page 110 ....
Dumping Page 111 ....
Dumping Page 112 ....
Dumping Page 113 ....
Dumping Page 114 ....
Dumping Page 115 ....
Dumping Page 116 ....
Dumping Page 117 ....
Dumping Page 118 ....
Dumping Page 119 ....
Dumping Page 120 ....
Dumping Page 121 ....
Dumping Page 122 ....
Dumping Page 123 ....
Dumping Page 124 ....
Dumping Page 125 ....
Dumping Page 126 ....
Dumping Page 127 ....
Dumping Page 128 ....
Dumping Page 129 ....
Dumping Page 130 ....
Dumping Page 131 ....
Dumping Page 132 ....
Dumping Page 133 ....
Dumping Page 134 ....
Dumping Page 135 ....
Dumping Page 136 ....
Dumping Page 137 ....
Dumping Page 138 ....
Dumping Page 139 ....
Dumping Page 140 ....
Dumping Page 141 ....
Dumping Page 142 ....
Dumping Page 143 ....
Dumping Page 144 ....
Dumping Page 145 ....
Dumping Page 146 ....
Dumping Page 147 ....
Dumping Page 148 ....
Dumping Page 149 ....
Dumping Page 150 ....
Dumping Page 151 ....
Dumping Page 152 ....
Dumping Page 153 ....
Dumping Page 154 ....
Dumping Page 155 ....
Dumping Page 156 ....
Dumping Page 157 ....
Dumping Page 158 ....
Dumping Page 159 ....
Dumping Page 160 ....
Dumping Page 161 ....
Dumping Page 162 ....
Dumping Page 163 ....
Dumping Page 164 ....
Dumping Page 165 ....
Dumping Page 166 ....
Dumping Page 167 ....
Dumping Page 168 ....
Dumping Page 169 ....
Dumping Page 170 ....
Dumping Page 171 ....
Dumping Page 172 ....
Dumping Page 173 ....
Dumping Page 174 ....
Dumping Page 175 ....
Dumping Page 176 ....
Dumping Page 177 ....
Dumping Page 178 ....
Dumping Page 179 ....
Dumping Page 180 ....
Dumping Page 181 ....
Dumping Page 182 ....
Dumping Page 183 ....
Dumping Page 184 ....
Dumping Page 185 ....
Dumping Page 186 ....
Dumping Page 187 ....
Dumping Page 188 ....
Dumping Page 189 ....
Dumping Page 190 ....
Dumping Page 191 ....
Dumping Page 192 ....
Dumping Page 193 ....
Dumping Page 194 ....
Dumping Page 195 ....
Dumping Page 196 ....
Dumping Page 197 ....
Dumping Page 198 ....
Dumping Page 199 ....
Dumping Page 200 ....
Dumping Page 201 ....
Dumping Page 202 ....
Dumping Page 203 ....
Dumping Page 204 ....
Dumping Page 205 ....
Dumping Page 206 ....
Dumping Page 207 ....
Dumping Page 208 ....
Dumping Page 209 ....
Dumping Page 210 ....
Dumping Page 211 ....
Dumping Page 212 ....
Dumping Page 213 ....
Dumping Page 214 ....
Dumping Page 215 ....
Dumping Page 216 ....
Dumping Page 217 ....
Dumping Page 218 ....
Dumping Page 219 ....
Dumping Page 220 ....
Dumping Page 221 ....
Dumping Page 222 ....
Dumping Page 223 ....
Dumping Page 224 ....
Dumping Page 225 ....
Dumping Page 226 ....
Dumping Page 227 ....
Dumping Page 228 ....
Dumping Page 229 ....
Dumping Page 230 ....
Dumping Page 231 ....
Dumping Page 232 ....
Dumping Page 233 ....
Dumping Page 234 ....
Dumping Page 235 ....
Dumping Page 236 ....
Dumping Page 237 ....
Dumping Page 238 ....
Dumping Page 239 ....
Dumping Page 240 ....
Dumping Page 241 ....
Dumping Page 242 ....
Dumping Page 243 ....
Dumping Page 244 ....
Dumping Page 245 ....
Dumping Page 246 ....
Dumping Page 247 ....
Dumping Page 248 ....
Dumping Page 249 ....
Dumping Page 250 ....
Dumping Page 251 ....
Dumping Page 252 ....
Dumping Page 253 ....
Dumping Page 254 ....
Dumping Page 255 ....
Dumping Page 256 ....
Dumping Page 257 ....
Dumping Page 258 ....
Dumping Page 259 ....
Dumping Page 260 ....
Dumping Page 261 ....
Dumping Page 262 ....
Dumping Page 263 ....
Dumping Page 264 ....
Dumping Page 265 ....
Dumping Page 266 ....
Dumping Page 267 ....
Dumping Page 268 ....
Dumping Page 269 ....
Dumping Page 270 ....
Dumping Page 271 ....
Dumping Page 272 ....
Dumping Page 273 ....
Dumping Page 274 ....
Dumping Page 275 ....
Dumping Page 276 ....
Dumping Page 277 ....
Dumping Page 278 ....
Dumping Page 279 ....
Dumping Page 280 ....
Dumping Page 281 ....
Dumping Page 282 ....
Dumping Page 283 ....
Dumping Page 284 ....
Dumping Page 285 ....
Dumping Page 286 ....
Dumping Page 287 ....
Dumping Page 288 ....
Dumping Page 289 ....
Dumping Page 290 ....
Dumping Page 291 ....
Dumping Page 292 ....
Dumping Page 293 ....
Dumping Page 294 ....
Dumping Page 295 ....
Dumping Page 296 ....
Dumping Page 297 ....
Dumping Page 298 ....
Dumping Page 299 ....
Dumping Page 300 ....
Dumping Page 301 ....
Dumping Page 302 ....
Dumping Page 303 ....
Dumping Page 304 ....
Dumping Page 305 ....
Dumping Page 306 ....
Dumping Page 307 ....
Dumping Page 308 ....
Dumping Page 309 ....
Dumping Page 310 ....
Dumping Page 311 ....
Dumping Page 312 ....
Dumping Page 313 ....
Dumping Page 314 ....
Dumping Page 315 ....
Dumping Page 316 ....
Dumping Page 317 ....
Dumping Page 318 ....
Dumping Page 319 ....
Dumping Page 320 ....
Dumping Page 321 ....
Dumping Page 322 ....
Dumping Page 323 ....
Dumping Page 324 ....
Dumping Page 325 ....
Dumping Page 326 ....
Dumping Page 327 ....
Dumping Page 328 ....
Dumping Page 329 ....
Dumping Page 330 ....
Dumping Page 331 ....
Dumping Page 332 ....
Dumping Page 333 ....
Dumping Page 334 ....
Dumping Page 335 ....
Dumping Page 336 ....
Dumping Page 337 ....
Dumping Page 338 ....
Dumping Page 339 ....
Dumping Page 340 ....
Dumping Page 341 ....
Dumping Page 342 ....
Dumping Page 343 ....
Dumping Page 344 ....
Dumping Page 345 ....
Dumping Page 346 ....
Dumping Page 347 ....
Dumping Page 348 ....
Dumping Page 349 ....
Dumping Page 350 ....
Dumping Page 351 ....
Dumping Page 352 ....
Dumping Page 353 ....
Dumping Page 354 ....
Dumping Page 355 ....
Dumping Page 356 ....
Dumping Page 357 ....
Dumping Page 358 ....
Dumping Page 359 ....
Dumping Page 360 ....
Dumping Page 361 ....
Dumping Page 362 ....
Dumping Page 363 ....
Dumping Page 364 ....
Dumping Page 365 ....
Dumping Page 366 ....
Dumping Page 367 ....
Dumping Page 368 ....
Dumping Page 369 ....
Dumping Page 370 ....
Dumping Page 371 ....
Dumping Page 372 ....
Dumping Page 373 ....
Dumping Page 374 ....
Dumping Page 375 ....
Dumping Page 376 ....
Dumping Page 377 ....
Dumping Page 378 ....
Dumping Page 379 ....
Dumping Page 380 ....
Dumping Page 381 ....
Dumping Page 382 ....
Dumping Page 383 ....
Dumping Page 384 ....
Dumping Page 385 ....
Dumping Page 386 ....
Dumping Page 387 ....
Dumping Page 388 ....
Dumping Page 389 ....
Dumping Page 390 ....
Dumping Page 391 ....
Dumping Page 392 ....
Dumping Page 393 ....
Dumping Page 394 ....
Dumping Page 395 ....
Dumping Page 396 ....
Dumping Page 397 ....
Dumping Page 398 ....
Dumping Page 399 ....
Dumping Page 400 ....
Dumping Page 401 ....
Dumping Page 402 ....
Dumping Page 403 ....
Dumping Page 404 ....
Dumping Page 405 ....
Dumping Page 406 ....
Dumping Page 407 ....
Dumping Page 408 ....
Dumping Page 409 ....
Dumping Page 410 ....
Dumping Page 411 ....
Dumping Page 412 ....
Dumping Page 413 ....
Dumping Page 414 ....
Dumping Page 415 ....
Dumping Page 416 ....
Dumping Page 417 ....
Dumping Page 418 ....
Dumping Page 419 ....
Dumping Page 420 ....
Dumping Page 421 ....
Dumping Page 422 ....
Dumping Page 423 ....
Dumping Page 424 ....
Dumping Page 425 ....
Dumping Page 426 ....
Dumping Page 427 ....
Dumping Page 428 ....
Dumping Page 429 ....
Dumping Page 430 ....
Dumping Page 431 ....
Dumping Page 432 ....
Dumping Page 433 ....
Dumping Page 434 ....
Dumping Page 435 ....
Dumping Page 436 ....
Dumping Page 437 ....
Dumping Page 438 ....
Dumping Page 439 ....
Dumping Page 440 ....
Dumping Page 441 ....
Dumping Page 442 ....
Dumping Page 443 ....
Dumping Page 444 ....
Dumping Page 445 ....
Dumping Page 446 ....
Dumping Page 447 ....
Dumping Page 448 ....
Dumping Page 449 ....
Dumping Page 450 ....
Dumping Page 451 ....
Dumping Page 452 ....
Dumping Page 453 ....
Dumping Page 454 ....
Dumping Page 455 ....
Dumping Page 456 ....
Dumping Page 457 ....
Dumping Page 458 ....
Dumping Page 459 ....
Dumping Page 460 ....
Dumping Page 461 ....
Dumping Page 462 ....
Dumping Page 463 ....
Dumping Page 464 ....
Dumping Page 465 ....
Dumping Page 466 ....
Dumping Page 467 ....
Dumping Page 468 ....
Dumping Page 469 ....
Dumping Page 470 ....
Dumping Page 471 ....
Dumping Page 472 ....
Dumping Page 473 ....
Dumping Page 474 ....
Dumping Page 475 ....
Dumping Page 476 ....
Dumping Page 477 ....
Dumping Page 478 ....
Dumping Page 479 ....
Dumping Page 480 ....
Dumping Page 481 ....
Dumping Page 482 ....
Dumping Page 483 ....
Dumping Page 484 ....
Dumping Page 485 ....
Dumping Page 486 ....
Dumping Page 487 ....
Dumping Page 488 ....
Dumping Page 489 ....
Dumping Page 490 ....
Dumping Page 491 ....
Dumping Page 492 ....
Dumping Page 493 ....
Dumping Page 494 ....
Dumping Page 495 ....
Dumping Page 496 ....
Dumping Page 497 ....
Dumping Page 498 ....
Dumping Page 499 ....
Dumping Page 500 ....
Dumping Page 501 ....
Dumping Page 502 ....
Dumping Page 503 ....
Dumping Page 504 ....
Dumping Page 505 ....
Dumping Page 506 ....
Dumping Page 507 ....
Dumping Page 508 ....
Dumping Page 509 ....
Dumping Page 510 ....
Dumping Page 511 ....
Dumping Page 512 ....
Dumping Page 513 ....
Dumping Page 514 ....
Dumping Page 515 ....
Dumping Page 516 ....
Dumping Page 517 ....
Dumping Page 518 ....
Dumping Page 519 ....
Dumping Page 520 ....
Dumping Page 521 ....
Dumping Page 522 ....
Dumping Page 523 ....
Dumping Page 524 ....
Dumping Page 525 ....
Dumping Page 526 ....
Dumping Page 527 ....
Dumping Page 528 ....
Dumping Page 529 ....
Dumping Page 530 ....
Dumping Page 531 ....
Dumping Page 532 ....
Dumping Page 533 ....
Dumping Page 534 ....
Dumping Page 535 ....
Dumping Page 536 ....
Dumping Page 537 ....
Dumping Page 538 ....
Dumping Page 539 ....
Dumping Page 540 ....
Dumping Page 541 ....
Dumping Page 542 ....
Dumping Page 543 ....
Dumping Page 544 ....
Dumping Page 545 ....
Dumping Page 546 ....
Dumping Page 547 ....
Dumping Page 548 ....
Dumping Page 549 ....
Dumping Page 550 ....
Dumping Page 551 ....
Dumping Page 552 ....
Dumping Page 553 ....
Dumping Page 554 ....
Dumping Page 555 ....
Dumping Page 556 ....
Dumping Page 557 ....
Dumping Page 558 ....
Dumping Page 559 ....
Dumping Page 560 ....
Dumping Page 561 ....
Dumping Page 562 ....
Dumping Page 563 ....
Dumping Page 564 ....
Dumping Page 565 ....
Dumping Page 566 ....
Dumping Page 567 ....
Dumping Page 568 ....
Dumping Page 569 ....
Dumping Page 570 ....
Dumping Page 571 ....
Dumping Page 572 ....
Dumping Page 573 ....
Dumping Page 574 ....
Dumping Page 575 ....
Dumping Page 576 ....
Dumping Page 577 ....
Dumping Page 578 ....
Dumping Page 579 ....
Dumping Page 580 ....
Dumping Page 581 ....
Dumping Page 582 ....
Dumping Page 583 ....
Dumping Page 584 ....
Dumping Page 585 ....
Dumping Page 586 ....
Dumping Page 587 ....
Dumping Page 588 ....
Dumping Page 589 ....
Dumping Page 590 ....
Dumping Page 591 ....
Dumping Page 592 ....
Dumping Page 593 ....
Dumping Page 594 ....
Dumping Page 595 ....
Dumping Page 596 ....
Dumping Page 597 ....
Dumping Page 598 ....
Dumping Page 599 ....
Dumping Page 600 ....
Dumping Page 601 ....
Dumping Page 602 ....
Dumping Page 603 ....
Dumping Page 604 ....
Dumping Page 605 ....
Dumping Page 606 ....
Dumping Page 607 ....
Dumping Page 608 ....
Dumping Page 609 ....
Dumping Page 610 ....
Dumping Page 611 ....
Dumping Page 612 ....
Dumping Page 613 ....
Dumping Page 614 ....
Dumping Page 615 ....
Dumping Page 616 ....
Dumping Page 617 ....
Dumping Page 618 ....
Dumping Page 619 ....
Dumping Page 620 ....
Dumping Page 621 ....
Dumping Page 622 ....
Dumping Page 623 ....
Dumping Page 624 ....
Dumping Page 625 ....
Dumping Page 626 ....
Dumping Page 627 ....
Dumping Page 628 ....
Dumping Page 629 ....
Dumping Page 630 ....
Dumping Page 631 ....
Table object data formatting end.
DB2DART Processing completed with warning(s)!
Complete DB2DART report found in:
/home/db2inst1/sqllib/db2dump/DART0000/MISPRD1.RPT
[db2inst1@xxxxxxxxxx DART0000]$ ls -lrt
total 154224
-rw-r----- 1 db2inst1 db2iadm1 2785 Jun 18 2013 TS2T1031.DEL
-rw-r----- 1 db2inst1 db2iadm1 2785 Jun 18 2013 Bkt.del
-rw-r----- 1 db2inst1 db2iadm1 4492 Jun 18 2013 Bkt_New.del
-rw-r----- 1 db2inst1 db2iadm1 4606 Apr 9 12:16 TS2T6919.DEL
-rw-r----- 1 db2inst1 db2iadm1 73205552 Apr 10 11:43 TS2T8723.DEL
-rw-r----- 1 db2inst1 db2iadm1 73205552 Apr 10 12:06 BT_FT_COMPONENT_LOG_BKT_Apr10.DEL
-rw-r----- 1 db2inst1 db2iadm1 18268 Aug 1 13:10 MISPRD1.BAK
-rw-r--r-- 1 db2inst1 db2iadm1 1595 Aug 1 13:12 DART.INF
-rw-r----- 1 db2inst1 db2iadm1 18267 Aug 1 13:12 MISPRD1.RPT
-rw-r----- 1 db2inst1 db2iadm1 7508322 Aug 1 13:12 MISPRD1.DEL
[db2inst1@xxxxxxxxxx DART0000]$ db2 IMPORT FROM MISPRD1.DEL OF DEL commitcount 10000 INSERT INTO MISSTG.BT_FT_ASST_CNSMPTN_BKT_NEW
SQL3109N The utility is beginning to load data from file "MISPRD1.DEL".
SQL3221W ...Begin COMMIT WORK. Input Record Count = "10000".
SQL3222W ...COMMIT of any database changes was successful.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "20000".
SQL3222W ...COMMIT of any database changes was successful.
SQL3110N The utility has completed processing. "22136" rows were read from
the input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "22136".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "22136" rows were processed from the input file. "22136" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 22136
Number of rows skipped = 0
Number of rows inserted = 22136
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 22136
[db2inst1@xxxxxxxxxx DART0000]$ db2 "select count(*) from MISSTG.BT_FT_ASST_CNSMPTN_BKT_NEW "
1
-----------
22136
1 record(s) selected.
[db2inst1@xxxxxxxxxx DART0000]$ db2 load query table MISSTG.BT_FT_ASST_CNSMPTN_BKT_NEW
Tablestate:
Normal
[db2inst1@xxxxxxxxxx DART0000]$ db2 rename table MISSTG.BT_FT_ASST_CNSMPTN_BKT to BT_FT_ASST_CNSMPTN_BKT_OLD
DB20000I The SQL command completed successfully.
[db2inst1@xxxxxxxxxx DART0000]$ db2 rename table MISSTG.BT_FT_ASST_CNSMPTN_BKT_NEW to BT_FT_ASST_CNSMPTN_BKT
DB20000I The SQL command completed successfully.
[db2inst1@xxxxxxxxxx DART0000]$ db2 "select count(*) from MISSTG.BT_FT_ASST_CNSMPTN_BKT"
1
-----------
22136
1 record(s) selected.
[db2inst1@xxxxxxxxxx DART0000]$ db2 load query table MISSTG.BT_FT_ASST_CNSMPTN_BKT
Tablestate:
Normal
[db2inst1@xxxxxxxxxx DART0000]$
HADR Setup
For example please consider the below
DC IP: <DC SERVER IP>
DR IP: <DR SERVER IP>
User name: db2inst1
Database name: MISPRD1
======== TAKEN AN ONLINE BACKUP IN DC - PRIMARY
1. sh /home/db2inst1/BACKUP/ONLINE_FULL/Full_Online_Backup.sh -> Backup image : MISPRD1.0.db2inst1.NODE0000.CATN0000.20150409170003.001 (Full Backup Image)
2. sh /home/db2inst1/BACKUP/ONLINE_FULL/Incremental_Online_Backup.sh -> Backup image :
MISPRD1.0.db2inst1.NODE0000.CATN0000.20150410215932.001 (Incremental Backup Image)
======= COPY THE BACKUP FILES TO DR
1. scp <backup_image> db2inst1@<DR SERVER IP>:< DR_Backup_Image_path >
======= RESTORE THE DB IMAGE IN DR
1. Go to the path where all the backup image present in DR
2. db2 RESTORE DATABASE MISPRD1 incremental FROM <DR_Backup_Image_path> TAKEN AT 20150410215932 on /db2fs1 , /db2fs2 , /db2fs3 , /db2fs4 dbpath on /db2path parallelism 6 without prompting
3. db2 RESTORE DATABASE MISPRD1 incremental FROM <Backup_Image_path> TAKEN AT 20150409170003 parallelism 6 without prompting"
4. db2 RESTORE DATABASE MISPRD1 incremental FROM <Backup_Image_path> TAKEN AT 20150410215932 parallelism 6 without prompting"
Note: Do not rollforward any logs, directly enable HADR
======= DC: UPDATE HADR CONFIGURATION PARAMETERS ON PRIMARY DATABASE -
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_LOCAL_HOST <SOURCE DC SERVER IP>
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_LOCAL_SVC DB2_HADR_MISPRD_1
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_REMOTE_HOST <DESTINATION DR SERVER IP>
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_REMOTE_SVC DB2_HADR_MISPRD_2
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_REMOTE_INST db2inst1
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_SYNCMODE ASYNC
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_TIMEOUT 200
db2 CONNECT TO MISPRD1
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 UNQUIESCE DATABASE
db2 CONNECT RESET
====== DR : UPDATE HADR CONFIGURATION PARAMETERS ON STANDBY DATABASE -
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_LOCAL_HOST <DR SERVER IP>
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_LOCAL_SVC DB2_HADR_MISPRD_2
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_REMOTE_HOST <DC SERVER IP>
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_REMOTE_SVC DB2_HADR_MISPRD_1
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_REMOTE_INST db2inst1
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_SYNCMODE ASYNC
db2 UPDATE DB CFG FOR MISPRD1 USING HADR_TIMEOUT 200
====== DR :START HADR ON STANDBY DATABASE - - SB_INST (DB2HADR)
db2 DEACTIVATE DATABASE MISPRD1
db2 START HADR ON DATABASE MISPRD1 AS STANDBY
====== DC : START HADR ON PRIMARY DATABASE - SAM_PR (MISPRD1)--
db2 DEACTIVATE DATABASE MISPRD1
db2 START HADR ON DATABASE MISPRD1 AS PRIMARY;
-- MONITOR HADR:-
db2pd -d misprd1 -hadr
db2pd -db misprd1 –hadr
STOPPING HADR :-
db2 stop hadr on database misprd1;
HADR CHECK LIST
Check1> Check at both DC & DR (Primary & Standby)
Command: db2pd -d <DB_NAME> -hadr |
grep -i connect
Output:
“ConnectStatus” should be “Connected”
Raised alert if “ConnectStatus”
shows “disconnected”. (Alert already configured in the monitoring tool by tools
team)
If the show
disconnected please restart the HADR in both Primary and Standby
Check2 > Check at either DC
& DR (Primary & Standby)
Command: db2pd -d <DB_NAME> -hadr |
grep -i LOG
Output:
Log number should be same. Difference
should not be more than 5. If “Connectstatus” shows “Connected” and Log
difference more than 5 send an alert. (Alert already
configured in the monitoring tool by tools team)
Monitor the
log difference , if it is rapidly increasing instead of decreasing (more than
100 ) inform application Team about it .
Steps we need to fellow to do DC-DR switchover
Pre-Switch Over Activities: Database Backup along with their configurations
db2 "call get_dbsize_info(?, ?, ?, 0)"
db2 "get db cfg for misprd1" > /dr_dc_backups/misprd1/misprd1_cfg.out
db2 "get dbm cfg" > /dr_dc_backups/misprd1/bodbins1_cfg.out
db2set -all > /dr_dc_backups/misprd1/db2set.out
db2 list database directory > /dr_dc_backups/misprd1/db_dir.out
db2 list node directory > /dr_dc_backups/misprd1/node_dir.out
crontab -l > /dr_dc_backups/misprd1/bodbins1_crontab.out
db2 "get db cfg for misprd1" > /dr_dc_backups/misprd1/misprd1_cfg.out
db2 "get dbm cfg" > /dr_dc_backups/misprd1/bodbins1_cfg.out
db2set -all > /dr_dc_backups/misprd1/db2set.out
db2 list database directory > /dr_dc_backups/misprd1/db_dir.out
db2 list node directory > /dr_dc_backups/misprd1/node_dir.out
crontab -l > /dr_dc_backups/misprd1/bodbins1_crontab.out
|
DR
Switch Over Plan |
||||
|
Sl. No. |
Step Description |
Owner |
Required Team(s) |
Dependencies /
Roadblocks (if any) |
|
1 |
Stop all your business applications that are
connected to database on DC site. |
Application Team |
|
ALL Jobs and application should be stopped |
|
2 |
Check if any connection is there with Database
. If it is there stop the connection |
DBA |
|
|
|
3 |
Verify and make sure the databases on DC/DR
are connected, they're in PEER state and log position of DR (Standby) is
identical with DC (Primary). |
DBA |
|
|
|
4 |
Verify if all the tablespaces on DR is in
normal state. |
DBA |
|
|
|
5 |
Check if all the tables are in normal state in
DR |
DBA |
|
|
|
6 |
Switch the HADR role between DC / DR |
DBA |
|
|
DC Side :
1>
db2 list applications
2>
db2 force application all
3>
db2 get db cfg for misprd1 | egrep
“STATUS|ROLE|STATE|POS”
DR Side :
1>
db2 get db cfg for misprd1 | egrep
“STATUS|ROLE|STATE|POS”
2>
db2 takeover hadr on db MISPRD1
3>
db2 connect to MISPRD1
4>
db2 list tablespaces | grep –i state
5>
db2 "select tabschema,tabname,status from
syscat.tables"
Post-Switch Over Activities : Database Backup along with their configurations
db2 "call get_dbsize_info(?, ?, ?, 0)"
Enable crons
db2 "get db cfg for misprd1" > /dr_dc_backups/misprd1/misprd1_cfg.out
db2 "get dbm cfg" > /dr_dc_backups/misprd1/bodbins1_cfg.out
db2set -all > /dr_dc_backups/misprd1/db2set.out
db2 list database directory > /dr_dc_backups/misprd1/db_dir.out
db2 list node directory > /dr_dc_backups/misprd1/node_dir.out
crontab -l > /dr_dc_backups/misprd1/bodbins1_crontab.out
db2 list tablespaces show detail|grep -iA1 detail>/dr_dc_backups/misprd1/tablespace_state.out
DELETE THE ARCHIVED LOGS TILL THE LAST BACKUP
db2 list history backup all for misprd1 | grep LOG
db2 "prune history 20121204174657 and delete"
RESTARTING THE DB
Below are the steps:
Step 1 : --first check if any Rollback process is running in DB .
Command: db2top -d misprd1
Then press ‘ l’ ( Sessions) . It will show the application name if any rollback process is there.
Step 2: If roll back process is not there then close all the application running on DB.
Command: db2 force applications all
[db2inst1@<server name> ~]$ db2 force applications all
DB20000I The FORCE APPLICATION command completed successfully.
See the list of applications currently connected:
Command: db2 list applications for database misprd1
Step 3: Stop the DB
Command: db2stop
Sometimes it can give the error ”The database manager was not stopped because databases are still active”.
[db2inst1@<server name> ~]$ db2stop
09/16/2013 14:08:03 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 use below command.
Command: db2stop force
[db2inst1@<server name> ~]$ db2stop force
Step 4: Now start the DB again.
Command: db2start
[db2inst1@<server name> ~]$ db2start
Checks Needs to be done after Restarting the DB.
1. Try connecting to DB (This may take some time; better to do it in the terminal of <server name> machine through VNC)
Command: db2 connect to misprd1.
[db2inst1@<server name> ~]$ db2 connect to misprd1
Database Connection Information
Database server = DB2/LINUXX8664 9.7.6
SQL authorization ID = DB2INST1
Local database alias = MISPRD1
[db2inst1@<server name> ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
2. Check Database status
Command: db2top –d misprd1 ( then press d)
[db2inst1@<server name> ~]$ db2top –d misprd1
3. Check Hadr setup. It should be connected and should be in sync.
Command: db2pd -db misprd1 –hadr
[db2inst1@<server name> ~]$ db2pd -db misprd1 -hadr
Database Partition 0 -- Database MISPRD1 -- Active -- Up 0 days 00:01:21 -- Date 2013-09-16-14.10.50.239453
HADR Information:
Role State SyncMode HeartBeatsMissed LogGapRunAvg (bytes)
Primary Peer Async 0 0
ConnectStatus ConnectTime Timeout
Connected Mon Sep 16 14:09:48 2013 (1379320788) 200
LocalHost LocalService
<DC SERVER> DB2_HADR_MISPRD_1
RemoteHost RemoteService RemoteInstance
<DR SERVER> DB2_HADR_MISPRD_2 db2inst1
PrimaryFile PrimaryPg PrimaryLSN
S0093920.LOG 0 0x0000080B2A6B2010
StandByFile StandByPg StandByLSN
S0093920.LOG 0 0x0000080B2A6B2010
If HADR is not active, start it in DC(10.1.3.29) as primary
Command: db2 start hadr on database misprd1 as primary
And check the status using the above command
4. Check the latest snapshot of DB. There should not be any idle process for long time.
Steps:
1. First get the switch list for DB partition
[db2inst1@<server name> ~]$ db2 get monitor switches
Monitor Recording Switches
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock Information (LOCK) = OFF
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 09/16/2013 14:08:49.183364
Unit of Work Information (UOW) = OFF
2. Update the switches. Set it to ON mode.
[db2inst1@<server name> ~]$ db2 update monitor switches using BUFFERPOOL on LOCK on
DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
[db2inst1@<server name> ~]$ db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on UOW on
DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
3. Get the snapshot for DB.
[db2inst1@<server name> ~]$ db2 get snapshot for all applications > snap_April07.out
Daily
Checklist of Database:
1.
Database Backup: Can set up
daily incremental and weekly full backup or weekly incremental backup and
monthly full backup.
2.
Mount point check - Should take
corrective action if it reach 75%
3.
HADR status - Sync or not.
4.
Tablespace state check - It
should be 0x0000 ( db2 list tablespaces )
5.
Table status check - It should
be normal ( db2 load query table <schemaname>.<tabname> )
6.
RUNSTAT - Can be done once in a
week ( When data load will be less )
7.
Table Space Utilization (Not
for temporary tablespace )
8.
Daily backup of diag.log and
DDL script.
9.
Archive log deletion after last
backup.
10. Log utilization of the database - Should not cross 70%.
