Db2 troubleshooting tools

-

The db2pd command::

You can use the Db2 problem determination tool, db2pd, to obtain troubleshooting information from database system memory sets, without acquiring any lock or latch in the database. Since the db2pd tool works directly with memory, it is possible to retrieve information that is changing as it is being collected using minimal Db2 engine resources.

If the database is running in a Massively Parallel Processing (MPP) setup, a pureScale configuration, or an HADR setup, this command must be run on all the nodes or members of the cluster. However, if you have logical partitions within one physical server in an MPP setup, it collects information for all the logical nodes of the current host.

If you want to see the HADR database status, you can execute the db2pd command either on the primary or on the standby server.

Use the following command to execute the tool:

db2pd -db SAMPLE -hadr

Database Member 0 -- Database SAMPLE -- Active -- Up 170 days 08:17:44 -- Date 2018-05-13-20.23.52.606937

HADR_ROLE = PRIMARY

REPLAY_TYPE = PHYSICAL

HADR_SYNCMODE = NEARSYNC

STANDBY_ID = 1

LOG_STREAM_ID = 0

HADR_STATE = PEER

HADR_FLAGS =

PRIMARY_MEMBER_HOST = DB2AXNODE001

PRIMARY_INSTANCE = db2inst1

PRIMARY_MEMBER = 0

STANDBY_MEMBER_HOST = DB2AXNODE002

STANDBY_INSTANCE = db2inst1

STANDBY_MEMBER = 0

HADR_CONNECT_STATUS = CONNECTED

HADR_CONNECT_STATUS_TIME = 11/24/2017 11:08:30.948572 (1511543310)

HEARTBEAT_INTERVAL(seconds) = 30

HEARTBEAT_MISSED = 3

HEARTBEAT_EXPECTED = 21499

HADR_TIMEOUT(seconds) = 120

TIME_SINCE_LAST_RECV(seconds) = 0

PEER_WAIT_LIMIT(seconds) = 300

LOG_HADR_WAIT_CUR(seconds) = 0.000

LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000681

LOG_HADR_WAIT_ACCUMULATED(seconds) = 787983.115

LOG_HADR_WAIT_COUNT = 529872503

SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 262088

SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 262088

PRIMARY_LOG_FILE,PAGE,POS = S0135197.LOG, 33536, 21171039888078

STANDBY_LOG_FILE,PAGE,POS = S0135197.LOG, 33521, 21171039826535

HADR_LOG_GAP(bytes) = 2508

STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0135197.LOG, 33520, 21171039822144

STANDBY_RECV_REPLAY_GAP(bytes) = 3728

PRIMARY_LOG_TIME = 05/13/2018 20:23:52.000000 (1526261032)

STANDBY_LOG_TIME = 05/13/2018 20:23:49.000000 (1526261029)

STANDBY_REPLAY_LOG_TIME = 05/13/2018 20:23:48.000000 (1526261028)

STANDBY_RECV_BUF_SIZE(pages) = 131072

STANDBY_RECV_BUF_PERCENT = 0

STANDBY_SPOOL_LIMIT(pages) = 33423360

STANDBY_SPOOL_PERCENT = 0

STANDBY_ERROR_TIME = NULL

PEER_WINDOW(seconds) = 300

PEER_WINDOW_END = 05/13/2018 20:28:50.000000 (1526261330)

READS_ON_STANDBY_ENABLED = N

 

If you want to extract all of the locking information on the SAMPLE database, you can run the db2pd command without actually locking or latching any resource in the database using the following command:

db2pd -db SAMPLE -locks

Database Member 0 -- Database SAMPLE -- Active -- Up 170 days 08:19:35 -- Date 2018-05-13-20.25.43.409995

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID

0x0A000300464B7B80 132 FFFA801500390006C0F6000252 RowLock ..X G 132 1 0 0x00200020 0x40000000 0

0x0A000300464CCA00 132 00CE00040000000143DD009952 RowLock ..X G 132 1 0 0x00200008 0x40000000 0

0x0A000300464E4800 132 00CE00040000000143DC00BA52 RowLock ..X G 132 1 0 0x00200008 0x40000000 0

0x0A000300464CDF80 132 00CE00040000000143DE002E52 RowLock ..X G 132 1 0 0x00200008 0x40000000 0

Comments

Post a Comment

Popular posts from this blog

Db2 export command example using file format (del , ixf)

-
You want to transfer data from a db2 database table to another db2 database or within the same database, one of the way is to use db2 export / import command. Using db2 export / import you can move your data to excel also.   DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of  del - delimited , wsf - worksheet format , ixf - Integration Exchange Format,asc.   The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data ...
Read more

How to fix DB2 Tablespace OFFLINE state issue?

-
Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...
Read more

How to determine fenced User-ID of a DB2 instance

-
When you create a DB2 instance in Unix, you have the option of specifying a fenced user-id different than instance owner. The purpose of fenced user id is to protect DB2 from untested or malicious code from the external stored procedures, user defined functions etc. When you try to run these external procedures, they run in a separate address space owned by the fenced user id.   You know about the fenced user id if you created a DB2 instance yourself on your Unix platform. But suppose, you are looking at a DB2 instance created by someone and you are wondering as which is the fenced user id, you can look at the user and group id of the file .fenced in ~/sqllib/ctrl directory and this will tell you as which is the fenced user id. For Eg:/home/db2inst1/sqllib/ctrl/.fencedID It will display the fenced user id for the instance db2inst1   Another method to find the fenced user id is   db2pd -fmp  
Read more