Monday, March 30, 2026

Creating SSH Keys for Use with OCI

Creating SSH Keys for Use with Oracle Cloud Services

Oracle Trace File Analyzer TFA

Oracle Trace File Analyzer Tips And Tricks | Oracledbwr

Whats new in oracle trace file analyzer 19.2 | PDF

Useful TFACTL commands - DBACLASS DBACLASS

Thursday, March 26, 2026

Oracle Audit

Procedure (Unified Auditing; 12c and later)

1) Verify/enable Unified Auditing view availability
- Test access:
select count(*) from unified_audit_trail;
- If it errors, you are likely in pre-12c or Unified Auditing not available; stop and clarify version.

2) Create a DML auditing policy for target tables
- For a specific table:
create audit policy app_dml_pol actions
insert on APP_SCHEMA.T1,
update on APP_SCHEMA.T1,
delete on APP_SCHEMA.T1;
audit policy app_dml_pol;
- For many existing tables (generate statements):
select 'alter audit policy app_dml_pol add actions insert, update, delete on '
|| owner || '.' || table_name || ';'
from dba_tables
where owner = 'APP_SCHEMA';
-- run the generated ALTER AUDIT POLICY statements

3) Automatically include future tables (workaround via DDL trigger; no schema-wide wildcard)
- One-time setup:
create or replace trigger app_schema_audit_ddl
after create on database
declare
v_stmt varchar2(4000);
begin
if ora_dict_obj_type = 'TABLE' and ora_dict_obj_owner = 'APP_SCHEMA' then
v_stmt := 'alter audit policy app_dml_pol add actions insert, update, delete on '
|| ora_dict_obj_owner || '.' || ora_dict_obj_name;
execute immediate v_stmt;
end if;
end;
/
-- Ensure the policy app_dml_pol exists before creating the trigger.

4) (Optional) Limit auditing to application users to reduce volume
- Example:
noaudit policy app_dml_pol;
audit policy app_dml_pol by users APP_USER1, APP_USER2;

5) Review/Report audited DML
- Recent DML counts by object and action:
select to_char(event_timestamp, 'YYYY-MM-DD HH24:MI') ts_min,
obj_name,
action_name,
count(*) cnt
from unified_audit_trail
where dbusername in ('APP_USER1','APP_USER2') -- or remove to see all
and action_name in ('INSERT','UPDATE','DELETE')
and event_timestamp >= systimestamp - interval '1' day
group by to_char(event_timestamp, 'YYYY-MM-DD HH24:MI'), obj_name, action_name
order by 1,2,3;

- Who/where summary:
select obj_name, action_name, dbusername, client_id, userhost, module,
count(*) cnt
from unified_audit_trail
where action_name in ('INSERT','UPDATE','DELETE')
and event_timestamp >= systimestamp - interval '1' day
group by obj_name, action_name, dbusername, client_id, userhost, module
order by cnt desc;

6) Purge/retention (prevent trail growth and performance impact)
- Create a purge job retaining 30 days (adjust as needed):
begin
dbms_audit_mgmt.init_cleanup(audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
default_cleanup_interval => 24);
dbms_audit_mgmt.create_purge_job(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_purge_interval => 24,
audit_trail_purge_name => 'PURGE_UNIFIED_AUDIT_30D',
use_last_arch_timestamp => true);
dbms_audit_mgmt.set_last_archivelog_timestamp(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
last_archive_time => systimestamp - interval '30' day);
end;
/

7) Disable if needed
- Stop auditing:
noaudit policy app_dml_pol;
- Remove objects from policy or drop policy when finished:
drop audit policy app_dml_pol;

Notes
- Auditing every DML on a large schema can add overhead; start with the most-suspect tables/users, then expand as required.
- For SYS activity (if relevant), enabling audit_sys_operations captures privileged operations; use only if needed.
- If fixed object stats jobs slow down due to a very large unified audit trail, lock stats on SYS.X$UNIFIED_AUDIT_TRAIL:
exec dbms_stats.lock_table_stats('SYS','X$UNIFIED_AUDIT_TRAIL');

Summary: Yes. Use Unified Auditing to audit INSERT/UPDATE/DELETE on the application tables. Create a policy, attach target tables (generate statements for many tables), optionally auto-add future tables with a DDL trigger, restrict to app users if desired, report from UNIFIED_AUDIT_TRAIL, and manage retention with DBMS_AUDIT_MGMT.

Tuesday, September 23, 2025

URL

DB MONITORING SCRIPTS DBACLASS

Tales From A Lazy Fat DBA

GitHub - fatdba/Oracle-Database-Scripts: My Oracle DB Scripts

DBATracker

oracle-developer (oracle-developer.net) · GitHub

ORACLE RAC STARTUP SEQUENCE - Oracle Consulting Services | USA | 99% Customer Retention | Doyensys

OracleView

download Oracle database virtual machines

Databases Are Fun – dohdatabase.com

Oracle Scratchpad | Just another Oracle weblog

Tanel Poder Consulting

GoldenGate Archives -

Home - All Things Oracle (Oracle Patch)

https://database-heartbeat.com/

Oracle Exadata Command Reference

Understanding Exadata disk layout | Page On-Call DBA Exadata

Step By Step Patching an Exadata cell node Exadata Storage Cell Patching

OracleDBPro - Pini Dibask Blog: Data Protection Flashback

Oracle Wait Events Cheat Sheet - Performance Tuning Quick Reference | Oracle DBA Scripts & Database Utilities Wait Events

SQL Plan Baseline, SQL Patch, SQL Profile: Differences and Use Cases – Osman’s DBlog SQL PROFILE ,SQL PATCH ,SQL BASE LINE

In Oracle RAC (Real Application Clusters), the components CSSD, CRS, EVMD, and DIAG are core parts of the Clusterware stack. They work together to ensure cluster stability, node coordination, failover, and diagnostics.

1. CSSD (Cluster Synchronization Services Daemon)

🔹 What it is

CSSD (ocssd.bin) is the heartbeat and cluster membership manager.

🔹 Core Responsibilities

✅ Node Membership Management

Tracks which nodes are alive or dead
Maintains the cluster node list

✅ Heartbeat Mechanism

Uses two types:

Network heartbeat → via private interconnect
Disk heartbeat → via voting disks

👉 If a node misses heartbeat → it is considered dead.

🔹 Split-Brain Prevention

This is CSSD’s most critical job.

Uses voting disks
Requires majority quorum
If a node loses quorum → it gets evicted (rebooted)

🔹 Failure Scenario

Example:

Node loses network connectivity
CSSD checks voting disks
If quorum lost → node is killed to protect data integrity

🔹 Key Files / Logs


$GRID_HOME/log/<node>/cssd/ocssd.log

🔹 Key Process


ocssd.bin


⚙️ 2. CRS (Cluster Ready Services)
🔹 What it is
CRS (crsd.bin) is the resource manager of Oracle Clusterware.

🔹 Core Responsibilities
✅ Resource Management
Manages:


Databases


Instances


Listeners


VIPs


ASM



✅ Start/Stop Resources


Starts resources in correct order


Handles dependencies


Example:
ASM → Database → Services


✅ Failover Management


If a resource fails → CRS restarts it


If node fails → relocates resources to another node



🔹 Resource Dependency Example
Database depends on ASM
Listener depends on network


🔹 Logs
$GRID_HOME/log/<node>/crsd/crsd.log


🔹 Key Process
crsd.bin


📡 3. EVMD (Event Manager Daemon)
🔹 What it is
EVMD (evmd.bin) is the event notification system.

🔹 Core Responsibilities
✅ Event Publishing


Publishes cluster events:


Node up/down


Instance start/stop


Failover events





✅ Event Subscription


Applications/scripts can subscribe to events



✅ FAN (Fast Application Notification)


Sends events to clients (e.g., JDBC, OCI)


Helps apps react instantly to failures



🔹 Example Use Case


Node crashes


EVMD sends event


Application connection pool drops dead connections immediately



🔹 Logs
$GRID_HOME/log/<node>/evmd/evmd.log


🔹 Key Process
evmd.bin


🩺 4. DIAG (Diagnostic Daemon)
🔹 What it is
DIAG (diagdaemon / integrated diag framework) is responsible for diagnostics and health monitoring.

🔹 Core Responsibilities
✅ Log Collection


Collects logs from all cluster components



✅ Health Monitoring


Tracks component health


Works with Cluster Health Monitor (CHM)



✅ Incident Detection


Detects critical issues


Generates trace files and dumps



🔹 Integration


Works with:


ADR (Automatic Diagnostic Repository)


Trace infrastructure





🔹 Logs Location
$GRID_HOME/log/<node>/diag/


🔗 How They Work Together
🔄 Startup Flow


OHASD starts


CSSD starts → establishes cluster membership


CRS starts → manages resources


EVMD starts → enables event system


DIAG runs in background



🔄 Failure Flow Example
Node Crash:


CSSD detects heartbeat loss


Node evicted


CRS relocates resources


EVMD sends notifications


DIAG logs everything



🧩 Architecture Relationship
OHASD (Oracle High Availability Service)
   │
   ├── CSSD  → Cluster membership + heartbeat
   ├── CRS   → Resource management
   ├── EVMD  → Event system
   └── DIAG  → Diagnostics


🚨 Quick Comparison Table
Component Role Critical Function
CSSD Cluster control Node membership, heartbeat
CRS Resource manager Start/stop/failover
EVMD Event system FAN notifications
DIAG Diagnostics Logs, health, incidents

🛠️ Important Commands
Check cluster status
crsctl stat res -t

Check cluster health
crsctl check cluster -all

Check CSS
crsctl check cssd

Component	Role	Critical Function
CSSD	Cluster control	Node membership, heartbeat
CRS	Resource manager	Start/stop/failover
EVMD	Event system	FAN notifications
DIAG	Diagnostics	Logs, health, incidents

Monday, July 14, 2025

Gather Statistics

1.Gather Dictionary Stats: EXEC DBMS_STATS.gather_dictionary_stats;

2.Gather Fixed Object Stats: EXEC DBMS_STATS.gather_fixed_objects_stats;

3.Gather Full Database Stats:

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

degree => 8

);

4. Gather Schema Stats

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

EXEC DBMS_STATS.gather_schema_stats(

ownname => 'SCOTT',

method_opt => 'FOR ALL COLUMNS SIZE 1',

granularity => 'ALL',

degree => 8,

cascade => TRUE,

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

);

5. Gather Table Stats

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

EXEC DBMS_STATS.gather_table_stats(

ownname => 'SCOTT',

tabname => 'EMP',

cascade => TRUE,

method_opt => 'FOR ALL COLUMNS SIZE 1',

granularity => 'ALL',

degree => 8

);

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'HR',
tabname => 'EMPLOYEES',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => TRUE
);
END;

6. Gather Index Stats

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_PK');

7. Gather Partition Stats

BEGIN

DBMS_STATS.gather_table_stats(

ownname => 'SCOTT',

tabname => 'SALES',

partname => 'SALES_Q1',

granularity => 'APPROX_GLOBAL AND PARTITION',

degree => 8

);

END;

The METHOD_OPT parameter in Oracle's DBMS_STATS procedures is a powerful and nuanced tool that controls how column statistics are gathered—especially histograms and extended statistics. Here's a breakdown to help you use it effectively

What METHOD_OPT Does

It determines:

Which columns get base statistics (min, max, NDV, nulls, etc.)
Whether histograms are created, and what type
Whether extended statistics (like column groups) are gathered

🧾 Syntax Overview

METHOD_OPT => 'FOR [column_spec] SIZE [histogram_spec]'

🔹 Column Spec Options


`FOR ALL COLUMNS ==> Gathers stats for all columns (default)`
`FOR ALL INDEXED COLUMNS ==> Only indexed columns (not recommended)`
`FOR ALL HIDDEN COLUMNS ==> Only virtual/hidden columns (special use case)`
`FOR COLUMNS (col1, col2) ==> Specific columns or column group`

🔸 Histogram Spec Options


`SIZE 1 ==> No histograms`
`SIZE AUTO ==> Oracle decides based on usage and data distribution (recommended)`
`SIZE SKEWONLY ==> Histograms only if data is skewed`
`SIZE REPEAT ==> Repeats previous histogram settings`
`SIZE ==> Explicit number of histogram buckets (e.g. 254)`

✅ Examples

1. No Histograms

METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'

2. Auto Histograms

METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'

3. Specific Columns with Histograms

METHOD_OPT => 'FOR COLUMNS (col1, col2) SIZE 254'

4. Column Group Statistics

METHOD_OPT => 'FOR COLUMNS (col1, col2) SIZE AUTO'

This gathers extended stats for the column group.

🧠 Best Practices

Use SIZE AUTO for most environments—it lets Oracle decide intelligently.
Avoid FOR ALL INDEXED COLUMNS unless you have a very narrow workload.
Use column groups for correlated columns in WHERE or JOIN clauses.

In Oracle's DBMS_STATS package, the ESTIMATE_PERCENT parameter controls how much of the data is sampled when gathering statistics. It directly affects the accuracy and performance of the stats collection process.

What Does ESTIMATE_PERCENT Do?

It specifies the percentage of rows Oracle should sample from a table (or index) to generate statistics. You can set it to:

A specific number (e.g., 10, 50, 100)
Or use the automatic setting: DBMS_STATS.AUTO_SAMPLE_SIZE

Recommended Setting: DBMS_STATS.AUTO_SAMPLE_SIZE

Oracle recommends using:

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

🔍 Why?

Oracle uses adaptive sampling algorithms to determine the best sample size.
It avoids full table scans unless necessary.
It ensures accurate NDV (Number of Distinct Values) without high overhead.
Required for incremental statistics and hybrid histograms

1. Auto Sample Size (Best Practice)

EXEC DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'HR',

tabname => 'EMPLOYEES',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

cascade => TRUE );

2. Full Table Scan (100% Sampling)

estimate_percent => 100

Monday, December 5, 2022

IBM DB2 LUW

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]$

db2level

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:

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>

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> ;

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

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 "

-----------

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"

-----------

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

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%.