Transcript db2 - Binus Repository

Matakuliah
Tahun
: T0413
: 2009
Trouble Shooting and Maintenance
Pertemuan 13
Problem Diagnosis
db2 ? <code>
Review the DB2
online manuals
(Information Center)
Problem!
Review
Admin notification log
or
the db2diag.log
Check the
DB2 Express-C forum
Review system with
Operating System
commands
Search for APARs, or
known problems
Bina Nusantara University
3
db2 help: ?
db2
db2
db2
db2
db2
Bina Nusantara University
?
?
?
?
?
SQL0104N
SQL104N
SQL-0104
SQL-104
SQL-104N
4
Finding More Information About Error Codes
• To find out more information about an error code received, in the
Command Editor input area, enter the code prefixed by a question
mark and click the Execute button
Prefix error code by
a question mark in
the Input area
More information
about the error
code appears in
the Output area
Bina Nusantara University
5
The DB2 Information Center
•
The DB2 Information Center contains the DB2 online manuals. It also includes the
manuals of products related to DB2.
•
It has a search field
•
It can be installed locally, or accessed through the internet
•
The internet version is the most up-to-date one. The URLs are:
– V9: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp
– V9.5: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp
•
Most of the answers to your questions can be found from the DB2 Information
Center!
Bina Nusantara University
6
DB2 Administration Notification Log
Log with diagnosis information at the point failure
On Linux/UNIX platforms, the administration notification log is a text
file called instance.nfy
On Windows, all administration notification messages are written to
the Event Log, or you can see in in the DB2 Journal
The DBM configuration parameter notifylevel specifies the level of
information to be recorded:
ƒ
ƒ
ƒ
ƒ
ƒ
0 -- No administration notification messages captured (not recommended)
1 -- Fatal or unrecoverable errors
2 -- Immediate action required
3 -- Important information, no immediate action required (default)
4 -- Informational messages
Bina Nusantara University
7
db2diag.log
Information included in the db2diag.log




The timestamp when the error occurred. Use it to find the error quickly
The application name and DB2 function involved in the problem.
A diagnostic message explaining the reason for the error.
Any available supporting data, such as SQLCA data structures and
pointers to the location of any extra dump or trap files.
If you can recreate the problem:
 Remove/rename the db2diag.log
 Recreate the problem. A new db2diag.log file will be generated
Bina Nusantara University
8
Example of a db2diag.log entry
Timestamp when the problem occurred
Level: Be concerned when you see
“Severe” or “Error” here.
Application name
Error message information. Use any of these for searching in “Google”
Bina Nusantara University
9
Locating the db2diag.log
WIN XP/2003 (default)
C:\Documents and Settings\All Users\Application Data\IBM\DB2\DB2COPY1\<instance name>
ƒWIN VISTA
ƒ ProgramData\IBM\DB2\
Linux/UNIX (default)
INSTHOME/sqllib/db2dump (INSTHOME is the home directory of the instance owner)
You can change the location of the db2diag.log using diagpath in the dbm cfg
ƒ db2 update dbm cfg using diagpath <path>
The verbosity of diagnostic text is determined by diaglevel in the dbm cfg
ƒ range is 0 to 4 (default is 3)
ƒ most verbose is 4
Bina Nusantara University
10
The DB2 Express-C Forum
Free community assistance
www.ibm.com/developerworks/forums/dw_forum.jsp?forum=805&cat=19
Mainly in English
The IBM DB2 Express-C team monitors the forum, though it is
the community who assists the most
You can also access it from the DB2 Express-C web site:
www.ibm.com/db2/express
(and click the button to go to the forum)
Bina Nusantara University
11
Maintenance Utilities
REORG, RUNSTATS, REBIND
REORG
RUNSTATS
REBIND
Bina Nusantara University
12
Database Operational View in Control Center
Bina Nusantara University
13
Table Reorganization (REORG)
•
•
•
•
Over time, tables and indexes get fragmented (much like a file system)
A REORG reclaims wasted space and re-organizes data to make
retrieval more efficient
Tables that are modified the most will benefit the most from REORGs
REORG has an Online and Offline option
– Offline REORG is faster and more efficient, but does not permit
access to the table
– Online REORG allows access to the table, but can consume a lot
of system resources; it works best for small tables
REORG TABLE <tablename>
Bina Nusantara University
Example:
REORG TABLE employee
14
Statistics (RUNSTATS)
• Keeps database statistics up-to-date
– e.g. number of rows in a table, index size, data value ranges,
etc.
• Statistics are used by DB2 whenever queries are executed
– DB2 Query Optimizer is a cost-based optimizer
– Analyzes these statistics to produce data access plan
• The frequency of statistics gathering should be determined by how
often the data in the table changes
RUNSTATS ON TABLE <schema.tablename>
Example:
RUNSTATS ON TABLE myschema.employee
Bina Nusantara University
15
RUNSTATS and REORG from the Control
Center
Bina Nusantara University
16
Control Center > (expand) All Databases folder > (expand) Database > (select) Tables Folder > (right-click)
Table > Reorganize/Run Statistics
Bind/Rebind Packages
 Applies to embedded SQL applications, SQL stored procedures
 Static SQL application depicted
embedded
C program
(.sqc)
Access plans for
static SQL are
determined at
BIND time
Bina Nusantara University
C file
(.c)
library
.exe
bind file
(.bnd)
package
17
REBIND
db2rbind database_alias -l <logfile>
Example:
db2rbind sample -l mylog.txt
Bina Nusantara University
18
Maintenance Choices
• Automated Maintenance
– Have DB2 automatically look after maintenance for you
(REORG, RUNSTATS, BACKUP)
• Manual Maintenance
– You perform maintenance activities when the need arises
• Create Scripts to Perform Maintenance
– Schedule regular execution of scripts
Bina Nusantara University
19
Automated Maintenance
•
Based on a user-defined maintenance window
– DB2 will perform maintenance on this window ONLY if needed.
– Two maintenance windows:
• ONLINE: For online maintenance activities such as
RUNSTATS and online BACKUP. These can be defined at
any time because the database remains fully accessible
• OFFLINE: For offline maintenance activities such as offline
BACKUP and REORG. Database may become inaccessible
and any connected applications may be affected.
– Database keeps track of operations performed and schedules
any needed maintenance in the next appropriate maintenance
window
Bina Nusantara University
20
Scheduled Maintenance Using Task Center
• More time-consuming than automatic
maintenance, but more customization possibilities
– E.g. different tables might have different backup
needs
• Task Center is the central location for all
scheduled activities for the DB2 administration
tools
– It is the main interface where all tasks can be
created, edited, and scheduled
Bina Nusantara University
21
Performance
• Configure parameters
– Use the Configuration Advisor
– The bufferpool is the most important parameter to configure
• This is like cache for databases
• Do not over allocate physical memory to the bufferpool
• Design good indexes
– Use the Design Advisor
– Indexes can reduce sorts
Bina Nusantara University
22
Performance
• Choose the right locking strategy
– Depending on your application logic choose the appropriate
isolation level. UR provides the most concurrency.
• Reduce deadlocks by minimizing:
– Repeatable Read isolation
– Lock Escalation
– Lock Conversion
– Bad Access plans (table scans)
– Catalog Modification
– Referential Constraint Enforcement
Bina Nusantara University
23
Performance
• Minimize network traffic
– Use stored procedures
– Limit the fetch size using clauses like:
• Optimize for X rows: Use this clause to determine the
best communications buffer size.
• fetch first x rows only: Limit on the number of rows
retrieved
Eg:
SELECT EMPNAME, SALARY FROM
EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 100 ROWS ONLY
OPTIMIZE FOR 20 ROWS
Bina Nusantara University
24
Performance
• Dynamic vs. Static SQL
– Static SQL performs better than dynamic SQL
– If must use dynamic SQL, ensure to use parameter markers
• Code your SQL appropriately
– Don’t ask for more than you need
• For example, asking for all the columns is not good, like
in: Select * from …
– Analyze bad performing SQL with Visual Explain
Bina Nusantara University
25
Visual ExplainAccess Plan
Execute &
Access Plan
Statement to
analyze
Bina Nusantara University
26
Visual Explain
Bina Nusantara University
27