What is troubleshooting?
Steps taken by DBA whenever DBA is approached by any user/team asking for help to fix an issue
What should be DBA's approach to troubleshooting?
Whenever a Database user approaches a DBA about an issue/error, first and foremost step should be gathering all the required information by way of asking sensible questions. The questions to ask can be below.
- Database Server name
- Name or Port number of the instance
- Database name
- What is the SQL/DB2 error code ?
- What was the attempted operation ?
- What was the expected outcome and what is the actual outcome along with duration?
- Till when was it working fine and from when it is giving errors?
- Are there any Changes introduced into the Server?
- Did this happen anytime in the past?
- What is the Business Impact and number of Users impacted? Severity/Priority
Gain
more understanding of the situation
aDB2
error messages are structured as follows:
aCCCnnnnnS
a•
CCC identifies the DB2 component returning the message.
aDB2
(or) SQL (or) DBA (or) ASN (or) EXP
a• nnnnn is a three- to
five-digit error code.
a• S
is the severity indicator.
a When S has a value W, it indicates a warning.
a When S has a value of N, it indicates an error
(i.e. a negative SQL code).
a When S has a value of I, it indicates an
Information
aYou
can get more information about your SQL/DB2 error code by using the following
command.
a>
db2 "?
<SQL/DB2 errorcode>" --Help command gives your explanation and user response.
ae.g.
and common error codes
aSQL104N
àSyntax error
aSQL206N
àColumn used doesn’t exist in the table
aSQL204N
àObject doesn’t exist
aSQL911N àMore about this below as it needs more understanding and and can occur frequently
aSQL0551N
à Privilege related issue
aDB2000I
à Information stating that command completed successfully.
aSQL0217W
à The statement was not executed as only Explain information
requests are being processed.
SQLSTATE=01604
aSQL0668N
à Related to table access and availability (REORG or LOAD
pending)
aSQL0803N
à It is data issue
> db2 "? SQL0911N"
SQL0911N The current
transaction has been rolled back because of a deadlock or timeout. Reason code
"<reason-code>".
Explanation:
The current unit of work was involved in an unresolved
contention for use of an object and had to be rolled back.
The reason codes are as follows:
2 - The transaction was
rolled back due to a deadlock.
68 - The transaction was rolled back due to a lock timeout.
Diagnostic logs
adb2diag.log à DB2
Diagnostic log
a <instancename>.nfy à DB2 Instance noticication log
Location of Diagnostic Log files
>db2
“get dbm cfg” | grep -i diagpath
Diagnostic data directory path (DIAGPATH) =
Default path in Linux/UNIX OS is <instance home dir>/sqllib/db2dump
Change Location
of Diagnostic Log files
>db2 “update dbm cfg using DIAGPATH
<pathname>”
Diagnostic levels
•>db2
“get dbm cfg” | grep -i DIAGLEVEL
•Diagnostic error capture level
(DIAGLEVEL) = 3
•This
parameter specifies the type of diagnostic errors that will be recorded in the
db2diag log file.
•DIAGLEVEL
(0-4)
–0 -
No diagnostic data captured
–1 -
Severe errors only
–2 -
All errors
–3 -
All errors and warnings
–4 -
All errors, warnings and informational messages
–
•>db2 get dbm cfg | grep -i NOTIFYLEVEL
• Notify Level (NOTIFYLEVEL) = 3
•This
parameter specifies the type of administration notification messages that are
written to the administration notification log.
db2diag command
Creating a new Diagnostic log
>db2diag –A
This will rename the current Diagnostic log file by
suffixing current time stamp as shown
below.
•
db2diag command
Retrieving error messages of Sever and Error “Level”
>db2diag –level “Error, Severe”
Retrieving entire information for the last n hour(s)
>db2diag –H nh
E.g. >db2diag –H 2h à To get all info from
db2diag.log for the last 2 hours
Retrieving entire information for the last n days(s)
>db2diag –H nd
E,g.
>db2diag –H 3d à To get all info from
db2diag.log for the last 3 days
No comments:
Post a Comment