• Troubleshooting Guide


  •   
  • FileName: db2p0e70.pdf [read-online]
    • Abstract: Cannot import tables from AS/400 . . 128. Columns defined as BINARY WITH. PRECISION are interpreted as INTEGER . 129. Prepare of SQL statement fails . 129. The Data Warehouse Center displays ... v "Chapter 10. Troubleshooting the Data Warehouse Center" on page 117 ...

Download the ebook

® ®
IBM DB2 Universal Database
Troubleshooting Guide
Version 7
GC09-2850-01
® ®
IBM DB2 Universal Database
Troubleshooting Guide
Version 7
GC09-2850-01
Before using this information and the product it supports, be sure to read the general information under
“Appendix D. Notices” on page 295.
This document contains proprietary information of IBM. It is provided under a license agreement and is protected by
copyright law. The information contained in this publication does not include any product warranties, and any
statements provided in this manual should not be interpreted as such.
Order publications through your IBM representative or the IBM branch office serving your locality or by calling
1-800-879-2755 in the United States or 1-800-IBM-4YOU in Canada.
When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any
way it believes appropriate without incurring any obligation to you.
© Copyright International Business Machines Corporation 1993, 2000. All rights reserved.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
Welcome to DB2 . . . . . . . . . . ix DB2 Support for Raw Devices when Using
Who Should Read This Book? . . . . . . ix Windows 2000 . . . . . . . . . . 34
Conventions . . . . . . . . . . . . ix Disk Alignment when Using Windows
How to Read the Syntax Diagrams . . . . x 2000. . . . . . . . . . . . . . 34
Problems Using Commands, Utilities, or
Part 1. Basic DB2 Troubleshooting 1 the Command Line Processor . . . . . 35
Locking and Deadlocks . . . . . . . 36
Improving Database Performance . . . . 38
Chapter 1. Good Troubleshooting Practices 3
Still Have Problems? . . . . . . . . . 40
Maintaining a Problem History . . . . . . 3
Determining Origins and Symptoms of
Chapter 3. Troubleshooting on the Client 43
Problems . . . . . . . . . . . . . 4
Overview of Client Connectivity . . . . . 43
Determining the Location of a Problem . . 4
Client Connectivity Tools . . . . . . 44
Responding to Unexpected Messages or
Troubleshooting Tips . . . . . . . . . 45
SQLCODEs . . . . . . . . . . . 5
Determining the Scope of a Client Problem 46
Responding to Abends . . . . . . . . 6
Initial Connection after Installation Fails 47
Responding to Suspensions and Loops . . 7
Clients Suddenly Experiencing Problems 48
Using All Available Information . . . . . . 9
User Name Not Valid on Windows 95 and
Updating DB2 Products . . . . . . . . 10
Windows 98 . . . . . . . . . . . 49
Updating DB2 Products Using Fix Packs 10
TCP/IP Problems . . . . . . . . . 49
Using Logged Data and Taking Traces . . . 11
IPX/SPX Problems . . . . . . . . . 53
Contacting IBM for Support . . . . . . . 11
NetBIOS Problems . . . . . . . . . 55
Problems When Using Named Pipes . . . 58
Chapter 2. Troubleshooting the DB2
APPC Problems . . . . . . . . . . 59
Universal Database Server . . . . . . 13
Still Have Problems? . . . . . . . . . 59
DB2 Administrative Tools . . . . . . . 14
Graphical Tools . . . . . . . . . . 14
Chapter 4. Troubleshooting Host
Miscellaneous Troubleshooting Tools . . . 15
Communications . . . . . . . . . . 61
The Database System Monitor . . . . . 18
Troubleshooting Host Connections . . . . 62
Troubleshooting Tips . . . . . . . . . 19
Direct Connection . . . . . . . . . 62
Problems Migrating to Version 7 . . . . 19
Gateway Connection . . . . . . . . 64
Problems When Applying Fix Packs . . . 20
Troubleshooting Tips for DB2 Connect . . . 64
Server Failures . . . . . . . . . . 20
Problems Encountered after Initial
Starting DB2 on Windows 95 and
Connection . . . . . . . . . . . 65
Windows 98 When the User Is Not Logged
Changing the Number of Connections . . 65
On . . . . . . . . . . . . . . 22
Authentication Problems . . . . . . . 66
Using a Windows 2000 Terminal Server
Windows Clients Do Not Connect. . . . 67
environment . . . . . . . . . . . 26
Troubleshooting Tips for Host Connections to
SQL1403N Received When Authenticating
DB2 Universal Database . . . . . . . . 67
Usernames or Groups . . . . . . . . 27
Troubleshooting Installation Problems on
Administration Difficulties . . . . . . 27
Windows 2000 . . . . . . . . . . . 68
Backing Up and Restoring Data . . . . 28
Still Have Problems? . . . . . . . . . 68
Data Not Loading . . . . . . . . . 33
Difficulties Importing Data . . . . . . 34
Chapter 5. Troubleshooting Applications 71
Troubleshooting Tips . . . . . . . . . 72
© Copyright IBM Corp. 1993, 2000 iii
Developing Applications . . . . . . . 72 Backup Data Links Manager Environment 109
Compiling and Linking Applications . . . 73 Export Operation . . . . . . . . . 110
Running Applications . . . . . . . . 74 Import Operation . . . . . . . . . 110
Using CLI/ODBC/JDBC Applications . . 75 Load Considerations . . . . . . . . 111
Using SQLJ Applications . . . . . . . 79 DB2 Data Links Manager for Windows
Using the db2ocat Tool . . . . . . . 80 NT . . . . . . . . . . . . . . 112
Stored Procedures . . . . . . . . . 82 Still Have Problems? . . . . . . . . . 112
Still Have Problems? . . . . . . . . . 83
Chapter 9. Troubleshooting Replication 113
Chapter 6. Troubleshooting DB2 Query DB2 DataPropagator . . . . . . . . . 114
Patroller . . . . . . . . . . . . . 85 Using the Log Header Control File with
Still Have Problems? . . . . . . . . . 85 db2flsn . . . . . . . . . . . . 114
Example of Using the db2flsn Command
Chapter 7. Troubleshooting DB2 Universal and the db2diag.log File . . . . . . 115
Database Enterprise - Extended Edition . . 87 DB2 DataPropagator Enhancements . . . 115
Troubleshooting Tips . . . . . . . . . 87 Still Have Problems? . . . . . . . . . 116
DB2 Universal Database Enterprise -
Extended Edition Will Not Install . . . . 88 Chapter 10. Troubleshooting the Data
Determining a Failing Node. . . . . . 88 Warehouse Center . . . . . . . . . 117
Database Manager Will Not Start . . . . 88 Prerequisite or related product issues . . . 117
Cannot Create Database . . . . . . . 91 A DataJoiner Version 2 CREATE SERVER
Commands Cannot Be Issued . . . . . 92 MAPPING statement for an Oracle data
Problems Backing Up On Database source fails . . . . . . . . . . . 117
Partitions . . . . . . . . . . . . 92 Unable to connect to a DB2 database from
Lost Database Partitions on Windows 2000 92 a Windows NT or Windows 2000 agent
Database Partition Size Rounding on site. . . . . . . . . . . . . . 117
Windows 2000 . . . . . . . . . . 93 Cannot connect to an ODBC data source
Problems with LOAD and with Splitting on UNIX . . . . . . . . . . . . 119
and Loading Data . . . . . . . . . 93 Unable to use TCP/IP to connect to
Problems with Indexing Data . . . . . 96 AS/400 from Windows NT or Windows
Problems during Recovery . . . . . . 96 2000 . . . . . . . . . . . . . 119
Developing Applications in DB2 Enterprise Connecting to a Sybase source fails . . . 120
- Extended Edition . . . . . . . . . 98 FTP log displays ″NAMEFMT not a valid
Severe Error Occurs on System. . . . . 99 command″ . . . . . . . . . . . 121
Additional Enterprise - Extended Edition The warehouse server or warehouse agent
Considerations . . . . . . . . . . . 101 is unresponsive when using ODBC
First Failure Data Capture . . . . . . 101 drivers that were purchased separately . 122
DB2 Trace Facility. . . . . . . . . 101 Installation issues . . . . . . . . . . 122
Other Methods. . . . . . . . . . 102 Windows NT or Windows 2000 cannot
General Instructions When in a Bad State 103 install services for Data Warehouse Center
Hierarchy of Shutdowns . . . . . . 103 components . . . . . . . . . . . 122
Still Have Problems? . . . . . . . . . 103 Initialization fails at ″Add install path to
NT service path″ message . . . . . . 123
Chapter 8. Troubleshooting DB2 Data Initialization fails with a ″3043 Disk or
Links Manager . . . . . . . . . . 105 Network Error″ message . . . . . . 123
DB2 Data Links Manager Considerations . . 105 Data Warehouse Center files disappear
DB2 Data Links Manager . . . . . . 106 after you install the product again . . . 123
Data Access Token . . . . . . . . 106 The Data Warehouse Center will not start 124
Recovery Data Links Manager Initialization fails with an ″Unable to
Environment . . . . . . . . . . 107 connect″ message . . . . . . . . . 124
iv DB2 Troubleshooting Guide
Initialization fails with a 9159: Win32 An file access error occurs when using
function RegOpenKeyEx failed message . 124 the Data Warehouse Center ODBC text
Product function issues . . . . . . . . 125 file driver or accessing files using
A Data Warehouse Center component is warehouse programs. . . . . . . . 135
unable to communicate with another Column is no longer visible in the
component . . . . . . . . . . . 125 column grid . . . . . . . . . . 136
The Data Warehouse Center and the Supplied warehouse program issues . . . 137
AS/400 agent are unable to communicate . 126 Step running VWPRCPY fails with RC2 =
The warehouse logger stops while the 12 . . . . . . . . . . . . . . 137
Data Warehouse Center is running . . . 126 On AS/400, the VWPFTP trace says that
A warehouse agent stops processing the job completed successfully, but data
when promoting a step . . . . . . . 127 was not transferred and the FTP message
Creation of an AIX or Solaris Operating log is missing . . . . . . . . . . 137
Environment warehouse agent process One of the OLAP Server warehouse
fails . . . . . . . . . . . . . 127 programs cannot find the rules file or
The Data Warehouse Center stops load file . . . . . . . . . . . . 137
processing if you run several steps at the User-defined programs are not found, or
same time . . . . . . . . . . . 128 they run with errors . . . . . . . . 138
Cannot import tables from AS/400 . . . 128 Still Have Problems? . . . . . . . . . 139
Columns defined as BINARY WITH
PRECISION are interpreted as INTEGER . 129 Chapter 11. Troubleshooting DB2 Satellite
Prepare of SQL statement fails . . . . 129 Edition . . . . . . . . . . . . . 141
The Data Warehouse Center displays Still Have Problems? . . . . . . . . . 142
return code 7356 . . . . . . . . . 129
Promoting a step that uses DB2 for MVS Chapter 12. Troubleshooting NetQuestion 143
Version 4 Release 1 as the source and an Still Have Problems? . . . . . . . . . 143
AS/400 agent hangs . . . . . . . . 131
Promoting a step that uses DB2 for
OS/390 as the warehouse target and the
Part 2. Advanced DB2
AS/400 agent fails . . . . . . . . 132 Troubleshooting . . . . . . . . 145
The Data Warehouse Center displays the
″Could not retrieve the step RC- 3142″ Chapter 13. Logs and Traces Roadmap 147
message when creating a step with a long
name . . . . . . . . . . . . . 132 Chapter 14. The DB2 Process Model . . 151
The warehouse server cannot create a Description of the DB2 Process Model . . . 151
warehouse agent or it did not receive a Differences between Intel and UNIX . . 154
valid reply back from the warehouse Viewing System Threads and Processes . . 155
agent . . . . . . . . . . . . . 133
The Start/End Date/Time is incorrect in Chapter 15. Logged Information . . . . 157
the Work In Progress window. . . . . 134 First Failure Data Capture . . . . . . . 157
DB2 reports an SQL0973N error when Setting Configuration Parameters
you open and close steps . . . . . . 134 Affecting FFDC . . . . . . . . . 158
A step that uses Microsoft Access as a Interpreting the db2diag.log . . . . . . 160
source fails . . . . . . . . . . . 134 Interpreting db2diag.log Entry Headers 160
The Data Warehouse Center supplies Interpreting an SQLCA Structure. . . . 162
incorrect table names or an ODBC OLE Interpreting Hexadecimal Codes . . . . 163
error occurs when using the Microsoft Some Examples for Interpreting the
Excel 95/97 ODBC driver to access Excel db2diag.log . . . . . . . . . . . 163
spreadsheets . . . . . . . . . . 135 Dump Files . . . . . . . . . . . . 169
Trap Files . . . . . . . . . . . . 170
Contents v
Gathering Stack Traceback Information on Chapter 19. Diagnostic Tools for Windows
UNIX-Based Systems. . . . . . . . 170 32-bit Operating Systems . . . . . . 229
Using the Administrative Tools Log . . . . 171 Diagnostic Tools for Windows 95 and
Administrative Tools Log File Sample . . 171 Windows 98 . . . . . . . . . . . 229
Using Message Files . . . . . . . . . 172 Diagnostic Tools for Windows NT . . . . 229
Alerts . . . . . . . . . . . . . . 173 Using Administrative Tools Provided by
SNMP Alerts . . . . . . . . . . 173 Windows NT . . . . . . . . . . 230
Taking Screen Captures . . . . . . . . 231
Chapter 16. Traces . . . . . . . . . 177 Using DB2-Supplied Tools . . . . . . . 231
Using the DB2 Trace Facility (db2trc) . . . 177
Starting a DB2 Trace . . . . . . . . 179 Chapter 20. GUI and Monitoring Tools 233
Verifying a DB2 Trace . . . . . . . 180 Database System Monitor . . . . . . . 233
Examples of Using db2trc . . . . . . 180 Using the Snapshot Monitor . . . . . 233
Taking Other Traces . . . . . . . . . 183 Using the Event Monitor . . . . . . 234
Administration Tools Trace. . . . . . 183 Control Center . . . . . . . . . . . 235
Taking a DB2 Connect Trace (ddcstrc) . . 184 Using Search Utility for DB2 Online
Taking a DRDA Trace (db2drdat) . . . 185 Information . . . . . . . . . . . 236
Taking an SNA Trace. . . . . . . . 186 Troubleshooting Tips. . . . . . . . 237
Using the DB2 CLI/ODBC/JDBC Trace Troubleshooting Page . . . . . . . . 238
Facility . . . . . . . . . . . . 186 Stored Procedure Builder . . . . . . . 238
DB2 Index Advisor . . . . . . . . . 239
Chapter 17. Diagnostic Tools for
UNIX-Based Systems . . . . . . . . 207 Chapter 21. Table and Table Space
Using the System Error Log (syslog) . . . 207 Recovery . . . . . . . . . . . . 241
Setting Up the System Error Log . . . . 208 Recovery Concepts . . . . . . . . . 241
Understanding the System Error Log . . 209 Recoverable and Non-Recoverable
Using the Core File . . . . . . . . . 210 Databases . . . . . . . . . . . 241
Accessing Core File Information . . . . 210 Database Logs . . . . . . . . . . 242
Viewing Process Status Using the ps Point of Recovery . . . . . . . . . 243
Command . . . . . . . . . . . . 212 Dropped Table Point-in-Time Recovery . . 243
Other Commands . . . . . . . . . . 213 Table Space OFFLINE . . . . . . . . 245
AIX Troubleshooting Commands. . . . 213 Archive Logging . . . . . . . . . 245
UNIX-Based Troubleshooting Commands 213 Table Space Usability . . . . . . . . 246
Performance Monitoring Tools . . . . 214 RESTART ... DROP PENDING
Commands for DB2 Enterprise - Extended TABLESPACE . . . . . . . . . . 246
Edition . . . . . . . . . . . . 215 RESTART ... DROP PENDING Table
Space State . . . . . . . . . . . 247
Chapter 18. Diagnostic Tools for OS/2 217 Table Space Usability . . . . . . . 247
Using the OS/2 Logging Facility . . . . . 218 RESTART ... DROP PENDING . . . . 248
Preliminary Considerations . . . . . 218 Database Directories . . . . . . . . . 248
Using OS/2 Logging Facility Commands 218 Database Files . . . . . . . . . . 249
Using the FFST for OS/2 Utility . . . . . 219
Adding the FFST for OS/2 Program Part 3. Appendixes . . . . . . . 251
Group to the Desktop Manager Window . 220
Configuring and Enabling the FFST for
Appendix A. DB2 Internal Return Codes 253
OS/2 Utility . . . . . . . . . . 220
Accessing FFST for OS/2 Error Records 223
Appendix B. SQL Communications
Routing Generic Alerts . . . . . . . 224
(SQLCA) . . . . . . . . . . . . 271
Using the pstat Command . . . . . . . 225
Viewing the SQLCA Interactively . . . . 271
vi DB2 Troubleshooting Guide
SQLCA Field Descriptions . . . . . . . 271 Using DB2 Wizards . . . . . . . . 292
Order of Error Reporting . . . . . . . 275 Setting Up a Document Server . . . . 293
DB2 Enterprise - Extended Edition Usage of Searching Information Online . . . . . 294
the SQLCA . . . . . . . . . . . . 276
Appendix D. Notices . . . . . . . . 295
Appendix C. Using the DB2 Library . . . 277 Trademarks . . . . . . . . . . . . 298
DB2 PDF Files and Printed Books . . . . 277
DB2 Information . . . . . . . . . 277 Index . . . . . . . . . . . . . 301
Printing the PDF Books . . . . . . . 286
Ordering the Printed Books . . . . . 287 Contacting IBM . . . . . . . . . . 309
DB2 Online Documentation . . . . . . 288 Product Information . . . . . . . . . 309
Accessing Online Help . . . . . . . 288
Viewing Information Online . . . . . 290
Contents vii
viii DB2 Troubleshooting Guide
Welcome to DB2
Welcome to the world of DB2 troubleshooting.
This guide provides information to get you started solving problems with DB2
servers and clients. It helps you to:
v Identify problems or errors in a concise manner
v Solve problems based on their symptoms
v Learn about available diagnostic tools
v Develop a troubleshooting strategy for day-to-day operations
Who Should Read This Book?
This guide is intended for customers, users, database administrators (DBAs),
application developers, and technical support representatives for DB2 clients
and servers. To use it, you should be familiar with:
v Communications, relational database, and local area network (LAN)
concepts
v Hardware and software requirements and options
v The overall configuration of your network
v Application programs and other facilities that run on your network
v Basic DB2 administrative tasks (see the Administration Guide: Implementation)
v The information on installation and early tasks described in the Quick
Beginnings guides for the products you installed
For more information on the documentation available in the DB2 library, see
“Appendix C. Using the DB2 Library” on page 277.
Conventions
You will find this book easier to use if you look for these conventions:
v Information is provided for all platforms currently supported by DB2.
Unless information is identified as being particular to one or more
platforms, it is applicable to all.
v The panels, screens, and menus shown are only examples and may not
exactly match what you see on your workstation.
v The format for date and time, and the characters used as separators for date
and time, are intended for a system that is configured to use the United
States national language format. These may be different on your display,
depending on the national language format used by your system.
v This book uses the following highlighting conventions:
© Copyright IBM Corp. 1993, 2000 ix
– Boldface type indicates the name of an item on your screen; for example,
a field, folder, icon, or menu. It is also used to indicate a command
name, a utility name, and to emphasize important words or phrases.
– Monospace type indicates an example or a command as you should type
it.
– Italic type is used for book titles and configuration variables. When used
in command strings, it indicates a variable value that you should replace
with your value.
How to Read the Syntax Diagrams
Throughout this book, syntax is described using syntax diagrams.
Read the syntax diagrams from left to right and top to bottom, following the
path of the line.
The ─── symbol indicates the beginning of a statement.
The ─── symbol indicates that the statement syntax is continued on the next
line.
The ─── symbol indicates that a statement is continued from the previous
line.
The ── symbol indicates the end of a statement.
Required items appear on the horizontal line (the main path).
STATEMENT required item
Optional items that are part of the syntax appear below the main path.
STATEMENT
optional item
If an optional item appears above the main path, that item has no effect on
the execution of the statement and is used only for readability.
optional item
STATEMENT
If you can choose from two or more items, they appear in a stack.
x DB2 Troubleshooting Guide
If you must choose one of the items, one item of the stack appears on the
main path.
STATEMENT required choice1
required choice2
If the statement is complete without any of the items, the entire stack appears
below the main path.
STATEMENT
optional choice1
optional choice2
If one of the items is the default, it appears above the main path and the
remaining choices are shown below.
default choice
STATEMENT
optional choice
optional choice
An arrow returning to the left, above the main line, indicates an item that can
be repeated. In this case, repeated items must be separated by one or more
blanks.
STATEMENT repeatable item
If the repeat arrow contains a comma, you must separate repeated items with
a comma.
,
STATEMENT repeatable item
A repeat arrow above a stack indicates that you can make more than one
choice from the stacked items or repeat a single choice.
Welcome to DB2 xi
Keywords appear in uppercase (for example, FROM). They must be spelled
exactly as shown. Variables appear in lowercase (for example, column-name).
They represent user-supplied names or values in the syntax.
If punctuation marks, parentheses, arithmetic operators, or other such symbols
are shown, you must enter them as part of the syntax.
Placeholder variables, such as path, appear in italics. Replace them with the
appropriate value when you key the syntax.
Sometimes a single variable represents a set of several parameters. For
example, in the following diagram, the variable parameter-block can be
replaced by any of the interpretations of the diagram that is headed
parameter-block:
STATEMENT parameter-block
parameter-block:
parameter1
parameter2 parameter3
parameter4
Adjacent segments occurring between “large bullets” (*) may be specified in
any sequence.
STATEMENT item1 * item2 * item3 * item4
The above diagram shows that item2 and item3 may be specified in either
order. Both of the following are valid:
STATEMENT item1 item2 item3 item4
STATEMENT item1 item3 item2 item4
xii DB2 Troubleshooting Guide
Part 1. Basic DB2 Troubleshooting
This part addresses the following topics:
v “Chapter 1. Good Troubleshooting Practices” on page 3
v “Chapter 2. Troubleshooting the DB2 Universal Database Server” on page 13
v “Chapter 3. Troubleshooting on the Client” on page 43
v “Chapter 4. Troubleshooting Host Communications” on page 61
v “Chapter 5. Troubleshooting Applications” on page 71
v “Chapter 6. Troubleshooting DB2 Query Patroller” on page 85
v “Chapter 7. Troubleshooting DB2 Universal Database Enterprise - Extended
Edition” on page 87
v “Chapter 8. Troubleshooting DB2 Data Links Manager” on page 105
v “Chapter 9. Troubleshooting Replication” on page 113
v “Chapter 10. Troubleshooting the Data Warehouse Center” on page 117
v “Chapter 11. Troubleshooting DB2 Satellite Edition” on page 141
v “Chapter 12. Troubleshooting NetQuestion” on page 143.
© Copyright IBM Corp. 1993, 2000 1
2 DB2 Troubleshooting Guide
Chapter 1. Good Troubleshooting Practices
This chapter emphasizes the following troubleshooting practices:
v “Maintaining a Problem History”
v “Determining Origins and Symptoms of Problems” on page 4
v “Using All Available Information” on page 9
v “Updating DB2 Products” on page 10
v “Using Logged Data and Taking Traces” on page 11
v “Contacting IBM for Support” on page 11
Maintaining a Problem History
Maintain a history of reported problems and the actions you took to identify
or resolve them. This information may help you isolate later problems and
anticipate or avoid others. In particular, you should record the following
information related to a problem:
v The problem description, including:
– The problem symptoms.
– A list of the DB2 utilities that were running, including RUNSTATS,
REORG, LOAD, IMPORT, and others.
– A complete description of any messages that were received, including
SQLCODEs, associated reason codes, or system error codes.
– Any SQLSTATE received. SQLSTATEs are useful for diagnosing
problems, because they are consistent across all platforms. For a list of
SQLSTATEs, see the Message Reference.
v What was happening at the time. By recording information such as the
following, you can spot patterns should a problem occur again:
– The actions that led up to the problem
– Any applications that were running at the time
– Any SQL statements that were running at the time, including
information on whether they were dynamic or static, and Data Definition
Language (DDL) or Data Manipulation Language (DML)
v The type of hardware and software running on your system, and their
configuration
v The service level installed on each system or partition server (on Enterprise
- Extended Edition systems), and any fix packs applied
v The results of any corrective actions
v The problem resolution
© Copyright IBM Corp. 1993, 2000 3
v The number assigned to the problem, if you contacted DB2 Customer
Support.
Determining Origins and Symptoms of Problems
If you are supporting an organization using DB2, you will receive calls from
users to resolve a variety of problems. Your response depends on:
v The severity of the problem
v The specific nature of the problem
v Any related information that you can gather
v Your experience in resolving similar problems
To solve a problem, start by obtaining a comprehensive description of the
problem. This way, you can begin to determine its origin. For example, a
problem may exist in any of the following:
v Hardware
v Operating system
v Networking system or other subsystem
v DB2 server
v DB2 client
v DB2 Connect gateway to host systems
Determining the Location of a Problem
Most applications run in a client/server environment. You must determine if a
problem is on the client, the server, or somewhere in between (that is, in the
LAN or communication protocol stack).
Investigating where the problem is detected or reported is the best way to
start. For example, if you receive an unexpected SQLCODE on a client, then
investigate the SQLCODE on that client. (See “Responding to Unexpected
Messages or SQLCODEs” on page 5 for information.)
Often the SQLCODE alone provides enough information to determine the
source and cause of the problem. If the SQLCODE does not give enough
information to determine the source of a problem, examine the db2diag.log
file at the partition server where the problem was reported. For example, if
the problem was reported on a client, first look at the db2diag.log file on that
particular client.
The db2diag.log file is an ASCII file written by DB2 that contains diagnostic
information for DB2. The db2diag.log file reports exceptions encountered in
DB2 code. If you know the date and time when the problem occurred, you
can go directly to the corresponding db2diag.log file entries.
4 DB2 Troubleshooting Guide
Note: The error messages relating to a user application problem most often
do not cause exceptions in DB2. This type of problem is handled as a
normal part of DB2 processing. As a result, they are not reported in the
db2diag.log file.
For information on this important file, see “First Failure Data Capture” on
page 157. When viewing the file, keep in mind that the most recent conditions
are always at the end.
Responding to Unexpected Messages or SQLCODEs
When you receive an unexpected message or SQLCODE, follow these steps
until you can determine the problem:
1. When you receive a message, take note of all available information,
including the following:
v The code, an eight-digit alphanumeric message identification number.
This code may begin with the prefix SQL, DBA or CCA. Also note all
reason codes, return codes, and other information associated with the
message returned.
v Any SQLSTATE received. SQLSTATEs are useful for diagnosing
problems, because they are consistent across all platforms. For a list of
SQLSTATEs, see the Message Reference.
v The text of the message (especially if the message does not include an
identification number or a code).
v The SQLCA if available.
v Any action suggested in the message.
v Diagnostic files, such as the db2diag.log file. In addition, note any
operating system diagnostic files such as traceback files, core files (for
UNIX-based systems), event logs (for Windows NT), syslog files (for
OS/2), and any dumps. See “Part 2. Advanced DB2 Troubleshooting” on
page 145.
v The environment in which the message occurred. For example, what the
user was doing at the time, the steps that led up to the problem, the
type of operating system, applications that were running, and the
communication protocol. Also note if any utilities such as RUNSTATS,
REORG, LOAD, IMPORT, or others were being used.
v The SQL statement that encountered the error, and any preceding
statements in the unit of work.
2. Check the online message help by typing db2 "? message" from the
command prompt, where message is the complete SQLCODE, SQLSTATE,
or message number. Read and follow the suggested actions.
3. Use the SQLCODE or message number to search available DB2
documentation for additional information.
Chapter 1. Good Troubleshooting Practices 5
4. If the problem persists, ensure that you have as much of the following
information as possible before contacting DB2 Customer Support:
v The DB2 diagnostic log (db2diag.log file), and any trap or dump files it
mentions. See “First Failure Data Capture” on page 157.
v The SQLCA structure from the db2diag.log file or syslog files, or as
captured by an application. See “Interpreting an SQLCA Structure” on
page 162.
5. If you determine


Use: 0.8597